面对 MySQL 查询索引失效，程序员的六大优化技巧！

2020-02-22

作者 | 曹建

责编 | 屠敏

我们都知道创建索引的目的是快速从整体集合中选择性地读取满足条件的一部分集合。MySQL中一张表是可以支持多个索引的。但是，你写SQL语句的时候，并没有主动指定使用哪个索引。不知道你有没有碰到过这种情况，一条创建了索引的SQL语句在查询过程中却没有使用索引，或是一条本来可以执行的很快的语句，却由于MySQL选错了索引，而导致查询速度变得很慢？充分优化和利用索引能够大大提高数据的查询效率，但是在实际的应用中MySQL可能并不总会选择合适且效率高的索引。那么我们今天就一起来讨论下 MySQL 索引以及索引的优化，首先我们来看一个案例，下面是一张建表的SQL如下：

CREATETABLE`t_test3`(

`id`bigint( 11) NOTNULL,

`name`varchar( 32) DEFAULTNULL,

PRIMARY KEY( `id`),

KEY`t_test_name`( `name`)

) ENGINE= InnoDBDEFAULTCHARSET=utf -8;

使用以下的SQL查看对应的执行计划：

desc select* fromt_test3 wherenamein( 'a', 'b');

事实上，在建立表的sql中我们是对name这一列建立了索引，为何在执行计划的时候没有使用索引呢？

要找到这个原因，我们需要首先了解下SQL在mysql中的执行过程，MYSQL 的整个架构可以分为 server 层和存储引擎层2个部分。Server 层包括连接器，查询缓存，分析器，优化器，执行器等模块；存储引擎层负责数据的存储与提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎，默认的是InnoDB。可以在建表的时候使用engine = memory来指定存储引擎。

其中Server 层执行步骤如下：

第一步连接器：通过账号和密码连接到对应的数据库上，连接器负责与客户端建立连接，获取权限，维持和管理连接。连接分为长连接和短连接，长连接是指连接成功后，客户端不断有请求，则一直使用同一个连接。短连接：处理几个请求后，断开连接，之后的请求需要重新连接。

第二步查询缓存：建立连接之后，mysql拿到一个查询请求后，会先查询缓存中之前是否执行过这条语句，如果查询缓存命中，则查询结果直接返回给客户端，如果查询缓存不命中，就会继续后面的执行阶段。完成以后，执行结果会被存入查询缓存中。大多数情况下不建议使用查询缓存。如果缓存命中，mysql不需要执行后面的复杂操作，就可以直接返回结果，效率很高，但是查询缓存失效非常频繁，只要有对一个表的更新，这个表的所有查询缓存都会被清空，因此可能你费力地把结果缓存起来，还没使用，就被一个更新全部清空了。除非你的业务是一张静态表，很长时间才会更新一次，这种情况下可以使用查询缓存。

第四步优化器：优化器是数据库的一个核心子系统，你也可以把他理解为 MySQL 数据库中的一个核心模块或者一个核心功能模块。优化器的目的是按照一定原则来得到它认为的目标SQL在当前情形下最有效的执行路径,优化器的目的是为了得到目标SQL的执行计划。经过分析器，mysql就知道你要做什么了。SQL 在执行的过程中经过优化器，并由优化器生成 SQL 的执行计划。

传统关系型数据库里面的优化器分为CBO和RBO两种：

RBO--- Rule_Based Potimizer 基于规则的优化器:RBO所用的判断规则是一组内置的规则，这些规则是硬编码在数据库的编码中的，RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划（比如在RBO里面，有这么一条规则：有索引使用索引。那么所有带有索引的表在任何情况下都会走索引）所以，RBO现在被很多数据库抛弃（oracle默认是CBO，但是仍然保留RBO代码，MySQL只有CBO），RBO最大问题在于硬编码在数据库里面的一系列固定规则，来决定执行计划。并没有考虑目标SQL中所涉及的对象的实际数量，实际数据的分布情况，这样一旦规则不适用于该SQL，那么很可能选出来的执行计划就不是最优执行计划了。
CBO---Cost_Based Potimizer 基于成本的优化器:CBO在会从目标诸多的执行路径中选择一个成本最小的执行路径来作为执行计划。这里的成本他实际代表了MySQL根据相关统计信息计算出来目标SQL对应的步骤的IO，CPU等消耗。也就是意味着数据库里的成本实际上就是对于执行目标SQL所需要IO,CPU等资源的一个估计值。而成本值是根据索引，表，行的统计信息计算出来的(计算过程比较复杂)。

