MySQL进阶——SQL性能分析
在上篇文章我们学习了MySQL进阶——存储引擎，这篇文章学习MySQL进阶——SQL性能分析。
SQL性能分析主要是从SQL语句执行频率、耗时时间、CPU使用情况和执行时表连接情况进行分析，常用的方法工具有：SQL执行频率、慢查询日志、profile详情和explain执行计划。
SQL执行频率
通过show [session|global] status命令可以提供服务器状态信息，通过如下命令，可以查看当前数据库的增删改查的访问频率：
SHOW GLOBAL STATUS LIKE 'Com_______';
其中：一个‘_’表示一个字符。
运行结果如下：
慢查询日志
通过SQL执行频率，我们可以发现增数据的SQL语句执行频率更多，但无法知道哪条SQL语句执行情况。
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
执行如下命令查看是否开启慢查询日志，
show variables like 'slow_query_log';
如下图所示：
配置文件开启
默认情况下，MySQL的慢查询日志是关闭的，我们可以在MySQL的配置文件（/etc/my.cnf）添加如下命令开启慢查询日志，
slow_query_log=1 # 开启慢查询日志
long_query_time=2 # 设置慢查询日志的时间为2秒，SQL语句执行超过2秒，就视为慢查询，记录慢查询日志
如下图所示：
注意：如果是在docker安装的mysql，需要先执行如下命令，进入MySQL容器内部再在MySQL配置文件中添加开启慢查询日志代码。
docker exec -it 容器ID /bin/bash
在/etc/my.cnf文件添加如下图代码：
配置完成后需要重启MySQL服务器。
查看慢查询日志开启，如下图所示：
SQL命令开启
当然我们也可以通过在mysql中执行如下命令，开启慢查询日志，
set global slow_query_log = ON;
set global slow_launch_time = 3;
show variables like 'slow%';
如下图所示：
注意：这种开启方式不是永久开启慢查询日志，只要MySQL重启了，慢查询日志就会关闭。
示例
当我们执行了SQL语句而操作时长超过2秒，就会记录慢查询，这里我们执行了删除UserTable表操作，大概用了10秒，慢日志如下图所示：
在日志中，我们可以看到执行日期、时长、用户、IP、数据库和SQL语句等相关信息。
profile详情
在慢查询日志中，我们只能获取超过设置的时间SQL语句信息，例如设置的时长为2秒，那么只能获取超过2秒的SQL语句信息，执行了1.99秒的SQL语句无法获取，这时我们就可以使用profie详情。
profiles详情能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
首先通过have_profiling参数，查看当前MySQL是否支持profile操作：
SELECT @@have_profiling;
如下图所示：
接着执行如下代码查看profile是否开启，
SELECT @@profiling; # 查看是否开启
SET profiling=1; # 开启profile
如下图所示：
接下来我们就可以使用show profiles命令查看SQL语句详情的执行时间了，如下图所示：
这样我们就可以查看每条SQL语句的耗时情况，我们可以通过上面的Query_ID详细地查看SQL语句各阶段的耗时情况、CPU使用情况，可以执行如下代码：
# show profile for query Query_ID; # 查看SQL语句各阶段的耗时情况
# show profile cpu for query Query_ID; # 查看SQL语句CPU使用情况
如下图所示：
explain执行计划
在上面的SQL性能分析中，我们只能获取到SQL语句的频率和耗时时间，无法知道SQL语句的执行过程中的表连接情况，这时我们可以通过explain或desc命令来查看SQL语句的执行过程中的表连接情况，其使用方法如下：
explain/desc SQL语句;
如下图所示：
其中：
-
ID：select查询的序列号，表示查询中执行select子句或者操作表的顺序，ID相同，执行顺序从上到下，ID不同，值越大，越先执行；
-
select_type：SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBOUERY(SELECT/WHERE之后包含了子查询)；
-
type：连接类型，性能由好到差的类型为：NULL、system、const、eq_ref、range、index、all；
-
possible_keys：可能用到的索引，一个或多个；
-
key：实际使用的索引，如果为NULL，则没有使用索引；
-
Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好；
-
rows：MySQL认为必要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的；
-
filtered：返回结果的行数占需读取行数的百分比，filtered的值越大越好；
好了，SQL性能分析就讲到这里了。
公众号：白巧克力LIN
该公众号发布Python、数据库、Linux、Flask、Django、自动化测试、Git、算法、前端、服务器等相关文章！
- END -