Go after
0%

Mysql性能分析和优化

发表于 更新于 分类于 阅读次数：
本文字数： 10k 阅读时长 ≈ 9 分钟

查询慢查询日志

Mysql性能分析和优化

性能分析，优化，监控工具

1.性能分析

慢查询日志，profile性能监控，performance_schema性能监控

1.1慢查询日志

分析：

  1. 通过慢查询日志获取查询时间较长的SQL
  2. 查看SQL的执行计划explain
  3. 使用show profile[s] 查看SQL的性能使用情况

注意：开启慢查询日志影响mysql性能，排查问题时再开启。

1.1.1开启慢查询日志

  1. 连接服务器

    工具：FinalShell

  2. 登录mysql

  3. 慢查询日志

    1. 开启
    2. 设置参数

  4. 查看慢查询日志

查看慢查询信息
1
2
## 登录mysql
show variables like '%slow%';
慢查询设置（开启、收集条件）
临时

重启失效

1
2
3
4
5
6
7
8
9
## 开启
set global slow_query_log='ON';
## 设置慢查询日志存放位置
set global slow_query_log_file='/usr/local/mysql/data/slow.log';
## 设置收集慢查询SQL时间
set global long_query_time= 1;

## 查看收集慢查询SQL时间
show global variables like 'long_query_time';
永久

修改配置文件

1
2
3
4
5
my.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1

1.1.2分析慢查询日志

mysqldumpslow工具

mysql自带

1
2
# 按照查询时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
1
2
3
4
5
6
7
8
9
10
-s 表示按照何种方式排序
    c 访问次数
    l 锁定时间
    r 返回记录
    t 查询时间
    al 平均锁定时间
    ar 平均返回记录数
    at  平均查询时间
-t 返回前面多少条数据
-g 搭配正则匹配模式，大小写不敏感
percona-toolkit工具
安装
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
yum -y install perl-DBD-MySQL
# 安装perl-Digest-MD5工具
yum -y install perl-Digest-MD5

yum -y install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

cd /usr/local/src
# 下载percona-toolkit 工具包
wget percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit.tar.gz
cd /usr/local/src/percona-toolkit-3.1.0

# 安装perl模块，制定依赖路径
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
make && make install
分析
1
2
3
4
5
# 执行工具pt-query-digest
cd /usr/local/percona-toolkit/bin
./pt-query-digest /usr/local/src/slowsqlExample/slow0312.log 
# 下载文件到本地
sz slow0312.log

