Loading [MathJax]/jax/output/CommonHTML/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >分区表场景下的 SQL 优化

分区表场景下的 SQL 优化

原创
作者头像
叶金荣
发布于 2017-07-04 01:49:17
发布于 2017-07-04 01:49:17
1K0
举报
文章被收录于专栏:iMySQL的专栏iMySQL的专栏

导读

有个表做了分区,每天一个分区。

该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗?

待优化场景

有一个大表,每天产生的数据量约100万,所以就采用表分区方案,每天一个分区。

下面是该表的DDL:

代码语言:txt
AI代码解释
复制
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `kid` int(11) DEFAULT '0',
  `uid` int(11) NOT NULL,
  `iid` int(11) DEFAULT '0',
  `icnt` int(8) DEFAULT '0',
  `tst` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `countp` smallint(11) DEFAULT '1',
  `isr` int(2) NOT NULL DEFAULT '0',
  `clv` int(5) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`,`date`),
  UNIQUE KEY `date` (`date`,`uid`,`iid`),
  KEY `date_2` (`date`,`kid`)
) ENGINE=InnoDB AUTO_INCREMENT=3180686682 DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE  COLUMNS(`date`)
(PARTITION p20161201 VALUES LESS THAN ('2016-12-02') ENGINE = InnoDB,
 PARTITION p20161202 VALUES LESS THAN ('2016-12-03') ENGINE = InnoDB,
 PARTITION p20161203 VALUES LESS THAN ('2016-12-04') ENGINE = InnoDB,
...

该表上经常发生下面的慢查询:

代码语言:txt
AI代码解释
复制
SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';

SQL优化之路

SQL优化思路

想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,同时要关注预计扫描的行数,以及是否产生了临时表(Using temporary) 或者 是否需要进行排序(Using filesort),想办法消除这些情况。

更进一步的优化策略则可能需要调整程序代码逻辑,甚至技术架构或者业务需求,这个动作比较大,一般非核心系统上的核心问题,不会这么大动干戈,绝大多数情况,还是需要靠DBA尽可能发挥聪明才智来解决。

SQL性能瓶颈定位

现在,我们来看下这个SQL的执行计划:

代码语言:txt
AI代码解释
复制
yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE 
  `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170302
         type: range
possible_keys: date,date_2
          key: date
      key_len: 3
          ref: const
         rows: 9384602
        Extra: Using where

这个执行计划看起来还好,有索引可用,也没临时表,也没filesort。不过,我们也注意到,预计要扫描的行数还是挺多的 rows: 9384602,而且要扫描zheng整个分区的所有数据,难怪效率不高,总是SLOW QUERY。

优化思考

我们注意到这个SQL总是要查询某一天的数据,这个表已经做了按天分区,那是不是可以忽略 WHERE 子句中的 时间条件呢

还有,既然去掉了 date 条件,反观表DDL,剩下的条件貌似就没有合适的索引了吧?

所以,我们尝试新建一个索引:

代码语言:txt
AI代码解释
复制
yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);

然后,把SQL改造成下面这样,再看下执行计划:

代码语言:txt
AI代码解释
复制
yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` partition(p2017030) WHERE 
  `icnt` > 100 AND `iid` = '502302'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170302
         type: ref
possible_keys: date,date_2,iid
          key: iid
      key_len: 10
          ref: const
         rows: 7800
        Extra: Using where
这优化效果,杠杠滴。

事实上,如果不强制指定分区的话,也是可以达到优化效果的:

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE 
  `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170302
         type: ref
possible_keys: date,date_2,iid
          key: iid
      key_len: 10
          ref: NULL
         rows: 7800
        Extra: Using where

后记

绝大多数的SQL通过添加索引、适当调整SQL代码(例如调整驱动表顺序)等简单手法来完成。

多说几句,遇到SQL优化性能瓶颈问题想要在技术群里请教时,麻烦先提供几个必要的信息:

  • 表DDL
  • 表常规统计信息,可执行 SHOW TABLE STATUS LIKE ‘t1’ 查看
  • 表索引分布信息,可执行 SHOW INDEX FROM t1 查看
  • 有问题的SQL及相应的执行计划 没有这些信息的话,就别去麻烦别人了吧。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
