MySQL查询优化终极版(强烈建议收藏)

select查询优化一直是日常开发和数据库运维绕不开的一道坎，SQL的查询速度决定了页面的加载速度，进一步决定了客户浏览体验。

为了找到MySQL查询优化解决方案，百度了大量技术博客和资料，发现一个很好的技术资源平台-昂焱数据（www.ayshuju.com），该平台包括了接口API、代码工具、技术文档、标准码表、统计数据、网站素材等丰富的技术资源。

在技术文档模块找到了一篇“MySQL查询优化绝世宝典”技术文档，该文档详细介绍了MySQL查询优化的所有场景与方法，并有示例说明。具体目录如下：

1 MySQL查询优化基础

1.1 MySQL索引说明及其选择

1.2 Explain工具使用详解

2 MySQL简单查询优化方法

2.1 覆盖索引

2.2 最左前缀

2.3 索引下推

2.4 避免回表

2.5 强制索引

3 MySQL长字符串索引优化方法

3.1 前缀索引

3.2 倒序存储

3.3 Hash字段

4 MySQL连接查询优化方法

4.1 小表驱动大表

4.2 算法 NLJ与MRR/BKA

5 MySQL数据库行业规范

5.1 MySQL数据库设计规范

5.2 MySQL数据库应用规范

下面根据这篇“MySQL查询优化绝世宝典”技术文档，引用几个比较常见的知识点和大家技术分享一下，共同学习，共同进步。

1.2 Explain工具使用详解

explain是优化SQL语句的利器，explain关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈。该工具能够解释SQL语句处理情况、表的加载顺序、表是如何连接、以及索引的使用情况。explain参数示例概览如下：

(1) id：一个简单的SQL语句表示

select查询语句序列号，有几个select语句就有几个id序列号，id序列号从1开始顺序递增。

(2)select_type：描述select语句的类型

▲simple：简单查询。查询不包含子查询和union等复合查询。

▲primary：复杂查询中最外层的select查询。

▲subquery：包含在select中的子查询（不在from子句中）

▲derived：包含在from子句中的子查询。mysql会将结果存放在一个临时表中，又称派生表。

▲union：在union中的第二个和随后的select语句。

▲union result：从union临时表中查询结果的select语句。

(3) table：查询的表对象-表名

显示数据库中的表名，但有时也可能是某些阶段执行结果的简称。

(4) partitions：匹配的表的分区

如果查询语句基于表分区，这里会显示查询要访问的分区。

(5) type：表示优化器将要使用的存取方式（性能好➞差：system>const>eq_ref>ref>range>index>all）

▲system：表中只有一行记录，属于特殊的const类型。

▲const：对于主键或唯一索引的where等值查询，索引检索一次就找到结果且最多返回一行数据。因只读取一次，所以速度非常快。

▲eq_ref：主要对于唯一索引检索，一般是两表关联，关联字段为主键或唯一索引，表中只有一条记录相匹配。

▲ref：常用于多表关联，针对非唯一索引或非主键索引，返回匹配某个值的所有行。

▲range：表示检索使用了索引范围查询，一般where条件中会出现<>、>、>=、<、<=、is null、between、in等范围查询。

▲index：表示全索引扫描，会遍历索引树B+Tree，只扫描索引不扫描数据。

▲all：表示全表扫描，该类型查询性能最差，all是从硬盘中检索，遍历全表查找匹配的行。

(6) possible_keys：可以使用的索引，如果没有索引可用返回Null;也可能显示多个索引可以使用的索引，如果没有索引可用返回Null;也可能显示多个索引。

(7) key：优化器最终选择的索引。如果没有索引可用，返回null

优化器在possible_keys范围内最终选择的索引。如果没有索引可用，返回null。

如果对优化器选择的索引不满意，可以用use index、ignore index、force index等指定索引。

(8) key_len：被选择的key索引的长度，如果没有使用索引，返回null

表示索引使用的字节数，一般情况下，索引长度越长精度越高，但效率偏低；长度越短，精度越低，但效率高。如果键为null，则长度为null。在不损失精确性的情况下，长度越短越好。key_len长度计算公式如下：

▲int not null（key_len）：4（字节）

▲int null（key_len）：4（字节）+1（null标记位）=5

▲varchar(n) null（key_len）：n*(utf8=3,gbk=2,latin1=1)+1(NULL标记位)+2(变长字段)

