08-优化 SQL 步骤
在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化，本章将详细介绍在 MySQL 中优化 SQL 语句的方法。
当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。
- 查看 SQL 执行频率；
- 定位低效率执行 SQL（慢查询日志）；
- explain 分析执行计划；
- show profile 分析 SQL；
- trace 分析优化器执行计划；
1. 查看 SQL 执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。该命令可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。
# 这些参数对于所有存储引擎的表操作都会进行累计
show status like 'Com_______';
show global status like 'Com_______';
# 这几个参数只是针对 InnoDB 存储引擎的，累加的算法也略有不同
show status like 'Innodb_rows_%';
Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。
2. 定位低效率执行 SQL
可以通过如下这两种方式定位执行效率较低的 SQL 语句。
2.1 慢查询日志
a. 概述
MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。
long_query_time 的默认值为 10，意思是运行 10 秒以上的语句。假如运行时间正好等于 long_query_time 的情况，并不会被记录下来。也就是说，在 MySQL 源码里是判断大于 long_query_time，而非大于等于。
由他来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条 SQL 执行超过 5 秒钟，我们就算慢 SQL，希望能收集超过 5 秒的 SQL，结合 explain 进行全面分析。
b. 开启
默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
通过 SHOW VARIABLES LIKE '%slow_query_log%'; 查看是否开启，默认情况下 slow_query_log 的值为 OFF，表示慢查询日志是禁用的，可以通过设置 slow_query_log 的值来开启。
使用 set global slow_query_log=1; 开启了慢查询日志只对当前数据库生效，如果 MySQL 重启后则会失效。
如果要永久生效，就必须修改配置文件 my.cnf（其它系统变量也是如此）。在 [mysqld] 下增加或修改参数 slow_query_log 和 slow_query_log_file 后，然后重启 MySQL 服务器。也就是将如下几行配置进 my.cnf 文件：
# 该参数用来控制慢查询日志是否开启，可取值： 1 代表开启、0 代表关闭
slow_query_log=1
# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log
# 该选项用来配置查询的时间限制，超过这个时间将认为值慢查询，将需要进行日志记录，默认10s
long_query_time=2
c. 使用
1. 查看当前多少秒算慢
这个是由参数 long_query_time 控制，通过命令 SHOW VARIABLES LIKE 'long_query_time%'; 查看知默认值为 10 秒。
可以使用命令 SET long_query_time=0.1 修改慢的阙值时间，也可以在 my.cnf 参数里面修改。
2. 设置慢的阈值时间
通过命令 set global long_query_time = 3;，修改之后需要重新连接或新开一个会话才能看到修改值。
3. 记录慢 SQL 并后续分析
tail -5 /文件位置/<host-name>-slow.log
4. 查询当前系统中有多少条慢查询记录
show global status like '%Slow_queries%';
5. 因为直接分析日志文件是个体力活，因此 MySQL 为我们提供了相关工具 mysqldumpslow 来对慢查询日志文件进行分析。
mysqldumpslow --help 查看帮助信息：
使用参考如下，建议在使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏现象。
# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/xxx-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询SQL
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xxx-slow.log
2.2 show processlist
慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题。此时可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
- 【id】用户登录 MySQL 时，系统分配的 "connection_id"，可以使用函数
connection_id()查看。 - 【user】显示当前用户。如果不是 root，这个命令就只显示用户权限范围的 SQL 语句。
- 【host】显示这个语句是从哪个 IP 的哪个端口上发的，可以用来跟踪出现问题语句的用户。
- 【db】显示这个进程目前连接的是哪个数据库
- 【command】显示当前连接的执行的命令，一般取值为休眠(sleep)，查询(query)，连接(connect) 等。
- 【time】显示这个状态持续的时间，单位是秒。
- 【state】显示使用当前连接的 SQL 语句的状态，是很重要的列。state 描述的是语句执行中的某一个状态，以一个查询 SQL 为例，可能需要经过 copying to tmp table、sorting result、sending data 等状态才可以完成。
- 【info】显示这个 SQL 语句，是判断问题语句的一个重要依据。
3. show profile 分析 SQL
3.1 简述
MySQL 从 5.0.37 版本开始增加了对 show profiles 和 show profile 语句的支持。show profile 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。
通过 have_profiling 参数，能够看到当前 MySQL 是否支持 profile。默认 profiling 是关闭的。可以通过 set 语句在 Session 级别开启 profiling。通过 profile，我们能够更清楚地了解 SQL 执行的过程。
3.2 使用
（1）执行 show profiles 指令，来查看最近 15 次 SQL 语句执行的耗时：
（2）通过 show profile for query <query_id> 诊断，查看该 SQL 执行过程中每个线程的状态和消耗的时间：
【注】Sending data 状态表示 MySQL 线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在 Sending data 状态下，MySQL 线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。
（3）在获取到最消耗时间的线程状态后，MySQL 支持进一步选择 all、cpu、block io、context switch、page faults 等明细类型查看 MySQL 在使用什么资源上耗费了过高的时间。
- ALL：显示所有的开销信息
- BLOCK IO：显示块 IO 开销
- CONTEXT SWITCHES：上下文切换开销
- CPU：显示 CPU 开销信息
- IPC：显示发送和接收开销信息
- MEMORY：显示内存开销信息
- PAGE FAULTS：显示页面错误开销信息
- SOURCE：显示和 Source_function，Source_file，Source_line 相关的开销信息
- SWAPS：显示交换次数开销信息
例如，选择查看 CPU 的耗费时间：
（4）如果在 show profile 诊断结果中出现了以下 4 条结果中的任何一条，则 sql 需要优化。
- converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
- Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
- Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！
- locked
3.3 小结
show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。可以修改全局的配置：set global profiling = on- 通过
show profiles查看 sql 语句的耗时时间，然后通过show profile命令对耗时时间长的 sql 语句进行诊断。 - 注意
show profile诊断结果中出现相关字段的含义，判断是否需要优化 sql 语句。
4. trace 分析优化器执行计划
MySQL 5.6 提供了对 SQL 的跟踪 trace，通过 trace 文件能够进一步了解为什么 Optimizer 选择 A 计划，而不是选择 B 计划。
打开 trace，设置格式为 JSON，并设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace="enabled=on", end_markers_in_json=on;
SET optimizer_trace_max_mem_size=1000000;
e.g. 执行 select * from tb_item where id < 4;，然后通过 select * from information_schema.optimizer_trace\G; 查看 MySQL 是如何执行 SQL 的：
6. InnoDB 的主键选择和插入优化
在使用 InnoDB 存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。
经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，使用 InnoDB 引擎而不使用自增主键绝对是一个糟糕的主意。
上文讨论过 InnoDB 的索引实现，InnoDB 使用聚集索引，数据记录本身被存于主索引（一颗 B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL 会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB 默认为 15/16），则开辟一个新的页（节点）。
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：
这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页的中间某个位置：
此时 MySQL 不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。
因此，只要可以，请尽量在 InnoDB 上采用自增字段做主键。
7. 回表操作、索引下推
- 当所要查找的字段不在非主键索引树上时，需要通过叶子结点的主键值去主键索引上获取对应的行数据，这个过程称之为“回表操作”。
- “索引下推”主要是减少了不必要的回表操作。对于查找出来的数据，先过滤掉不符合条件的，其余的再去主键索引树上查找。
