网站建设主题大全,wordpress文章放视频,泸州中泸建设集团有限公司网站,足球哪个网站做的比较好一、背景
DDL一直是DBA业务中的大项#xff0c;看了TIDB的DDL讲解#xff0c;恰巧我们的mysql业务大表也遇到了DDL的变更项#xff0c;变更内容是将varchar(10)变更成varchar(20),这个变更通过官方文档很容易知道是不需要rebuild的#xff08;这里要注意下这个varchar(255…一、背景
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 YESWHERE 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 YESWHERE NAME LIKE %stages%;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 02.建表并准备测试数据
# 创建表
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 num1;
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 dont 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 its 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/464所以变更前后的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等等的具体操作欢迎各位大佬指出不足之处
