分享｜2021MySQL 慢查询SQL优化总结（上）｜技术人求职记
HQ数字卡
2633
2021.03.18
2021.03.22
发布于 未知归属地
MySQL技术人求职记技术交流

  1. 前言

  2. 为什么要优化慢查询 SQL ？

  3. 如何查看慢查询 SQL ?

  4. 测试case

    1. 准备测试Mock数据
    2. 慢查询SQL优化

  5. 总结

前言

MySQL 是我们常用的数据库，也是后端研发面试的必面内容。工作中，我们需要优化慢查询SQL来降低服务响应的时间。

为什么要优化慢查询 SQL ？

慢查询 SQL 执行时间较长，消耗资源多，当请求 QPS 增大的时候，会影响正常查询，从而导致数据库性能下降，严重时候可能会讲数据库拖垮。

如何查看慢查询 SQL ?

慢 SQL 只有在语句执行完成之后才会写入慢日志文件。测试情况下，我们查询日志即可，正式工作中，一般日志会经过统一处理，在 kibana 中查询分析预警。

下面我将以一个case来带你一步一步优化一条慢SQL。

测试case

准备测试Mock数据

  1. 我目前是本地调试的版本是 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
--------------
  1. 建表语句
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));
  1. 初始化测试数据
  • 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) 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;
  1. 优化工具-explain
    待优化SQL的初次分析:
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)

从Extra中看出主要有Using temporary,Using filesort,Using join buffer三个问题.

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的情况
> SHOW PROFILE ALL FOR QUERY 1 ;

增加索引后的SQL分析,还算不错.如果有更好的优化办法,请和我交流.

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 索引的理解，欢迎大家查看 2021MySQL 慢查询SQL优化总结(下) | 技术人求职记

评论 (1)