优化案例:CASE WHEN进行 SQL 改写优化
本文介绍了如何利用MySQL数据库对某电商平台进行性能优化,通过合理设计数据表、索引、使用分页算法、合理设计表结构、使用缓存等技术手段,使电商平台在面临高并发、大数据量的场景下,仍能保持较高的性能,并减少数据库压力,提高系统的稳定性。
叶金荣
2017/07/03
2.3K0
NOT NULL列用IS NULL也能查到数据?
有没有觉得很奇怪,为什么查到了2条 dt 列值为 '0000-00-00 00:00:00' 的记录?
田帅萌
2018/08/14
8760
NOT NULL列用IS NULL也能查到数据?
MySQL 8.0窗口函数优化SQL一例
最近在折腾把所有mysql slow query log写入到数据库中,再集中展示,向业务部门开放,也方便业务部门的同学自行查看并优化各自业务内的慢SQL。增加了定期生成报表的功能,统计最近1~2周内的慢查询数量变化情况,给业务方同学更直观的数据对比,了解最近这段时间的慢查询数量变化情况,是多了还是少了。于是有了下面这一坨SQL:
老叶茶馆
2021/01/12
7760
第41期:MySQL 哈希分区表
提到分区表,一般按照范围(range)来对数据拆分居多,以哈希来对数据拆分的场景相来说有一定局限性,不具备标准化。接下来我用几个示例来讲讲 MySQL 哈希分区表的使用场景以及相关改造点。
爱可生开源社区
2022/05/10
1.2K0
如何更快随机UPDATE?
不过,上面这种多表UPDATE(Multiple-table UPDATE)有局限性,就是只能更新一行记录,不能同时更新多行,所以也可以改写成下面的SQL:
阿炳数记
2019/02/27
5620
MySQL为什么"错误"选择代价更大的索引
群友提出问题,表里有两个列c1、c2,分别为INT、VARCHAR类型,且分别创建了unique key。
老叶茶馆
2021/12/20
1K0
要精通SQL优化?那就学一学explain吧!
在MySQL中,我们知道加索引能提高查询效率,这基本上算是常识了。但是有时候,我们加了索引还是觉得SQL查询效率低下,我想看看有没有使用到索引,扫描了多少行,表的加载顺序等等,怎么查看呢?其实MySQL自带的SQL分析神器Explain执行计划就能完成以上的事情!
java技术爱好者
2021/01/06
6320
第 50 期:根据 EXPLAIN EXTRA 栏提示进行优化(二)
接着上一篇继续,依然是举例说明 EXPLAIN EXTRA 提示栏里的相关释义,这些提示将有助于我们更好的理解 SQL 语句在 MySQL 里的具体执行计划。
爱可生开源社区
2025/01/22
1070
第 50 期:根据 EXPLAIN EXTRA 栏提示进行优化(二)
第23期:索引设计(组合索引不适用场景改造)
上篇文章已经详细介绍 MySQL 组合索引的概念以及其适用场景,这篇主要介绍 MySQL 组合索引的不适用场景以及改造方案。
爱可生开源社区
2021/03/16
2830
第23期:索引设计(组合索引不适用场景改造)
索引三剑客之降序索引和不可见索引
MySQL 8.0中引入了三个索引方面的新特性,暂且将其称为“索引三剑客”。前面我们已经学习了三剑客之一的函数索引,现在我们来见识一下另外两剑客 - 降序索引和不可见索引。
吹水老王
2022/05/17
1.5K0
索引三剑客之降序索引和不可见索引
第22期:索引设计(组合索引适用场景)
建立在多个列上的索引即组合索引(联合索引),适用在多个列必须一起使用或者是从左到右方向部分连续列一起使用的业务场景。
爱可生开源社区
2021/03/16
3520
第22期:索引设计(组合索引适用场景)
MySQL通过索引优化-这里可能有你不知道的索引优化细节(二)
用执行计划分别测试一下union all、in和or,发现union all分两步执行,而in和or只用了一步,效率高一点。
行百里er
2020/12/02
9330
MySQL通过索引优化-这里可能有你不知道的索引优化细节(二)
第37期:适当的使用 MySQL 原生表分区
MySQL 数据库现在主要用的引擎是 InnoDB ,InnoDB 没有类似于 MERGE 引擎这样的原生拆表方案,不过有原生分区表,以水平方式拆分记录集,对应用端透明。
爱可生开源社区
2021/11/30
6360
第37期:适当的使用 MySQL 原生表分区
第 47 期:EXPLAIN TYPE 列的 JOIN 常见场景详解(上)
对于关系型数据库来说,表的设计 以及 SQL 的写法 尤为重要,占据性能的 90% 都不为过,所以这次专门针对这两大类知识领域,为您抽丝剥茧详细分析。
爱可生开源社区
2024/12/27
1150
第 47 期:EXPLAIN TYPE 列的 JOIN 常见场景详解(上)
MySQL EXPLAIN SQL 输出信息描述
EXPLAIN语句能够被用于获取一些关于SQL执行时的相关信息,比如表的连接顺序,对表的方式方式等等。通过对该相关信息进行进一步的分析,我们 可以通过对表添加适当的索引,以及优化连接顺序,使用提示等等手段来达到使SQL高效运行的目的。本文描述了EXPLAIN的用法并给出了相关示例。 一、EXPLAIN概述 EXPLAIN 语句主要是用于解析SQL执行计划,通过分析执行计划采取适当的优化方式提高SQL运行的效率。 EXPLAIN 语句输出通常包括id列,select_type,table,ty
Leshami
2018/08/13
1.1K0
MySQL中InnoDB引擎对索引的扩展
MySQL中,使用InnoDB引擎的每个表,创建的普通索引(即非主键索引),都会同时保存主键的值。
数据和云
2020/03/25
1.2K0
不会看 Explain执行计划,劝你简历别写熟悉 SQL优化
昨天中午在食堂,和部门的技术大牛们坐在一桌吃饭,作为一个卑微技术渣仔默默的吃着饭,听大佬们高谈阔论,研究各种高端技术,我TM也想说话可实在插不上嘴。
程序员小富
2020/05/20
8740
不会看 Explain执行计划,劝你简历别写熟悉 SQL优化
第21期:索引设计(函数索引)
通常来讲,索引都是基于字段本身或者字段前缀(第 20 篇),而函数索引是基于字段本身加上函数、操作符、表达式等计算而来。如果将表达式或者操作符也看做函数的话,简单来说,这样的索引就可以统称函数索引。
爱可生开源社区
2021/02/26
8370
第21期:索引设计(函数索引)
MySQL通过索引优化-这里可能有你不知道的索引优化细节(一)
我在 MySQL优化必备之执行计划explain,索引基本知识,索引数据结构推演 里,提到了索引的一些基本概念,提到MySQL优化,很多人第一时间会想到建索引。
行百里er
2020/12/02
7880
MySQL通过索引优化-这里可能有你不知道的索引优化细节(一)
第 51 期:根据 EXPLAIN EXTRA 栏提示进行优化(三)
爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。----
爱可生开源社区
2025/02/25
930
第 51 期:根据 EXPLAIN EXTRA 栏提示进行优化(三)
推荐阅读
相关推荐
优化案例:CASE WHEN进行 SQL 改写优化
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档