mysql-online-ddl是否需要rebuild

最新推荐文章于 2025-09-02 15:52:45 发布

原创最新推荐文章于 2025-09-02 15:52:45 发布 · 923 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #java

mysql 同时被 2 个专栏收录

58 篇文章

订阅专栏

源码解析

1 篇文章

订阅专栏

文章通过实验展示了在MySQL中变更VARCHAR字段长度对索引的影响，发现即使长度未超过UTF8MB4的临界值，若有索引，仍可能引发表重建。作者查阅源码了解到在特定条件下，字段长度变更不需重建，但实验环境（5.7.22）并未支持这一特性，而在5.7.30中该操作不会导致重建。文章讨论了DDL操作的内部机制，并表达了对进一步学习DDL相关知识的计划。

一、背景

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_instruments
       SET ENABLED = 'YES'
       WHERE NAME LIKE 'stage/innodb/alter%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> UPDATE performance_schema.setup_consumers
       SET 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 DO
  INSERT 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 compatible
  packed representation with old type, so it is theoretically
  possible to perform change by only updating data dictionary
  without 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 like
  varchar(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 or
  altered by ALTER TABLE.
*/

class Alter_info
{
public:
  /*
    These flags are set by the parser and describes the type of
    operation(s) specified by the ALTER TABLE statement.
    They do *not* describe the type operation(s) to be executed
    by the storage engine. For example, we don't yet know the
    type 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 DEFAULT
  static 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 during
    partitions 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 VALIDATION
      for 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 expansion
      like varchar(X) changed to varchar(X + N) and has a compatible
      packed data representation, we mark it for fast/INPLACE change
      in index definition. Some engines like InnoDB supports INPLACE
      alter for such cases.

      In other cases, key definition has changed if we are using a
      different field or if the used key part length is different, or
      key 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;

在这里插入图片描述