当前位置： 首页 > news >正文

mysql-online-ddl是否需要rebuild

news 2025/8/21 16:29:19

一、背景

DDL一直是DBA业务中的大项，看了TIDB的DDL讲解，恰巧我们的mysql业务大表也遇到了DDL的变更项，变更内容是将varchar(10)变更成varchar(20),这个变更通过官方文档很容易知道是不需要rebuild的（这里要注意下这个varchar(255)的临界值与字符集有关，如果是utf8则为255/3，如果是utf8mb4则为255/4），但是问题是这个字段是索引字段，所以这里我就不太确定了，那么怎么办呢，解决办法就是看源码和测试了。

二、实验

1.打开性能监控

我们可以从官网的文章1，文章2中明白性能监控是如何打开的

mysql> UPDATE performance_schema.setup_instrumentsSET ENABLED = 'YES'WHERE NAME LIKE 'stage/innodb/alter%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0mysql> UPDATE performance_schema.setup_consumersSET ENABLED = 'YES'WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

2.建表并准备测试数据

# 创建表
mysql> create table ddl_test(id int(10) unsigned not null auto_increment primary key,name varchar(20) not null,email varchar(30) not null,key idx_name_mail(name,email));
Query OK, 0 rows affected (0.02 sec)
# 创建存储过程插入数据
DELIMITER //
CREATE PROCEDURE insert_test_data(in record int)
BEGIN
DECLARE num INT DEFAULT 0;
START TRANSACTION;
WHILE num <= record DOINSERT INTO ddl_test(name, email) values(concat("asd",num),concat("uvw",num));SET num = num+1;
END WHILE;
COMMIT;
END //
DELIMITER ;
# 调用存储过程
call insert_test_data(1000000);
# 删除存储过程drop procedure insert_test_data;

3.更改字段长度并查看性能监控

# 查看版本
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.16 sec)
# 清空事件监控表
truncate table performance_schema.events_stages_history;
# 变更测试表字段长度
alter table ddl_test change name name varchar(23) NOT NULL;
# 查看时间监控表，通过下面可以看到明显进行了rebuild操作
mysql> select * from performance_schema.events_stages_history;
+-----------+----------+--------------+------------------------------------------------------+----------------+----------------------+----------------------+---------------+----------------+----------------+------------------+--------------------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME                                           | SOURCE         | TIMER_START          | TIMER_END            | TIMER_WAIT    | WORK_COMPLETED | WORK_ESTIMATED | NESTING_EVENT_ID | NESTING_EVENT_TYPE |
+-----------+----------+--------------+------------------------------------------------------+----------------+----------------------+----------------------+---------------+----------------+----------------+------------------+--------------------+
|   1222844 |  4000077 |      4000077 | stage/innodb/alter table (read PK and internal sort) | ut0stage.h:241 | 10065196379488577152 | 10065197613867973152 | 1234379396000 |           5526 |          11290 |          4000076 | STATEMENT          |
|   1222844 |  4000078 |      4000078 | stage/innodb/alter table (merge sort)                | ut0stage.h:501 | 10065197613867973152 | 10065198518747528152 |  904879555000 |           8289 |          12306 |          4000076 | STATEMENT          |
|   1222844 |  4000079 |      4000079 | stage/innodb/alter table (insert)                    | ut0stage.h:501 | 10065198518747528152 | 10065198836121509152 |  317373981000 |          11052 |          12845 |          4000076 | STATEMENT          |
|   1222844 |  4000080 |      4000080 | stage/innodb/alter table (flush)                     | ut0stage.h:501 | 10065198836121509152 | 10065200599444653152 | 1763323144000 |          12845 |          12845 |          4000076 | STATEMENT          |
|   1222844 |  4000081 |      4000081 | stage/innodb/alter table (log apply index)           | ut0stage.h:501 | 10065200599444653152 | 10065200599846345152 |     401692000 |          13229 |          13229 |          4000076 | STATEMENT          |
|   1222844 |  4000082 |      4000082 | stage/innodb/alter table (flush)                     | ut0stage.h:501 | 10065200599846345152 | 10065200599869246152 |      22901000 |          13229 |          13229 |          4000076 | STATEMENT          |
|   1222844 |  4000083 |      4000083 | stage/innodb/alter table (end)                       | ut0stage.h:501 | 10065200599873146152 | 10065200615285329152 |   15412183000 |          13229 |          13229 |          4000076 | STATEMENT          |
+-----------+----------+--------------+------------------------------------------------------+----------------+----------------------+----------------------+---------------+----------------+----------------+------------------+--------------------+
7 rows in set (0.07 sec)

