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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Np45gmho-1668585609383)(https://action-weikai.oss-cn-shanghai.aliyuncs.com/SQL%E8%B0%83%E4%BC%98%20%E5%A4%B4%E5%9B%BE_20210324143331.png)]

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

1.无主键的分区表。

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

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

create table p1(c1 int,c2 int,c3 int) partition by hash(c1) partitions 10;
Query OK, 0 rows affected (0.22 sec)
   
create table p2(c1 int,c2 int,c3 int) partition by hash(c2) partitions 10;
Query OK, 0 rows affected (0.18 sec)
1
2
3
4
5

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

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

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

select * from p1 where 1 limit 800000,1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|   97 |   32 |  139 |
+------+------+------+
1 row in set (0.27 sec)
1
2
3
4
5
6
7

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

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)
1
2
3
4
5
6
7

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

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

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)
   
insert p3(c1,c2,c3) table p1;
Query OK, 1000000 rows affected (6.61 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
1
2
3
4
5
6

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

select c1,c2,c3 from p3 where id = 800001;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
| 97 |   32 |  139 |
+----+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

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

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

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

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

create table p1_copy like p1;
Query OK, 0 rows affected (0.19 sec)
   
insert p1_copy table p1;
Query OK, 1000000 rows affected (5.46 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
1
2
3
4
5
6

与之前我们探讨的分区表类似，基于两点：

1.查询包含分区键（我们这里是HASH分区，所以默认限制为等值检索）。

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

select count(*) from p1 where c1 = 10 and c2 =10;
+----------+
| count(*) |
+----------+
|      115 |
+----------+
1 row in set (0.04 sec)
1
2
3
4
5
6
7

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

alter table p1_copy add key idx_c2(c2);
Query OK, 0 rows affected (6.26 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3

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

select count(*) from p1_copy where c1 = 10 and c2 =10;
+----------+
| count(*) |
+----------+
|      115 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

2.查询不包含分区键。

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

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

---

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ETRDTh56-1668585609384)(https://action-weikai.oss-cn-shanghai.aliyuncs.com/SQL%E8%B0%83%E4%BC%98%20%E4%BD%9C%E8%80%85%E5%90%8D%E7%89%87_20210419154454.png)]