分区表下做SQL分区表查询优化示范

发布时间：2022-04-22 10:19:10 所属栏目：系统来源：互联网

导读：一般，我们建立数据库表时，表数据都存放在一个文件，而对表做分区之后，就可以把一个数据文件拆分到多个数据文件中，这样有利于数据操作和提高效率。对于大量数据的数据表，我们做分区是有必要的，为了提高SQL的执行效率，做SQL优化也很重要的。下面分享一

一般，我们建立数据库表时，表数据都存放在一个文件，而对表做分区之后，就可以把一个数据文件拆分到多个数据文件中，这样有利于数据操作和提高效率。对于大量数据的数据表，我们做分区是有必要的，为了提高SQL的执行效率，做SQL优化也很重要的。下面分享一下在分区表场景下如何做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优化之路

       1.SQL优化思路

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

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

       2.SQL性能瓶颈定位

       现在，我们来看下这个SQL的执行计划：

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE
`date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'G

       3.优化思考

       我们注意到这个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

（编辑：开发网_开封站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

如何优雅的舍弃 CentO	戴尔科技助力企业巩固
Linkerd Service Mesh	如何利用光盘重装系统