MySQL慢查询日志及优化方法

字数统计: 3.7k字   |   阅读时长≈ 16分

目录

目录 Created by gh-md-toc

SQL语句优化

MySQL 慢查询日志开启及存储格式

作用

使优化的目的性更强

practice

慢查询日志开启

  • 慢查询是否开启
1
mysql> show variables like 'slow_query_log';
1
2
3
4
5
6
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.01 sec)
  • 记录使用索引的查询
1
2
3
4
5
mysql> show variables like '%log%';

| log_queries_not_using_indexes | off

mysql> set global log_queries_not_using_indexes=on;
  • 多久算慢
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> show variables like 'long_query_time';

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time=0.1;

# attention: 新打开会话窗口才生效
mysql> show variables like 'long_query_time';

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
  • 开启慢查询日志
1
mysql> set global slow_query_log=on;

查看日志

  • 查看日志位置
1
2
3
4
5
6
7
8
9
10
mysql> show variables like 'slow%';

+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_launch_time    | 2                                       |
| slow_query_log      | ON                                      |
| slow_query_log_file | /usr/local/var/mysql/linking-3-slow.log |
+---------------------+-----------------------------------------+
3 rows in set (0.00 sec)
  • 查询产生日志
1
mysql> select * from user;

操作业务系统，产生日志

  • 查看慢日志
1
2
3
4
5
6
7
8
9
10
11
12
# Linux
~> tail -50 /usr/local/var/mysql/linking-3-slow.log

    # Time: 2020-06-09T13:19:07.814701Z
    # User@Host: root[root] @ localhost []  Id:     9
    # Query_time: 0.002819  Lock_time: 0.002468 Rows_sent: 2  Rows_examined: 2
    use mysql;
    SET timestamp=1591708747;
    select * from user;

# windows 
打开C:\Program Files\mysql-5.7.22-winx64\data\8BSM2RTZA5UH6Y2-slow.log

==重要==：mysql默认开启以上配置，需在my.ini 中设置；否则每次重启MySQL都要配置，而且日志会丢失

1
2
3
slow_query_log = on
slow_query_log_file = /path/to/data/slow_query.log
long_query_time = 1

存储格式

1
2
3
4
5
查询时间 | # Time: 2020-06-09T13:19:07.814701Z 
主机信息 ｜ # User@Host: root[root] @ localhost []  Id:     9
SQL执行信息 ｜ # Query_time: 0.002819  Lock_time: 0.002468 Rows_sent: 2  Rows_examined: 2
SQL执行时间 ｜ SET timestamp=1591708747;
SQL内容 ｜ select * from user;

MySQL 慢查询日志分析工具

日志量巨大，人工很难分析，借助工具，生成报表，更人性化的可视化分析。

mysql 自带服务 - mysqldumpslow

Windows

Windows 下 MySQL5.7.22 bin/下是mysqldumpslow.pl文件，需要安装perl执行

查看帮助

1
C:\Program Files\mysql-5.7.22-winx64\bin> perl mysqldumpslow.pl --help

执行分析

1
C:\Program Files\mysql-5.7.22-winx64\bin> mysqldumpslow.pl -r -s c -a -t > C:\Program Files\mysql-5.7.22-winx64\data\slow.log

Linux

  • 查看参数
1
mysqldumpslow -h
  • 分析慢日志
1
~> mysqldumpslow -t 3 /usr/local/var/mysql/linking-3-slow.log ｜ more

percona提供 - pt-query-digest

1
pt-query-digest /usr/local/var/mysql/linking-3-slow.log ｜ more

比 mysqldumpslow 更友好，清晰看到每个查询次数、耗时占用百分比

如何通过慢查询日志发现有问题的sql

  • 查询次数多，占总时间多的
  • 行数，IO 消耗大
  • 未命中索引

以上指标，用 pt-query-digest 能很快分析出来

通过explain查询和分析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
mysql> explain select user from user;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 276     | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 另一种方式，更清晰
mysql> explain select user from user \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 276
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

关注以上返回的字段，重点关注type（避免all，全表查询）、key（主键）、key_len、rows、Extra(避免 using temporary、using filesort 方式)

count()、max() 函数优化

  • max()，字段加索引
  • count()，count(*)和count(id) 可能不一样，存在id=NULL的情况下
1
mysql> select count(year=2018 or null) as y2018, count(year=2019 or null) from tablename;

子查询优化

  • join查询，注意数据重复问题，一对多关系；使用distinct函数去重
1
mysql> select distinct t.id from t join t1 on t.id=t1.tid;

group by 优化

  • 用explain查看执行计划
  • 注意改写sql，用子查询以及join优化sql

limit 优化

  • 1.使用有索引的列或主键进行order by操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> explain select * from mysql.help_relation limit 50,5 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: help_relation
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1291
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

rows: 1291

  • 2.记录上次返回的主键，在下次查询时使用主键过滤（避免数据量大时扫描过多的记录）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> explain select * from mysql.help_relation where mysql.help_keyword_id>50 and mysql.help_keyword_id<=55 limit 50,5 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: help_relation
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

