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

测试使用的版本：MySQL 8.0.32

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

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

 

创建一个示例表：

> 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 记录

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

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

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

 

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

> 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 索引。

 

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

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

>

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

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

只有 680M 的大小。

> 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 列，那么还是应该使用隐形主键来强制执行主键。这样，你就能获得性能上的优势，同时，这种变化对应用程序也是透明的。

 

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

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

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

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 应该是一个双赢的解决方案。

 

总之：

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

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