CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `sex` varchar(255) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain的输出结果Extra字段为Using index时,能够触发索引覆盖。
如何实现索引覆盖
常见的方法是:将被查询的字段,建立到联合索引里去。
(1)先通过普通索引定位到主键值;
(2)再通过聚集索引定位到行记录;
这就是所谓的 回表查询 ,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。
不管是SQL-Server官网,还是MySQL官网,都表达了:只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。
添加索引后
select id,name,sex … where name=‘张三’;
将单列索引(name)升级为联合索引(name, sex),即可避免回表。
select id,name,sex … order by name limit 500,100;
将单列索引(name)升级为联合索引(name, sex),也可以避免回表。
当排序的数据很大,超过 sort_buffer 的大小,那么就需要文件排序,文件排序涉及到分批排序与合并,很耗时,造成这个问题的根本原因是 sort_buffer 不够用
mysql> show variables like 'max_length_for_sort_data'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | max_length_for_sort_data | 1024 | +--------------------------+-------+
联合索引虽然可以解决这种问题,但是在实际应用中切不可盲目建立,要根据实际的业务逻辑来判断是否需要建立,如果不是经常有类似的查询,可以不用建立,因为联合索引会占用更多的存储空间和维护开销。