mysql优化分析

慢查询日志：

默认关闭，检测时开启，费性能

1.慢查询日志的查看开启

#1.查看开启状况：
show variables like "%slow_query_log%"
#2.开启慢查询日志(关机失效)
set global  slow_query_log = 1;
#3.查看慢查询设定阈值 单位 /秒
SHOW VARIABLES LIKE 'long_query_time%';
#4.设定慢查询阈值 单位/秒
set long_query_time=2  

----
#1.查看日志文件存放的位置

（2） 如永久生效需要修改配置文件 my.cnf 中[mysqld]下配置
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

（3） 运行查询时间长的 sql，打开慢查询日志查看

1.2 日志分析工具 mysqldumpslow

得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/日志文件名称

得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/日志文件名称

得到按照时间排序的前 10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/日志文件名称

另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/日志文件名称 | more

（1） 查看mysqldumpslow的帮助信息

explain 分析检测

explain + sql

profile分析调优

Show Profile是什么？

Show Profile：MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

分析步骤

1、是否支持，看看当前的MySQL版本是否支持。

# 查看Show Profile功能是否开启
mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2、开启Show Profile功能，默认是关闭的，使用前需要开启。

# 开启Show Profile功能
mysql> SET profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

3、运行SQL

SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000;

SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;

4、查看结果，执行SHOW PROFILES;

Duration：持续时间。

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.00156100 | SHOW VARIABLES LIKE 'profiling'                   |
|        2 | 0.56296725 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        3 | 0.52105825 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        4 | 0.51279775 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5   |
+----------+------------+---------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

5、诊断SQL，SHOW PROFILE cpu,block io FOR QUERY Query_ID;

# 这里的3是第四步中的Query_ID。
# 可以在SHOW PROFILE中看到一条SQL中完整的生命周期。
mysql> SHOW PROFILE cpu,block io FOR QUERY 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000097 | 0.000090 |   0.000002 |            0 |             0 |
| checking permissions | 0.000010 | 0.000009 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000039 | 0.000058 |   0.000000 |            0 |             0 |
| init                 | 0.000046 | 0.000046 |   0.000000 |            0 |             0 |
| System lock          | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000023 | 0.000037 |   0.000000 |            0 |             0 |
| preparing            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000041 | 0.000053 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.520620 | 0.516267 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000060 | 0.000051 |   0.000000 |            0 |             0 |
| end                  | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000032 | 0.000064 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

Show Profile查询参数备注：

• ALL：显示所有的开销信息。
• BLOCK IO：显示块IO相关开销（通用）。
• CONTEXT SWITCHES：上下文切换相关开销。
• CPU：显示CPU相关开销信息（通用）。
• IPC：显示发送和接收相关开销信息。
• MEMORY：显示内存相关开销信息。
• PAGE FAULTS：显示页面错误相关开销信息。
• SOURCE：显示和Source_function。
• SWAPS：显示交换次数相关开销的信息。

6、Show Profile查询列表，日常开发需要注意的结论：

• converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。
• Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。
• Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！
• locked：死锁。