MySQL 查询优化一般步骤
发布于: 2020 年 05 月 26 日
MySQL是我们常用的数据库。SQL查询优化是开发者必备的技能。本文记录了一次完整的优化过程。优化的核心是使用explain来帮助我们优化SQL查询语句，提升查询效率。
准备基础数据
create table a (id int auto_increment,seller_id bigint,seller_name varchar(100) collate utf8_bin ,gmt_create varchar(30),primary key(id)); insert into a (seller_id,seller_name,gmt_create) values (100000,'uniqla','2017-01-01');insert into a (seller_id,seller_name,gmt_create) values (100001,'uniqlb','2017-02-01');insert into a (seller_id,seller_name,gmt_create) values (100002,'uniqlc','2017-03-01');insert into a (seller_id,seller_name,gmt_create) values (100003,'uniqld','2017-04-01');insert into a (seller_id,seller_name,gmt_create) values (100004,'uniqle','2017-05-01');insert into a (seller_id,seller_name,gmt_create) values (100005,'uniqlf','2017-06-01');insert into a (seller_id,seller_name,gmt_create) values (100006,'uniqlg','2017-07-01');insert into a (seller_id,seller_name,gmt_create) values (100007,'uniqlh','2017-08-01');insert into a (seller_id,seller_name,gmt_create) values (100008,'uniqli','2017-09-01');insert into a (seller_id,seller_name,gmt_create) values (100009,'uniqlj','2017-10-01');insert into a (seller_id,seller_name,gmt_create) values (100010,'uniqlk','2017-11-01');insert into a (seller_id,seller_name,gmt_create) values (100011,'uniqll','2017-12-01');insert into a (seller_id,seller_name,gmt_create) values (100012,'uniqlm','2018-01-01');insert into a (seller_id,seller_name,gmt_create) values (100013,'uniqln','2018-02-01');insert into a (seller_id,seller_name,gmt_create) values (100014,'uniqlo','2018-03-01');insert into a (seller_id,seller_name,gmt_create) values (100015,'uniqlp','2018-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()); 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)); 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());create table c (id int auto_increment,user_id varchar(50),order_id varchar(100),state bigint,gmt_create varchar(30),primary key(id));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:
select a.seller_id,a.seller_name,b.user_name,c.state from a,b,cwhere a.seller_name=b.seller_name and b.user_id=c.user_id and c.user_id=17 anda.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create;优化工具-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优化步骤总结
找出慢SQL
explain分析SQL
调整SQL语句或者调整表结构
相关参考文章
划线
评论
复制
发布于: 2020 年 05 月 26 日阅读数: 46
版权声明: 本文为 InfoQ 作者【HQ数字卡】的原创文章。
原文链接:【http://xie.infoq.cn/article/47b824f5f4a918eaf3afdf1d5】。文章转载请联系作者。
HQ数字卡
关注
还未添加个人签名 2019.09.29 加入
略懂后端的RD
评论