常用命令

  1. 分析慢查询文件

    1
    pt-query-digest  slow.log > slow_report.log

    报告：

    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
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    第一部分
    该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
    # 95.2s user time, 1.9s system time, 1.09G rss, 1.17G vsz
    工具执行时间
    # Current date: Thu Jun 10 16:45:43 2021
    运行分析工具的主机名    
    # Hostname: iZbp15xr4imyrogpz1216f9Z
    被分析的文件名
    # Files: /usr/lib/mysql/slow.log
    语句总数量，唯一的语句数量，QPS，并发数
    # Overall: 38.97k total, 321 unique, 0.03 QPS, 0.06x concurrency _________
    日志记录的时间范围
    # Time range: 2021-05-25T07:31:57 to 2021-06-10T08:45:02
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time         76953s      1s    467s      2s      3s      9s      1s
    # Lock time           729s       0     29s    19ms   214us   546ms   119us
    # Rows sent         98.47M       0   1.68M   2.59k    0.99  46.83k    0.99
    # Rows examine      57.71G       0   1.69M   1.52M   1.61M 303.67k   1.53M
    # Query size         1.34G       6  29.15M  35.96k   84.10 477.84k   84.10

    第二部分
    # Profile
    Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定
    Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
    Response：总的响应时间
    time：该查询在本次分析中总的时间占比
    calls：执行次数，即本次分析总共有多少条这种类型的查询语句
    R/Call：平均每次执行的响应时间
    V/M：响应时间Variance-to-mean的比率
    Item：查询对象
    # Rank Query ID                        Response time   Calls R/Call V/M   
    # ==== =============================== =============== ===== ====== ===== 
    #    1 0xABD1DCCCCD5AA5128E10C27B34... 1246.6948 41.7%   283 4.4053  0.04 UPDATE ziweidashi_deviceinfo
    #    2 0x6914B81AAD1785E50708ABD113...  877.6900 29.3%   339 2.5891  0.09 SELECT birthDay_notify
    #    3 0x44D9474C6D5C58DD07B5FEEA0D...  299.4193 10.0%    71 4.2172  0.05 SELECT tmall_product_orders
    #    4 0xA9BE84CBE3DAA9B1CDD9B5A9EC...  127.0137  4.2%    46 2.7612  0.04 SELECT daily_user_action_log
    #    5 0xCF0E12117C971C3013142E3717...  118.3138  4.0%    49 2.4146  0.05 SELECT tmall_user_take_coupon_record
    #    6 0x94263184D24186330B13193534...   97.0805  3.2%    35 2.7737  0.56 SELECT tgg_users
    #    7 0xC51165F1287A2ECDA221AC1F54...   52.5870  1.8%    22 2.3903  0.04 SELECT util_user_task_log
    #    8 0xB8004D6D8A7A7967E04CD81E26...   43.7895  1.5%    16 2.7368  0.08 SELECT daily_user_action_log
    #    9 0x910E19224F33DAA6391927B8E8...   41.3720  1.4%    15 2.7581  1.17 SELECT qifugong_tianbi_record
    # MISC 0xMISC                            86.7871  2.9%    30 2.8929   0.0 <12 ITEMS>


    第三及后续部分，第一条查询语句 query id：0xABD1DCCCCD5AA5128E10C27B34BC04E7
    # Query 1: 0.01 QPS, 0.03x concurrency, ID 0xABD1DCCCCD5AA5128E10C27B34BC04E7 at byte 355748
    # Scores: V/M = 0.04
    # Time range: 2020-03-11 12:24:03 to 2020-03-12 00:16:13
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         31     283
    # Exec time     41   1247s      4s      8s      4s      5s   437ms      4s
    # Lock time     69   386ms    24us   371ms     1ms    93us    21ms    44us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine  18 180.00M 651.14k 651.45k 651.29k 650.62k       0 650.62k
    # Query size    10  27.64k     100     100     100     100       0     100
    # String:
    数据库名
    # Databases    taxen_ziweidashi
    执行主机
    # Hosts        118.190.93.166
    执行用户
    # Users        devAccount
    查询时间占比
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms
    #    1s  ################################################################
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `taxen_ziweidashi` LIKE 'ziweidashi_deviceinfo'
    #    SHOW CREATE TABLE `taxen_ziweidashi`.`ziweidashi_deviceinfo`
    UPDATE ziweidashi_deviceinfo
             SET expired = 1 
            WHERE createTime   <=   1583942580685\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select  expired = 1 from ziweidashi_deviceinfo where  createTime   <=   1583942580685\G

    # Query 2: 0.03 QPS, 0.07x concurrency, ID 0x6914B81AAD1785E50708ABD11319E02E at byte 13829
    # Scores: V/M = 0.09
    # Time range: 2020-03-11 12:22:13 to 16:05:47
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         37     339
    # Exec time     29    878s      2s      4s      3s      4s   472ms      2s
    # Lock time      5    29ms    31us     4ms    86us    98us   229us    66us
    # Rows sent      0      24       0       2    0.07       0    0.32       0
    # Rows examine  67 665.20M   1.96M   1.96M   1.96M   1.96M       0   1.96M
    # Query size    59 154.47k     462     467  466.60  463.90    2.07  463.90
    # String:
    # Hosts        10.66.186.115
    # Users        root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms
    #    1s  ################################################################
    #  10s+
    # Tables
    #    SHOW TABLE STATUS LIKE 'birthDay_notify'
    #    SHOW CREATE TABLE `birthDay_notify`
    # EXPLAIN /*!50100 PARTITIONS*/
    select birthdayno0_.id as id1_1_, birthdayno0_.index_card_show_date as index_ca2_1_, birthdayno0_.userId as userId3_1_, birthdayno0_.push_content as push_con4_1_, birthdayno0_.card_content as card_con5_1_, birthdayno0_.birthday_userId as birthday6_1_, birthdayno0_.birthday_contactId as birthday7_1_, birthdayno0_.need_push as need_pus8_1_ from birthDay_notify birthdayno0_ where birthdayno0_.userId=1304747 and birthdayno0_.index_card_show_date='2020-03-11 00:00:00'

  2. 分析最近12小时内的查询

    1
    pt-query-digest  --since=12h  slow.log > slow_report_last12.log

  3. 分析制定时间范围内的查询

    1
    pt-query-digest slow.log --since '2017-02-07 09:30:00' --until '2017-02-07 10:00:00'> > slow_report_time.log

  4. 分析bin log

    1
    2
    mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
    pt-query-digest --type=binlog mysql-bin000093.sql > slow_report_bin.log

  5. 分析general log

    1
    pt-query-digest --type=genlog localhost.log > slow_report_general.log

