179
阿裏雲數據庫挑戰賽"SQL優化大師"獲獎案例
一、前言
2017/07在阿裏雲舉辦的第一屆“阿裏雲數據庫挑戰賽第一季“慢SQL性能優化賽”期間，我得到知數堂葉老師的鼎力相助，成功突破重圍，過關斬將，獲得“SQL優化大師”榮譽稱號!
阿裏雲數據庫挑戰賽
第一季“SQL優化大師”
通過這次挑戰賽的實踐，加上中間葉老師的指導，讓我增進了對SQL優化的認識。
在此，分享下我的SQL優化過程，希望能給各位提供一些SQL優化方麵的思路，大家共同交流進步。
二、優化過程
1、優化前
- 原始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
- 原始表結構
create table a(
id int auto_increment,
seller_id bigint,
seller_name varchar(100) collate utf8_bin ,
gmt_create varchar(30),
primary key(id)) character set utf8;
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)) character set utf8;
create table c (
id int auto_increment,
user_id varchar(50),
order_id varchar(100),
state bigint,
gmt_create varchar(30),
primary key(id)) character set utf8;
2、優化前的SQL執行計劃
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
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16109
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16174
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 359382
filtered: 1.00
Extra: Using where; Using join buffer (Block Nested Loop)
3、優化後
- 先看下經過優化後的終版SQL執行計劃
mysql> explain select a.seller_id, a.seller_name,b.user_name,
c.state from a left join b
on (a.seller_name=b.seller_name)
left join c on (b.user_id=c.user_id)
where c.user_id='17'
and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(), INTERVAL 600 MINUTE);
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: i_seller_name,i_user_id
key: i_user_id
key_len: 3
ref: const
rows: 1
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
partitions: NULL
type: ref
possible_keys: i_user_id
key: i_user_id
key_len: 3
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ref
possible_keys: i_seller_name
key: i_seller_name
key_len: 25
ref: test1.b.seller_name
rows: 1
filtered: 11.11
Extra: Using where
優化完後這個SQL毫秒級出結果（看下方profiling截圖）
4、優化思路
- 硬件&係統環境
硬盤：SSD（pcie)
內存：16G
CPU：8核
操作係統：選擇Centos7係統，xfs文件係統
內核參數做些調整：
vm.swappiness = 5 #建議設置5-10
io schedule選擇 deadline/noop 之一
MySQL 版本選擇
推薦MySQL 5.6以上的版本，最好是MySQL 5.7。
MySQL 5.6優化器增加了ICP、MRR、BKA等特性，5.7在性能上有更多提升。
MySQL參數調整
innodb_buffer_pool_size #物理內存的50% - 70%
innodb_flush_log_at_trx_commit = 1
innodb_max_dirty_pages_pct = 50 #建議不高於50
innodb_io_capacity = 5000 #SSD盤
#大賽要求關閉QC
query_cache_size = 0
query_cache_type = 0
SQL調優過程詳解
首先，我們看到原來的執行計劃中3個表的查詢都是全表掃描（type = ALL），所以先把關聯查詢字段以及WHERE條件中的字段加上索引。
1、添加索引
alter table a add index i_seller_name(seller_name);
alter table a add index i_seller_id(seller_id);
alter table b add index i_seller_name(seller_name);
alter table b add index i_user_id(user_id);
alter table c add index i_user_id(user_id);
alter table c add index i_state(state);
添加完索引後，再看下新的執行計劃：
explain select a.seller_id,
a.seller_name,b.user_name ,c.state from a
left join b on (a.seller_name=b.seller_name)
left join c on( b.user_id=c.user_id ) where c.user_id='17'
and a.gmt_create BETWEEN DATE_ADD(NOW(),
INTERVAL - 600 MINUTE) AND
DATE_ADD(NOW(), INTERVAL 600 MINUTE)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: i_user_id
key: i_user_id
key_len: 53
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
partitions: NULL
type: ref
possible_keys: i_user_id
key: i_user_id
key_len: 53
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ref
possible_keys: i_seller_name
key: i_seller_name
key_len: 303
ref: func
rows: 947
filtered: 11.11
Extra: Using index condition; Using where
我們注意到執行計劃中3個表的key_len列都太大了，最小也有53字節，最大303字節，要不要這麼誇張啊～
2、修改字符集、修改字段數據類型
默認字符集是utf8（每個字符最多占3個字節），因為該表並不存儲中文，因此隻需要用latin1字符集（最大占1個字節）。
除此外，我們檢查3個表的字段數據類型，發現有些varchar(100)的列實際最大長度並沒這麼大，有些實際存儲datetime數據的卻采用varchar(30)類型，有些用bigint/int就足夠的也采用varchar類型，真是醉了。於是分別把這些數據類型改為更合適的類型。
修改表字符集和調整各個列數據類型很重要的作用是可以減小索引的key_len，從而減少關聯的字段的字節，減少內存消耗。
優化後的表結構
CREATE TABLE `a` (
`id` int NOT NULL AUTO_INCREMENT,
`seller_id` int(6) DEFAULT NULL,
`seller_name` char(8) DEFAULT NULL,
`gmt_create` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i_seller_id` (`seller_id`),
KEY `i_seller_name` (`seller_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `b` (
`id` int NOT NULL AUTO_INCREMENT,
`seller_name` char(8) DEFAULT NULL,
`user_id` smallint(5) DEFAULT NULL,
`user_name` char(10) DEFAULT NULL,
`sales` int(11) DEFAULT NULL,
`gmt_create` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i_seller_name` (`seller_name`),
KEY `i_user_id` (`user_id`),
KEY `i_user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `c` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` smallint(5) DEFAULT NULL,
`order_id` char(10) DEFAULT NULL,
`state` int(11) DEFAULT NULL,
`gmt_create` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i_user_id` (`user_id`),
KEY `i_state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
以上是我在阿裏雲數據庫挑戰賽中的獲獎案例，感謝在比賽過程中葉老師對我的提點和幫助，同時非常感謝知數堂教授SQL優化技能！
最後，我想說的是，隻要掌握SQL優化的幾個常規套路，你也可以完成絕大多數的SQL優化工作滴！
附錄：3個表數據初始化
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');
...重複N次寫入
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());
...重複N次寫入
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() );
...重複N次寫入
原文發布時間為：2017-09-30
本文作者：田帥萌
本文來自雲棲社區合作夥伴“老葉茶館”，了解相關信息可以關注“老葉茶館”微信公眾號
最後更新：2017-09-30 23:04:00