MySQL索引优化

索引优化

EXPLAIN查询分析

参考另一篇，通过explain我们可以确定查询语句的访问类型，用到的索引，扫描行数以及extra信息。

回表查询与覆盖索引

InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。铺助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过铺助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

例如将eplain extra属性值由using where（使用回表查询）优化成using index（触发索引覆盖），避免回表查询。

覆盖索引：只需要在一棵索引树上就能获取sql所需的所有列数据，无需回表查询，速度更快。常见方法是将被查询的字段建立组合索引。

因此，要尽可能的是sql查询处于using index索引覆盖状态。

最左前缀原则（针对于复合索引）

复合索引使用时遵循最左前缀原则，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

上图左侧复合索引起作用，右侧不起作用。

LIKE查询

面试题：MySQL在使用like模糊查询时，索引能不能起作用？

like模糊查询时，索引是可以被使用的，只有将%字符写到后面才能使用到索引。

NULL查询

对于MySQL，NULL是一个特殊值，处理方式与其他值不同，不能使用=<>等运算符，对NULL做算术运算的结果还是NULL，count该字段时不会包括NULL等，NULL比空字符需要更多的存储空间。

NULL需要增加额外的列来标记数据是否为NULL。

面试题：如果MySQL表的某一列合有NULL值，那么包合该列的索引是否有效？

MySQL可以在含有NULL的列上使用索引（复合索引也可以），但是NULL和其他数据有区别，不建议列上使用NULL，建议给默认值0，空串。

索引与排序

MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

filesort有两种排序算法：双路排序和单路排序。

• 双路排序（老版本）：需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据。

• 单路排序（新版本）：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。解决方案：少使用select*;增加sort_buffer_size容量和max_length_for_sort_data容量。

判断使用了哪种排序方式：如果我们Explain分析SQL，结果中Extral属性显示Using filesort，表示使用了filesort排序方式，需要优化。如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用index排序方式，建议大家尽肯能采用覆盖索引。

where age = 18 order by name;
覆盖索引需要设置(age, name)索引

以下几种情况，会使用filesort方式的排序。

对索引列同时使用了ASC和DESC

exp lain select id from user order by age asc，name desc;//对应（age，name）索引

WHERE子句和ORDER BY子句满足最左前缀，但where-子句使用了范围查询（例如>、<、in等）

exp1ain select id from user where age>l0 order by name;//对应（age，name）索引

ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列

explain select id from user order by name;//对应（age，name）索引

使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引

explain select id from user order by name，age;/对应（name）、（age）两个索引

WHERE子句与ORDER BY子句，使用了不同的索引

explain select id from user where name=‘tom‘order by age;/对应（name）、（age）索引

WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式

explain select id from user order by abs（age）;/对应（age）索引

慢查询日志

slow_query_log，默认不开启，可以记录超时sql、没有使用到索引的sql等。log_query_time

索引与慢查询关系

如何判断是否使用了索引？

通过explain 命令分析查看，结果中的key值，是否为NULL。

应用了索引是否一定快？

explain select * from user where id > 0;

虽然使用了主键索引，但是扫描了整张表的聚簇索引树，因此索引失去意义。

查询是否使用索引，只是表示一个SQL语句的执行过程：而是否为慢查询，是由它执行的时间决定的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。

使用索引时，不只要看索引是否起作用，还需要关心索引是否减少了扫描记录行数（过滤性）。

索引过滤性与索引字段、表数据量、表设计结构都有关系。

慢查询大概有下面几个原因：

1. 全表扫描：all，扫描全表
2. 全索引扫描：index，扫描整棵索引树
3. 索引过滤性不好：字段设置不够好（例如age=18）、过滤后数据量大等
4. 频繁的回表查询：尽量少使用select *，尽量使用覆盖索引。

分页查询优化

如何查看分页查询耗时

启动profile功能

show variables like '%profile%';
set profiling=1;
show profile; // 查看耗时

样例表中40000多数据。

select * from user limit 10000,10; //0.005
select * from user limit 10000,100; //0.005 
select * from user limit 10000,10000; //0.018 耗时大约3倍

查询位移相同：低于100条，时间变化不大。随着查询数量增大，时间增多。

select * from user limit 10,100;//0.0004
select * from user limit 100,100;//0.0005
select * from user limit 10000,100;//0.006 耗时大约10倍

查询量相同：偏移量超过100，查询时间增大。分页查询机制，每次都是从数据库第一条记录开始扫描，越往后查询越慢。

分页优化方案

1. 利用覆盖索引优化

select id from user limit 10000,100; // 耗时0.002

2. 使用子查询做优化。

   如果业务确实需要多个字段，必须要用select *，则可以使用子查询优化。原理是先使用子查询定位到第10000条。

select * from user 
where id >= (select id from user limit 10000, 1) limit 100; // 耗时0.002

优化操作：id是主键，并且子查询使用覆盖索引。