mysql慢查询分析
1. 慢查询配置
开启慢查询日志
查询状态
show variables like 'slow_query_log'；
设置开启或关闭
set global slow_query_log = ['ON'/'OFF']；
指定慢查询日志log文件
set global slow_query_log_file = /var/lib/mysql/slowlog.log
设置超时则写入到慢查询日志中，可以有6位小数，到微秒；设置为0，则所有sql记录到慢查询日志中
set global long_query_time = xx.xxx秒
所有未使用索引的sql记录到日志中
set globallog_queries_not _using_indexes= ['ON'/'OFF']
持久化配置：my.cnf中配置慢查询配置
查询慢查询配置：show variables like '%query%';
2. mysqldumpslow命令分析慢查询
/*传入慢查询日志作为参数*/
mysqldumpslow[ OPTS... ][ LOGS...]
mysqldumpslow slowlog.log
3.0 函数及存储过程耗时分析
mysql分析sql语句耗时情况
mysql -h10.3.40.98 -uroot -p command
mysql> use viewdb;
mysql> set profiling=1;
call func();
show profiles;
4. 使用Percona Toolkit工具分析慢查询
/* Percona Toolkit工具 源码下载 */
/* 安装依赖 */
yum install perl-DBI.x86_64
yum install perl-DBD-MySQL.x86_64
yum install perl-IO-Socket-SSL.noarch
yum install perl-Digest-MD5.x86_64
yum install perl-TermReadKey.x86_64
yum install perl-CPAN
yum install perl-Time-HiRes
/* 下载源码 */
wget percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit.tar.gz
cd percona-toolkit-3.5.5
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
/* 将容器中的log复制出来 */
docker cp ae:/var/lib/mysql/slowlog.log /home/bk
/* 示例 */
pt-query-digest [OPTIONS][FILES][DSN]
pt-query-digest /home/bk/slowlog.log
5.监控长时间运行的SQL
/* 只能查询到自己账户下的sql */
SELECT id, 'user', 'host', DB, command, 'time', state, info FROM information_schema.PROCESSLIST WHERE TIME>=60;
6.EXPLAIN执行计划内容分析
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| id | select_type | table | partitions | type | possible keys | key | key_len | ref | rows | filtered | Extra |
6.1 ID
- ID表示查询执行的顺序
- ID相同时由上到下执行
- ID不同时，由大到小执行
6.2 select_type
| 值 | 含义 |
|---|---|
| SIMPLE | 不包含子查询或是UNION操作的查询 |
| PRIMARY | 查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARY |
| SUBQUERY | SELECT列表中的子查询 |
| DEPENDENT SUBQUERY | 依赖外部结果的子查询 |
| UNION | union操作的第二个或是之后的查询的值为union |
| DEPENDENTUNION | 当UNION做为子查询时，第二或是第二个后的查询的select_type值 |
| UNION RESULT | UNION产生的结果集 |
| DERIVED | 出现在FROM子句中的子查询 |
6.3 table
- 指明是从那个表中获取数据
- "unionM,N"由ID为MN查询union产生的结果集
- "derived N" 或者 "subquery N" 由ID为N的查询产生的结果
6.4 partitions
- 对于分区表，显示查询的分区ID
- 对于非分区表，显示NULL
6.5 type
| 性能 | 值 | 含义 |
|---|---|---|
| 高 | system | 这是const联接类型的一个特例,当查询的表只有一行时使用 |
| const | 表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式 | |
| eq_ref | 唯一索或主键引查找，对于每个索引键，表中只有一条记录与之匹配 | |
| ref | 非唯一索引查找，返回匹配某个单独值的所有行 | |
| ref or_null | 类似于ref类型的查询，但是附加了对NULL值列的查询 | |
| index_merge | 该联接类型表示使用了索引合并优化方法 | |
| range | 引范围扫描，常见于between、>、<这样的查询条件 | |
| index | FULLindex Scan全索扫描，同ALL的区别是，遍历的是索引树 | |
| 低 | DERIVED | 出现在FROM子句中的子查询 |
6.6 possible keys
- 指出查询中可能会用到的索引
6.7 key
- 指出查询时实际用到的索引
6.8 key_len
- 实际使用索引的最大长度
6.9 ref
- 指出那些列或常量被用于索引查找
6.10 rows
- 跟据统计信息预估的扫描的行数
6.11 filtered
表示返回结果的行数占需读取行数的百分比，越高越好
6.12 Extra
| 值 | 含义 |
|---|---|
| Distinct | 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作 |
| Not exists | 使用notexists来优化查询 |
| Using filesort | 使用文件来进行排序，通常会出现在order by 或group by查询中 |
| Using index | 使用了覆盖索引进行查询 |
| Using temporary | MySQL需要使用临时表来处理查询，常见于排序，子查询和分组查询 |
| Using where | 需要在MySQL服务器层使用WHERE条件来过滤数据 |
| select tables optimizedaway | 直接通过索引来获得数据，不用访问表 |
