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

# 创建表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)BEGINDECLARE 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;

# 查看版本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)

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 concurrentwrites 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 doneby 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 operationsspecified using HA_ALTER_FLAGS and Alter_inplace_information.The level of concurrency allowed during this operation dependson 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 doneby 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 duringprepare_inplace_alter_table() and inplace_alter_table() insidethe storage engine. Note that the allowed level of concurrencyduring this operation will be the same as forinplace_alter_table() and thus might be higher than duringprepare_inplace_alter_table(). (E.g concurrent writes wereblocked 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 doneby 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);

/** 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; */

/**  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;

/**  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  };

 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;

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;