共计 4266 个字符，预计需要花费 11 分钟才能阅读完成。
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 或 GROUP
BY 中的字段都来自其余的表而非连贯程序中的第一个表的话, 就会创立一个长期表了。
最常见的是 left join 多表关联，order by 没有应用主表字段排序，而应用了关联表字段排序，所以当多表关联时，尽量应用主表字段进行 order by。
▲Using index
示意间接拜访索引就足够获取到所须要的数据，不须要通过索引回表；是查问性能高的一个体现。
▲Using join buffer (Block Nested Loop)
此类 select 查问语句须要进一步优化。常见的是两个表关联，关联字段都没有建设索引。常见的优化计划是在被驱动表的关联字段上建设索引。
▲Impossible where
示意 where 后条件是永假条件，导致 select 语句无奈抉择任何一行数据。常见的例子如 select * from
tbl_student where 1<0;
2.1 笼罩索引
笼罩索引满足的条件是 select 后所有字段和 where 后所有字段都是索引字段。若是组合索引，必须是 select 后字段 +where 后字段 = 组合索引字段，组合索引字段程序不受限制。笼罩索引还有一点要留神，就是不能应用 select * 来查问，这样就无奈造成笼罩索引。
这样做的目标是防止回表查问，因为查问的数据可间接在索引中找到，进步了查问效率。个别笼罩索引的标记是 explain 的 Extra 属性为 Using
index。上面是示例试验表构造及数据：
▲(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 后组合索引第一个字段与条件值存在“隐式转换”，该字段类型与条件值类型不统一导致生效。
工夫无限，如果须要能够自行到昂焱数据官网（www.ayshuju.com）上下载。
