mysql 8.0 add column、drop column 的实验

超、凢脫俗 2023-03-03 14:29 8阅读 0赞

os: centos 7.4.1708  
db: mysql 8.0.20

# create table #

mysql> show create table part_range\G
    *************************** 1. row ***************************
           Table: part_range
    Create Table: CREATE TABLE `part_range` (
      `id` int NOT NULL,
      `fname` varchar(32) DEFAULT NULL,
      `lname` varchar(32) DEFAULT NULL,
      `hired` date NOT NULL DEFAULT '1970-01-01',
      `separated` date NOT NULL DEFAULT '9999-12-31',
      `job_code` int NOT NULL,
      `store_id` int NOT NULL,
      KEY `idx_part_range_fname` (`fname`),
      KEY `idx_part_range_id` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    /*!50100 PARTITION BY RANGE (`store_id`)
    (PARTITION p0 VALUES LESS THAN (1000000) ENGINE = InnoDB,
     PARTITION p1 VALUES LESS THAN (2000000) ENGINE = InnoDB,
     PARTITION p2 VALUES LESS THAN (3000000) ENGINE = InnoDB,
     PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
    1 row in set (0.00 sec)

# ls -l /var/lib/mysql/test/*part_range*
    -rw-r----- 1 mysql mysql 213909504 Jul 31 05:24 /var/lib/mysql/test/part_range#p#p0.ibd
    -rw-r----- 1 mysql mysql 213909504 Jul 31 05:21 /var/lib/mysql/test/part_range#p#p1.ibd
    -rw-r----- 1 mysql mysql 213909504 Jul 31 05:22 /var/lib/mysql/test/part_range#p#p2.ibd
    -rw-r----- 1 mysql mysql 213909504 Jul 31 05:22 /var/lib/mysql/test/part_range#p#p3.ibd

# add column #

mysql> alter table part_range add column pyb varchar(10);
    Query OK, 0 rows affected (4.32 sec)
    Records: 0  Duplicates: 0  Warnings: 0

# add column default #

mysql> alter table part_range add column pyb_default varchar(10) default '0';
    Query OK, 0 rows affected (8.07 sec)
    Records: 0  Duplicates: 0  Warnings: 0

两个时间都比较短，肯定不是进行 recreate table 操作

mysql> select * from part_range limit 10;
    +----+----------------------------------+----------------------------------+------------+------------+----------+----------+------+-------------+
    | id | fname                            | lname                            | hired      | separated  | job_code | store_id | pyb  | pyb_default |
    +----+----------------------------------+----------------------------------+------------+------------+----------+----------+------+-------------+
    |  1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | 2020-07-30 | 2020-07-30 |        1 |        1 | NULL | 0           |
    |  2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | 2020-07-29 | 2020-07-29 |        2 |        2 | NULL | 0           |
    |  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2020-07-28 | 2020-07-28 |        3 |        3 | NULL | 0           |
    |  4 | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | 2020-07-27 | 2020-07-27 |        4 |        4 | NULL | 0           |
    |  5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | 2020-07-26 | 2020-07-26 |        5 |        5 | NULL | 0           |
    |  6 | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 2020-07-25 | 2020-07-25 |        6 |        6 | NULL | 0           |
    |  7 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 2020-07-24 | 2020-07-24 |        7 |        7 | NULL | 0           |
    |  8 | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | 2020-07-23 | 2020-07-23 |        8 |        8 | NULL | 0           |
    |  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2020-07-22 | 2020-07-22 |        9 |        9 | NULL | 0           |
    | 10 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 | 2020-07-21 | 2020-07-21 |       10 |       10 | NULL | 0           |
    +----+----------------------------------+----------------------------------+------------+------------+----------+----------+------+-------------+
    10 rows in set (0.00 sec)

# drop column #

mysql> alter table part_range drop column pyb;
    Query OK, 0 rows affected (2 min 23.45 sec)
    Records: 0  Duplicates: 0  Warnings: 0

执行过程中观察数据库下的文件

-rw-r----- 1 mysql mysql 213909504 Jul 31 05:24 part_range#p#p0.ibd
    -rw-r----- 1 mysql mysql 213909504 Jul 31 05:21 part_range#p#p1.ibd
    -rw-r----- 1 mysql mysql 213909504 Jul 31 05:22 part_range#p#p2.ibd
    -rw-r----- 1 mysql mysql 213909504 Jul 31 05:22 part_range#p#p3.ibd
    -rw-r----- 1 mysql mysql  50331648 Jul 31 05:27 #sql-ib1213-110714257.ibd
    -rw-r----- 1 mysql mysql    147456 Jul 31 05:27 #sql-ib1214-110714258.ibd
    -rw-r----- 1 mysql mysql    147456 Jul 31 05:27 #sql-ib1215-110714259.ibd
    -rw-r----- 1 mysql mysql    147456 Jul 31 05:27 #sql-ib1216-110714260.ibd

mysql> alter table part_range drop column pyb_default;
    Query OK, 0 rows affected (2 min 45.32 sec)
    Records: 0  Duplicates: 0  Warnings: 0

同样执行过程中观察数据库下的文件

-rw-r----- 1 mysql mysql 218103808 Jul 31 05:30 part_range#p#p0.ibd
    -rw-r----- 1 mysql mysql 218103808 Jul 31 05:28 part_range#p#p1.ibd
    -rw-r----- 1 mysql mysql 218103808 Jul 31 05:29 part_range#p#p2.ibd
    -rw-r----- 1 mysql mysql 218103808 Jul 31 05:29 part_range#p#p3.ibd
    -rw-r----- 1 mysql mysql 213909504 Jul 31 05:30 #sql-ib1217-110714265.ibd
    -rw-r----- 1 mysql mysql    147456 Jul 31 05:30 #sql-ib1218-110714266.ibd
    -rw-r----- 1 mysql mysql    147456 Jul 31 05:30 #sql-ib1219-110714267.ibd
    -rw-r----- 1 mysql mysql    147456 Jul 31 05:30 #sql-ib1220-110714268.ibd

可以看到 drop column 时是做了 recreate table 操作。

所以对大表做 drop column 需要绝对慎重，一不小心就是生产事故。