sql – InnoDB排序真的很慢吗？

我在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

解决方法

（编辑：上饶站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!