分区表场景下的 SQL 优化

导读

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

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

待优化场景

有一个大表，每天产生的数据量约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的执行计划：

[e-mail][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，剩下的条件貌似就没有合适的索引了吧？

所以，我们尝试新建一个索引：

[e-mail][mydb]> alter table t1 add index iid (iid, icnt);

然后，把sql改造成下面这样，再看下执行计划：

[e-mail][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
这优化效果，杠杠滴。

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

[e-mail][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分区表优化的资料请关注其它相关文章！