分区表场景下的 SQL 优化

标签: dev | 发表时间:2017-04-12 00:00 | 作者:
出处:http://itindex.net/admin/pagedetail

导读

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

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

待优化场景

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

下面是该表的DDL:

   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,
...

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

   SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';

SQL优化之路

SQL优化思路

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

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

SQL性能瓶颈定位

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

   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,剩下的条件貌似就没有合适的索引了吧?

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

   yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);

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

   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及相应的执行计划 没有这些信息的话,就别去麻烦别人了吧。

最后安利下,知数堂培训马上推出  SQL开发优化 课程,由业界资深SQL优化专家郑老师授课。

该课程关键字:MySQL、Oracle、SQL调优、EXPLAIN、DBMS_XPLAN、OPTIMIZER TRACE、SQL改写、NESTED LOOP、OUTER JOIN、HASH JOIN、ERD图、HINT、SORT MERGE、Materialized View、ROWNUM。

学完本课程,无论您是DBA工程师、运维工程师,还是开发工程师,抑或系统架构师、技术主管,都将大幅增强您的职场实力,加薪50%轻轻松松。此外,我们也会将优秀的学员直接推向各大一线互联网公司。

有兴趣的同学可以扫码加入知数堂QQ群  579036588 关注课程进展。

不再加原创

喜欢就转发

打赏可勾搭


靠谱好茶&在线培训,都在〖老叶茶馆〗http://yejinrong.com

相关 [分区 sql 优化] 推荐:

分区表场景下的 SQL 优化

- - IT瘾-dev
有个表做了分区,每天一个分区. 该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗. 有一个大表,每天产生的数据量约100万,所以就采用表分区方案,每天一个分区. 该表上经常发生下面的慢查询:. 想要优化一个SQL,一般来说就是先看执行计划,观察 是否尽可能用到索引,同时要关注 预计扫描的行数,以及 是否产生了临时表(Using temporary) 或者  是否需要进行排序(Using filesort),想办法消除这些情况.

sql优化

- - 数据库 - ITeye博客
是对数据库(数据)进行操作的惟一途径;. 消耗了70%~90%的数据库资源;独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低;. 可以有不同的写法;易学,难精通. 固定的SQL书写习惯,相同的查询尽量保持相同,存储过程的效率较高. 应该编写与其格式一致的语句,包括字母的大小写、标点符号、换行的位置等都要一致.

sql 优化

- - SQL - 编程语言 - ITeye博客
转:数据库SQL优化大总结之 百万级数据库优化方案. 2014-07-18 09:33 雲霏霏 雲霏霏的博客 字号:. 网上关于SQL优化的教程很多,但是比较杂乱. 近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充. 网上关于SQL优化的教程很多,但是比较杂乱. 近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充.

优化sql查询

- - 数据库 - ITeye博客
1、 首先要搞明白什么叫执行计划. 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用 “全表扫描”方式.

索引SQL优化

- - SQL - 编程语言 - ITeye博客
(一)深入浅出理解索引SQL优化 (转).         实际上,您可以把索引理解为一种特殊的目录. 微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引). 下面,我们举例来说明一下聚集索引和非聚集索引的区别:  .

sql语句优化

- - 数据库 - ITeye博客
性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化. 为了获得稳定的执行性能,SQL语句越简单越好. 对复杂的SQL语句,要设法对之进行简化. 1)不要有超过5个以上的表连接(JOIN). 2)考虑使用临时表或表变量存放中间结果.

SQL Server优化50法

- - CSDN博客推荐文章
虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷).   I/O吞吐量小,形成了瓶颈效应.   没有创建计算列导致查询不优化.   内存不足网络速度慢查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

Oracle SQL性能优化

- - 数据库 - ITeye博客
(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效):. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

mysql的sql优化(前奏)

- - 数据库 - ITeye博客
要优化mysql首先要知道什么地方需要优化,然后才能针对具体问题进行优化. 什么分库分表,建立索引....摆脱不要那么官方好吗. 1.学会和培养使用mysql的查看命令的使用习惯. 什么你忘记如何创建表的语句了. SHOW CREATE TABLE Name: 'SHOW CREATE TABLE' Description: Syntax: SHOW CREATE TABLE tbl_name Shows the CREATE TABLE statement that creates the named table.