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

1. 无主键的分区表

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

<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)

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

<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秒。

<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)

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

<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 里最优，速度最快。

<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. 无主键分区表的索引设计

<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秒。

<mysql:8.0.31:(ytt)>select count(*) from p1 where c1 = 10 and c2 =10;
+----------+
| count(*) |
+----------+
|      115 |
+----------+
1 row in set (0.04 sec)

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

<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秒。

<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. 查询不包含分区键。

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

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