1. 数据库服务器的优化步骤当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。整个流程划分成了 观察（Show status）
和 行动（Action）
两个部分。字母 S 的部分代表观察（会使
用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。
详细解释一下这张图:
首先在S1部分，我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，比如双十一、促销活动等。这样的话，我们可以通过A1这一步骤解决，也就是加缓存，或者更改缓存失效策略。如果缓存策略没有解决，或者不是周期性波动的原因，我们就需要进一步分析查询延迟和卡顿的原因。接下来进入S2这一步，我们需要开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。我们可以通过设置long_query_time参数定义“慢"的阈值，如果SQL执行时间超过了long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。在S3这一步骤中，我们就知道了执行慢的sQL，这样就可以针对性地用EXPLAIN查看对应SQL语句的执行计划，或者使用show profile查看SQL中每一个步骤的时间成本。这样我们就可以了解SQL查询慢是因为执行时间长，还是等待时间长。如果是SQL等待时间长，我们进入A2步骤。在这一步骤中，我们可以调优服务器的参数，比如适当增加数据库缓冲池等。如果是SQL执行时间长，就进入A3步骤，这一步中我们需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。如果A2和A3都不能解决问题，我们需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了性能瓶颈，进入A4阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等。以上就是数据库调优的流程思路。如果我们发现执行SQL时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的SQL，这三种分析工具你可以理解是SQL调优的三个步骤:慢查询、EXPLAIN和SHOW PROFILING。结论：
2. 查看系统性能参数
在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。SHOW STATUS语句语法如下：
SHOW [GLOBAL|SESSION] STATUS LIKE '参数';
一些常用的性能参数如下：
• Connections：连接MySQL服务器的次数。
• Uptime：MySQL服务器的上线时间。
• Slow_queries：慢查询的次数。
• Innodb_rows_read：Select查询返回的行数
• Innodb_rows_inserted：执行INSERT操作插入的行数
• Innodb_rows_updated：执行UPDATE操作更新的行数
• Innodb_rows_deleted：执行DELETE操作删除的行数 • Com_select：查询操作的次数。
• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
• Com_update：更新操作的次数。
• Com_delete：删除操作的次数。
举例说明：
3. 统计SQL的查询成本：last_query_cost一条SQL查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MysQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。如果我们想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的
last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL语句所需要读取的页的数量
。我们依然使用第 student_info 表为例：如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;
运行结果（1 条记录，运行时间为 0.042s ）然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;
运行结果（100 条记录，运行时间为 0.046s ）：然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+
你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间
基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然 页 数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并 没有增加多少查询时间 。
使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。
SQL查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论:
位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的(差不多10ms)，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。
4. 定位执行慢的 SQL：慢查询日志
MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。
它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。
4.1 开启慢查询日志参数
1. 开启slow_query_log
相关命令：
#查询慢查询日志开启状态
show variables like '%slow_query_log';
#开启慢查询日志
set global slow_query_log= OFF;
#关闭慢查询日志
set global slow_query_log= ON;
#查询慢查询日志超时时间
show variables like '%long_query_time%';
#设置超时时间是1s
set global long_query_time = 1;
show global variables like '%long_query_time%';
set long_query_time=1;
show variables like '%long_query_time%';
#显示自上次MySQL服务器启动以来执行的慢查询的数量。慢查询是指执行时间超过了long_query_time变量设置的阈值的查询。
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
#本条sqlname字段上没有建立索引,所以查询速度慢,用时6.7s
SELECT name,count(class_id) FROM student.student_info group by name
#查询慢查询日志数量
show status like 'slow_queries';
#查看是否开启以及日志的位置
show variables like '%slow_query_log%';
# 知道慢查询日志的位置是 ：/var/lib/mysql/ca6eb32875f2-slow.log
#查看索引
SHOW INDEX FROM student_info
#删除索引
DROP INDEX idx_sid ON student.student_inf
#查看是否开启以及日志的位置
show variables like '%slow_query_log%';
如下的方式相较于前面的命令行方式，可以看作是永久设置的方式。
修改my.cnf文件，[mysqld]下增加或修改参数long_query_time、slow_query_log和slow_query_log_file后，然后重启MySQL服务器。
[mysq1d]
slow_query_log=ON # 开启慢査询日志的开关
slow_query_log_file=/var/lib/mysql/atguigu-slow.1og #慢査询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
1og_output=FILE
如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname-slow.log。
4.2 查看慢查询数目
查询当前系统中有多少条慢查询记录
#显示自上次MySQL服务器启动以来执行的慢查询的数量。
#慢查询是指执行时间超过了long_query_time变量设置的阈值的查询。
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
结果如下：
4.3 案例演示：
(表创建过程参考上篇文章：适合创建索引的11种情况)
#本条sqlname字段上没有建立索引,所以查询速度慢,用时6.7s
SELECT name,count(class_id)
FROM student.student_info
group by nam
执行结果：
2. 分析
#查询慢查询日志数量
show status like 'slow_queries';
补充说明:
除了上述变量，控制慢查询日志的还有一个系统变量: min_examined_row_limit。这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time的值，那么，这个查询就被记录到慢查询日志中;反之，则不被记录到慢查询日志中。
mysgl> show variables like 'min%';
+------------------------+-------+
Variable_name十---- | Value|
+------------------------+-------+
min_examined_row_limit | 0 |
+------------------------+-------+
1 row in set, 1 warning (8.88 sec)
这个值默认是0。与long_query_time=10合在一起，表示只要查询的执行时间超过10秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。你也可以根据需要，通过修改“my.ini"文件，来修改查询时长，或者通过SET指令，用SQL语句修改“min_examined_row_limit”的值。
4.5 慢查询日志分析工具：mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
。查看mysqldumpslow的帮助信息,(不要在navicate中执行，在命令行中执行)
mysqldumpslow --help
执行结果如下：我用的docker测试的，（执行位置是docker -exec -it mysql容器id /bin/bash）
mysqldumpslow 命令的具体参数如下：
a: 不将数字抽象成N，字符串抽象成S
s: 是表示按照何种方式排序：
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 （默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；
举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：
root@ca6eb32875f2:/# mysqldumpslow -a -s t -t 5 /var/lib/mysql/ca6eb32875f2-slow.log
Reading mysql slow query log from /var/lib/mysql/ca6eb32875f2-slow.log
Count: 133 Time=8.68s (1154s) Lock=0.00s (0s) Rows=1.0 (133), root[root]@[118.25.139.98]
SELECT N
Count: 83 Time=7.49s (621s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[118.25.139.98]
SET SQL_SELECT_LIMIT=DEFAULT
Count: 25 Time=16.13s (403s) Lock=6.89s (172s) Rows=0.0 (0), root[root]@[118.25.139.98]
SELECT * FROM config_info_beta WHERE id = N
Count: 15 Time=13.10s (196s) Lock=1.56s (23s) Rows=1.0 (15), root[root]@[118.25.139.98]
SELECT count(*) FROM users WHERE N=N
Count: 13 Time=10.84s (140s) Lock=1.01s (13s) Rows=1.0 (13), root[root]@[118.25.139.98]
SELECT count(*) FROM permissions WHERE role= 'S'
工作常用参考：
#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/ca6eb32875f2-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/ca6eb32875f2-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/ca6eb32875f2-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/ca6eb32875f2-slow.log | more
定位慢查询sql
4.6 关闭慢查询日志
方式1：永久性方式
修改my.cnf或者my.ini文件，把[mysqld]组下的slow_query_log值设置为OFF，修改保存后，再重启MysQL服务,即可生效;
[mysqld] slow_query_log=OFF
重启MySQL服务，执行如下语句查询慢日志功能
SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长
方式2：临时性方式
使用SET语句来设置。（1）停止MySQL慢查询日志功能，具体SQL语句如下。
SET GLOBAL slow_query_log=off;
4.7 删除慢查询日志
从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件即可。使用命令mysqladmin flush-logs来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件。
mysqladmin -uroot -p flush-logs slow
提示慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。
