开发者社区> 技术小能手> 正文

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

简介: 分享下我的SQL优化过程,希望能给各位提供一些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截图)


image


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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【Spring Boot】使用JDBC 获取相关的数据
【Spring Boot】使用JDBC 获取相关的数据 使用JDBC 获取相关的数据 什么是JDBC Java Database Connectivity 是一种用于执行SQL语句的Java API,与数据库建立连接、发送 操作数据库的语句并处理结果。
4831 0
第11章—使用对象关系映射持久化数据—SpringBoot+SpringData+Jpa进行查询修改数据库
SpringBoot+SpringData+Jpa进行查询修改数据库 JPA由EJB 3.0软件专家组开发,作为JSR-220实现的一部分。但它又不限于EJB 3.0,你可以在Web应用、甚至桌面应用中使用。
1113 0
转载:30多条mysql数据库优化方法,千万级数据库记录查询轻松解决
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描, Sql 代码 : select id from t where num is null; 可以在 num 上设置默认值 0,确保表中 num 列没有 null 值,然后这样查询: Sql 代码 : select id from t where num=0; 3.应尽量避免在 where 子句中使用!=或操作符,否则将引擎放弃使用索引而进行全表扫描。
867 0
SQL Server Profiler和数据库引擎优化顾问
原文:SQL Server Profiler和数据库引擎优化顾问  简介           说到Sql的【性能工具】真是强大,SQL Server Profiler的中文意思是SQL Server事件探查,这个到底是做什么用的呢?我们都知道探查的意思大多是和监视有关,其实这个SQL Server Profiler就是一个Sql的监视工具,可以具体到每一行Sql语句,每一次操作,和每一次的连接。
1167 0
SpringBoot实战(四)之使用JDBC和Spring访问数据库
这里演示的是h2databse示例,所以简单的介绍普及下h2database相关知识 H2数据库是一个开源的关系型数据库。 H2是一个嵌入式数据库引擎,采用java语言编写,不受平台的限制,同时H2提供了一个十分方便的web控制台用于操作和管理数据库内容。
2314 0
关于数据库安装的问题SP2-0667: Message file sp1.msb not found
昨天自己在OEL上准备把10g,11g的环境都准备一下,我规划了一下硬盘空间,然后创建了相应的用户,12c的Oms也在这次的范围之内。 drwx------  4 oms10g  dba     4096 Jan 18 20:56 oms10g...
2597 0
数据库SQL优化大总结之 百万级数据库优化方案
原文链接:http://www.cnblogs.com/yunfeifei/p/3850440.html
3799 0
+关注
技术小能手
云栖运营小编~
7208
文章
9
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载