MySql--表信息相关

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 0 本文字数： 2.7k 阅读时长 ≈ 10 分钟

摘要

常见面试题

表信息相关

查看建表语句（包括之后对表的修改）

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `id` int NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

# 只查看字段信息
mysql> desc actor;

查看表信息

方式1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from information_schema.TABLES where TABLE_SCHEMA='test_db' and TABLE_NAME='tbl_test_info'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test_db
     TABLE_NAME: tbl_test_info
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 199
 AVG_ROW_LENGTH: 7986
    DATA_LENGTH: 1589248
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 49152
      DATA_FREE: 4194304
 AUTO_INCREMENT: 394
    CREATE_TIME: 2022-09-08 03:52:27
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_bin
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT: 测试表

方式2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> use test_db;
mysql> show table status like 'tbl_test_info'\G
*************************** 1. row ***************************
           Name: tbl_test_info
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 199
 Avg_row_length: 7986
    Data_length: 1589248
Max_data_length: 0
   Index_length: 49152
      Data_free: 4194304
 Auto_increment: 394
    Create_time: 2022-09-08 03:52:27
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_bin
       Checksum: NULL
 Create_options:
        Comment: 测试表

查看表字段信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 方式1
mysql> show full columns from test_db.tbl_test_info;
+-------------+--------------+-------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+-----------------------------+
| Field       | Type         | Collation   | Null | Key | Default           | Extra                                         | Privileges                      | Comment                     |
+-------------+--------------+-------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+-----------------------------+
| id          | int          | NULL        | NO   | PRI | NULL              | auto_increment                                | select,insert,update,references | 主键                        |
| source_id   | int          | NULL        | NO   | MUL | NULL              |                                               | select,insert,update,references | 来源id                      |
| source      | int          | NULL        | NO   |     | NULL              |                                               | select,insert,update,references | 来源，1：百度           |
| name        | varchar(100) | utf8mb4_bin | NO   |     | NULL              |                                               | select,insert,update,references | 分类                        |
| name_cn     | varchar(100) | utf8mb4_bin | NO   |     | NULL              |                                               | select,insert,update,references | 分类-中文                   |
| age         | varchar(100) | utf8mb4_bin | NO   |     | NULL              |                                               | select,insert,update,references | 适合的年龄段                |
| bookNum     | int          | NULL        | YES  |     | 0                 |                                               | select,insert,update,references | 该分类下小说的数量          |
| create_time | timestamp    | NULL        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             | select,insert,update,references | 创建时间                    |
| update_time | timestamp    | NULL        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references | 更新时间                    |
+-------------+--------------+-------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+-----------------------------+

# 方式2
mysql> select COLUMN_NAME 列名, COLUMN_TYPE 数据类型,DATA_TYPE 字段类型,CHARACTER_MAXIMUM_LENGTH 长度,IS_NULLABLE 是否为空,COLUMN_DEFAULT 默认值,COLUMN_COMMENT 备注 ,column_key 约束 from information_schema.columns  where table_schema='test_db' and table_name='tbl_test_info';
+-------------+--------------+--------------+--------+--------------+-------------------+-----------------------------+--------+
| 列名        | 数据类型     | 字段类型     | 长度   | 是否为空     | 默认值            | 备注                        | 约束   |
+-------------+--------------+--------------+--------+--------------+-------------------+-----------------------------+--------+
| id          | int          | int          |   NULL | NO           | NULL              | 主键                        | PRI    |
| source_id   | int          | int          |   NULL | NO           | NULL              | 来源id                      | MUL    |
| source      | int          | int          |   NULL | NO           | NULL              | 来源，1：百度           |        |
| name        | varchar(100) | varchar      |    100 | NO           | NULL              | 分类                        |        |
| name_cn     | varchar(100) | varchar      |    100 | NO           | NULL              | 分类-中文                   |        |
| age         | varchar(100) | varchar      |    100 | NO           | NULL              | 适合的年龄段                |        |
| bookNum     | int          | int          |   NULL | YES          | 0                 | 该分类下小说的数量          |        |
| create_time | timestamp    | timestamp    |   NULL | NO           | CURRENT_TIMESTAMP | 创建时间                    |        |
| update_time | timestamp    | timestamp    |   NULL | NO           | CURRENT_TIMESTAMP | 更新时间                    |        |
+-------------+--------------+--------------+--------+--------------+-------------------+-----------------------------+--------+

