第44期:无主键分区表该不该使用

2022-11-16 16:06:55 浏览数 (1)

本来想着分区表在上一篇后就不续写了,最近又有同学咨询我分区表的新问题:无主键的分区表建议使用吗? 在此基础上的索引该如何设计? 基于这两个问题,我们来简单探讨下。

1.无主键的分区表。

对于MySQL分区表来讲,有一个强制规定:分区键必须是主键或者是主键的交集!

但是对于没有主键的分区表,则没有这个规定, 可以选用任何一个键来作为分区键。比如下面表p1和表p2,都无显式定义主键。表p1的分区键为c1,表p2的分区键为c2。

代码语言:sql复制
<mysql:8.0.31:(ytt)>create table p1(c1 int,c2 int,c3 int) partition by hash(c1) partitions 10;
Query OK, 0 rows affected (0.22 sec)
   
<mysql:8.0.31:(ytt)>create table p2(c1 int,c2 int,c3 int) partition by hash(c2) partitions 10;
Query OK, 0 rows affected (0.18 sec)

这种无主键的分区表使用场景不具备普遍性。 例如对于业务检索语句包含主键的需求,无主键的表无法简单实现。

下面要定位到表p1的第800001行(表p1有100W行记录,忽略造数据过程),那编写的SQL语句就比有主键的表多出很多步骤,而且性能很差。

我们这样写SQL: 时间0.27秒。

代码语言:sql复制
<mysql:8.0.31:(ytt)>select * from p1 where 1 limit 800000,1;
 ------ ------ ------ 
| c1   | c2   | c3   |
 ------ ------ ------ 
|   97 |   32 |  139 |
 ------ ------ ------ 
1 row in set (0.27 sec)

或者可以这样写SQL:时间1.85秒。

代码语言:sql复制
<mysql:8.0.31:(ytt)>select c1,c2,c3 from (select *,row_number() over() as sn from p1) T where sn=800001;
 ------ ------ ------ 
| c1   | c2   | c3   |
 ------ ------ ------ 
|   97 |   32 |  139 |
 ------ ------ ------ 
1 row in set (1.85 sec)

上面两条SQL都可以实现目标,但是效率都不高。第一条全表扫,严重依赖于磁盘的性能;第二条用子查询,依赖于MySQL自身对子查询的处理性能。

基于对比,我们来创建一张有主键的表p3,复制表p1的数据。

代码语言:sql复制
<mysql:8.0.31:(ytt)>create table p3(id int auto_increment,c1 int,c2 int,c3 int,primary key(id,c1)) partition by hash(c1) partitions 10;
Query OK, 0 rows affected (0.25 sec)
   
<mysql:8.0.31:(ytt)>insert p3(c1,c2,c3) table p1;
Query OK, 1000000 rows affected (6.61 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

再次实现同样的检索:由于同样的检索基于主键,在MySQL里最优,速度最快。

代码语言:sql复制
<mysql:8.0.31:(ytt)>select c1,c2,c3 from p3 where id = 800001;
 ---- ------ ------ 
| c1 | c2   | c3   |
 ---- ------ ------ 
| 97 |   32 |  139 |
 ---- ------ ------ 
1 row in set (0.00 sec)

所以无主键的分区表和无主键的非分区表一样,仅限于数据量小并且检索不频繁的场景。

2.无主键分区表的索引设计。

既然有这样的场景存在,那么这样的表该如何建立索引?

为了对比,我们复制表p1为p1_copy.

代码语言:sql复制
<mysql:8.0.31:(ytt)>create table p1_copy like p1;
Query OK, 0 rows affected (0.19 sec)
   
<mysql:8.0.31:(ytt)>insert p1_copy table p1;
Query OK, 1000000 rows affected (5.46 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
与之前我们探讨的分区表类似,基于两点:
1.查询包含分区键(我们这里是HASH分区,所以默认限制为等值检索)。

对于如下检索,因为是全表扫,无索引性能严重依赖磁盘性能: 时间0.04秒。

代码语言:sql复制
<mysql:8.0.31:(ytt)>select count(*) from p1 where c1 = 10 and c2 =10;
 ---------- 
| count(*) |
 ---------- 
|      115 |
 ---------- 
1 row in set (0.04 sec)

对于包含分区键的查询,建立索引没有必要包含分区键,因为分区键本身就已经可以精确定位到某一个分区。

代码语言:sql复制
<mysql:8.0.31:(ytt)>alter table p1_copy add key idx_c2(c2);
Query OK, 0 rows affected (6.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行同样的查询:时间为0秒。

代码语言:sql复制
<mysql:8.0.31:(ytt)>select count(*) from p1_copy where c1 = 10 and c2 =10;
 ---------- 
| count(*) |
 ---------- 
|      115 |
 ---------- 
1 row in set (0.00 sec)
2.查询不包含分区键。

对于查询不包含分区的检索语句,也没有必要包含分区键,因为会扫描所有分区,即使包含了分区键,也会扫描所有分区,对查询无益,对表的后期更新也无益。

所以最终的结论是对于无主键的分区表,在权衡各方利弊后,分区键都没有必要包含在索引定义里。


关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!

0 人点赞