分区表场景下的 SQL 优化

这篇文章主要介绍了分区表场景下的 SQL 优化,帮助大家更好的理解和学习SQL,感兴趣的朋友可以了解下

导读

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

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

待优化场景

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

下面是该表的DDL:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

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,

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

?

1

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

SQL优化之路

SQL优化思路

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

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

SQL性能瓶颈定位

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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

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

?

1

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

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

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分区表优化的资料请关注钦钦技术栈其它相关文章!

原文链接:https://cloud.tencent.com/developer/article/1005390

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/21429/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-13 11:20:43
下一篇 2022-09-13 11:20:49

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!