索引使用策略及优化

原文： https://www.cnblogs.com/xpp142857/p/7373005.html http://blog.codinglabs.org/articles/theory-of-mysql-index.html

面试官常常会问你，怎么查看一个sql语句有没有使用索引这种类似的问题，或者问你sql怎么优化，那么如何了解sql怎么执行，执行情况如何呢?这就要用到Mysql的explain命令了。这部分往往是和业务相关的，所以不只是要求DBA掌握，实际上DBA又不关心业务，是后端程序员必备的技能，也是面试考察重点。

explain结果值及其含义

select_type

• 表示SELECT语句的类型。
• 该参数有几个常用的取值：
  • SIMPLE：表示简单查询，其中不包括连接查询和子查询;
  • PRIMARY：表示主查询，或者是最外层的查询语句;
  • UNION：表示连接查询的第二个或后面的查询语句;

table

• 表示查询的表

type

表示表的连接类型。该参数有几个常用的取值：

• const：表示表中有多条记录，但只从表中查询一条记录;
• eq_ref：表示多表连接时，后面的表使用了UNIQUE或者PRIMARY KEY;
• ref：表示多表查询时，后面的表使用了普通索引;
• unique_ subquery：表示子查询中使用了UNIQUE或者PRIMARY KEY;
• index_ subquery：表示子查询中使用了普通索引;
• range：表示查询语句中给出了查询范围;
• index：表示对表中的索引进行了完整的扫描;
• all：表示此次查询进行了全表扫描(该条SQL需要优化)

possible_keys

表示查询中可能使用的索引 如果备选的数量大于3那说明已经太多了，因为太多会导致选择索引而损耗性能， 所以建表时字段最好精简，同时也要建立联合索引，避免无效的单列索引；

key

表示查询使用到的索引

key_len

表示索引字段的一长度

ref

表示使用哪个列或常数与索引一起来查询记录

rows

表示查询的行数;

试图分析所有存在于累计结果集中的行数，虽然只是一个估值，却也足以反映 出SQL执行所需要扫描的行数，因此这个值越小越好

Extra

表示查询过程的附件信息。

索引使用策略及优化

基于以上explain的基础，我们对mysql索引进行优化。 MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。本章讨论的高性能索引策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。

示例数据库

为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图（引用自MySQL官方手册）： MySQL官方文档中关于此数据库的页面为http://dev.mysql.com/doc/employee/en/employee.html。里面详细介绍了此数据库，并提供了下载地址和导入方法，如果有兴趣导入此数据库到自己的MySQL可以参考文中内容。

最左前缀原理与相关优化

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。

这里先说一下联合索引的概念。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数，但是这里我不想讨论太多关系代数的话题，因为那样会显得很枯燥，所以这里就不再做严格定义。另外，单列索引可以看成联合索引元素数为1的特例。

以employees.titles表为例，下面先查看其上都有哪些索引：

从结果中可以到titles表的主索引为，还有一个辅助索引。为了避免多个索引使事情变复杂（MySQL的SQL优化器在多索引时行为比较复杂），这里我们将辅助索引drop掉：

1ALTER TABLE employees.titles DROP INDEX emp_no;

这样就可以专心分析索引PRIMARY的行为了。

情况一：全列匹配。

很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒：

效果是一样的。

情况二：最左前缀匹配。

当查询条件精确匹配索引的左边连续一个或几个列时，如或，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。

情况三

查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。

此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。 首先我们看下title一共有几种不同的值：

 1SELECT DISTINCT(title) FROM employees.titles;
 2+--------------------+
 3| title              |
 4+--------------------+
 5| Senior Engineer    |
 6| Staff              |
 7| Engineer           |
 8| Senior Staff       |
 9| Assistant Engineer |
10| Technique Leader   |
11| Manager            |
12+--------------------+

只有7种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

这次key_len为59，说明索引被用全了，但是从type和rows看出IN实际上执行了一个range查询，这里检查了7个key。看下两种查询的性能比较：

“填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据，则后者性能优势会更加明显。当然，如果title的值很多，用填坑就不合适了，必须建立辅助索引。

情况四：查询条件没有指定索引第一列

由于不是最左前缀，索引这样的查询显然用不到索引。

情况五：匹配某列的前缀字符串。

此时可以用到索引，如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀.

情况六：范围查询

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了“between”并不意味着就是范围查询，例如下面的查询：

看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

相关阅读

• MySQL索引背后的数据结构及算法原理