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

文章插图
作者 | 曹建
责编 | 屠敏
我们都知道创建索引的目的是快速从整体集合中选择性地读取满足条件的一部分集合。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两种：