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