铁匠 铁匠
首页
收藏
java
架构之路
常用算法
  • Java
  • nginx
  • 系统运维
  • 系统安全
  • mysql
  • redis
参考文档
关于
链接
  • 分类
  • 标签
  • 归档

专注、不予评判地关注当下
首页
收藏
java
架构之路
常用算法
  • Java
  • nginx
  • 系统运维
  • 系统安全
  • mysql
  • redis
参考文档
关于
链接
  • 分类
  • 标签
  • 归档
  • mysql

    • MySQL-InnoDB 存储引擎概述
    • MySql 索引
    • MySql 事务
    • MySql 日志系统(WAL)
    • MySql order by 的执行过程与优化
    • MySQL 性能分析与常见性能问题总结
      • 性能分析工具
        • slow log 慢查询日志
        • explain 查看执行计划
        • show profile 分析每一步的耗时
        • 系统性能监控表performance_schema
      • 导致 sql 慢的常见原因和解决方案
        • MySQL本身压力过大,cpu 使用率和 ioutil 过高
        • 刷脏页,导致同一个 sql 偶尔变慢
        • mdl 锁导致一个简单的查询
        • 索引失效
    • mysql推荐使用规范
    • MySQL 常用配置整理
    • mysql常用命令整理
    • mycli-强大的MySQL命令行客户端
  • redis

  • 数据库
  • mysql
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 order by 的执行过程与优化 mysql推荐使用规范→

最近更新
01
策略模式
01-09
02
模板方法
01-06
03
观察者模式
01-06
更多文章>
Theme by Vdoing | Copyright © 2016-2023 铁匠 | 粤ICP备15021633号
  • 跟随系统
  • 浅色模式
  • 深色模式
  • 阅读模式