MySQL性能优化

一、优化思路

  数据查询慢，不代表 SQL 语句写法有问题。 首先，我们需要找到问题的源头才能“对症下药”。用一张流程图展示 MySQL 优化的思路：

     从图中可以清楚地看出，导致数据查询慢的原因有多种，如：缓存失效，在此一段时间内由于高并发访问导致 MySQL 服务器崩溃；SQL 语句编写问题；MySQL 服务器参数问题；硬件配置限制 MySQL 服务性能问题等。

二、查看 MySQL 服务器运行的状态值

    如果系统的并发请求数不高，且查询速度慢，可以忽略该步骤直接进行 SQL 语句调优步骤。执行命令：

show status

   由于返回结果太多，此处不贴出结果。其中，再返回的结果中，我们主要关注 “Queries”、“Threads_connected” 和 “Threads_running” 的值，即查询次数、线程连接数和线程运行数。

  我们可以通过执行如下脚本监控 MySQL 服务器运行的状态值：

#!/bin/bash
while true
do
mysqladmin -uroot -p"密码" ext | awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}' >> status.txt
sleep 1
done

  执行该脚本 24 小时，获取 status.txt 里的内容，再次通过 awk 计算：每秒请求 MySQL 服务的次数

awk '{q=$1-last;last=$1}{printf("%d %d %d\n",q,$2,$3)}'>> status.txt

  如果观察的数据有周期性的变化，需要修改缓存失效策略。

三、获取需要优化的SQL语句

   方式一：查看运行的线程

   执行命令:

show processlist

  返回结果：

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info          
+----+------+-----------+------+---------+------+----------+------------------+
|  9 | root | localhost | test | Query   |    0 | starting | show processlist 
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

　从返回结果中我们可以了解该线程执行了什么命令/SQL 语句以及执行的时间。实际应用中，查询的返回结果会有 N 条记录。其中，返回的 State 的值是我们判断性能好坏的关键，其值出现如下内容，则该行记录的 SQL 语句需要优化：

Converting HEAP to MyISAM # 查询结果太大时，把结果放到磁盘，严重
Create tmp table #创建临时表，严重
Copying to tmp table on disk  #把内存临时表复制到磁盘，严重
locked #被其他查询锁住，严重
loggin slow query #记录慢查询
Sorting result #排序

   方式二：开启慢查询日志

   在配置文件 my.cnf 中的 [mysqld] 一行下边添加两个参数：

slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow-query.log
long_query_time = 2

log_queries_not_using_indexes = 1

　其中，slow_query_log = 1 表示开启慢查询；

              slow_query_log_file 表示慢查询日志存放的位置；

              long_query_time = 2 表示查询 >=2 秒才记录日志；

              log_queries_not_using_indexes = 1 记录没有使用索引的 SQL 语句。

  注意：slow_query_log_file 的路径不能随便写，否则 MySQL 服务器可能没有权限将日志文件写到指定的目录中。建议直接复制上文的路径。

 修改保存文件后，重启 MySQL 服务。在 /var/lib/mysql/ 目录下会创建 slow-query.log 日志文件。连接 MySQL 服务端执行如下命令可以查看配置情况。

show variables like 'slow_query%';

show variables like 'long_query_time';

  测试慢查询日志：

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

  打开慢查询日志文件

[root@localhost mysql]# vim /var/lib/mysql/slow-query.log
/usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2017-10-05T04:39:11.408964Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 2.001395  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1507178351;
select sleep(2);

　我们可以看到刚才执行了 2 秒的 SQL 语句被记录下来了。虽然在慢查询日志中记录查询慢的 SQL 信息，但是日志记录的内容密集且不易查阅。因此，我们需要通过工具将 SQL 筛选出来.

   MySQL 提供 mysqldumpslow 工具对日志进行分析。我们可以使用 mysqldumpslow --help 查看命令相关用法

   常用参数如下：

  -s：排序方式，后边接着如下参数
        c：访问次数
        l：锁定时间
        r：返回记录
        t：查询时间
    al：平均锁定时间
    ar：平均返回记录书
    at：平均查询时间
    -t：返回前面多少条的数据
    -g：翻遍搭配一个正则表达式，大小写不敏感

　案例:

获取返回记录集最多的10个sql
mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log

获取访问次数最多的10个sql
mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log

获取按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log

　　参见：https://juejin.im/post/59d83f1651882545eb54fc7e