第五步执行器：开始执行的时候，首先会判断此次连接是否有对应的操作权限，如果没有，则返回没有权限的错误。如果有权限，则打开表继续执行。打开表的时候，执行器会根据表的引擎定义，去使用这个引擎提供的接口。

比如下面这条sql语句执行器流程是这样的：

select* fromt_test3 wherename= 'a';

调用InnoDB引擎接口获取这个表的第一行，判断name的值是不是a，如果不是则跳过，如果是则将这行存在结果集中。
调用引擎接口获取下一行，重复相应的判断逻辑，直到取到最后一行数据
执行器将遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

通过了解sql执行的过程以及优化器，发现mysql采用的是第二种基于成本的优化器，它会根据sql执行的成本选择合适的路径。所以可以推断出上面sql执行计划没有采用对应列的索引原因。当我在表中插入一万条数据的时候，再重新查看对应的执行计划时，发现此时，该sql的查询类型会使用range类型及使用name对应的索引进行查询。

当数据量比较小的时候，会使用all类型进行查询对应数据，当数据量比较大时，查询数据量增大时，会采用range类型，并使用对应列的索引进行查询。这便涉及到了数据库查询索引的离散度。离散度，外文 Measures of Dispersion，是指通过随机地观测变量各个取值之间的差异程度，用来衡量风险大小的指标。离散度在不超过全表的10%-15%的前提下索引才可以显示索引所具有的价值。当离散度超过该值的情况下全表扫描可能反倒比索引扫描更有效。我们所追求的目标就是创建全表扫描所无法比拟的有效索引。比如当我们对一张学生表信息中对性别添加索引，性别只有两种值，会产生大量的重复，离散度较小，使用性别索引会增加查询开销，使得在使用性别的索引查询时可能比没有性别索引的查询更慢。

基于数据库索引的离散度，可以参考以下两个建议进行创建索引：

在允许的情况下，对具有较好离散度的列单独创建索引，这样可以提高该索引的使用弹性；
对于离散度较差的列，通过对多列进行合理的组合来创建组合索引，虽然这样做在很大程度上降低了各个列的使用弹性，但是却可以发挥多个列的综合效应。

在实际应用的过程中，MySQL索引失效的情形很多。例如：在WHERE条件的LIKE关键字匹配的字符串以”%“开头，这种情况下，索引是不会起到作用的；WHERE条件中使用OR关键字来连接多个查询条件，如果有一个条件没有使用索引，那么其他的索引也不会起作用；多列索引的第一个字段没有使用，那么这个多列索引也不会起作用。使用in查询时，in查询条件超过数据库表的一半的时候也会失效。

根据这些情况，我们必须选择对索引有正确的理解，并不是创建索引就能增加查询速度。根据使用索引的特性，对创建索引的一些技巧总结如下：

首先数据量小的表不需要建立索引，因为数据量小的表即使建立索引也不会有大的用处，还会增加额外的索引开销。
不经常引用的列不要建立索引，因为不常用，即使建立了索引也没有多大意义。
经常频繁更新的列不要建立索引，因为肯定会影响插入或更新的效率。
尽量避免在 where 子句中使用！ = 或者 <> 操作符，查询引用会放弃索引而进行全表扫描。
数据类型越小越简单的索引更好。越小越简单的数据类型通常在磁盘、内存和cpu缓存中需要的空间更少，处理起来更快。
尽量避免NULL：在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。可以采用0、一个特殊的值或者一个空串代替空值。

在实际应用的过程中，mysql并不总会选择合理的索引进行查询，此时便可以使用force index(index name)来强制告诉mysql选择哪一个索引。使用一下sql查询：

desc select* fromt_test3 forceINDEX(t_test_name) wherenamein( 'a', 'b');

其对应的执行计划与上图的执行计划相同，采用的是sql中指定的索引。

因此我们在一些情况下首先可以适当的使用force index(indexname) 强制告诉mysql使用什么索引。force index( index name )指令可以指定本次查询使用哪个索引！一条sql只会用到一个索引，mysql优化器会计算出一个合适的索引，但是这个索引不一定是最好的。force index指令可以避免MySql优化器用到了一个低效的索引，并可以提高sql的执行效率。