铁匠
FengJianxin
2020-08-27
目录

MySQL 性能分析与常见性能问题总结

# 性能分析工具

# slow log 慢查询日志

开启 slow log。并将日志收集到 elk，定时监控 elk 数据，根据实际情况针对不同查询时长设置不同阀值，并发出报警。

设置参数：

  • slow_query_log - 是否开启 slow log
    show variables like '%slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
    1
    2
    3
    4
    5
    6
  • long_query_time - 超过long_query_time时长的 sql 会记录到 slow log
    show variables like '%long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
    1
    2
    3
    4
    5
    6
    通常 C 端系统会设置为0.1（100毫秒），不同系统应该根据实际情况而定。

# explain 查看执行计划

通过 slow log，我们可以监控到执行时间较长的 sql。通过explain可以分析 sql 慢的原因。

explain SELECT * FROM tabee_name  WHERE province = '1' AND city = '1' AND sex = 2;
+----+-------------+-------------+------------+------+-----------------------+-----------------------+---------+-------------------+--------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys         | key                   | key_len | ref               | rows   | filtered | Extra       |
+----+-------------+-------------+------------+------+-----------------------+-----------------------+---------+-------------------+--------+----------+-------------+
| 1  | SIMPLE      | tabee_name  | <null>     | ref  | idx_province_city_sex | idx_province_city_sex | 190     | const,const,const | 275170 | 100.0    | Using index |
+----+-------------+-------------+------------+------+-----------------------+-----------------------+---------+-------------------+--------+----------+-------------+
1
2
3
4
5
6

explain 主要字段含义

官网说明：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html (opens new window)

  • id - 执行计划序号，序号越大越先执行。需要相同则从上往下执行。如果是联合查询，会有多个。

  • select_type: 查询类型

    select_type 说明
    SIMPLE 普通查询，没有联合查询或子查询
    PRIMARY 主查询，包含子查询 sql 的最外层
    SUBQUERY where column = (子查询) 条件中的子查询
    DERIVED 派生表查询，select from (子查询) 中的子查询
    UNION sql 中 UNION 后面的查询
    UNION RESULT UNION 的结果

  • table: 查询表名

  • partitions: 分区表信息

  • type: 单表访问方式

    type 说明
    null 查询过程不需要访问表
    system/const 通过一次扫描就查到了数据，system 是 const 的一个特例
    eq_ref 唯一索引扫描，常见于主键索引和唯一索引查询
    ref 非唯一索引扫描，符合最左前缀原则的扫描也包含在内
    ref_or_null 与 ref 类似，但是可以用于 null 值搜索，通常是有 column is null 的查询条件
    index_merge 使用了索引合并优化
    range 索引范围扫描，通常 where 条件中有 <, >, between
    index 全索引扫描
    all 全表扫描

    执行效率：

    null > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > all
    1

    附：《阿里巴巴-Java开发手册-嵩山版》 五 - （二）索引规约 - 8

    提示

    【推荐】SQL性能优化的目标：至少要达到range级别，要求是ref级别，如果可以是consts最好。

    说明：

    1. consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
    2. ref 指的是使用普通的索引（normal index）。
    3. range 对索引进行范围检索。

    反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大

  • possible_keys: 可能使用到的索引，但优化器分析后并不一定会使用

  • key: 实际使用到的索引，如果为 null 表示没有使用索引

  • key_len: 当前使用的索引长度（字节数）

  • ref: 使用哪些列或者常数从索引中查找

  • rows: 预估扫描行数

  • filtered: 匹配的行与扫描行数的百分比

  • Extra: 额外信息

    Extra 说明
    Using index 使用了覆盖索引
    Using filesort 查询需要排序

# show profile 分析每一步的耗时

explain 分析执行计划，只是在执行阶段，show profile（也可以通过 sql 查询 select * from information_schema.profiling） 可以查到 sql 执行的全部过程，包括 CPU、磁盘IO 和内存开销。

官方文档：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html (opens new window)

用法

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
  | ALL：显示所有开销信息
  | BLOCK IO：阻塞的输入输出次数
  | CONTEXT SWITCHES：上下文切换相关开销信息
  | CPU：显示CPU的相关开销信息 
  | IPC：接收和发送消息的相关开销信息
  | MEMORY ：显示内存相关的开销，目前无用
  | PAGE FAULTS ：显示页面错误相关开销信息
  | SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数) 
  | SWAPS：显示 swap 交换次数的相关开销信息
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

MySQL profiling 默认是关闭的，可以按下面步骤开启（只对当前 session 有效）

-- 查看是否支持 profiling
select @@have_profiling;
-- 查看是否开启了 profiling，0 表示未开启
select @@profiling;
-- 开启 profiling，只对当前 session 有效
SET profiling = 1;

