投稿
  1. 极客之音首页
  2. 数据库

[译文]MySQL 8.0 中生成不可见主键（GIPK）

小半 • • 数据库 • 阅读 270

主键就像一行的英雄，在执行表上的任何任务时，它在表中具有更多有益的特性。

DBA 知道表中主键的重要性以及如何处理它。

  1. 1. 具有主键的显著功能

  2. 2. 要求

  3. 3. 启用 GIPK

  4. 4. 处理 GIPK

  5. 5. 基准测试

  6. 6. 加载数据

  7. 7. 限制

  8. 8. 结论

具有主键的显著功能

  1. 1. 执行任何在线的更改或归档

  2. 2. 更快的复制（基于行的复制）

  3. 3. 表分区

  4. 4. 主键在集群环境中是必须的（InnoDB Cluster/ Galera/ Xtradb Cluster）

  5. 5. 更好的查询性能

从8.0.30开始，不需要为表中主键维护单独的列。有一个动态全局变量sql_generate_invisible_primary_key（GIPK）。可以在不停机的情况下启用此变量。

通过启用此变量，如果任何创建的表中缺少主键，则将在表中自动创建主键和不可见列。自动生成的列名的默认名称是my_row_id。

此功能的主要优点是我们可以简化集群迁移和更快的复制同步。

该结构仅基于行的复制。

要求

Binlog format ROW
MySQL Version >= 8.0.30
Engine InnoDB

启用 GIPK

它是一个全局变量，可以在不停机的情况下动态启用它。默认情况下，它处于禁用状态。

+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+

mysql> set global sql_generate_invisible_primary_key=1;
Query OK, 0 rows affected (0.00 sec)

+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | ON    |
+------------------------------------+-------+

GIPK 常识

mysql> CREATE TABLE `gipk` (`name` varchar(50) DEFAULT NULL,  `number` int DEFAULT NULL ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql>  show create table gipkG
*************************** 1. row ***************************
       Table: gipk
Create Table: CREATE TABLE `gipk` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `name` varchar(50) DEFAULT NULL,
  `number` 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)

启用 GPIK 后，重新连接，以应用它。

在上面的示例中，创建了两个列 name 和 number。但是 MySQL 已经自动创建了另一个名为 my_row_id 的不可见主键列。

可以根据用例使列不可见或者可见。只需要用变更语句在不可见列和可见列之间切换。

mysql> ALTER TABLE gipk ALTER COLUMN my_row_id SET VISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  show create table gipkG
*************************** 1. row ***************************
       Table: gipk
Create Table: CREATE TABLE `gipk` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `number` 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)

尽管它是一个自动生成的列，但它将在创建语句中和 information_schema.columns 表中可见。

mysql>  show create table gipkG
*************************** 1. row ***************************
       Table: gipk
Create Table: CREATE TABLE `gipk` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `name` varchar(50) DEFAULT NULL,
  `number` 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)
mysql> SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "gipk";
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| my_row_id   | bigint    |
| name        | varchar   |
| number      | int       |
+-------------+-----------+
3 rows in set (0.00 sec)

通过关闭 show_gipk_in_create_table_and_information_schema，可以完全隐藏它。这样列信息将从创建表语句和 information_schema.columns 表中完全消失。

它是一个动态变量，默认情况下将处于启用状态。

+--------------------------------------------------+-------+
| Variable_name                                    | Value |
+--------------------------------------------------+-------+
| show_gipk_in_create_table_and_information_schema | ON    |
+--------------------------------------------------+-------+

mysql> set global show_gipk_in_create_table_and_information_schema=0;
Query OK, 0 rows affected (0.00 sec)

+--------------------------------------------------+-------+
| Variable_name                                    | Value |
+--------------------------------------------------+-------+
| show_gipk_in_create_table_and_information_schema | OFF   |
+--------------------------------------------------+-------+
mysql> SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "gipk";
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| name        | varchar   |
| number      | int       |
+-------------+-----------+
2 rows in set (0.00 sec)

mysql> show create table gipkG
*************************** 1. row ***************************
       Table: gipk
Create Table: CREATE TABLE `gipk` (
  `name` varchar(50) DEFAULT NULL,
  `number` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

现在列完全不可见。

处理 GIPK

在列不见状态下，不可以改变列名

mysql> ALTER TABLE gipk RENAME COLUMN my_row_id to id;

ERROR 4110 (HY000): Altering generated invisible primary key column 'my_row_id' is not allowed.

为了实现这一点，首先，需要将列可见，然后需要执行重命名列，以根据我们的方便更改列名。

mysql>  show create table gipkG
*************************** 1. row ***************************
       Table: gipk
Create Table: CREATE TABLE `gipk` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `number` 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)

mysql> ALTER TABLE gipk RENAME COLUMN my_row_id to id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table gipkG
*************************** 1. row ***************************
       Table: gipk
Create Table: CREATE TABLE `gipk` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `number` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

基准压测

已经对其进行基准测试，以确定启用 GIPK 后是否存在任意问题。

表结构：

mysql> show create table gipkG
*************************** 1. row ***************************
       Table: gipk
Create Table: CREATE TABLE `gipk` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int unsigned NOT NULL,
  `k` int unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

mysql> show create table non_gipkG
*************************** 1. row ***************************
       Table: non_gipk
