前言

继上一篇组件专栏后，开启新的篇章。这次带来的专栏我自己命名为从零开始的修炼手册，为什么取这个名字？是纪念我从此文开始的SQL学习之路，这篇文章还帮我在公司的技术PK赛中拿了500元钱，算是一点意外收获。

读者能从文中学到SQL全面的优化知识，从理论到实践，全文超过1W字，比网上任何一篇文章更加全面，篇幅会非常巨大，手把手保姆式教学，带案例精讲，看到就是赚到。同时我还会推荐一些看过的文章，这些文章会补充一些参数解析和故事，避免本文太干太硬把读者噎住。该文会有部分网图，懒人不想画图是这样的，开摆，哈哈。

注意本篇是理论篇，实践篇后续发送.

数据库查询流程

server层

从起源讲起，首先我们需要了解的是一条SQL查询语句下来，会经历哪些流程。

客户端发起连接。客户端可以是我们的代码连接也可以是navicat这种应用，客户端底层会带着账号密码，尝试向mysql建立一条TCP长链接
MySQL的连接管理模块校验账号密码，如果无误就成功建立连接
MySQL分析器校验来自客户端的SQL，判断下SQL语句有没有语法错误
MySQL优化器会根据一定的规则(比如代价)去选择合适的执行计划(比如选择什么样的索引)
MySQL执行器根据执行计划调用存储引擎的接口函数执行SQL

截图.png

存储引擎层

存储引擎以MySQL5.7版本为例，常用的有不支持事务的MyISAM和支持事务的Innodb，一般用Innodb比较多，我们就重点说这个。

InnoDB中，因为直接操作磁盘会比较慢，所以加了一层内存提提速，叫buffer pool，这里面，放了很多内存页，每一页16KB，有些内存页放的是数据库表里看到的那种一行行的数据，有些则是放的索引信息。

截图.png

在存储引擎里SQL的执行流程就简单了

根据优化器中计算得到的索引，去查询相应的索引页，如果不在buffer pool里则从磁盘里加载索引页
通过索引页得到数据页的具体位置，如果这些数据页不在buffer pool中，则从磁盘里加载进来
返回客户端查询结果

截图.png

SQL变慢的原因

大多数情况正常，偶尔慢

数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘
执行的时候，遇到锁，如表锁、行锁
MySQL并发量太高
buffer pool命中率低，设置过小

一直执行的很慢

没有用索引或者索引失效：例如该字段没有索引，由于对字段进行运算、函数操作导致无法用索引。
有索引可能会走全表扫描

慢SQL优化准备

先把纲领性的文件打出来，官方亲自教你如何优化SQL dev.mysql.com/doc/refman/…

慢查询日志

所有的优化都是带有目的性的。我们可以采取通用的方法去提前避免部分问题，但是一般遇到问题都是特事特办，特别是SQL的优化更是场景繁多。因此我们在优化SQL的时候必须要知道SQL是否真的有性能问题，这时候我们可能会有性能监控工具去辅助查询，但是这里我介绍下最朴素的查找方式，MySQL自带的慢SQL日志。

关闭MySQL缓存

查询是否开启缓存SHOW VARIABLES LIKE '%cache%' ，OFF/关闭 ON/开启，如果是ON的话可以通过命令或者修改配置文件。关闭MySQL缓存是为了保证后续测试的真实性。默认就关闭的，这缓存一般用不到。

查询慢SQL日志

查询慢日志相关信息
SHOW VARIABLES LIKE '%query%'      
参数解析
slow_query_log 默认是off关闭的，使用时，需要改为on 打开　　　　　　
slow_query_log_file 记录的是慢日志的记录文件　　　　
long_query_time 默认是10S，每次执行的sql达到这个时长，就会被记录

如何打开慢查询

当前会话打开慢SQL日志(设置完可能需要重新连接数据库)
SET GLOBAL slow_query_log = ON;
将默认时间改为1S： 
SET GLOBAL long_query_time = 1;

PS：仅通过命令改的话，当再次重启数据库服务时，所有设置又会自动恢复成默认值，永久改变需去my.ini中改

找出需要优化的慢SQL

截图.png

如图日志文件会有很多行慢SQL，咱们重点关注SQL语句，Query_time和Lock_time就可以了。

分析慢SQL

找到慢SQL了，接下来就开始上优化手段，比如三驾马车

show profile 查看SQL执行情况分析---高版本官方已弃用，推荐使用Performance Schema
explain 查看SQL执行计划
optimizer_trace 查看有哪些执行计划

show profile 分析

Show Profile 是 MySQL 提供的可以用来分析当前查询 SQL 语句执行的资源（如CPU、IO、IPC、SWAP等）消耗情况的工具，可用于 SQL调优的测量。默认情况下处于关闭状态，开启会消耗一定的性能，一般在 SQL 分析和优化的时候使用，只保存最近15次的运行结果。

截图.png

PS:正式环境关闭，或者用到的时候再打开

开启和关闭profile

