面试MySQL所需要知道的：索引和SQL优化（下）SQL的执行计划

Explain分析SQL执行计划

在SQL查询语句前面，加上explain关键字，MySQL就会对这条查询语句进行分析，分别有以下字段

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

通过分析SQL执行计划，可以了解到SQL是否正确使用到索引

id、select_type、table

id表示查询中对表操作的顺序：

id值相同，从上到下执行

id值不同，id值高的先执行

---

select_type用来表示查询的不同类型：

1. SIMPLE：简单的Select查询，不包含子查询或UNION
2. PRIMARY：若查询包括子查询，最外层的查询为PRIMARY
3. SUBQUERY：表示子查询
4. DERIVED：FROM列表中的子查询被标记为DERIVED
5. UNION：UNION后的Select查询被标记为UNION
6. UNION RESULT：从UNION表获取结果的Select

---

table则表示数据来源于哪些表

type

type表示访问的类型，从上往下，性能由高到低

• system：相当于系统表，是const联结类型的特例，往往出现在查询的表只有一行
• const：通过一次索引就能找到，表中只有一个匹配行时使用，通常是对主键或者唯一索引的查询，是效率最高的联结方式
• eq-ref：主键索引或唯一索引查找，对于每一个索引键，表中只有一条记录匹配
• ref：非唯索引查找，返回匹配某个值的所有行
• ref_of_null：和ref类型，增加了NULL值列的查询
• index_merge：使用了索引合并优化方法
• range：只检索给定范围的行，常见于between、>、<、like等查询条件
• index：FULL index Scan，全索引扫描
• ALL：FULL table Scan，全表扫描，效率最低

通常对于大量数据的数据库，通常是对于All、index的类型会考虑进行优化

possible_keys、key、key_len

possible_keys表示查询的字段可能涉及到的索引

---

key表示实际使用到的索引，没有使用索引就显示NULL。

---

key_len是根据表的定义计算出的，一个索引使用的字节数。它显示的是索引的最大长度而不是实际长度，key_len值越小越好（在不降低查询精准性的情况下）

ref、rows、Extra

ref显示哪些列或常量被用来查找索引列上的哪些值

---

rows是根据索引的选用，推算出每张表会有多少行需要被查询

---

Extra显示一些其他的重要信息，下面是一些常见的语句

• using where：使用了where过滤
• impossible where：where语句中存在问题
• using join buffer：使用了join缓存
• using index：表示使用了覆盖索引
• using filesort：并没有按照表内的索引进行排序，称为文件内排序，常见于ORDER BY 和 GROUP BY，这种情况需要优化。
• using temporart：通常发生在不同列集上的ORDER BY，产生了临时表用于保存中间结果，也需要优化。

索引失效

什么是索引失效？即索引和SQL语句的配合不到位，使得索引并没有被正确使用，要么是索引建立的选择不对，要么是索引正确建立，但SQL语言的使用不对，通常有这么一些情况。

---

1. 注意最左匹配原则，不能跳过索引
   在前一篇文章中，写到了关于联合索引的顺序问题是很重要的。如果索引中最左边的的列没有用上，该索引就不会被用上。如果索引的中间列没有用上，则后面的列也不会被用上。

2. 索引列上不能使用表达式或函数

3. 注意隐式的类型转换问题，会使索引失效

4. NOT IN 和 <>的操作无法用到索引

5. like的%不能在左边使用

6. 范围操作也会使得索引断开
   比如索引 index(name,age,city)，sql语句是select name,age,sex where name=“张三” and age>18 and city="上海"
   只会索引name列和age列，age列之后的city就不会被索引。

7. 使用or联结时可能会导致索引失效

索引的误区

1. 索引是越多越好吗？
   A：并不是，索引是会降低更新操作的性能的，同时过多的索引，也会在SQL优化器在选择索引来执行SQL语句时造成负担。所以经常要删除多余的无用索引。

2. 覆盖索引很优秀，为什么不添加很多字段，这样查询条件基本都能覆盖？
   A：索引也是要占空间的，索引的长度也是需要考虑的

3. 查找语句必须按照索引建立的顺序执行？
   A：并不是，优化器会自动优化查询条件的顺序，和索引键值的顺序相对应

SQL语句的优化

索引的优化固然是重中之重，但是SQL语句的优化也是值得考虑的一点。

---

SQL语句的加载顺序

1. FROM <left_table>
2. ON <join_condition>
3. <join_type> JOIN <right_table>
4. WHERE <where_condition>
5. GROUP BY <group_by_list>
6. HAVING <having_condition>
7. SELECT
8. DISTINCT <select_list>
9. ORDER BY <order_by_list>
10. LIMIT <limit_number>

---

子查询优化

子查询的使用简单明了，通常需要把SQL语句子查询优化为JOIN查询，但是需要注意关联键的关系，以及是否有数据的重复。

不过当数据量太大时，子查询和JOIN查询都有性能硬伤，往往需要考虑别的优化手段

---

GROUP BY的优化

使用GROUP BY时需要注意文件内排序和临时表的情况。

如果我们要对关联查询中进行GROUP BY的话，最好选用同一表中的列来进行GROUP BY。

---

LIMIT的优化

LIMIT通常是使用在ORDER BY 从句中，用于分页查询，所以我们需要小心LIMIT带来的文件内排序的IO问题

可以选择主键列或索引列进行ORDER BY操作，也可以记录上次返回的主键，进行过滤，下一次从上一次的位置开始扫描。