转载~
数据库优化是一个很常见的面试题，下面就针对这一问题详细聊聊如何进行索引与sql的分析与优化。
一、执行计划（EXPLAIN）
MySQL 提供了一个 EXPLAIN 命令，它 「可以对 sql语句进行分析，并输出sql执行的详细信息」，可以让我们有针对性的优化。例如：
explain select * from student where id > 2;
这里需要注意一下版本差异
- 「MySQL 5.6.3」 MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后可以 EXPLAIN SELECT，UPDATE，DELETE
- 「MySQL 5.7」 MySQL 5.7以前想要显示 partitions 需要使用 explain partitions 命令；想要显示filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。
1.1执行计划详解
「在使用索引的时候首先应该学会分析SQL的执行，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，可以知道MySQL是如何处理SQL语句」。
使用格式：
#explain sql语句 如下：
explain select * from student where id > 2;
从执行计划输出的结果可以看出，它有很多的字段，每个字段都有自己的含义
- 「id」 「选择标识符」：在一个查询语句中每个【SELECT】关键字都对应一个唯一的 id。两种例外的情况：
* - 「id相同」优化器对子查询做了 「半连接（semi-jion）优化」时，两个查询的 id 是一样的
explain select * from student where id in(select id from student where id > 1);
- 「id为null」
explain select * from student union select * from student where id > 1;
因为 「union会对结果去重，内部创建了一个。
- 「select_type」 「查询的类型」，常用的值如下：查询的类型 类型含义 SIMPLE 简单的select查询，不包含子查询或union查询，是最常见的。 PRIMARY 若查询中包含有子查询，最外层查询会别标记为PRIMARY UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED SUBQUERY 在SELECT或WHERE列表中包含了子查询 DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生);MySQL会递归执行这些子查询, 把结果放在临时表里。 UNION RESULT 从UNION表获取结果的SELECT DEPENDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层 UNCACHEABLE SUBQUREY 无法被缓存的子查询
- 「table」 输出结果集的表，即查询的表名
- 「partitions」 匹配的分区
- 「type」 表示存储引擎查询数据时采用的方式。它 「可以判断出查询是全表扫描还是基于索引的部分扫描」。 常用属性值如下，从上至下效率依次增强。
* - ALL：表示全表扫描，性能最差。
- index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
- range：表示使用索引范围查询。使用>、>=、
- 「possible_keys」 表示在某个查询语句中，对某个表执行单表查询时 「可能用到的索引列表」
- 「key」 表示在某个查询语句中，列表示 「实际用到的索引」有哪些。
- 「key_len」 表示查询使用索引的字节数量。可以判断是否全部使用了组合索引。
如果键是 NULL，则长度为 NULL。 「使用的索引的长度」。在不损失精确性的情况下，长度越短越好 。
- 「ref」 当使用索引列等值匹配的条件去执行查询时，ref 列展示 「与索引列作等值匹配的对象」。
- 「rows」 「扫描出的行数(估算的行数)」，
* - 如果查询优化器决定使用全表扫描的方式对某个表执行查询时，rows 列就代表预计需要扫描的行数；
- 如果使用索引来执行查询时，rows 列就代表预计扫描的索引记录行数。
- 「filtered」 按表条件过滤的行百分比
* - 如果是全表扫描，filtered 值代表满足 where 条件的行数占表总行数的百分比
- 如果是使用索引来执行查询，filtered 值代表从索引上取得数据后，满足其他过滤条件的数据行数的占比。
- 「Extra」 Extra 是 EXPLAIN 输出中另外一个很重要的列，各种操作都会在Extra提示相关信息，常见几种如下：
* - Using where：表示查询需要通过索引回表查询数据。
- Using index：表示查询需要通过索引，索引就可以满足所需数据。
- Using filesort：表示查询出来的结果需要额外排序，
数据量小在内存排序，数据量大在磁盘排序，因此有Using filesort 建议优化。
- Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。
二、回表查询
在之前《索引基本原理》 中提到InnoDB索引有聚簇索引和辅助索引。
- 聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。
- 辅助索引的叶子节点存储的是主键值和索引字段值
由上图可知： 「通过辅助索引无法直接定位行记录，通常情况下，需要扫两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，即回表查询」。性能比扫一遍索引树低。
三、覆盖索引
索引覆盖： 「只需要在一棵索引树上就能获取SQL所需的所 有列数据，无需回表，速度更快」
覆盖索引形式：，搜索的索引键中的字段恰好是查询的字段
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。
四、最左前缀原则
在之前《索引基本原理》 中提到组合索引的概念，在组合索引的使用中最关键的就是最左前缀原则。
「组合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列， 那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效」。
五、索引与排序
5.1排序方式
MySQL查询支持filesort和index两种方式的排序，
- filesort是先把结果查出，然后在缓存或磁盘进行排序 操作，效率较低。
- index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。
5.2 排序方式的选择
- 「使用index方式的排序的场景」
* - ORDER BY 子句索引列组合满足索引最左前列
explain select id from user order by id; //对应(id)、(id,name)索引有效
- WHERE子句+ORDER BY子句索引列组合满足索引最左前缀
#对应(age,name)组合索引
explain select id from user where age=18 order by name;
- 「使用filesort方式的排序的场景」
* - 对索引列同时使用了ASC和DESC
#对应(age,name)组合索引
explain select id from user order by age asc,name desc;
- WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、
- ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前缀
#对应(age,name)组合索引
explain select id from user order by name;
- 使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引
#对应(name)、(age)两个索引
explain select id from user order by name,age;
- WHERE子句与ORDER BY子句，使用了不同的索引
#对应(name)、(age)索引
explain select id from user where name='tom' order by age;
- WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式
#对应(age)索引
explain select id from user order by abs(age);
5.3排序算法
filesort有两种排序算法：双路排序和单路排序。
- 双路排序：需要两次磁盘扫描读取，得到最终数据。第一次将排序字段读取出来，然后排序；第二 次去读取其他字段数据。
- 单路排序：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。
- 如果查询数据超出缓存 sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。
- 解决方案：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量。
如果Explain分析SQL时Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。如果Extra属性显示Using index时，表示覆盖索引，所有操作在索引上完成。
Original: https://www.cnblogs.com/yunlongn/p/16630242.html
Author: 云扬四海
Title: MySQL十二：索引分析
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/588792/
转载文章受原作者版权保护。转载请注明原作者出处！