数据库优化及慢查询日志分析

在数据库调优中，我们的目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以快速帮我们找到调优的思路和方式。
一、数据库服务器的优化步骤
1、观察服务器状态是否存在周期性波动(双11，618等)，如果是执行2，否跳到3；
2、加缓存，更改缓存失效策略。如果解决则结束，否的话执行3；
3、仍有不规则延迟或卡顿，则开启慢查询，explain、show profiling ；如果是SQL等待时间过长，则执行4，否则(SQL执行时间过长)则跳到5；
4、调优服务器参数
5、索引设计优化；join表过多，需要优化；数据库表优化；
6、如果以上步骤都没解决问题，则检查SQL查询是否达到瓶颈；如果是执行7，否则跳到8；
7、读写分离(主从架构)；分库分表(垂直分库、垂直分表、水平分表)
8、重新检查
小结 数据路的调优主要从硬件、系统配置、数据库表结构、SQL及索引四个方面进行，成本依次递减、效果依次递增。
二、查看系统性能参数
在MySQL种，可以使用show status 语句查询一些MySQL数据库服务器的性能参数、执行频率。
show status 的语法如下：

show status [global | session] status like '参数'

一些常用的性能参数：
1、Connections：连接MySQL服务器的次数
2、Uptime：服务器上线的时间
3、Slow_queries：慢查询次数
4、Innodb_rows_read：select查询返回的行数
5、Innodb_rows_inserted：执行insert操作插入的行数
6、Innodb_rows_updated：执行update操作更新的行数
7、Innodb_ros_deleted：执行delete操作删除的行数
8、Com_select：查询操作的次数
9、Com_insert：插入操作的次数
10、Com_update：更新操作的次数
11、Com_delete：删除操作的次数
若查询MYSQL服务器的连接次数，则可执行以下语句

show status like 'Connections'

其他相关查询替换相关参数即可
三、统计SQL的查询成本：last_query_cost
一条SQL查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MYSQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。
如果我们要想查看某条SQL的查询成本，可以在执行完该SQL之后，通过查看当前会话中的last_query_cost变量来得到他的查询成本。它通常也是我们评价一个查询执行效率的常用指标，这个查询成本对应的值是指SQL语句所需要读取的数据页数量。查询完相关SQL后执行以下语句
show status like ‘last_query_cost’
值越大说明需要花费的成本越大
四、定位执行慢的SQL：慢查询日志
MySQL的慢查询日志，用来记录在MysQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。
它的主要作用是，帮助我们发现那些执行时间特别长的sQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询由志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
1、开启慢查询日志参数
在使用之前，需要先查看一下慢查询是否已经开启，执行以下命令：

show variables like 'slow_query_log';

如果值为on说明已经开启，否则执行以下命令开启慢日志查询

set global slow_query_log = on;

2、查看一下慢查询日志文件的位置

show variables like 'slow_query_log_file';

3、查询long_query_time的阈值，并修改指定的阈值
ong_query_time 既是一个全局变量又是一个会话变量，所以需要同时设置,若只设置全局，则需新建会话才能生效。

show global variables like 'long_query_time';  #全局
show variables like 'long_query_time';    #会话
set global long_query_time = 1; #全局
set long_query_time = 1;    #会话

**补充：**上面的操作只是对本次MySQL服务器有效，若服务器重启，则会失效。若想永久有效，需要修改MySQL的配置文件my.cnf

[mysqld]
slow_query_log=ON     #开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/localhost-slow.log    #慢查询日志的目录和文件名信息
long_query_time=3     #设置慢查询的阙值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

4、查询当前慢查询SQL的数量

show status like 'slow_queries';

5、关闭慢查询日志
方式一：临时性关闭
set global slow_query_log = off;
执行以上语句后，重启服务器即可。
方式二：永久性关闭
修改MySQL的配置文件my.cnf，将slow_query_log的值改为off,保存后，重启MySQL服务器即可
重启服务器命令：systemctl restart mysqld;

文章难免有所遗漏，详细的慢日志查询可以观看宋红康老师的视频教程，视频链接如下：
上半部分
下半部分