MySQL 添加主键减少磁盘空间的使用
2024-02-18 17:25 abce 阅读(26) 评论(0) 编辑 收藏 举报测试使用的版本：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 应该是一个双赢的解决方案。
总之：
- 检查一下改变索引类型是否能节省磁盘空间也许是值得的！
- 如果由于应用程序限制而无法添加主键，可以考虑使用隐形主键！
