MySQL 添加主键减少磁盘空间的使用

测试使用的版本：MySQL 8.0.32

时至今日的 MySQL 8.3.0，默认都是不需要在定义表的时候定义主键。不过 Group Replication 和 Percona XtraDB Cluster (PXC) 缺省不支持没有主键的表。表缺少主键会有很多负面的性能影响，最大的确定是影响复制的速度。

今天，来简单说说使用主键的另一个原因：磁盘上的数据大小！

 

创建一个示例表：

?

1 2 3 4 5 6 7 8 9 10 11 12

> show create table t\G *************************** 1. row ***************************        Table: t Create Table: CREATE TABLE `t` (   `a` bigint NOT NULL,   `b` bigint DEFAULT NULL,   KEY `a` (`a`),   KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)   >

插入条 10000000 记录

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

DELIMITER //   CREATE PROCEDURE InsertRandomData() BEGIN     DECLARE i INT DEFAULT 0;     WHILE i < 10000000 DO         INSERT INTO t (a, b)         VALUES             (i+1, FLOOR(RAND() * 4294967295));         SET i = i + 1;     END WHILE; END //   DELIMITER ;   CALL InsertRandomData();

查看记录数和磁盘空间的占用情况

?

1 2 3 4 5 6 7 8 9 10

> select count(*) from t; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (0.23 sec)   # ls -lh t.ibd -rw-r----- 1 mysql mysql 936M Feb 17 18:13 t.ibd

磁盘使用的空间是 936M。

 

此时，表上没有主键，只创建了两个辅助索引。但是通过以下的查询，可以看到三个索引:

?

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

> select SPACE,INDEX_ID,i.NAME as index_name, t.NAME as table_name, CLUST_INDEX_SIZE, OTHER_INDEX_SIZE from information_schema.INNODB_INDEXES i JOIN information_schema.INNODB_TABLESPACES t USING(space) JOIN information_schema.INNODB_TABLESTATS ts WHERE t.NAME=ts.NAME AND t.NAME='abc/t'\G *************************** 1. row ***************************            SPACE: 27211         INDEX_ID: 28360       index_name: b       table_name: abc/t CLUST_INDEX_SIZE: 25340 OTHER_INDEX_SIZE: 30066 *************************** 2. row ***************************            SPACE: 27211         INDEX_ID: 28359       index_name: a       table_name: abc/t CLUST_INDEX_SIZE: 25340 OTHER_INDEX_SIZE: 30066 *************************** 3. row ***************************            SPACE: 27211         INDEX_ID: 28358       index_name: GEN_CLUST_INDEX       table_name: abc/t CLUST_INDEX_SIZE: 25340 OTHER_INDEX_SIZE: 30066 3 rows in set (0.17 sec)   >

这是因为多了一个 GEN_CLUST_INDEX 索引。

 

接下来，尝试将二级辅助索引改成主键

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

> select count(DISTINCT(a)) from t; +--------------------+ | count(DISTINCT(a)) | +--------------------+ |           10000000 | +--------------------+ 1 row in set (7.60 sec)   > alter table t add primary key(a), drop key a; Query OK, 0 rows affected (53.84 sec) Records: 0  Duplicates: 0  Warnings: 0   > show create table t\G *************************** 1. row ***************************        Table: t Create Table: CREATE TABLE `t` (   `a` bigint NOT NULL,   `b` bigint DEFAULT NULL,   PRIMARY KEY (`a`),   KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)   >

再次查看磁盘空间的占用情况

?

1 2	# ls -lh t.ibd -rw-r----- 1 mysql mysql 680M Feb 18 10:35 t.ibd

只有 680M 的大小。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

> select SPACE,INDEX_ID,i.NAME as index_name, t.NAME as table_name, CLUST_INDEX_SIZE, OTHER_INDEX_SIZE from information_schema.INNODB_INDEXES i JOIN information_schema.INNODB_TABLESPACES t USING(space) JOIN information_schema.INNODB_TABLESTATS ts WHERE t.NAME=ts.NAME AND t.NAME='abc/t'\G *************************** 1. row ***************************            SPACE: 27212         INDEX_ID: 28362       index_name: b       table_name: abc/t CLUST_INDEX_SIZE: 26750 OTHER_INDEX_SIZE: 15864 *************************** 2. row ***************************            SPACE: 27212         INDEX_ID: 28361       index_name: PRIMARY       table_name: abc/t CLUST_INDEX_SIZE: 26750 OTHER_INDEX_SIZE: 15864 2 rows in set (0.15 sec)   >

 

隐藏的（内部）聚簇索引（GEN_CLUST_INDEX）与生成的不可见主键（GIPK）

每个 InnoDB 表都有一个聚簇键，因此不定义一个聚簇键不会节省任何磁盘空间，有时甚至恰恰相反，如上文所示。因此，即使有问题的表的现有列都不是唯一的，最好还是添加另一个唯一列作为主键。内部的 GEN_CLUST_INDEX 不会暴露给 MySQL 上层，只有 InnoDB 引擎知道它，因此它对复制速度毫无用处。因此，显式主键是更好的解决方案。

不过，如果由于传统应用程序的问题而无法添加新的 PK 列，那么还是应该使用隐形主键来强制执行主键。这样，你就能获得性能上的优势，同时，这种变化对应用程序也是透明的。

 

让我们看看它在实践中是如何运行的：

?

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

> set sql_require_primary_key=1;   > create table nopk (a int); ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.   > set sql_generate_invisible_primary_key=1;   > create table nopk (a int);   > show create table nopk\G *************************** 1. row ***************************        Table: nopk Create Table: CREATE TABLE `nopk` (   `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,   `a` int DEFAULT NULL,   PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)   > select * from nopk; +------+ | a    | +------+ |  100 | +------+

因此，应用程序根本不知道新列的存在。但如果需要，我们仍然可以使用它，例如，轻松地将表读取或写入分割成可预测的块：

?

1 2 3 4 5 6

mysql > select my_row_id,a from nopk; +-----------+------+ | my_row_id | a    | +-----------+------+ |         1 |  100 | +-----------+------+

对于缺少主键的现有表，在执行 sql_require_primary_key 变量之前，最好先启用 sql_generate_invisible_primary_key，然后使用逻辑转储和还原重新创建数据。简单的表优化不会添加隐形 PK。无论如何，对于传统应用程序来说，不可见 PK 应该是一个双赢的解决方案。

 

总之：

- 检查一下改变索引类型是否能节省磁盘空间也许是值得的！

- 如果由于应用程序限制而无法添加主键，可以考虑使用隐形主键！