▲varchar(n) not null（key_len）：n*(utf8=3,gbk=2,latin1=1)+2(变长字段)

▲char(n) null（key_len）：n*(utf8=3,gbk=2,latin1=1)+1(NULL标记位)

▲char(n) not null（key_len）：n*(utf8=3,gbk=2,latin1=1)

▲datetime（key_len）：8（字节）

(9) ref：索引中被使用的列

显示where后索引查找值所用到的列或常量，常见有const常量、func函数、null、字段名等。例如const表示检索来自常数值，如name='小名'；func表示检查来自函数表达式。

注意：如果where后条件是组合索引，但没有按照组合索引顺序使用，则ref为null。

(10) rows：要得到最终记录，而要扫描经过的记录数，该值是预估值

例如要查询10条记录，结果扫描了100行才把这10条记录查找出来，那么rows=100。rows数值越大，说明查询效率越低。

(11) filtered：该值只有where后条件字段建立索引，才准确；公式=最终记录/扫描记录*100%表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数据的百分比。

(12) extra：获取数据过程中的额外信息，有利于SQL优化

▲Using where

表示此select查询语句where后过滤条件字段无索引或者索引失效。

常见的优化方法是在where后字段上加上索引。

▲Using filesort

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

最常见的是order by后面的字段没有建立索引，就会触发filesort。优化方法是在order by后字段建立索引，避免每次查询都全量排序。

▲Using temporary

表示由于排序没有走索引、使用union、子查询连接查询、使用某些视图等，一般出现在多张表的数据需要排序的情况下。例如有ORDER BY子句和一个不同的GROUP BY子句, 或者如果ORDER BY或GROUPBY中的字段都来自其他的表而非连接顺序中的第一个表的话, 就会创建一个临时表了。

最常见的是left join多表关联，order by没有使用主表字段排序，而使用了关联表字段排序，所以当多表关联时，尽量使用主表字段进行order by。

▲Using index

表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；是查询性能高的一个表现。

▲Using join buffer (Block Nested Loop)

此类select查询语句需要进一步优化。常见的是两个表关联，关联字段都没有建立索引。常见的优化方案是在被驱动表的关联字段上建立索引。

▲Impossible where

表示where后条件是永假条件，导致select语句无法选择任何一行数据。常见的例子如select * fromtbl_student where 1<0;

2.1 覆盖索引

覆盖索引满足的条件是select后所有字段和where后所有字段都是索引字段。若是组合索引，必须是select后字段+where后字段=组合索引字段，组合索引字段顺序不受限制。覆盖索引还有一点要注意，就是不能使用select *来查询，这样就无法形成覆盖索引。

这样做的目的是避免回表查询，因为查询的数据可直接在索引中找到，提高了查询效率。一般覆盖索引的标志是explain的Extra属性为Usingindex。下面是示例试验表结构及数据：

▲(username,age,score)没有建立索引

explain select score,age from tbl_stu where username='cc';

▲(username,age,score)建立了索引

alter table tbl_stu add index ind_ua(username,age,score);
explain select score,age from tbl_stu where username='cc';

2.2 最左前缀

最左前缀原则顾名思义，强调select查询语句的where后面各个条件字段要按照最左组合索引字段（即组合索引第一个字段）必使用原则。也就是说where条件字段，只有使用了组合索引的第一个字段，整个组合索引才会生效。组合索引字段使用的先后顺序不受限制。

例如tbl_demo表的组合索引(a,b,c),只有在where后使用了a字段，才可以使用b字段或c字段。使用的先后顺序不影响组合索引的有效性。组合索引(a,b,c)生效的组合有以下四个：

① select* from tbl_demo where a=xx;

② select* from tbl_demo where a=xx and b=xx;

③ select* from tbl_demo where a=xx and c=xx;

④ select* from tbl_demo where a=xx and b=xx and c=xx;

使用最左前缀原则要注意如后几个方面会导致组合索引失效或影响性能。一是组合索引字段遇到范围查询(>、<、between、like)就停止匹配，影响性能；二是使用组合索引第一个字段like时%在最前面会导致组合索引失效；三是组合索引第一个字段参与表达式计算会导致失效；四是where后组合索引第一个字段与条件值存在“隐式转换”，该字段类型与条件值类型不一致导致失效。