基于其它表创建新的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 只创建表结构，完整表结构
mysql> create table table_new like table_old;
# 将老表数据导入新表，要求新表和老表的表结构必须一模一样
mysql> insert into table_new select * from table_old;
# 将老表数据导入新表，自己关联新表和老表的字段
mysql> insert into table_new(字段1,字段2,…….) select 字段1,字段2,…….  from table_old;
# 清空表数据
mysql> truncate table table_new;
# 删除表
mysql> drop table table_new;

# 创建新表的同时将老表数据导入，这种建表方式不会创建索引，不推荐使用
mysql> create table table_new select * from table_old;
# 只会创建表结构，同样不会创建索引等信息
mysql> create table table_new select * from table_old where 1=2;

千万级表新增字段怎么处理

核心就一句话：千万级表绝对不要默默 COPY 重建。8.0 加列优先走 ALGORITHM=INSTANT，只改数据字典，耗时和表大小无关；走不了再 INPLACE；还是不放心或磁盘不够，再用 gh-ost / pt-online-schema-change 在线改。加列和加索引、回填数据必须拆开，不要写在一条 ALTER 里。

三种 ALGORITHM

算法 干什么 千万级能不能用
INSTANT 只改数据字典，旧行不重写，读时用元数据里的默认值补上 首选。秒级，几乎不锁表
INPLACE 可能重建表，但多数情况允许并发 DML（LOCK=NONE） 次选。要额外磁盘（大约一张表的大小），结束时可能短暂停写
COPY 新建表 → 拷数据 → 改名替换，全程挡住写入 禁止作为默认手段。千万级机械盘可能数小时，从库也会卡住

8.0.12 起加列默认就会尽量用 INSTANT；8.0.29（本文 8.0.30 已包含）起可以加在表的任意位置，也可以 INSTANT DROP COLUMN。显式写上 ALGORITHM=INSTANT，不支持就立刻报错，避免被悄悄降级成 COPY。

1
2
3
4
5
6
7
8
9
10
11
12
-- 先看行格式、表大小（DATA_LENGTH 单位字节）
SHOW TABLE STATUS LIKE 't'\G
SHOW INDEX FROM t WHERE Index_type = 'FULLTEXT';

SELECT NAME, ROW_FORMAT, INSTANT_COLS, TOTAL_ROW_VERSIONS
FROM information_schema.INNODB_TABLES
WHERE NAME = 'db/t';

-- 只加列，带默认值，强制瞬时完成
ALTER TABLE t
  ADD COLUMN extra_status TINYINT NOT NULL DEFAULT 0 COMMENT '额外状态',
  ALGORITHM=INSTANT;

Query OK, 0 rows affected 才是 INSTANT 的正常样子：0 行被重写。

INSTANT 加列的限制（8.0.30）

下面这些会让瞬时加列失败，需要换 INPLACE 或工具：

  • ROW_FORMAT=COMPRESSED、临时表、数据字典表空间里的表

  • 表上有 FULLTEXT 索引（曾经有过全文索引、后来删掉也可能残留，需 OPTIMIZE TABLE 重建后再试）

  • 一条 ALTER 里混了不支持 INSTANT 的操作，比如同时 ADD INDEX、改列类型。加列和建索引一定要拆成两条

  • 存储型生成列（STORED GENERATED）不能瞬时加；虚拟列可以

  • 加完后行的最大可能长度超限

  • 瞬时加/删列会增加行版本，TOTAL_ROW_VERSIONS 上限 64。到顶会报 Maximum row versions reached，需要 OPTIMIZE TABLE 或一次 INPLACE 重建把版本清零

VARCHAR 扩长度是否 INSTANT，见 MySql索引 里 VARCHAR(50) 与 VARCHAR(500) 那节：跨过 255 字节边界会改长度前缀，不能瞬时完成。

INSTANT 走不了时

1
2
3
4
-- 允许并发读写的原地重建，显式禁止 COPY
ALTER TABLE t
  ADD COLUMN extra_status TINYINT NOT NULL DEFAULT 0,
  ALGORITHM=INPLACE, LOCK=NONE;

注意：

  • 磁盘至少留出当前表数据 + 索引那么大的空闲空间，INPLACE 重建相当于另写一份

  • 关注 innodb_online_alter_log_max_size：DDL 期间的 DML 打进在线变更日志，这个值太小会中途失败

  • 主从环境下，主库跑完 COPY/INPLACE 从库还要再跑一遍，千万级很容易把从库延迟打爆。INSTANT 没有这个问题

主从延迟、锁等待已经很高、磁盘余量不到 1.5 倍表大小时，不要在主库硬刚 INPLACE，改用在线改表工具（下面单独说 gh-ost）。

业务低峰、可接受短暂停写时，才考虑维护窗口里的 COPY。

gh-ost 是什么