-- 查看所有 profiling 会话
show profiles;
-- 查看上一个 profiling 会话
show profile;
-- 通过 query id 指定查看 profiling 会话
show profile for query 2
1
2
3
4
5
6
7
8
9
10
11
12
13

官方示例

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)

mysql> SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table       | 0.000056 |
| After create         | 0.011363 |
| query end            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)

mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating table       | 0.000056 | 0.000028 |   0.000028 |
| After create         | 0.011363 | 0.000217 |   0.001571 |
| query end            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

# 系统性能监控表performance_schema

官方文档：https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html (opens new window)

查看performance_schema是否开启（默认开启）

> show variables like 'performance_schema'
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1
2
3
4
5
6

开启监控项目

-- 事件采集配置
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
-- 事件存储配置
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';

-- 也可以只开启部分监控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE name LIKE 'wait%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE name LIKE '%wait%';
1
2
3
4
5
6
7
8

常用监控查询语句

# 查询执行最多的sql

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    AVG_TIMER_WAIT as '平均耗时',
    COUNT_STAR as '执行次数',
    SUM_ROWS_SENT as '结果行数',
    SUM_ROWS_EXAMINED as '扫描行数',
    FIRST_SEEN,
    LAST_SEEN 
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    SCHEMA_NAME NOT IN ( 'mysql', 'sys', 'performance_schema', 'information_schema' ) 
ORDER BY
    COUNT_STAR DESC 
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 查询平均耗时最长的sql

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    AVG_TIMER_WAIT as '平均耗时',
    COUNT_STAR as '执行次数',
    SUM_ROWS_SENT as '结果行数',
    SUM_ROWS_EXAMINED as '扫描行数',
    FIRST_SEEN,
    LAST_SEEN 
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    SCHEMA_NAME NOT IN ( 'mysql', 'sys', 'performance_schema', 'information_schema' ) 
ORDER BY
    AVG_TIMER_WAIT DESC 
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 查询扫描行数最多的sql

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    AVG_TIMER_WAIT as '平均耗时',
    COUNT_STAR as '执行次数',
    SUM_ROWS_SENT as '结果行数',
    SUM_ROWS_EXAMINED as '扫描行数',
    FIRST_SEEN,
    LAST_SEEN 
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    SCHEMA_NAME NOT IN ( 'mysql', 'sys', 'performance_schema', 'information_schema' ) 
ORDER BY
    SUM_ROWS_EXAMINED DESC 
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 查询结果行数最多的sql

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    AVG_TIMER_WAIT as '平均耗时',
    COUNT_STAR as '执行次数',
    SUM_ROWS_SENT as '结果行数',
    SUM_ROWS_EXAMINED as '扫描行数',
    FIRST_SEEN,
    LAST_SEEN 
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    SCHEMA_NAME NOT IN ( 'mysql', 'sys', 'performance_schema', 'information_schema' ) 
ORDER BY
    SUM_ROWS_SENT DESC 
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 查询没有使用过的索引

SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME 
FROM
    performance_schema.table_io_waits_summary_by_index_usage 
WHERE
    INDEX_NAME IS NOT NULL 
    AND COUNT_STAR = 0 
    AND OBJECT_SCHEMA NOT IN ( 'mysql', 'sys', 'performance_schema', 'information_schema' ) 
ORDER BY
    OBJECT_SCHEMA,
    OBJECT_NAME
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 查找使用最多的索引

SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    SUM_TIMER_WAIT / 1000000000000 as '总等待时间',
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE 
FROM
    performance_schema.table_io_waits_summary_by_index_usage 
WHERE 
    OBJECT_SCHEMA NOT IN ( 'mysql', 'sys', 'performance_schema', 'information_schema' )
ORDER BY
    SUM_TIMER_WAIT DESC 
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 查询执行错误sql

SELECT
    EVENT_NAME,
    SQL_TEXT,
    TIMER_WAIT / 1000000000000 as '等待时间',
    LOCK_TIME / 1000000000000 as '锁等待时间',
    MYSQL_ERRNO,
    TIMER_START
FROM
    performance_schema.events_statements_history 
WHERE
    MYSQL_ERRNO != 0
ORDER BY 
    TIMER_START DESC
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 查询物理IO最多的表

SELECT
    file_name,
    event_name,
    SUM_NUMBER_OF_BYTES_READ,
    SUM_NUMBER_OF_BYTES_WRITE 
FROM
    performance_schema.file_summary_by_instance 
ORDER BY
    SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC 
LIMIT 10
1
2
3
4
5
6
7
8
9
10

# 查询逻辑IO最多的表

SELECT
    object_name,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    SUM_TIMER_WAIT 
FROM
    performance_schema.table_io_waits_summary_by_table 
ORDER BY
    sum_timer_wait DESC
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11

# 查询每个阶段耗时情况

SELECT
    EVENT_ID,
    EVENT_NAME,
    SOURCE,
    TIMER_END - TIMER_START 
