阿里云数据库挑战赛"SQL优化大师"获奖案例-低调大师

阿里云数据库挑战赛"SQL优化大师"获奖案例

2017-09-29 505

一、前言

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
本文作者：田帅萌
本文来自云栖社区合作伙伴“老叶茶馆”，了解相关信息可以关注“老叶茶馆”微信公众号

微信关注我们

原文链接：https://yq.aliyun.com/articles/221567

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

云博士智能诊断功能全新升级

9月28日，阿里云自主研发的智能客服机器人--云博士高调亮相“云上安全中国力量”安全发布会。作为企业安全护航助手，云博士的智能诊断、安全预警、决策辅助等能力震惊业界，刷新了人们对于安全领域人工智能应用的期待。阿里云智能平台负责人也在微博上回应称，智能机器人的定位就应该是诊脉断病的智能doctor！ 9月30日，云博士的智能诊断功能全新升级，官方对它描述是随时随地进行全局诊断、精准诊断，及时发现高危风险，为您保驾护航。紧接着我们测试了这个新功能。点击“开始诊断”，可以看到“全局诊断”和“实例诊断”两种方式。全局诊断是指针对所有实例进行检测，实例诊断是指针对特定实例进行检测。我们点击了“全局检测”，很快检测出账号过期的3个提醒，同时给出了实例名、状态（月日已经过期）、可能的影响以及处理建议，并有直接到文档和控制台的快速入口。整体感觉功能

2017-09-30

599

阿里云的函数计算今年4月份推出后，陆续推出了Nodejs6.10、Python2.7和Java8等编译环境，今天我们借助压测工具jmeter来评测一下关于函数计算自动弹性扩容这个特性，在测试之前，我们先梳理一下要进行的内容项： 1、单线程测试基准响应时间，单线程跑1000次，得出某函数计算处理的平均响应时间。 2、5线程测试单个请求的响应时间：测试小规模并发下，函数计算响应时间比单线程情况如何 3、20线程测试函数弹性扩容情况：测试大并发规模下，函数计算响应时间情况，以及是否丢包等。待续未完。

2017-09-30

589

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。