共计 5770 个字符,预计需要花费 15 分钟才能阅读完成。
[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 = 'seven@live.com' select * from tb1 where email = 'seven@live.com' 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 数据表进行查看, 可参数链接:
- https://www.cnblogs.com/sideo…
- https://www.cnblogs.com/Jiang…
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
这一部分更详细的可以参考如:
- https://www.cnblogs.com/ggjuc…
information_schema .profiling
待用到时补充.