分区表下做SQL分区表查询优化示�
Admin 2021-04-15 群英技术资�
一般,我们建立数据库表时,表数据都存放在一个文件,而对表做分区之后,就可以把一个数据文件拆分到多个数据文件中,这样有利于数据操作和提高效率。对于大量数据的数据表,我们做分区是有必要的,为了提高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的执行计划:
[email protected][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�
3.优化思�
我们注意到这个SQL总是要查询某一天的数据,这个表已经做了按天分区,那是不是可以忽� WHERE 子句中的 时间条件呢?还有,既然去掉了 date 条件,反观表DDL,剩下的条件貌似就没有合适的索引了吧�
所以,我们尝试新建一个索引:
[email protected][myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);
然后,把SQL改造成下面这样,再看下执行计划�
[email protected][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 这优化效果,杠杠滴� 事实上,如果不强制指定分区的话,也是可以达到优化效果的: [email protected][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分区表查询优化的介绍,希望大家有所收获,更多SQL优化相关的内容可以关注其他文章�
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:[email protected]进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容�
猜你喜欢
在SQL中,我们经常会使用到查询操作,如果只是普通查询,可能会出现重复数据的情况,那么SQL要怎么实现查询且去掉重复数据呢?下面小编给大家介绍几种方法,有需要的朋友可以参考参考�
对于SQL新手来说,可能对于开窗函数不是很了解,因此这篇文章就给大家分享一些SQL开窗函数的实例,具有一定的借鉴价值,感兴趣的朋友就继续往下看吧�
FORMAT() 函数用于对字段的显示进行格式化,有不少朋友对于FORMAT() 函数的用法比较感兴趣,下面小编给大家整理和分享了相关知识和资料,易于大家学习和理解,有需要的朋友可以借鉴参考,下面我们一起来了解一下吧�
SQL通用数据类型有哪些?数据类型定义了存储在列中的值的类型。下文有详解方法和实例,内容详细,逻辑清晰,有需要的朋友可以参考,希望大家阅读完这篇文章后能有所收获,那么下面就一起来了解一下吧�
Microsoft SQL Server 事物日志中每个记录都由一个唯一的日志序列号(LSN)标识,如果 LSN2 大于 LSN1,则 LSN2 所标识的日志记录描述的更改发生在日志记� LSN1 描述的更改之后�
成为群英会员,开启智能安全云计算之旅
立即注册Copyright © QY Network Company Ltd. All Rights Reserved. 2003-2020 群英 版权所�
增值电信经营许可证 : B1.B2-20140078