rows: 8

  • 减少了扫描行数
  • 主键连续增长

另外如果是确定只需要一条数据，则可limit 1，找到则停止扫描，减少扫描行数。

索引优化

如何选择合适的列建立索引

  • 1.where从句，group by 从句，order by 从句，on从句中出现的列
  • 2.索引字段越小越好
  • 3.离散度大的列放在联合索引的前面
1
2
3
4
mysql> select * from payment where staff_id=2 and customer_id=467;

# index(staff_id,customer_id)好？还是index(customer_id,staff_id)好？
# 由于customer_id的离散度更大，所以后者更好。
  • 怎么判断离散程度？
1
2
3
4
5
# 表的数据结构
mysql> desc payment;
# 查看字段唯一值数量
mysql> select count(distinct customer_id),count(distinct staff_id) from payment;
# 数值大大离散程度高

索引优化SQL的方法

索引有利于查询，不利于写入，索引太多也不利于查询

重复及冗余索引

  • 重复索引

重复索引指相同的列以相同的顺序建立的同类型的索引；如primary key和id列上建立的索引就可能是重复的。如下例子中，primary key和id列上的索引就是重复索引

主键就是唯一索引了，不需要重复建立

1
2
3
4
5
6
create table test (
  id int not null primary key,
  name varchar(10) not null,
  title varchar(50) not null,
  unique(id)
)engine=innodb;
  • 冗余索引

冗余索引指多个索引的前缀列相同，或是联合索引中包含了主键的索引。如下key(name,id)就是冗余索引

1
2
3
4
5
6
create table test (
  id int not null primary key,
  name varchar(10) not null,
  title varchar(50) not null,
  key(name,id)
)engine=innodb;

查找重复及冗余索引

  • 1.sql方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 切换数据库
use information_schema;
# 查询比较
select a.table_schema as '数据名',
    a.table_name as '表名',
    a.index_name as '索引1',
    b.index_name as '索引2',
    a.column_name as '重复列名'
from statistics a join statistics b 
on a.table_schema=b.table_schema 
and a.table_name=b.table_name 
and a.seq_in_index=b.seq_in_index 
and a.column_name=b.column_name
where a.seq_in_index=1 
  and a.index_name<>b.index_name;
  • 2.工具 pt_duplicate-key-checker
1
2
3
pt_duplicate-key-checker \
-uroot -p 'root' \
-h 127.0.0.1

第三方工具，更友好的输出信息

前面的例子已经安装过，可直接使用

索引维护的方法

业务及表结构变更后，索引可能不用了，过期不用对索引进行管理

删除不用的索引

MySQL 暂时没有官方方法，工具提供了通过慢查询日志配合pt-index-usage工具来分析索引使用情况。

1
2
3
pt-index-usage \
  -uroot -p 'root' \
  mysql-slow.log

注意主从，及集群版本，对所有的慢查询索引都需要统一分析。

SQL及索引优化是MySQL优化的重中之重，如果做不好，后面的表结构调整收效甚微。

数据库结构优化

选择合适的数据类型

如何确定选择的类型==合适==：

  • 1.可存下数据的最小数据类型
  • 2.简单的数据类型。int比varchar在mysql中处理简单
  • 3.尽可能使用not null定义字段
  • 4.尽量少用text、blog等大字段或二进制字段，非用不可的话考虑分表

例如：

  • 1.使用 long 存储日期时间戳，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来转换
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> create table timestamp(id int auto_increment not null,timestr int,primary key(id));

mysql> insert into timestamp(timestr) values(UNIX_TIMESTAMP('2020-06-11 20:55'));

mysql> select * from timestamp where id =1;

+----+------------+
| id | timestr    |
+----+------------+
|  1 | 1591880100 |
+----+------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(timestr) from timestamp;

+------------------------+
| from_unixtime(timestr) |
+------------------------+
| 2020-06-11 20:55:00    |
+------------------------+
1 rows in set (0.00 sec)
  • 2.使用bigint存储IP地址，利用inet_aton(),inet_ntoa()两个函数转换
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> create table sessions(id int auto_increment not null, ipaddress bigint, primary key(id));

mysql> insert into sessions(ipaddress) values(inet_aton('192.168.0.1'));

mysql> select * from sessions;

+----+------------+
| id | ipaddress  |
+----+------------+
|  1 | 3232235521 |
+----+------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(ipaddress) from sessions;
+----------------------+
| inet_ntoa(ipaddress) |
+----------------------+
| 192.168.0.1          |
+----------------------+
1 row in set (0.00 sec)

表的范式化优化

表的范式化

范式化是说表设计的规范，目前说范式化一般指第三设计范式，即要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖，则该表符合第三范式。

|商品名称|价格|重量|有效期|分类|分类描述
|–|–|–|–|–|–|–|–
|可乐|3.00|1ml|20200630|饮料|碳酸饮料|
|农夫山泉|2.5|1L|20201230|饮料|碳酸饮料|

存在依赖关系：商品名称>分类>分类描述

