全字段排序 VS rowid 排序

CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `city` int(11) NOT NULL,
 `name` varchar(16) NOT NULL,
 `age` int(11) NOT NULL,
 `addr` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from t where city='杭州' order by name limit 1000 ;

全字段排序

rowid排序

rowid 排序相对于全字段排序，不会把所有字段都放入sort_buffer。所以在sort buffer中进行排序之后还得回表查询。

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer中， 这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

优化

现在我们加一个索引。

alter table t add index city_user(city, name);

那么上面的查询语句会大大优化。

因为查出city=杭州的name的属性已经是排好序的，无需在排序。

再优化

来，我们再加上这样的一个索引。

alter table t add index city_user_age(city, name, age);

你明白了怎么回事了么？？？  覆盖索引知道吗？ 对的，如果在索引树上查到了自己所需的所有信息就不需要回主键的索引树（回表）再查询一边。这样的话，上面的查询讲大大省事。

当然，我们为个查询都能用上覆盖索引也不是百利而无一害，维护索引也是有一定的代价的。