gh-ost（GitHub Online Schema Transmogrifier）是 GitHub 开源的在线改表工具：https://github.com/github/gh-ost。它不走 MySQL 自带的 ALTER TABLE，而是在实例旁边另建一张「影子表」，改完结构、拷完数据、追上增量后，再把影子表和原表改名换过来。

工作过程可以记成四步：

  • 1.CREATE TABLE t_ghc LIKE t，再对影子表执行你的 ALTER（这时影子表是空的，DDL 瞬间完成）

  • 2.按主键分批 INSERT INTO t_ghc SELECT ... FROM t，把存量数据拷过去

  • 3.挂成假从库，读 binlog（必须 ROW 格式）把拷贝期间发生的 INSERT/UPDATE/DELETE 回放到影子表，一直追到接近实时

  • 4.短暂锁原表、再追一次 binlog、RENAME TABLE t TO t_old, t_ghc TO t，切换通常只要几百毫秒到几秒

和 Percona 的 pt-online-schema-change（pt-osc）比，关键差别是增量不靠触发器：

gh-ost pt-osc
增量怎么追 读 binlog 回放 在原表上建 AFTER 触发器，把变更写入影子表
主库压力 较低，触发器那笔额外写入没有了 每次 DML 多打一次触发器，高峰容易把主库打满
能否暂停 可以随时限流、暂停，连上再继续 触发器停不干净，中途停更麻烦
切换 自己控制 cut-over，可先在从库迁再切主 同样 RENAME，但触发器要一起摘
前提 表要有主键或非空唯一键；binlog 为 ROW；磁盘再留一张表的空间 同样要主键和大约一张表的磁盘

什么时候才需要它：INSTANT / INPLACE 用不了，或者表太大、从库已经延迟、不敢赌 InnoDB 在线 DDL 结束那一下暂停。千万级、亿级改列类型、重建主键这类「必定拷表」的 DDL，才是 gh-ost 的主场。8.0 瞬时加一个带默认值的列，没必要上它。

1
2
3
4
5
6
7
# 先 --verbose 空跑看计划，确认无误再 --execute
gh-ost \
  --user=root --password=xxx --host=127.0.0.1 --database=db --table=t \
  --alter="ADD COLUMN extra_status TINYINT NOT NULL DEFAULT 0" \
  --allow-on-master --exact-rowcount --concurrent-rowcount \
  --chunk-size=1000 --max-load=Threads_running=30 \
  --verbose

切换前它会等业务低峰（可用 --postpone-cut-over-flag-file 自己决定何时切）。拷数据期间原表照常读写；代价是磁盘上会同时存在原表 + 影子表，千万级就是再买一份表那么大的空间。

一句话：gh-ost 是「用影子表 + binlog 做在线 DDL」的外部工具，不是 MySQL 引擎特性。能 INSTANT 就别用它。

回填和加索引不要跟加列绑在一起

INSTANT 加列只是让旧行「读起来有默认值」，并没有 UPDATE 过每一行。如果要把历史数据改成别的值：

1
2
3
-- 按主键分段，小事务提交，避免长事务和 binlog 爆炸
UPDATE t SET extra_status = 1
 WHERE id > 1000000 AND id <= 1010000 AND extra_status = 0;

每批几千到几万行，看 QPS 和从库延迟再调。千万级全表一个 UPDATE 等于自己造一条超级慢 SQL，排查方法见 MySql--慢查询。

需要按新列查，再建索引，单独一条：

1
2
ALTER TABLE t ADD INDEX idx_extra_status (extra_status),
  ALGORITHM=INPLACE, LOCK=NONE;

建二级索引是 INPLACE、不重建聚簇索引，但千万级仍然要扫表，选业务低峰。索引代价见 MySql索引。

MySql8 千万级表新增字段怎么处理？

  • 先问能不能瞬时加列：ROW_FORMAT 不是 COMPRESSED、没有全文索引、ALTER 里只加列、给好 DEFAULT。然后 ALGORITHM=INSTANT 强制，成功则与 1 千万还是 1 亿行无关，秒级完成。
  • 8.0.29 起可以加在任意位置；瞬时加/删列有 64 次行版本上限，到顶先 OPTIMIZE TABLE 再继续。
  • INSTANT 失败再 ALGORITHM=INPLACE, LOCK=NONE，准备大约一张表大小的磁盘，并盯从库延迟。磁盘不够、从库已经延迟、业务不能赌结束时的暂停，用 gh-ost。
  • 千万级禁止默认 COPY。加列、回填、加索引三条 SQL 拆开：列用 INSTANT，数据按主键分批 UPDATE，索引单独 INPLACE。