优化案例--分区表场景下的SQL优化

  • 时间:2017-05-02
  • 分类:数据库/系统
  • 536 人浏览
[导读]有个表做了分区,每天一个分区。 该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗?

待优化场景

有一个大表,每天产生的数据量约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: rangepossible_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: refpossible_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: refpossible_keys: date,date_2,iid          key: iid      key_len: 10          ref: null         rows: 7800        extra: using where 后记

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


来源:本文为线上采编,如涉及作品内容、版权和其它问题,请及时与本网联系,我们将在第一时间删除!