存在非关键字段“分类描述”对关键字段“商品名称”的传递函数依赖。

  • 不符合第三范式的表存在以下问题

    • 1.数据冗余（分类、分类描述对于每个商品都会记录）
    • 2.插入异常
    • 3.更新异常
    • 4.删除异常

  • 优化：拆分表

商品表

商品名称 价格 重量 有效期
可乐 3.00 1ml 20200630
农夫山泉 2.5 1L 20201230

分类表

分类 分类描述
饮料 碳酸饮料
饮料 碳酸饮料

关联表

分类 商品名称
饮料 可乐
水果 苹果

表反范式化

范式化会增加许多关联表，影响效率。

反范式化是指：为了查询效率，考虑把原本符合第三范式的表适当增加冗余，以达到优化查询效率的目标。以空间换时间的操作。

少点join

垂直拆分表（竖着切）

解决字段过多问题

  • 按列来拆分
  • 不常用、大字段（varchar,text,blob,clob）、不常用的字段放到一个表

水平拆分表（横着切）

解决单表数据量过大问题。水平拆分每个表的结构一致。

  • 对某字段进行hash运算，mode(column,5)，分5份
  • 针对不同的hashID把数据存到不同的表中

挑战：

  • 1.跨分区表进行查询
  • 2.统计及后台报表操作

前台分表查询，后台汇总表操作

系统配置优化

操作系统优化

常用Linux系统配置优化

  • 网络方面，修改/etc/sysctl.conf
1
2
3
4
5
6
7
# 增加TCP支持的队列数
net_ipv4.tcp_max_syn_backlog=65535
# 减少断开连接时，资源回收
net_ipv4.tcp_max_tw_buckets=8000
net_ipv4.tcp_tw_reuse=1
net_ipv4.tcp_tw_recycle=1
net_ipv4.tcp_fin_timeout=10

  • 文件大小不能超过 2 G

  • 缓冲池内存不能超过系统

  • 打开文件数限制

文件句柄，默认1024

1
2
3
4
5
6
# 查看
~> ulimit -a

# 修改/etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
  • MySQL服务器上最好关闭iptable、selinux等防火墙软件

大型应用，建议用硬件防火墙，不要用软件防火墙。

  • 文件分区类型，ext4、fts

MySQL 配置优化

  • MySQL 配置文件
    • 指定配置参数、使用配置文件两种方式(一般/etc/my.cnf,/etc/mysql/my.cnf;windows: C:/program files/MySQL/my.ini)

可通过命令查找配置文件顺序

1
2
3
4
5
6
~> which mysqld

~> mysqld --verbose --help | grep -A 1 'Default options'

    Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

多个配置文件，后面覆盖前面.
从上面可以看出, 服务器首先会读取/etc/my.cnf文件，如果发现该文件不存在，再依次尝试从后面的几个路径进行读取。

  • 常用参数1
    • innodb_buffer_pool_size: 单独数据库服务器，推荐为总内存的75%
1
2
3
4
5
6
7
8
9
10
11
12
13
select engine,round(sum(data_length+index_length)/1024/1024,1) as "total mb" from information_schema.tables where table_schema not in ("information_schema","performance_schema") group by engine;

    +--------+----------+
    | engine | total mb |
    +--------+----------+
    | NULL   |     NULL |
    | CSV    |      0.0 |
    | InnoDB |      2.3 |
    | MyISAM |      0.3 |
    +--------+----------+
    4 rows in set (0.13 sec)

# innodb_buffer_pool_size >= total mb
  • 常用参数2
    • innodb_buffer_pool_instances

MySQL5.5后引入，指定缓冲池个数，默认一个。一般4、8份。

  • 常用参数3
    • innodb_buffer_log_size

innodb buffer log 大小，日志每秒刷新，所以不用太大

  • 常用参数4
    • innodb_flush_log_at_trx_commit

多久将变更刷新到磁盘；为==关键==参数，影响IO；默认为1，可设0/1/2，一般设为2，数据安全性要求高可设为1.

  • 常用参数5
    • innodb_read_io_threads
    • innodb_write_io_threads

决定innodb读写IO进程数，默认4

  • 常用参数6
    • innodb_file_per_table

==关键==参数，控制innodb每一个表使用独立的表空间，默认off，就是所有的表都会建立在共享表空间

  • 常用参数7
    • innodb_stats_on_metadata

决定MySQL在什么情况下会刷新innodb表的统计信息。刷新使优化器使用索引。一般设为off，不让他自己刷新

第三方配置工具

硬件优化

CPU选择

  • MySQL一些工作（replicate、sql）只能使用到单核CPU
  • 核不是越多越好，MySQL 5.5 版本不要超过32核

磁盘IO优化

常用RAID磁盘阵列，增加磁盘IO

  • RAID0，条带，串行，IO最好，不安全
  • RAID2，镜像，可替换，安全
  • RAID5
  • RAID0+1，同时具备0-1优点，一般数据库建议使用这个级别磁盘

SNA核NAT是否适合数据库？

  • 1.常用语高可用解决方案
  • 2.顺序读写效率很高，但是随机读写不尽如人意
  • 3.数据库随机读写比率很高

参考

扫一扫，分享到微信

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia-plus根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true