MySQL 8.0.12 instant add column 体验，亿级数据秒速增加字段

最新推荐文章于 2024-08-31 02:52:11 发布
vkingnew
最新推荐文章于 2024-08-31 02:52:11 发布
阅读量3.6k
点赞数
分类专栏： MySQL
本文链接：https://blog.csdn.net/vkingnew/article/details/81267320
版权
MySQL 专栏收录该内容
138 篇文章 6 订阅
订阅专栏
运行环境：CentOS 7.5+8.0.12

Instant add column功能自MySQL 8.0.12版本引入。
快速创建1亿条记录用于测试
下载sysbench
#wget https://repo.percona.com/release/7Server/os/x86_64/sysbench-1.0.15-2.el7.x86_64.rpm
# yum -y localinstall sysbench-1.0.15-2.el7.x86_64.rpm 
需要额外安装软件包：
mariadb-libs和 postgresql-libs
登录数据库创建库sbtest：
# mysql -h 192.168.0.124 -uroot -poracle -P3306
mysql> create database sbtest;
运行脚本：
sysbench /usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.0.124 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=oracle --table_size=100000000 --tables=1 --threads=24 --time=120 --report-interval=10 --db-driver=mysql prepare
sysbench /usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.0.124 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=oracle --table_size=100000000 --tables=1 --threads=24 --time=120 --report-interval=10 --db-driver=mysql run
由于本地测试环境磁盘空间不足只生成了5000万条记录，单机的话关掉binlog，若有主从的话设置失效时间为几分钟即可。
最终生成12G数据，一共5000 0000条记录。神奇的是我的虚拟机2G内存4核心居然跑出了3158的QPS。

sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 24
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 24 tps: 590.63 qps: 3547.76 (r/w/o: 0.00/2364.12/1183.65) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 24 tps: 389.08 qps: 2337.88 (r/w/o: 0.00/1559.73/778.15) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 24 tps: 392.98 qps: 2355.76 (r/w/o: 0.00/1569.91/785.85) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 24 tps: 387.28 qps: 2323.68 (r/w/o: 0.00/1549.02/774.66) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 24 tps: 451.17 qps: 2708.80 (r/w/o: 0.00/1806.46/902.34) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 24 tps: 486.47 qps: 2917.90 (r/w/o: 0.00/1944.96/972.93) lat (ms,95%): 130.13 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 24 tps: 498.59 qps: 2991.53 (r/w/o: 0.00/1994.36/997.18) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 24 tps: 523.15 qps: 3138.53 (r/w/o: 0.00/2092.22/1046.31) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 24 tps: 515.78 qps: 3095.86 (r/w/o: 0.00/2064.31/1031.55) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 24 tps: 638.36 qps: 3830.06 (r/w/o: 0.00/2553.34/1276.72) lat (ms,95%): 106.75 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 24 tps: 641.33 qps: 3845.56 (r/w/o: 0.00/2562.91/1282.65) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 24 tps: 804.92 qps: 4830.69 (r/w/o: 0.00/3220.86/1609.83) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           252948
        other:                           126474
        total:                           379422
    transactions:                        63237  (526.38 per sec.)
    queries:                             379422 (3158.27 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1332s
    total number of events:              63237

Latency (ms):
         min:                                    1.03
         avg:                                   45.56
         max:                                 1347.15
         95th percentile:                      132.49
         sum:                              2880920.77

Threads fairness:
    events (avg/stddev):           2634.8750/31.83
    execution time (avg/stddev):   120.0384/0.03


若要生成一亿的数据至少需要25G的磁盘空间。

--instant add column 体验：
--修改表名：
mysql> alter table sbtest1 rename to test;
Query OK, 0 rows affected (0.06 sec)

mysql> alter table test rename to sbtest1, algorithm=instant;
Query OK, 0 rows affected (0.03 sec)
使用了instant操作时间快一倍。
--添加字段：
mysql> alter table sbtest1 add column cityname varchar(10);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 add column cityname2 varchar(10) , algorithm=instant;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
使用instant加字段的时间是原来的1/4，不过比起MySQL5.7版本则节约了不少。

--设置默认值和删除默认值：
mysql> alter table sbtest1 alter column cityname set default 'wuhan';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 alter column cityname set default 'beijing',algorithm=instant,lock=default;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 alter column cityname  drop default;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 alter column cityname drop default,algorithm=instant;                     
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

--修改列操作：
mysql> alter table sbtest1 modify cityname2 datetime;
时间

mysql> show processlist;
+----+-----------------+-----------+--------+---------+------+------------------------+-----------------------------------------------+
| Id | User            | Host      | db     | Command | Time | State                  | Info                                          |
+----+-----------------+-----------+--------+---------+------+------------------------+-----------------------------------------------+
|  4 | event_scheduler | localhost | NULL   | Daemon  | 3189 | Waiting on empty queue | NULL                                          |
| 60 | root            | localhost | sbtest | Query   |  130 | copy to tmp table      | alter table sbtest1 modify cityname2 datetime |
| 61 | root            | localhost | NULL   | Query   |    0 | starting               | show processlist                              |
+----+-----------------+-----------+--------+---------+------+------------------------+-----------------------------------------------+
3 rows in set (0.03 sec)

虚拟列的增加和删除：
mysql> alter table sbtest1 add column (d int generated always as (k+1) virtual),algorithm=instant;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 drop column d ,algorithm=instant;                                      
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 add column (d int generated always as (k+1) virtual);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 drop column d;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
--增加索引：
mysql> alter table sbtest1 add column createtime datetime not null DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 add column modifytime datetime not null DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP,algorithm=instant;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0



目前MySQL官方支持的操作：
Change index option
Rename table (in ALTER way)
SET/DROP DEFAULT
MODIFY COLUMN
Add/drop virtual columns
Add columns(non-generated) – We call this instant ADD COLUMN
目前支持的这些操作对主从同步特别有好处，减少了主动同步的延迟。对于drop column目前还不支持，还有就是更新大事务目前也不支持。

可以通过information_schema监控快速加字段的信息：
mysql> select * from information_schema.INNODB_TABLES where name like 'sbtest%';
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1062 | sbtest/sbtest1 |   33 |     11 |     5 | Dynamic    |             0 | Single     |            4 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.01 sec)

mysql> select * from information_schema.innodb_columns where table_id=1062;
+----------+------------+-----+-------+----------+-----+-------------+---------------+
| TABLE_ID | NAME       | POS | MTYPE | PRTYPE   | LEN | HAS_DEFAULT | DEFAULT_VALUE |
+----------+------------+-----+-------+----------+-----+-------------+---------------+
|     1062 | id         |   0 |     6 |     1283 |   4 |           0 | NULL          |
|     1062 | k          |   1 |     6 |     1283 |   4 |           0 | NULL          |
|     1062 | c          |   2 |    13 | 14680574 | 480 |           0 | NULL          |
|     1062 | pad        |   3 |    13 | 14680574 | 240 |           0 | NULL          |
|     1062 | cityname   |   4 |    12 | 14680079 |  40 |           1 | NULL          |
|     1062 | cityname2  |   5 |    12 | 14680079 |  40 |           1 | NULL          |
|     1062 | createtime |   6 |     3 |   525580 |   5 |           1 | 99a07a451a    |
|     1062 | modifytime |   7 |     3 |   525580 |   5 |           1 | 99a07a4559    |
+----------+------------+-----+-------+----------+-----+-------------+---------------+
8 rows in set (0.02 sec)

当前版本中对instant add column有限制：
1.Only support adding columns in one statement, that is if there are other non-INSTANT operations in the same statement, it can’t be done instantly
2.Only support adding columns at last, not in the middle of existing columns
3.Not support COMPRESSED row format, which is seldom used
4.Not support a table which already has any fulltext index
5.Not support any table residing in DD tablespace
6.Not support temporary table(it goes with COPY)
参考：
https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/