1.2profile性能监控

MySQL5.6中已过时，使用performance_schema代替。13.7.5.31 SHOW PROFILE Statement

1.3performance_schema性能监控

参考：MySql8.027.19.1 Query Profiling Using Performance Schema

SQL在各阶段的耗时分析，默认开启

查看是否开启

SHOW VARIABLES LIKE ‘performance_schema’;

开启

1
2
3
# my.cnf
[mysqld]
performance_schema=ON

修改performance_schema监控配置，并分析一个测试SQL

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
# 查看performance_schema是否开启
SHOW VARIABLES LIKE 'performance_schema';

# setup_actors表的初始内容是匹配任何用户和主机，因此对于所有前台线程，默认情况下启用监视和历史事件收集功能
select * from `performance_schema`.setup_actors;

# 更新setup_actors表中的默认行，以禁用对所有前台线程的历史事件收集和监视，并插入一个新行，为运行查询的用户启用监视和历史事件收集:
UPDATE performance_schema.setup_actors
       SET ENABLED = 'NO', HISTORY = 'NO'
       WHERE HOST = '%' AND USER = '%';
			 
INSERT INTO performance_schema.setup_actors
       (HOST,USER,ROLE,ENABLED,HISTORY)
       VALUES('localhost','test_user','%','YES','YES');
# 收集者
# ​ instruments: 生产者，用于采集mysql中各种各样的操作产生的事件信息，对应配置表中的配置项我们可以称为监控采集配置项。
select * from performance_schema.setup_instruments;
		
# 开启收集者		
UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';


# 消费者
# ​ consumers:消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置项我们可以称为消费存储配置项。
select * from performance_schema.setup_consumers;

# 开启消费者
UPDATE PERFORMANCE_SCHEMA.setup_consumers 
SET ENABLED = 'YES' 
WHERE
	NAME LIKE '%events_statements_%';
	
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE
	NAME LIKE '%events_stages_%';		 

# 测试SQL语句
select * from dept;

# 查询测试SQL语句在events_statements_history_long中的id
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%select * from dept%';
# 查询测试SQL语句各模块执行耗时		 
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=258;

2.优化

表和字段结构设计，索引，SQL语句，Mysql配置，操作系统配置，硬件，内存预热

开发人员关注表结构设计、索引和SQL语句方面的优化。

DBA学习Mysql配置，操作系统配置，硬件

2.1表和字段结构设计

  • 减少关联查询，创建合理的冗余字段

  • 字段太多的大表，拆表

  • 表中不常用的字段或者存储数据比较多的字段，拆表。（商品表中的商品介绍字段单独拆解到另一个表中，使用ID关联）

  • 每张表都要有主键，主键最好是int类型。

  • 字段数据类型优先选用较小的数据结构。

    例如：年龄使用TINYINT，而不是INT

  • 日期类型使用int类型，日期排序以及对比等操作的效率会更高