Create Table: CREATE TABLE `non_gipk` (
  `id` int unsigned NOT NULL,
  `k` int unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

加载数据

表大小

+----------+----------+------------+
| Database | Table    | Size in GB |
+----------+----------+------------+
| mydbops  | non_gipk |      20.76 |
+----------+----------+------------+

+----------+-------+---------------+
| Database | Table | Table size GB |
+----------+-------+---------------+
| mydbops  | gipk  |         21.83 |
+----------+-------+---------------+

创建了具有 GIPK 和没有主键的表。使用 mysql 随机数据将数据加载到表中。令人吃惊的是，在 GIPK 和 没有主键的情况下，数据加载所需时间是相同的。因此，即使启用了 GIPK，批量加载也不会有太多延迟。

全表扫描

mysql> select * from gipk order by id limit 1;
+----+------------+------------------------------------------------------------------------+------------------------------+
| id | k          | c                                                                      | pad                          |
+----+------------+------------------------------------------------------------------------+------------------------------+
|  9 | 1542554247 | fugit sapiente consectetur ab non repudiandae ducimus laboriosam quas! | dolore veritatis asperiores. |
+----+------------+------------------------------------------------------------------------+------------------------------+
1 row in set (2 min 56.14 sec)

mysql> select * from non_gipk order by id limit 1;
+----+------------+---------------------------------------+--------------------------------------+
| id | k          | c                                     | pad                                  |
+----+------------+---------------------------------------+--------------------------------------+
|  9 | 1542554247 | voluptas facere sed dolore iure nisi. | at ipsam id voluptatem et excepturi. |
+----+------------+---------------------------------------+--------------------------------------+
1 row in set (4 min 22.99 sec)

在有 GIPK 和没有主键表的情况下执行了全表扫描， GIPK 表的性能有所提高。执行所需的时间减少了一半。

在线表结构变更和归档

为了执行更安全的在线更改和逐块归档数据，percona 工具包起着至关重要的作用。对于 percona 工具包操作（pt-osc/archiver），基本需求是主键。如果表上没有主键，该工具将无法在该表上生效。

启用 GIPK 的优点是，将拥有不可见的主键。通过使用该主键，Percona 工具可以执行在线表结构变更或者归档等操作。

[root@localhost mydbopslabs]# pt-archiver --source h=localhost,D=mydbops,t=non_gipk,u=root,p='*****' --where "1=1" --limit 5000 --progress 5000 --statistics --no-check-charset --commit-each --bulk-delete --purge --file '/home/mydbopslabs/non_gipk_%d_%m_%Y_%H_%m_%s.csv' --output-format=csv --dry-run
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Cannot find an ascendable index in table at /bin/pt-archiver line 3261.

[root@localhost mydbopslabs]# pt-archiver --source h=localhost,D=mydbops,t=gipk,u=root,p='******' --where "1=1" --limit 5000 --progress 5000 --statistics --no-check-charset --commit-each --bulk-delete --purge --file '/home/mydbopslabs/non_gipk_%d_%m_%Y_%H_%m_%s.csv' --output-format=csv --dry-run
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
/home/mydbopslabs/non_gipk_06_10_2022_02_10_05.csv
SELECT /*!40001 SQL_NO_CACHE */ `my_row_id`,`id`,`k`,`c`,`pad` FROM `mydbops`.`gipk` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND (`my_row_id` < '100000000') ORDER BY `my_row_id` LIMIT 5000
SELECT /*!40001 SQL_NO_CACHE */ `my_row_id`,`id`,`k`,`c`,`pad` FROM `mydbops`.`gipk` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND (`my_row_id` < '100000000') AND ((`my_row_id` >= ?)) ORDER BY `my_row_id` LIMIT 5000
DELETE FROM `mydbops`.`gipk` WHERE (((`my_row_id` >= ?))) AND (((`my_row_id` <= ?))) AND (1=1) LIMIT 5000

在没有主键的表上执行归档时，归档失败，但在 GIPK 表上成功，因为它有一个不可见的主键。

[root@localhost mydbopslabs]# pt-online-schema-change h=localhost,D=mydbops,t=non_gipk --user='root' --password='*****' --no-check-alter  --critical-load "Threads_running=900" --recursion-method=none --max-load  "Threads_running=1000" --no-check-plan --alter "engine=innodb" --dry-run
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
# A software update is available:
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run.  `mydbops`.`non_gipk` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table mydbops._non_gipk_new OK.
Altering new table...
Altered `mydbops`.`_non_gipk_new` OK.
The new table `mydbops`.`_non_gipk_new` does not have a PRIMARY KEY or a unique index required for the DELETE trigger.
Please check you have at least one UNIQUE and NOT NULLABLE index.
2022-10-06T02:48:59 Dropping new table...
2022-10-06T02:48:59 Dropped new table OK.
Dry run complete.  `mydbops`.`non_gipk` was not altered.
[root@localhost mydbopslabs]# pt-online-schema-change h=localhost,D=mydbops,t=gipk --user='root' --password='*****' --no-check-alter  --critical-load "Threads_running=900" --recursion-method=none --max-load  "Threads_running=1000" --no-check-plan --alter "engine=innodb" --dry-run
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run.  `mydbops`.`gipk` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table mydbops._gipk_new OK.
Altering new table...
Altered `mydbops`.`_gipk_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2022-10-06T02:49:15 Dropping new table...
2022-10-06T02:49:15 Dropped new table OK.
Dry run complete.  `mydbops`.`gipk` was not altered.

在线表结构变更和归档在没有主键的表上失败，但在 GIPK 表上成功，因为它有一个不可见主键。

限制

  • • 当建表语句中有自增列时， GIPK 失败。

  • • 仅支持 InnoDB 存储引擎。

  • • GIPK 仅支持基于行的复制。

总结

不再需要担心单独创建和维护主键。 GIPK 还解决了迁移到 InnoDB Cluster 的问题，其中主键是必须的。通过启用 sql_generate_invisible_primary_key ， 现在有了一个自动主键作为救命稻草。


原文始发于微信公众号（Mwkk）：[译文]MySQL 8.0 中生成不可见主键（GIPK）

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/100509.html

赞 (0)
小半的头像小半
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！