# 查看profile相关配置
SHOW VARIABLES LIKE '%profiling%'
# 参数解析
have_profiling--只读变量，用于控制是否由系统变量开启或禁用profiling
profiling--开启SQL语句剖析功能
profiling_history_size--设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用
# 开启和关闭profiling，注意在命令行页面使用命令，navicat的新建查询使用命令会不生效
# 该方法仅限于当前会话进行修改，全局修改需要改MySQL配置文件
SET profiling = ON;
SET profiling = OFF;

查询可分析的SQL语句

#查看当前存储的X条分析结果
SHOW PROFILES;
#参数解析
Query_ID：SQL语句的ID编号
Duration：SQL语句执行时长
Query：具体的SQL语句

截图.png

#指定分析SQL语句，可选参数例如CPU，填了就有没填就和navicat默认的剖析一样只有耗时
SHOW PROFILE CPU, BLOCK IO FOR QUERY 12;

如果使用图形化工具比如navicat，可以使用剖析功能，但是这个剖析列不全

截图.png

这里贴一下官网，官网对各个参数有详细的解释，这里给的是5.7版本，右上可选版本dev.mysql.com/doc/refman/…

在官网这个命令已经不推荐了，这里就不详细叙述了，官方推荐Performance Schema

MySQL性能模式

# 使用命令查看性能模式有没有打开，默认打开的
SHOW VARIABLES LIKE 'performance_schema'
# 没有打开的用命令打开，永久生效该MySQL配置
SET performance_schema=ON

该性能模式其实就是MySQL推出用来升级替换profile的，相比profile功能更加丰富，比如多了权限。而且记录的数据更多，所有运行的SQL都会被记录。

目前性能模式网上资料不是特别多，建议还是看官网dev.mysql.com/doc/refman/…

一般来说profile和Performance Schema并非是我们关注的重点，因为硬件的限制通常是我们最后来处理的问题，大多数情况下还是要选择改动代价较小的软件方向去优化，比如索引。

explain执行计划详解

使用很简单，explain 你的SQL，如果用navicat之类的图形化工具，点解释就OK，比如

截图.png

查出来了有如下这些列，里面有很多参数，我来慢慢讲

截图.png

id--select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序

id 相同，执行顺序由上至下
id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
id 有相同也有不同：id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。

select_type--查询类型

simple：简单的 select 查询，查询中不包含子查询或者 UNION
primary：查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
derived：在 FROM 列表中包含的子查询被标记为 DERIVED (衍生)，MySQL 会递归执行这些子查询, 把结果放在临时表里。
subquery：在 SELECT 或 WHERE 列表中包含了子查询
depedent subquery：在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层
uncacheable subquery：无法使用缓存的子查询
union：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在FROM 子句的子查询中，外层 SELECT 将被标记为：DERIVED
union result：从 UNION 表获取结果的 SELECT

table--查询的表名

并不一定是真实存在的表，有别名显示别名，也可能为临时表，例如上边的DERIVED、等。

partitions--分区信息

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

type--查询类型

查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。
const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。
eq_ref：查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref
ref：区别于eq_ref ，ref表示使用非唯一性索引，会找到很多个符合条件的行。
ref_or_null：这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。
index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
unique_subquery：替换下面的 IN子查询，子查询返回不重复的集合。
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。
index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。
ALL：将遍历全表以找到匹配的行，性能最差。

PS：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

possible_keys--可能使用的索引

表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引。

key--实际使用的索引

区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL。

key_len--索引中使用的字节数

可通过该列计算查询中使用的索引的长度。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度。

如何计算 key_len？

先看索引上字段的类型 + 长度，比如：int=4; varchar(20)=20; char(20)=20。如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 要乘 3，GBK 要乘 2,varchar 这种动态字符串要加 2 个字节,允许为空的字段要加 1 个字节

ref--哪些列或常量被用于查找索引列上的值

常见的有：const，func，null，字段名。

当使用常量等值查询，显示const，
当关联查询时，会显示相应关联表的关联字段
如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
其他情况null

rows--需要读取的行数

以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。

这是评估SQL 性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好。

filtered--符合条件的记录数的百分比

filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

在MySQL.5.7版本以前想要显示filtered需要使用explain extended命令。MySQL.5.7后，默认explain直接显示partitions和filtered的信息。

Extra--额外信息

不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。下面带*号的类型，通常都是需要优化的。

Using index：使用了覆盖索引进行查询，此时不需要访问表，从索引中就可以获取到所需的全部数据
Using index condition：MySQL5.6 之后新增的 ICP，using index condtion 就是使用了 ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
Using where：代表数据库引擎返回结果后mysql server还会再次筛选--回表查询
Using temporary：表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。
*Using filesort：通常出现在 GROUP BY 或 ORDER BY 语句中，且排序或分组没有基于索引，此时需要使用文件在内存中进行排序，因为使用索引排序的性能好于使用文件排序，所以出现这种情况可以考虑通过添加索引进行优化。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
*Using join buffer：在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。
Impossible where：表示在我们用不太正确的where语句，导致没有符合条件的行。
select tables optimized away：在没有 group by 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

