阿里云数据库挑战赛"SQL优化大师"获奖案例
本文共 8275 字，大约阅读时间需要 27 分钟。
一、前言
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,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
- 原始表结构
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,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*************************** 1. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: ALLpossible_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 bon (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截图）
- 硬件&系统环境
硬盘：SSD（pcie)
内存：16G
CPU：8核
操作系统：选择Centos7系统，xfs文件系统
内核参数做些调整：
vm.swappiness = 5 #建议设置5-10io 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 = 1innodb_max_dirty_pages_pct = 50 #建议不高于50innodb_io_capacity = 5000 #SSD盘 #大赛要求关闭QCquery_cache_size = 0query_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: refpossible_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: refpossible_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: refpossible_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=latin1CREATE 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=latin1CREATE 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
本文作者：田帅萌本文来自云栖社区合作伙伴“老叶茶馆”，了解相关信息可以关注“老叶茶馆”微信公众号转载地址：http://tsuml.baihongyu.com/
你可能感兴趣的文章
处理异常
查看>>
一个WIndows Server 2008 R2 AD组策略复制排错过程（Warning 13508）
查看>>
SpringBoot之@EnableAutoConfiguration原理及自定义扩展
查看>>
.net CHARTING图表控件下载地址
查看>>
老男孩教育每日一题-第82天-发现文件系统只读了，是什么原因？如何解决？
查看>>
python 的函数、值传递、和作用域(例子）
查看>>
关于 CKEditor上传文件时调用ckeditor的技巧
查看>>
用g++编程时遇到权限问题
查看>>
MySQL设置UTF8字符
查看>>
High-Level Streams DSL(翻译)
查看>>
系统集成资质培训 - 2011年下半年培训机构
查看>>
如何生成安全的密码 Hash：MD5, SHA, PBKDF2, BCrypt 示例
查看>>
mongodb
查看>>
ecshop物流跟踪实现
查看>>
自定义标签例子
查看>>
LAMP架构之分离式-php-fpm
查看>>
C#：为详情查看界面设计的万用TextBox自定义控件
查看>>
trim 杂谈
查看>>
四人过桥、三盏灯 三个开关 的答案
查看>>
我的友情链接
查看>>