MySQL 性能调优

mysql - 这篇文章属于一个选集。

§ 8: 本文

怎么查看一条语句是否走了索引？
#

考察 explain 执行计划输出的信息。

explain select * from t_user where id + 1 = 10;

执行计划，参数有：

possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

All（全表扫描）：在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。
index（全索引扫描）：index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。
range（索引范围扫描）：range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
ref（非唯一索引扫描）：ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
eq_ref（唯一索引扫描）：eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
const（结果只有一条的主键或唯一索引扫描）：const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

extra 显示的结果，这里说几个重要的参考指标：

Using filesort ：当查询语句中包含 order by 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

回答
#

可以通过 explain 查看 SQL 的执行计划，关注 type 字段，这个字段表明 SQL 扫描的方式，如果 type 字段不是 all 或者 index 就代表是索引扫描的方式，这种情况就代表 SQL 走了索引，并且我们还可以通过 key 字段，看这条查询用了哪个索引字段来走索引，如果 key 为 null，也代表没有走索引。

extra 字段中的 using index 和 using where 的区别？
#

考察 explain 执行计划输出的信息。

Using index：这意味着MySQL能够使用覆盖索引（Covering Index）来避免访问表的行。覆盖索引是指一个查询的所有列都包含在索引中，因此查询可以仅通过查看索引来获取所需的信息，无需再去访问表的行。这通常能提高查询的性能。
Using where：这意味着MySQL服务器将在存储引擎检索行后再进行过滤。换句话说，存储引擎返回的行并不一定满足WHERE子句的条件，MySQL服务器需要对这些行进行额外的检查。

这两者并不互斥，可以同时出现在"Extra"字段中，这取决于查询的具体情况。

回答
#

using index 表示查询使用了索引覆盖，不会回表，这个可以提高查询效率。

using where 表示MySQL的存储引擎返回给 server 层的数据并不一定满足 where 子句的条件，所以MySQL从存储引擎拿到的数据，还得在 server 层进行了 where 子句的条件判断，来过滤出最终 sql 所需要查询的数据。

怎么找到慢 SQL?
#

考察慢查询日志的应用

回答
#

可以开启慢查询日志，MySQL 就会自动将执行比较慢的 SQL 语句记录在慢查询日志中，具体多慢我们可以自己设置的，比如设置 3 秒，那么 MySQL 就会将执行超过 3 秒的 SQL 语句记录在慢查询日志中。

如何优化慢 SQL?
#

常见 SQL 优化的方法。

优化数据访问：limit 子句缩减数据行数、避免 select *
拆分查询：分而治之的思想，将一个大查询拆分多个小查询，每个小查询只返回一部分查询结果。
覆盖索引：当索引中的列包含所有查询中需要使用的列的时候，可以避免回表
避免索引失效：检查 SQL 是否因为写的不合理，导致索引失效。
分解联表查询：让业务层分多个查询来聚合，或者增加冗余字段减少联表查询
排序优化：对于有排序场景，如果 extra 显示 filesort，这时候就需要考虑对排序的字段建立索引，避免文件排序

回答
#

优化数据访问：要先确认这条查询语句是否查询了不必要的数据行，可以通过 limit 子句来缩减查询返回的数据行数，如果查询语句用了 select *，需要改进 SQL 语句，只返回需要查询的列。
切分查询：针对一个大查询可以拆分多个小查询，每个小查询只返回一部分查询数据。比如删除一千万行数据，可以改进成分批删除，每一次只删除一批数据，然后睡眠一下，再删除下一批，这样可以将一次性的压力分散到一个很长的时间段中，不仅可以降低对服务器的性能影响，还可以大大减少删除时锁的持续时间。
覆盖索引：如果没有索引字段的话，就需要考虑建立索引，或者建立联合索引，通过覆盖索引的查询，这样就避免回表查询，可以提高查询性能。
避免索引失效：检查 SQL 语句有没有问题，比如对索引进行了计算和函数操作、联合索引没有遵循最左匹配原则等，这些场景都会导致索引失效，这时候就需要修改 SQL 避免索引失效的发生。
分解联表查询：针对联表查询的 SQL 语句，可以将联表查询分解成多个单表查询的语句，然后在业务层来聚合数据，或者增加冗余字段减少联表查询。
排序优化：针对 order by 排序操作，如果执行计划的 extra 显示了文件排序，这时候我们可以对排序字段和其他字段建立联合索引，因为索引数据是天然有序的，这样对索引字段进行排序操作的时候，就不需要文件排序了，提高了查询性能。