三、源码

1.inplace_alter所需的几大操作步骤：

源码地址

enum_alter_inplace_result check_if_supported_inplace_alter(TABLE*			altered_table,Alter_inplace_info*	ha_alter_info);/** Allows InnoDB to update internal structures with concurrent
writes blocked (provided that check_if_supported_inplace_alter()
did not return HA_ALTER_INPLACE_NO_LOCK).
This will be invoked before inplace_alter_table().
@param altered_table TABLE object for new version of table.
@param ha_alter_info Structure describing changes to be done
by ALTER TABLE and holding data used during in-place alter.
@retval true Failure
@retval false Success
*/
bool prepare_inplace_alter_table(TABLE*			altered_table,Alter_inplace_info*	ha_alter_info);/** Alter the table structure in-place with operations
specified using HA_ALTER_FLAGS and Alter_inplace_information.
The level of concurrency allowed during this operation depends
on the return value from check_if_supported_inplace_alter().
@param altered_table TABLE object for new version of table.
@param ha_alter_info Structure describing changes to be done
by ALTER TABLE and holding data used during in-place alter.
@retval true Failure
@retval false Success
*/
bool inplace_alter_table(TABLE*			altered_table,Alter_inplace_info*	ha_alter_info);/** Commit or rollback the changes made during
prepare_inplace_alter_table() and inplace_alter_table() inside
the storage engine. Note that the allowed level of concurrency
during this operation will be the same as for
inplace_alter_table() and thus might be higher than during
prepare_inplace_alter_table(). (E.g concurrent writes were
blocked during prepare, but might not be during commit).
@param altered_table TABLE object for new version of table.
@param ha_alter_info Structure describing changes to be done
by ALTER TABLE and holding data used during in-place alter.
@param commit true => Commit, false => Rollback.
@retval true Failure
@retval false Success
*/
bool commit_inplace_alter_table(TABLE*			altered_table,Alter_inplace_info*	ha_alter_info,bool			commit);
/** @} */bool check_if_incompatible_data(HA_CREATE_INFO*		info,uint			table_changes);

2.alter操作的几种类型划分：

源码地址

/** Operations for creating secondary indexes (no rebuild needed) */
static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ONLINE_CREATE= Alter_inplace_info::ADD_INDEX| Alter_inplace_info::ADD_UNIQUE_INDEX| Alter_inplace_info::ADD_SPATIAL_INDEX;/** Operations for rebuilding a table in place */
static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ALTER_REBUILD= Alter_inplace_info::ADD_PK_INDEX| Alter_inplace_info::DROP_PK_INDEX| Alter_inplace_info::CHANGE_CREATE_OPTION/* CHANGE_CREATE_OPTION needs to check innobase_need_rebuild() */| Alter_inplace_info::ALTER_COLUMN_NULLABLE| Alter_inplace_info::ALTER_COLUMN_NOT_NULLABLE| Alter_inplace_info::ALTER_STORED_COLUMN_ORDER| Alter_inplace_info::DROP_STORED_COLUMN| Alter_inplace_info::ADD_STORED_BASE_COLUMN| Alter_inplace_info::RECREATE_TABLE/*| Alter_inplace_info::ALTER_STORED_COLUMN_TYPE*/;/** Operations that require changes to data */
static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ALTER_DATA= INNOBASE_ONLINE_CREATE | INNOBASE_ALTER_REBUILD;/** Operations for altering a table that InnoDB does not care about */
static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_INPLACE_IGNORE= Alter_inplace_info::ALTER_COLUMN_DEFAULT| Alter_inplace_info::ALTER_COLUMN_COLUMN_FORMAT| Alter_inplace_info::ALTER_COLUMN_STORAGE_TYPE| Alter_inplace_info::ALTER_VIRTUAL_GCOL_EXPR| Alter_inplace_info::ALTER_RENAME;/** Operations on foreign key definitions (changing the schema only) */
static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_FOREIGN_OPERATIONS= Alter_inplace_info::DROP_FOREIGN_KEY| Alter_inplace_info::ADD_FOREIGN_KEY;/** 整理重点看下，改变字段长度其实就是改变了索引的长度 */
/** Operations that InnoDB cares about and can perform without rebuild */
static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ALTER_NOREBUILD= INNOBASE_ONLINE_CREATE| INNOBASE_FOREIGN_OPERATIONS| Alter_inplace_info::DROP_INDEX| Alter_inplace_info::DROP_UNIQUE_INDEX| Alter_inplace_info::RENAME_INDEX| Alter_inplace_info::ALTER_COLUMN_NAME//这里的PACK_LENGTH要注意，也可以理解成字段长度的变化，实际是指字段存储的大小的变化，比如字段是varchar,utf8mb4来看，那么varchar(10)和varchar(63)的PACK_LENGTH都是相等的| Alter_inplace_info::ALTER_COLUMN_EQUAL_PACK_LENGTH| Alter_inplace_info::ALTER_INDEX_COMMENT| Alter_inplace_info::ADD_VIRTUAL_COLUMN| Alter_inplace_info::DROP_VIRTUAL_COLUMN| Alter_inplace_info::ALTER_VIRTUAL_COLUMN_ORDER//Alter_inplace_info::ALTER_COLUMN_INDEX_LENGTH;这个定义是5.7.23才开始有的| Alter_inplace_info::ALTER_COLUMN_INDEX_LENGTH;/* | Alter_inplace_info::ALTER_VIRTUAL_COLUMN_TYPE; */

