Mysql8.0秒级加字段

心已赠人 2022-09-08 05:55 148阅读 0赞

Mysql 8.0版本合并了腾讯互娱数据库团队的Patch，可以实现秒级添加字段，这个功能可谓是mysql数据库攻城狮的福音，解决了之前5.6，5.7版本添加字段很高的运维成本。

下面是验证mysql8.0版本秒级添加字段的过程

首先用sysbench模拟一张12G左右的大表出来，数据量在5000W

nohup sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=10.10.57.205 --mysql-port=3306 --mysql-user=root --mysql-password='Test1234!' --oltp-test-mode=complex --oltp-tables-count=1 --oltp-table-size=50000000 --threads=10 prepare

登录数据库，查看一下sysbench生成的表

mysql> show databases;
    
    +--------------------+
    
    | Database           |
    
    +--------------------+
    
    | information_schema |
    
    | mysql              |
    
    | performance_schema |
    
    | sbtest             |
    
    | sys                |
    
    +--------------------+
    
    5 rows in set (0.00 sec)
    
    
    
    mysql> use sbtest;
    
    Reading table information for completion of table and column names
    
    You can turn off this feature to get a quicker startup with -A
    
    
    
    Database changed
    
    mysql> show tables;
    
    +------------------+
    
    | Tables_in_sbtest |
    
    +------------------+
    
    | sbtest1          |
    
    +------------------+
    
    1 row in set (0.00 sec)
    
    
    
    mysql> select count(*) from sbtest1;
    
    +----------+
    
    | count(*) |
    
    +----------+
    
    | 50000000 |
    
    +----------+
    
    1 row in set (17.84 sec)

查看一下表对应的文件大小

[root@cbov10-205 sbtest]# du -sh *
    
    12G     sbtest1.ibd

查看测试的表结构

mysql> show create table sbtest1\G;
    
    *************************** 1. row ***************************
    
           Table: sbtest1
    
    Create Table: CREATE TABLE `sbtest1` (
    
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    
      `k` int(10) unsigned NOT NULL DEFAULT '0',
    
      `c` char(120) NOT NULL DEFAULT '',
    
      `pad` char(60) NOT NULL DEFAULT '',
    
      PRIMARY KEY (`id`),
    
      KEY `k_1` (`k`)
    
    ) ENGINE=InnoDB AUTO_INCREMENT=50000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
    
    1 row in set (0.00 sec)

在12G大小的表上添加一列，果然是秒级返回

mysql> alter table sbtest1 add column name char(60) not null default '';
    
    Query OK, 0 rows affected (0.18 sec)
    
    Records: 0  Duplicates: 0  Warnings: 0
    
    
    
    mysql> show create table sbtest1\G;
    
    *************************** 1. row ***************************
    
           Table: sbtest1
    
    Create Table: CREATE TABLE `sbtest1` (
    
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    
      `k` int(10) unsigned NOT NULL DEFAULT '0',
    
      `c` char(120) NOT NULL DEFAULT '',
    
      `pad` char(60) NOT NULL DEFAULT '',
    
      `name` char(60) NOT NULL DEFAULT '',
    
      PRIMARY KEY (`id`),
    
      KEY `k_1` (`k`)
    
    ) ENGINE=InnoDB AUTO_INCREMENT=50000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
    
    1 row in set (0.00 sec)

参考链接：

Mysql8.0秒级加字段

https://mp.weixin.qq.com/s/K7EisTWJQH7EfIT1XXslow