CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) 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 ;
mysql会给每个线程分配一块内存用于排序,成为sort_buffer
全字段排序
缺点:
1.造成sort_buffer中存放不下很多数据,因为除了排序字段还存放其他字段,对sort_buffer的利用效率不高
2.当所需排序数据量很大时,会有很多的临时文件,排序性能也会很差
优点:MySQL认为内存足够大时会优先选择全字段排序,因为这种方式比rowid 排序避免了一次回表操作
优点:更好的利用内存的sort_buffer进行排序操作,尽量减少对磁盘的访问
缺点:回表的操作是随机IO,会造成大量的随机读,不一定就比全字段排序减少对磁盘的访问
覆盖索引,索引上的信息足够满足查询请求,不需要再回到主键索引上取数据。
问题:
1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。
因为优化器认为走二级索引再去回表成本比全表扫描排序更高。
所以选择走全表扫描,然后根据两种方式选择一种来排序
2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的.
因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。
即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。