AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

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

2017-09-30 3372
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 分享下我的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
本文作者：田帅萌
本文来自云栖社区合作伙伴“老叶茶馆”，了解相关信息可以关注“老叶茶馆”微信公众号

文章标签：
云数据库 RDS MySQL 版
SQL
Java
索引
数据库
关系型数据库
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
技术小能手
目录
相关文章
游客vwx7bav3mqbk6
|
SQL Oracle 关系型数据库
MySQL的sql_mode模式说明及设置
MySQL的sql_mode模式说明及设置
游客vwx7bav3mqbk6
1654 112
技术自由圈/原疯狂创客圈
|
存储 SQL 关系型数据库
mysql底层原理：索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog（图解+秒懂+史上最全）
mysql底层原理：索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog（图解+秒懂+史上最全）
技术自由圈/原疯狂创客圈
2970 43
mysql底层原理：索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog（图解+秒懂+史上最全）
艾伦~耶格尔
|
SQL 存储 关系型数据库
MySQL体系结构详解：一条SQL查询的旅程
本文深入解析MySQL内部架构，从SQL查询的执行流程到性能优化技巧，涵盖连接建立、查询处理、执行阶段及存储引擎工作机制，帮助开发者理解MySQL运行原理并提升数据库性能。
艾伦~耶格尔
451 0
游客vphb4ae2je2zi
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB，多种引擎优惠上线！MySQL倚天版88元/年，SQL Server 2核4G仅299元/年，PostgreSQL 227元/年起。高可用、可弹性伸缩，安全稳定。详情见官网活动页。
游客vphb4ae2je2zi
1910 152
上云就上阿狸云
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎，提供高性价比、稳定安全的云数据库服务，适用于多种行业与业务场景。
上云就上阿狸云
1317 156
程序员爱钓鱼
|
SQL 关系型数据库 MySQL
Go语言数据库编程：使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口，支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容，涵盖实际开发中常用的技巧与注意事项，适合快速掌握Go语言数据库编程基础。
程序员爱钓鱼
1798 213
艾伦~耶格尔
|
SQL 监控 关系型数据库
SQL优化技巧：让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧，涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面，帮助开发者显著提升数据库性能，解决慢查询问题，适用于高并发与大数据场景。
艾伦~耶格尔
608 2
上云就上阿狸云
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型，包括关系型与NoSQL，主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起，支持按需付费与优惠套餐，适用于各类应用场景。
上云就上阿狸云
2060 4
蓝易云
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数，并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得，在做任何重要变更前备份相关配置文件，并确保理解每个参数对系统性能可能产生影响后再做出调节。
蓝易云
1043 11

热门文章

最新文章

  • 1
    「直播实录」中英数据库专家谈：数据库的过去、未来和现在
  • 2
    【玩转ElasticSearch】降维打击！使用ElasticSearch作为时序数据库
  • 3
    数据库的介绍、分类、作用和特点
  • 4
    python链接hive数据库实现增删改查操作
  • 5
    【计算机三级数据库技术】第5章 UML与数据库应用系统--附思维导图
  • 6
    NoSQL数据库与传统关系型数据库的比较
  • 7
    Springboot 整合Shiro 轻量级权限框架，从数据库设计开始带你快速上手shiro
  • 8
    数据库的类型有哪些
  • 9
    数据库复制 text字段解决方法
  • 10
    06.SQLServer性能优化之---数据库级日记监控
  • 1
    阿里云RDS云数据库全解析：产品功能、收费标准与活动参考
    2211
  • 2
    阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
    1910
  • 3
    阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
    2060
  • 4
    鸿蒙 HarmonyOS NEXT端云一体化开发-云数据库篇
    725
  • 5
    阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
    1317
  • 6
    云数据库RDS数据库迁移上云
    625
  • 7
    阿里云的云数据库RDS简介
    961
  • 8
    媒体声音 | 专访阿里云数据库周文超：GenAI时代，数据管理底座强势升级
    997
  • 9
    阿里云数据库：构建高性能与安全的数据管理系统
    981
  • 10
    从MySQL到云数据库，数据库迁移真的有必要吗？
    484

    • 相关课程

    更多
  • SQL调优与架构优化
  • SQL基础开发与应用
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    一条命令迁移，帮你实现 OpenClaw 与 Hermes Agent 记忆互通！