MySQL 慢查询优化分析
2021.07.15 20:47浏览量：702简介：MySQL 慢查询分析
前言
MySQL 是我们常用的数据库，工作中，我们需要优化慢查询SQL来降低服务响应的时间。
为什么要优化慢查询 SQL ？
慢查询 SQL 执行时间较长，消耗资源多，当请求 QPS 增大的时候，会影响正常查询，从而导致数据库性能下降，严重时候可能会讲数据库拖垮。
如何查看慢查询 SQL ?
慢 SQL 只有在语句执行完成之后才会写入慢日志文件。测试情况下，我们查询日志即可，正式工作中，一般日志会经过统一处理，在 kibana 中查询分析预警。
下面我将以一个case来带你一步一步优化一条慢SQL。
测试case
准备测试Mock数据
- 我目前是本地调试的版本是 5.7.22
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.22, for osx10.13 (x86_64) using EditLine wrapper
Connection id: 8
SSL: Not in use
Current pager: less
Using outfile: ''
Using delimiter: ;
Server version: 5.7.22
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 day 2 hours 14 min 22 sec
Threads: 1 Questions: 17 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 100 Queries per second avg: 0.000
--------------
- 建表语句
create table a (id int auto_increment,seller_id bigint,seller_name varchar(100) collate utf8_bin ,gmt_create varchar(30),primary key(id));
create table b (id int auto_increment,seller_name varchar(100),user_id varchar(50),user_name varchar(100),sales bigint,gmt_create varchar(30),primary key(id));
create table c (id int auto_increment,user_id varchar(50),order_id varchar(100),state bigint,gmt_create varchar(30),primary key(id));
- 初始化测试数据
a表测试数据
insert into a (seller_id,seller_name,gmt_create) values (100000,'uniqla','2020-01-01'); insert into a (seller_id,seller_name,gmt_create) values (100001,'uniqlb','2020-02-01'); insert into a (seller_id,seller_name,gmt_create) values (100002,'uniqlc','2020-03-01'); insert into a (seller_id,seller_name,gmt_create) values (100003,'uniqld','2020-04-01'); insert into a (seller_id,seller_name,gmt_create) values (100004,'uniqle','2020-05-01'); insert into a (seller_id,seller_name,gmt_create) values (100005,'uniqlf','2020-06-01'); insert into a (seller_id,seller_name,gmt_create) values (100006,'uniqlg','2020-07-01'); insert into a (seller_id,seller_name,gmt_create) values (100007,'uniqlh','2020-08-01'); insert into a (seller_id,seller_name,gmt_create) values (100008,'uniqli','2020-09-01'); insert into a (seller_id,seller_name,gmt_create) values (100009,'uniqlj','2020-10-01'); insert into a (seller_id,seller_name,gmt_create) values (100010,'uniqlk','2020-11-01'); insert into a (seller_id,seller_name,gmt_create) values (100011,'uniqll','2020-12-01'); insert into a (seller_id,seller_name,gmt_create) values (100012,'uniqlm','2021-01-01'); insert into a (seller_id,seller_name,gmt_create) values (100013,'uniqln','2021-02-01'); insert into a (seller_id,seller_name,gmt_create) values (100014,'uniqlo','2021-03-01'); insert into a (seller_id,seller_name,gmt_create) values (100015,'uniqlp','2021-04-01'); insert into a (seller_id,seller_name,gmt_create) values (100015,'uniqlp','2021-05-01'); insert into a (seller_id,seller_name,gmt_create) values (100015,'uniqlp','2021-06-01'); insert into a (seller_id,seller_name,gmt_create) values (100015,'uniqlp','2021-07-01'); insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a; insert into a (seller_id,seller_name,gmt_create) values (100016,'uniqlq',now());b表测试数据
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqla','1','a',1,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlb','2','b',3,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlc','3','c',1,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqld','4','d',4,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqle','5','e',5,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlf','6','f',1,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlg','7','g',7,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlh','8','h',1,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqli','9','i',1,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlj','10','j',15,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlk','11','k',61,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqll','12','l',31,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlm','13','m',134,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqln','14','n',1455,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlo','15','o',166,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlp','16','p',15,now());
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('uniqlq','17','s',109,now());
- c表测试数据
insert into c (user_id,order_id,state,gmt_create) values( 21,1,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 22,2,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 33,3,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 43,4,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 54,5,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 65,6,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 75,7,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 85,8,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 95,8,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 100,8,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) values( 150,8,0 ,now() );
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
insert into c (user_id,order_id,state,gmt_create) values( 17,8,0 ,now() );
慢查询SQL优化
我们有一条待优化的查询SQL如下：
select a.seller_id,a.seller_name,b.user_name,c.state
from a,b,c
where a.seller_name=b.seller_name
and b.user_id = c.user_id
and c.user_id = 17
and a.gmt_create
BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create;
- 优化工具-explain
待优化SQL的初次分析:
从Extra中看出主要有Using temporary,Using filesort,Using join buffer三个问题.mysql> explain select a.seller_id,a.seller_name,b.user_name,c.state from a,b,c where a.seller_name=b.seller_name and b.user_id=c.user_id and c.user_id=17 and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create; +----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 15609 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 16674 | Using where; Using join buffer | | 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 360681 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+ 3 rows in set (0.00 sec)Using filesort
说明使用了一次额外排序,从排序的地方下手,就找到了a.gmt_create.
Using join buffer
说明mysql使用using join buffer算法来优化改sql的查询.根据SQL语句,找到where地方,增加索引.
综上分析,增加下列索引:
alter table a add index idx_seller_gmt(`gmt_create`,`seller_name`);
alter table b add index idx_seller(`seller_name`,`user_id`);
alter table c add index idx_user(`user_id`);
若想查看详细的SQL执行过程,可以按照以下步骤执行.
- 开启
SET profiling=1;
- 执行SQL
- 查看所有的SQL执行
SHOW PROFILES; - 查看某个SQL的情况
增加索引后的SQL分析,还算不错.如果有更好的优化办法,请和我交流.> SHOW PROFILE ALL FOR QUERY 1 ;mysql> explain select a.seller_id,a.seller_name,b.user_name,c.state from a,b,c where a.seller_name=b.seller_name and b.user_id=c.user_id and c.user_id=17 and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create; +----+-------------+-------+-------+----------------+----------------+---------+---------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+---------------+------+-------------+ | 1 | SIMPLE | a | range | idx_seller_gmt | idx_seller_gmt | 123 | NULL | 1 | Using where | | 1 | SIMPLE | b | ref | idx_seller | idx_seller | 403 | func | 82 | Using where | | 1 | SIMPLE | c | ref | idx_user | idx_user | 203 | ali.b.user_id | 1803 | Using where | +----+-------------+-------+-------+----------------+----------------+---------+---------------+------+-------------+ 3 rows in set (0.02 sec)
小结
- 一次常规的慢查询 SQL 优化就完成了，请各位试试操作优化一下。
- 以上优化主要使用了 MySQL 的索引优化。在 MySQL 5.7 版本之后，默认的存储引擎是 InnoDB。下面来分析一下原理。
InnoDB
在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式为索引组织表。
InnoDB 索引模型
- 主键索引(聚簇索引)的叶子节点存的是整行数据。
- 非主键索引(二级索引)的叶子节点存的是主键的值
- 一般主键索引是在建表的时候就确定了，ID常常作为主键索引
- 二级索引，往往是随着数据库数据量的增大，我们通过不断的优化，设计，迭代出来的。
- 唯一索引不允许索引具有相同的行。
- 复合索引：两个或者两个以上的列创建的索引。复合索引一般按照最左匹配原则创建。
主键索引和普通索引有什么区别？
- 从数据结构来看，普通索引获取整行数据需要再次在主键索引中搜索一次，这个过程也叫回表。
- 主键索引不允许null，普通索引的值可以为null。
- 一个表只能有一个主键，可以有多个索引。
MySQL 默认使用的索引一定正确吗？
一般情况下，在设计并创建好索引之后，MySQL 一般自己选择使用哪个索引。
在大多数情况下，MySQL 选择执行的索引是正确的。实际开发中，有时候 MySQL 执行的索引不正确。这时候，我们可以使用 force index 强行指定要执行的索引。或者，重新修改优化我们的 SQL 来提升查询效率。
Explain 执行计划字段说明
- id：序列号，id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行
- select_type：查询数据的操作类型（SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION）
- table：行所在的表名字
- type：访问类型，常见的类型有：
all(全表扫描) index（扫描全部索引树） range（扫描部分索引） index_merge（索引合并） ref（非唯一性索引扫描） eq_ref（唯一性索引扫描） const system（常量）
效率由高到底：system > const > eq_ref > ref > range > index > all
- possible_keys：可供选择的索引
- Key ：解析器判断最优的使用索引
- key_len：索引中使用的字节数，在不损失精确性的情况下，长度越短越好
- Ref ：显示该表的索引字段join了哪张表的哪个字段
- Rows ：根据表统计信息及选用情况，大致估算出找到记录所需扫描的行数，数值越小越好
- filtered：返回结果的行数占读取行数的百分比
- Extra ：包含了一些比较重要的额外信息
Using filesort：需要额外排序，不能通过索引顺序达到排序效果。
Using temporary：查询使用临时表，一般出现排序，分组，多表join，查询效率不高。
Using index：使用了覆盖索引，效率比较好。
总结
除了上述的针对单条语句的优化，还可以从以下方面优化
- 硬件优化：更大的内存， SSD 磁盘
- 架构优化：读写分离，增加缓存
- 合理的表设置： 合适的字段
上面是我对 MySQL 慢查询优化的一些总结，写的不对地方请大家指出，我们一起学习，共同进步。
发表评论
登录后可评论，请前往 登录 或 注册