rental表的存储引擎是innodb, rental_myisam表的存储引擎是myisam。两张表除存储引擎不同外,表结构都一样。
CREATE TABLE `rental` ( `rental_id` int(11) NOT NULL AUTO_INCREMENT, `rental_date` datetime NOT NULL, `inventory_id` mediumint(8) unsigned NOT NULL, `customer_id` smallint(5) unsigned NOT NULL, `return_date` datetime DEFAULT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`rental_id`), KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`) ) ENGINE=innodb DEFAULT CHARSET=utf8; CREATE TABLE `rental_myisam` ( `rental_id` int(11) NOT NULL AUTO_INCREMENT, `rental_date` datetime NOT NULL, `inventory_id` mediumint(8) unsigned NOT NULL, `customer_id` smallint(5) unsigned NOT NULL, `return_date` datetime DEFAULT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`rental_id`), KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
使用explain分析,发现 rental表 使用了主键索引排序,而 rental_myisam表 却并没有用到主键索引排序,反而extra里用到"文件排序",不太懂为啥会是这样的区别?
explain select * from rental where return_date > '2005-08-30' order by rental_idG
explain select * from rental_myisam where rental_date > '2005-01-01' order by rental_id\G