sql – InnoDB排序真的很慢吗？

副标题#e#

我在myISAM中拥有所有表,但是当我长时间运行更新作业时,表级锁定开始杀了我.我将我的主要表格转换为InnoDB,现在我的许多查询都花费了超过1分钟来完成它们在myISAM上几乎是瞬间完成的.它们通常停留在排序结果步骤中.我做错什么了吗？

例如 ：

SELECT * FROM `metaward_achiever` 
 INNER JOIN `metaward_alias` ON (`metaward_achiever`.`alias_id` = `metaward_alias`.`id`) 
 WHERE `metaward_achiever`.`award_id` = 1507  
 ORDER BY `metaward_achiever`.`modified` DESC 
 LIMIT 100

现在大约需要90秒.这是描述：

+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
| id | select_type | table             | type   | possible_keys                                         | key                        | key_len | ref                             | rows  | Extra                       |
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | metaward_achiever | ref    | metaward_achiever_award_id,metaward_achiever_alias_id | metaward_achiever_award_id | 4       | const                           | 66424 | Using where; Using filesort | 
|  1 | SIMPLE      | metaward_alias    | eq_ref | PRIMARY                                               | PRIMARY                    | 4       | paul.metaward_achiever.alias_id |     1 |                             | 
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+

现在看来我的查询中的TONS卡在“排序结果”步骤中：

mysql> show processlist;
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id     | User | Host      | db   | Command | Time | State          | Info                                                                                                 |
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 460568 | paul | localhost | paul | Query   |    0 | NULL           | show processlist                                                                                     | 
| 460638 | paul | localhost | paul | Query   |    0 | Sorting result | SELECT `metaward_achiever`.`id`,`metaward_achiever`.`modified`,`metaward_achiever`.`created`,`met | 
| 460710 | paul | localhost | paul | Query   |   79 | Sending data   | SELECT `metaward_achiever`.`id`,`met | 
| 460722 | paul | localhost | paul | Query   |   49 | Updating       | UPDATE `metaward_alias` SET `modified` = '2009-09-15 12:43:50',`created` = '2009-08-24 11:55:24',` | 
| 460732 | paul | localhost | paul | Query   |   25 | Sorting result | SELECT `metaward_achiever`.`id`,`met | 
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

为什么这个简单的更新卡住了49秒？

如果有帮助,这里是模式：

| metaward_alias | CREATE TABLE `metaward_alias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,`modified` datetime NOT NULL,`created` datetime NOT NULL,`string_id` varchar(255) DEFAULT NULL,`shortname` varchar(100) NOT NULL,`remote_image` varchar(500) DEFAULT NULL,`image` varchar(100) NOT NULL,`user_id` int(11) DEFAULT NULL,`type_id` int(11) NOT NULL,`md5` varchar(32) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `string_id` (`string_id`),KEY `metaward_alias_user_id` (`user_id`),KEY `metaward_alias_type_id` (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=858381 DEFAULT CHARSET=utf8 | 

| metaward_award | CREATE TABLE `metaward_award` (
  `id` int(11) NOT NULL AUTO_INCREMENT,`string_id` varchar(20) NOT NULL,`owner_id` int(11) NOT NULL,`name` varchar(100) NOT NULL,`description` longtext NOT NULL,`owner_points` int(11) NOT NULL,`url` varchar(500) NOT NULL,`parent_award_id` int(11) DEFAULT NULL,`slug` varchar(110) NOT NULL,`true_points` double DEFAULT NULL,KEY `metaward_award_owner_id` (`owner_id`),KEY `metaward_award_parent_award_id` (`parent_award_id`),KEY `metaward_award_slug` (`slug`),KEY `metaward_award_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=122176 DEFAULT CHARSET=utf8 | 

| metaward_achiever | CREATE TABLE `metaward_achiever` (
  `id` int(11) NOT NULL AUTO_INCREMENT,`award_id` int(11) NOT NULL,`alias_id` int(11) NOT NULL,`count` int(11) NOT NULL,KEY `metaward_achiever_award_id` (`award_id`),KEY `metaward_achiever_alias_id` (`alias_id`)
) ENGINE=InnoDB AUTO_INCREMENT=77175366 DEFAULT CHARSET=utf8 |

这些在我的my.cnf中

innodb_file_per_table
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 16M
innodb_flush_method=O_DIRECT

解决方法

这是MySQL必须手动排序的大型结果集(66,424行).尝试向metaward_achiever.modified添加索引.

#p#副标题#e##p#分页标题#e#

MySQL 4.x存在一个限制,它只允许MySQL为每个表使用一个索引.由于它使用了metaward_achiever.award_id列上的索引来进行WHERE选择,因此它不能使用metaward_achiever.modified上的索引进行排序.我希望你使用的是MySQL 5.x,这可能会改进它.

您可以通过对此简化查询进行说明来查看此内容：

SELECT * FROM `metaward_achiever` 
 WHERE `metaward_achiever`.`award_id` = 1507  
 ORDER BY `metaward_achiever`.`modified` DESC 
 LIMIT 100

如果你可以使用WHERE选择和排序的索引来获得这个,那么你就可以了.

您还可以使用metaward_achiever.award_id和metaward_achiever创建复合索引.如果MySQL不使用它,那么您可以提示它或删除仅在award_id上的那个.

或者,如果您可以摆脱metaward_achiever.id并将metaward_achiever.award_id作为主键并在metaward_achiever.modified上添加一个键,或者更好的是将metaward_achiever.award_id与metaward.modified结合使用您的主键,那么您将真的好.

您可以尝试通过修改设置来优化文件排序.不幸的是,我没有这方面的经验,因为我们的DBA处理配置,但你可能想看看这个伟大的博客：
http://www.mysqlperformanceblog.com/

这是一篇关于filesort的文章：
http://s.petrunia.net/blog/?p=24