以上已经解释的非常清楚了，如果还有不清楚的，可以看看官网dev.mysql.com/doc/refman/…

optimizer_trace优化分析

惯例先推一下官网dev.mysql.com/doc/interna…，然后推荐一篇我觉得写的非常好的文章blog.itpub.net/28218939/vi…

optimizer_trace是一个跟踪功能，跟踪执行的语句的解析优化执行的过程，并将跟踪到的信息记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。可以通过optimizer_trace系统变量启停跟踪功能，MySQL从5.6开始提供了相关的功能，但是MySQL默认是关闭它的，我们在需要使用的时候才会手动去开启。optimizer_trace可以是会话或者是全局开启，但是每个会话都只能跟踪它自己执行的语句，表中默认只记录最后一个查询的跟踪结果（表中记录的跟踪结果数可以通过optimizer_trace的参数设置）

# 使用经典命令show variables like '%xxx%'查看相关系统变量参数
SHOW VARIABLES LIKE '%optimizer_trace%';

截图.png

optimizer_trace
- enabled：启用/禁用optimizer_trace功能
- one_line：决定了跟踪信息的存储方式，为on表示使用单行存储，否则以JSON树的标准展示形式存储。单行存储中跟踪结果中没有空格，造成可读性极差，但对于JSON解析器来说是可以解析的，将该参数打开唯一的优势就是节省空间，一般不建议开启

optimizer_trace_features：该变量中存储了跟踪信息中可控的打印项，可以通过调整该变量，控制在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中的trace列需要打印的JSON项和不需要打印的JSON项。默认打开该参数下的所有项
optimizer_trace_max_mem_size ：optimizer_trace内存的大小，如果跟踪信息超过这个大小，信息将会被截断
optimizer_trace_limit 约束的是跟踪信息存储的个数
optimizer_trace_offset 则是约束偏移量

# 调大一点，避免默认值16384太小，信息被阶段
SET optimizer_trace_max_mem_size=100000;
# 打开追踪，默认关闭的
SET optimizer_trace="enabled=on";
# 调用自己的查询语句
SELECT ...; 
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 不用了就关掉
SET optimizer_trace="enabled=off";

INFORMATION_SCHEMA.OPTIMIZER_TRACE表字段含义

QUERY：跟踪的SQL语句
TRACE：跟踪信息（JSON格式）
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪信息过长时，被截断的跟踪信息的字节数
INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空。一般出现在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下

该工具在官网有详细的案例dev.mysql.com/doc/interna…，并且上面推荐的文章写的非常的好，好到我不想再重复一遍，索性就这样吧。

理论总结

以上理论篇简要介绍了数据库的查询流程，慢SQL的定位和优化需要使用的三架马车。从我个人角度来看，常规的一般了解explain这个命令即可。后面的实战篇也是从索引开始优化，毕竟这算是改动影响最小的手段了。除此之外还会介绍SQL场景优化，MySQL配置优化等手段，尽量会给出一套开箱即用的方法论。

写在最后

我在公司里也算是解决了不少SQL方面的问题，这么多类型的SQL看下来，我感觉核心的问题解决点还是在表设计上面。如果一开始在设计表的时候，让查询都能做到单表查，那么大部分的SQL查询基本就没有问题，就算有性能问题，解决也很简单，无非就是索引。但是工作中大部分需要优化的时候，通常遇到的是庞大且业务复杂的SQL，这种老SQL通常限制很大，代码不能改，硬件配置不能换，表结构不能动。综上所述，能下手的地方就很少，要么是改SQL语句，要么是优化索引，所以在实践篇的时候，我会整理出一套详细的方法论，按步骤教你如何优化一条SQL语句的写法，同时如何最大限度的利用索引，当然也不会忘了硬件上的优化。

从零开始的SQL修炼手册分为理论和实践两篇的原因自然不是我故意要拆开，而是篇幅太长，可能对阅读不太友好。实践篇我不想单单只给出方法，我还要加上案例去做讲解。理论篇差不多9K字，我个人不喜欢画图，有画的好看的都是网图，理论篇看下来可能会很干，但我也希望看到本文的大家能有所收获。实践篇配图的话，应该会很长，底稿我写了，只是需要润色，之前公司讲过一次，有现成的图可用，所以实战篇的发布不会间隔太长时间，尽量下周吧。

最后的最后，感谢看到本文的读者，希望你们生活愉快，我们从零开始的SQL修炼手册-实战篇，不见不散。

从零开始的SQL修炼手册-理论篇

前言

数据库查询流程

server层

存储引擎层

SQL变慢的原因

大多数情况正常，偶尔慢

一直执行的很慢

慢SQL优化准备

慢查询日志

关闭MySQL缓存

查询慢SQL日志

如何打开慢查询

找出需要优化的慢SQL

分析慢SQL

show profile 分析

MySQL性能模式

explain执行计划详解

optimizer_trace优化分析

理论总结

写在最后