FROM
    performance_schema.events_stages_history_long 
WHERE
    NESTING_EVENT_ID = ${EVENT_ID};
1
2
3
4
5
6
7
8
9

# 查询每个阶段锁等待耗时情况

SELECT
    EVENT_ID,
    EVENT_NAME,
    SOURCE,
    TIMER_WAIT,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    OPERATION,
    NESTING_EVENT_ID 
FROM
    performance_schema.events_waits_history_long 
WHERE
    NESTING_EVENT_ID = ${EVENT_ID}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 查询最近执行的事务（包括回滚的）

SELECT
    EVENT_ID,
    EVENT_NAME,
    STATE,
    TRX_ID,
    AUTOCOMMIT,
    TIMER_WAIT,
    ACCESS_MODE,
    ISOLATION_LEVEL
FROM
    performance_schema.events_transactions_current
ORDER bY 
    TIMER_START DESC
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 导致 sql 慢的常见原因和解决方案

# MySQL本身压力过大，cpu 使用率和 ioutil 过高

解决方案：分库分表，多个实例分摊读写压力

# 刷脏页，导致同一个 sql 偶尔变慢

解决方案：

  1. 硬盘使用 SSD 提升 IOPS，并且把innodb_io_capacity参数设置为磁盘 IOPS
  2. 如果使用的是 SSD，参数innodb_flush_neighbors设置为 0（刷脏页的时候不要把相邻的脏页也一起刷掉），机械硬盘则设置为 1

# mdl 锁导致一个简单的查询

如：select * form user where id = 1很久没有返回

可以用show processlist或者select * from information_schema.processlist看一下 sql 执行状态，如果State字段是Waiting for table metadata lock，那就是被 mdl 锁住了

show processlist
+----+------+-----------------+------+---------+------+---------------------------------+------------------------------+
| Id | User | Host            | db   | Command | Time | State                           | Info                         |
+----+------+-----------------+------+---------+------+---------------------------------+------------------------------+
| 38 | root | localhost:64790 | test | Query   | 7    | Waiting for table metadata lock | select * from t where id = 1 |
+----+------+-----------------+------+---------+------+---------------------------------+------------------------------+
1
2
3
4
5
6

解决方案：

  1. 在业务低峰是执行 ddl，如果问题已经存在，先停止ddl语句
  2. 使用MySQL新特性（有版本要求，AliSQL分支的特性，MariaDB 10.3 和 MySQL 8.0 已经合并），ddl 无法获取 mdl 锁自动释放，参考：https://falseisnotnull.wordpress.com/2018/04/23/mysql-vs-mariadb-wait-nowait-skip-locked/ (opens new window)
    -- 无法获得mdl锁，立即放弃
ALTER TABLE table_name NOWAIT add column ...
-- 无法获得mdl锁，等到n秒后还是无法获得则放弃
ALTER TABLE table_name WAIT N add column ...
    1
    2
    3
    4
  3. 使用 online ddl 组件，如：github开源的gh-ost (opens new window)

# 索引失效

  1. 查询条件使用函数，导致无法使用索引

    解决方案：查询尽量不要使用MySQL函数，会导致无法使用索引或者全索引扫描

  2. 隐式类型转换或者隐式编码转换，导致无法使用索引

    • 隐式类型转换是指：查询条件中数据类型与表定义不一致，MySQL 会调用类型转换函数，导致无法使用索引，例如card_id字段定义是varchar(20)，查询条件是where card_id = 10000

      解决方案：查询条件使用相同的数据类型

    • 隐式编码转换是指：两个关联查询的表，字符编码不一致（如：select * from a, b where a.card_id = b.card_id，a 是utf8mb4，b 是utf8），查询的时候 MySQL 会调用编码转换函数，导致无法使用索引。

      解决方案：表定义时使用相同的字符编码

    注意

    如果是 update 语句，索引失效还会导致行锁升级为表锁，导致整个表数据无法更新，直到锁释放。

    如：update tb_name set count = 1000 where card_id = 1000，由于card_id是varchar类型，update 语句中没有使用',MySQL 会做类型转换，导致无法使用索引，而锁了全表。

    通过以下命令可以查看当前状态是否存在表锁

    show status like '%table_name%';
show open tables where In_use > 0;
    1
    2

  3. 查询条件有计算逻辑，会导致无法使用索引（如：where id + 1 = 10）

    解决方案：将计算放到=后面，如：where id = 10 - 1

  4. 使用不等于（!=,<>）条件，无法使用索引

  5. is null可以使用索引，is not null不能使用索引

#mysql#性能分析
MySql order by 的执行过程与优化
mysql推荐使用规范

← mysql推荐使用规范→

最近更新
01
策略模式
01-09
02
模板方法
01-06
03
观察者模式
01-06
更多文章>
Theme by Vdoing | Copyright © 2016-2023 铁匠 | 粤ICP备15021633号