2.2索引

数量不要超过6个。通过索引返回的数据量需要小于1/3才有效。

建立索引

  • on两边，where字段
  • 组合索引（最左前缀）
  • order排序
  • group by分组统计

避免失效

  • or，is null,is not null
  • between右边
  • where 子句中的“=”左边进行函数、算术运算或其他表达式运算
  • 不遵循组合索引最左前缀
  • 字符串不加’’(单引号)
  • like (%abc)，可通过组合索引解决

2.3SQL语句

SQL分析，SQL注意点

2.3.1SQL分析

SQL的执行计划explain，使用show profile[s] 查看SQL的性能使用情况

explian 详见Mysql索引

实例
  1. asdf
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    Count: 36240  Time=1.20s (43521s)  Lock=0.00s (4s)  Rows=1.0 (36240), root[root]@4hosts
    select IFNULL(max(zkkq_id), 1) from attendance_card;
    # attendance_card本身是有序的，从小到大
    # 优化 速度：1.2→0.1
    select ifnull( zkkq_id,1) from attendance_card order by id desc limit 1;

    # 判断表是否为空
    # 100万 0.6s
    select count(*) from table
    # 100万 0.01s
    select 1 from logmgr_log_history limit 1
  2. 索引失效。or、order by id limit
    1
    WHERE (branch_code = ? OR branch_name LIKE concat(?, ?, ?)) AND create_time >= ? AND create_time <= ? ORDER BY id DESC LIMIT ?
    or 导致branch_code失效。
    order by id desc limit 20 使create_time失效，而使用了id

2.3.2SQL优化

  1. 避免select *

  2. 日期条件字段使用bigint，存储时间戳

  3. left join on 后不加and, where 后加and

  4. 表索引多，没有走正确的索引时，user index(index_user_id) ,强制走索引

  5. 左连接注意SQL执行顺序，mysql会做优化，小表驱动大表，也可自己优化

  6. limit

    有截断效果（停止全表扫描），但还是在server层处理数据

    1
    2
    3
    4
    5
     一千万条数据
    # 速度很慢 10
    select * from user limit 9000000 for 10
    # 速度很快 0.03
    select * from user where id > 9000000 limit 10

  7. count
    count（*）找普通索引，即去最小的索引树遍历，包含null

    count（字段） ，走缓存，不包含null

    count（1） 包含null

  8. exists
    尽量使用NOT EXISTS替代NOT IN，使用EXISTS替代DISTINCT。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT DISTINCT r.*
    FROM task r
             LEFT JOINtask_sku_box box ON r.rece_id = box.rece_id
    WHERE box.sku_code = '1111';

    explain SELECT r.*
    FROM task r
    where exists(
            select 1 from task_sku_box box where r.rece_id = box.rece_id and sku_code = '1111'
        );

  9. sql索引字段上尽量不要使用函数，在Java中计算好，直接传入。

    1
    select * from user where create_time > now() -1
    1
    2
    // 当前时间前三个月
    LocalDateTime threeMonthsAgo = LocalDateTime.now().minusMonths(3);

2.4Mysql配置

Buffer Pool

默认128M，修改为系统内存的4/5，尽量从内存中读取。

buffer pool越大和存储引擎I/O次数越少。

1
2
mysql> show global status like 'innodb_buffer_pool_pages_%'; 
| Innodb_buffer_pool_pages_free | 0 | 0 表示已经被用光
1
2
# 修改配置 my.cnf
innodb_buffffer_pool_size =3000M

redo log file

默认8M,修改为Buffer的1/4.降低磁盘写入次数。

1
innodb_log_file_size = 750M

2.5操作系统设置

2.6硬件

固态硬盘

2.7内存预热

mysql监控工具

收费：阿里云

免费：druid连接池