MySQL 性能分析备忘录
[TOC]
最后修改时间: 2019年10月21日15:08:59
救急
show full processlist
查看当前线程处理情况, 确认当前有哪些语句在执行, 执行情况如何.
特别注意执行时间长的, 如果确定有问题, 那么可以使用 kill {id} 干掉该连接
show full processlist 等价于以下语句
select id, db, user, host, command, time, state, info from information_schema.processlist order by time desc;
注意:
show processlist时, root账号可以看到所有账号的连接, 如果是普通账号则只能看到自己的连接.- 如果显示长度太长看的很乱, 可以使用
\G, 即show full processlist\G, 来将显示结果纵向输出, 方便查看.
扩展.
\G等价于分号
\G是将显示的表格以纵向输出, 方便查看.
Slow Query Log 慢查询日志
慢查询日志用于记录执行时间超过指定阀值的SQL命令.
确认开启情况
mysql> show variables like 'slow_query_log%'; +---------------------+------------------------------------------------+ | Variable_name | Value | +---------------------+------------------------------------------------+ | slow_query_log | ON | | slow_query_log_file | C:\laragon\data\mysql\DESKTOP-C1GGBS1-slow.log | +---------------------+------------------------------------------------+ mysql> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+
开启方式
配置文件
############### 慢查询日志 ################ # 打开慢查询日志 slow_query_log=1 # 日志记录位置 log_output=file # 慢查询日志记录文件 slow_query_log_file=/var/run/mysqld/mysqld-slow.log # 慢查询时间阀值 long_query_time=10
命令方式(mysqld实例重启后失效)
-- 必须全局开启慢查询日志记录 set global slow_query_log=1; -- 设置慢查询时间阀值 set global long_query_time=1;
如果想要分析一些语句的执行, 则可以考虑将当前Session的慢查询时间阀值设为0
set long_query_time=0;
注意
当数据库被拖垮时(高负载), 任何简单的语句都可能执行超时, 此时的慢查询日志能提供的帮助就有限了.
慢查询日志分析工具
mysqldumpslow
在实际生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
# 分析慢日志
mysqldumpslow -a -n 50 -s c /var/run/mysqld/mysqld-slow.log
# 参数说明
--verbose 版本
--debug 调试
--help 帮助
-v 版本
-d 调试模式
-s ORDER 排序方式, 默认是 'at'
what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time 平均锁定时间
ar: average rows sent 平均返回记录数
at: average query time 平均查询时间
c: count 访问计数
l: lock time 锁定时间
r: rows sent 返回记录
t: query time 查询时间
-r 反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM 显示前N条
-a 不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN 正则匹配；grep: only consider stmts that include this string
-h HOSTNAME mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l 总时间中不减去锁定时间；don't subtract lock time from total time示例
得到返回记录集最多的10个SQL。 mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log 得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log 得到按照时间排序的前10条里面含有左连接的查询语句。 mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。 mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
SQL Explain
对于慢查询日志中执行慢的语句分析其 SQL语句的执行计划
EXPLAIN 可以帮助了解:
- 数据表的读取顺序
- SELECT子句的类型
- 数据表的访问类型
- 可使用的索引 possible_keys
- 实际使用的索引 key
- 使用的索引长度 ken_len
- 上一个表的连接匹配条件
- 被优化器查询的行的数量
- 额外的信息(如使用使用外部排序, 是否使用临时表)
举例
EXPLAIN 结果列分析
<u>字段 id</u>
SQL 执行顺序是根据
- id从大到小执行
- id相同时按照顺序从上往下执行.
<u>字段 select_type(查询类型)</u>
| 查询类型 | 说明 |
|---|---|
| SIMPLE | 简单查询 不包含UNION查询或子查询 |
| PRIMARY | 最外层查询 查询中若 包含任何复杂的子部分，最外层查询则被标记为PRIMARY |
| SUBQUERY | 子查询 在 SELECT 或 WHERE 中包含了子查询 |
| DEPENDENT SUBQUERY | !!! 子查询, 但依赖于外层查询的结果 注意确认, 避免大表驱动小表 |
| DERIVED | 子查询 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) |
| UNION | 联合 UNION 若第二个SELECT出现在UNION之后，则被标记为UNION |
| UNION RESULT | 使用联合的结果 从UNION表获取结果的SELECT |
关于UNION, 网上有写以下这段, 但我个人不理解UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
<u>字段 table(数据表)</u>
访问的数据表
<u>字段 partitions(分区)</u>
匹配的分区
<u>字段 type(访问方式)</u>
查询时的访问方式, 性能：all < index < range < index_merge < ref < eq_ref < system/const
一般来说至少需要保证访问方式是 range, 最好是 ref 级别.
| 访问方式 | 说明 |
|---|---|
| ALL | 全表扫描，对于数据表从头到尾找一遍select * from tb1;特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from tb1 where email = '[email protected]'select * from tb1 where email = '[email protected]' limit 1;虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 |
| INDEX | 全索引扫描，对索引从头到尾找一遍 因为非主键索引树比较小, 所以会比 ALL 更快 |
| RANGE | 对索引列进行范围查找 通常是在索引树上快速定位到某一索引项, 再向左/右遍历. |
| INDEX_MERGE | 合并索引，使用多个单列索引搜索, 最后结果取交集或并集 比如使用了UNION 且单独用到了两个索引. |
| REF | 使用索引快速定位(根据索引查找一个或多个值), 该索引是 普通索引 或 唯一索引的部分前缀 |
| EQ_REF | 使用主键索引或唯一索引快速定位 通常出现在多表的join查询, 连接时使用primary key 或 unique 索引(都只能匹配到一行记录) |
| CONST | 通过主键或唯一索引精确查找到一行 常量 表最多有一个匹配行(主键或唯一索引),因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次 |
| SYSTEM | 系统 表仅有一行, 这是const联接类型的一个特例, 可以忽略这种情况。 |
Q. ALL 和 INDEX 的区别
A. 两个都是全索引扫描, 不同的是 ALL 是对主键索引扫描, INDEX 是对非主键索引扫描.
这里要理解, 所谓的全表扫描指的是对主键索引扫描.
Q. EQ_REF 和 CONST 的区别
A. 相同点都是使用主键/唯一索引精确查找到行记录. 不同点在于:
CONST查询条件通常是索引列 = 具体常量值EQ_REF通常是在多表关联查询时作为连接条件使用.
<u>字段 possible_keys(候选索引)</u>
<u>字段 key(实际使用的索引)</u>
如果是合并索引(INDEX_MERGE), 则此处可能存在超过1个的key
<u>字段 key_len(使用索引的实际长度)</u>
该字段可以评估组合索引是否完全被使用或仅仅是最左前缀被用到.
该字段显示的值为索引字段的最大可能长度, 并非实际使用长度.(即 key_len 是根据表定义计算, 而非表内检索)
计算规则
| 字段类型 | 计算方式 | |
|---|---|---|
| 字符串 | char(n) | n字节长度 |
| varchar(n) | 若是utf8编码, 则是 n3 + 2 字节 如果是 utf8mb4 编码, 则是 4 n + 2 字节. | |
| 数值 | tinyint | 1字节 |
| smallint | 2字节 | |
| mediumint | 3字节 | |
| int | 4字节 | |
| bigint | 8字节 | |
| 时间 | date | 3字节 |
| timestamp | 4字节 | |
| datetime | 8字节 |
如果对应索引字段允许为 null, 则还要额外消耗1个字节来存储 NULL.
<u>字段 ref</u>
表示索引的查找条件, 可能是常量(const) 或 联合查询中另一张表的某个字段.
<u>字段 row(扫描行数)</u>
估算的需要扫描的行数, 注意是估算的.
在某些情况下若索引统计信息偏差较大, 则此处的预估扫描行数也会过大, 导致影响查询计划的选择.
可以使用 SHOW INDEX FROM 表名 来查看索引统计信息
可以使用 ANYLYSIS TABLE 表名来重新统计索引信息.
<u>字段 filtered</u>
<u>字段 Extra(额外信息)</u>
该列包含MySQL解决查询的详细信息
| 值 | 说明 |
|---|---|
| Using filesort | mysql无法依靠索引直接获取有序记录, 而是对结果进行额外排序. mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成 explain不会告诉你mysql将使用哪一种文件排序 也不会告诉你排序会在内存里还是磁盘上完成。 |
| Using index | 使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 |
| Using index condition | 索引下推优化, 5.6新增特性 |
| Using temporary | 意味着mysql在对查询结果排序时会使用一个临时表 |
| Using where | 使用了 where 过滤 这意味着mysql服务器将在存储引擎检索行后再进行过滤 许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验 因此不是所有带where子句的查询都会显示“Using where”。 有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 |
| Range checked for each record(index map: N) | 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。 |
| using join buffer | 在表联结时, 使用了连接缓存 |
Profiling
写在最前: SHOW PROFILE 命令将被弃用, 注意, 仅仅是
SHOW PROFILE命令弃用.替代方案是从 information_schema 中的profiling数据表进行查看, 可参数链接:
SHOW PROFILE 命令方式(旧)
查看语句执行的时间在各个步骤的开销
show profile 分析SQL性能工具(检测数据存在于临时表中)
- 在会话级别开启profile
SET profiling=1; - 发送sql
查看profile的资源开销结果
show profiles查看所有的分析结果(会有一个数量上限)SHOW PROFILE查看最后一条执行语句的分析结果show profile for query <id>查看指定执行语句的详细分析结果show profile cpu, block io for query <id>查看详细信息, 且包含 cpu, block.io 执行时间
- 关闭profile
这一部分更详细的可以参考如:
information_schema .profiling
待用到时补充.