3.这里解释了什么是索引长度变更：

源码地址

/**Change column datatype in such way that new type has compatiblepacked representation with old type, so it is theoreticallypossible to perform change by only updating data dictionarywithout changing table rows.
*/
static const HA_ALTER_FLAGS ALTER_COLUMN_EQUAL_PACK_LENGTH = 1ULL << 14;/**Change in index length such that it does not require index rebuild.For example, change in index length due to column expansion likevarchar(X) changed to varchar(X + N).
*/
static const HA_ALTER_FLAGS ALTER_COLUMN_INDEX_LENGTH = 1ULL << 42;

4.这里定义了algorithm和lock的划分：

源码地址

/**Data describing the table being created by CREATE TABLE oraltered by ALTER TABLE.
*/class Alter_info
{
public:/*These flags are set by the parser and describes the type ofoperation(s) specified by the ALTER TABLE statement.They do *not* describe the type operation(s) to be executedby the storage engine. For example, we don't yet know thetype of index to be added/dropped.*/// Set for CHANGE [COLUMN] | MODIFY [CHANGE]// Set by mysql_recreate_table()static const uint ALTER_CHANGE_COLUMN         = 1L <<  2;// Set for ALTER [COLUMN] ... SET DEFAULT ... | DROP DEFAULTstatic const uint ALTER_CHANGE_COLUMN_DEFAULT = 1L <<  8;enum enum_enable_or_disable { LEAVE_AS_IS, ENABLE, DISABLE };/**The different values of the ALGORITHM clause.Describes which algorithm to use when altering the table.*/enum enum_alter_table_algorithm{// In-place if supported, copy otherwise.ALTER_TABLE_ALGORITHM_DEFAULT,// In-place if supported, error otherwise.ALTER_TABLE_ALGORITHM_INPLACE,// Copy if supported, error otherwise.ALTER_TABLE_ALGORITHM_COPY};/**The different values of the LOCK clause.Describes the level of concurrency during ALTER TABLE.*/enum enum_alter_table_lock{// Maximum supported level of concurency for the given operation.ALTER_TABLE_LOCK_DEFAULT,// Allow concurrent reads & writes. If not supported, give erorr.ALTER_TABLE_LOCK_NONE,// Allow concurrent reads only. If not supported, give error.ALTER_TABLE_LOCK_SHARED,// Block reads and writes.ALTER_TABLE_LOCK_EXCLUSIVE};/**Status of validation clause in ALTER TABLE statement. Used duringpartitions and GC alterations.*/enum enum_with_validation{/**Default value, used when it's not specified in the statement.Means WITH VALIDATION for partitions alterations and WITHOUT VALIDATIONfor altering virtual GC.*/ALTER_VALIDATION_DEFAULT,ALTER_WITH_VALIDATION,ALTER_WITHOUT_VALIDATION};

5.这里是变更索引长度的具体逻辑：

源码地址

 for (key_part= table_key->key_part, new_part= new_key->key_part;key_part < end;key_part++, new_part++){new_field= get_field_by_index(alter_info, new_part->fieldnr);/*If there is a change in index length due to column expansionlike varchar(X) changed to varchar(X + N) and has a compatiblepacked data representation, we mark it for fast/INPLACE changein index definition. Some engines like InnoDB supports INPLACEalter for such cases.In other cases, key definition has changed if we are using adifferent field or if the used key part length is different, orkey part direction has changed.*/if (key_part->length != new_part->length &&ha_alter_info->alter_info->flags == Alter_info::ALTER_CHANGE_COLUMN &&(key_part->field->is_equal((Create_field *)new_field) == IS_EQUAL_PACK_LENGTH)){ha_alter_info->handler_flags|=Alter_inplace_info::ALTER_COLUMN_INDEX_LENGTH;}else if (key_part->length != new_part->length)return true;

6.具体分析

我们通过上面的2,3,5可以发现，ALTER_COLUMN_INDEX_LENGTH在PACK_LENGTH（这个pack_length在varchar的保存实际内容的长度，而我们是utf8mb4,所以临界值是255/4=64，所以变更前后的pack_length相同）不变的情况下是norebuild的，但是我们上面的实验也确实出现了rebuild，那么这是为什么呢，我在注释中也写了，ALTER_COLUMN_INDEX_LENGTH是在5.7.23中才开始加的，而我们的实验环境是5.7.22，所以才会进行rebuild

static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ALTER_NOREBUILD= INNOBASE_ONLINE_CREATE| Alter_inplace_info::ALTER_VIRTUAL_COLUMN_ORDER//Alter_inplace_info::ALTER_COLUMN_INDEX_LENGTH;这个定义是5.7.23才开始有的| Alter_inplace_info::ALTER_COLUMN_INDEX_LENGTH;

在这里插入图片描述

四、再次实验确定

我们采用5.7.30进行实验，发现performance_schema.events_stages_history的内容为空，所以我们的判断是正确的

总结：

初次探索源码，有很多地方可能理解的不对，之后会学习些DDL相关的如加MDL锁，rebuild等等的具体操作，欢迎各位大佬指出不足之处

http://www.lryc.cn/news/40514.html

相关文章：

  • 力扣-超过经理收入的员工
  • 决策树基础知识点解读
  • 【C++】入门知识之 命名空间与输入输出
  • redis持久化的几种方式
  • 数据持久化层--查询分离
  • 一文读懂Js中的this指向
  • 零费用、零学习成本，用户快速可自定义json格式
  • 2023年全国最新高校辅导员精选真题及答案25
  • 二、数据结构-线性表
  • CGAL 点云上采样
  • 阿里云短信验证码实战
  • Android APP隐私合规检测工具Camille使用
  • 手把手学会DFS （递归入门）
  • 由《三体》太阳文明末日场景想到的……
  • es6的Proxy与Reflect
  • Linux环境部署vue项目 + nginx访问（包含nginx配置简介）
  • 到底什么是跨域，如何解决跨域（常见的几种跨域解决方案）？
  • pm3包1.4版本发布----一个用于3组倾向性评分的R包
  • 没有关系的话，那就去建立关系吧
  • Vue项目
  • 【webrtc】ICE 到VCMPacket的视频内存分配
  • 进阶C语言——指针（二）【题目练习】
  • Ajax简介
  • ChatGPT 4 测试 两数比较大小问题。
  • SSM-CRUD整合视频教程：Spring、SpringMVC、MyBatis、bootstrap、pagehelper、JSR303后端校验
  • Linux常用命令——基于Ubuntu22.04
  • Sentinel
  • 再也不想去字节跳动面试了，6年测开面试遭到这样打击.....
  • 【深度解刨C语言】符号篇（全）
  • VS Code 将推出更多 AI 功能给 Java 开发者