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

手机网站建设价位西安便民信息发布平台

news 2025/10/5 14:43:52
手机网站建设价位,西安便民信息发布平台,音频文件放到网站空间里生成链接怎么做,vi设计公司哪里一、背景 DDL一直是DBA业务中的大项，看了TIDB的DDL讲解，恰巧我们的mysql业务大表也遇到了DDL的变更项，变更内容是将varchar(10)变更成varchar(20),这个变更通过官方文档很容易知道是不需要rebuild的（这里要注意下这个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 = '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.yayakq.cn/news/978924/

相关文章：

  • 个人网站规划书模板上海高端品牌网站制作
  • 常见网站图标签名在线生成器
  • 合肥网站建设程序门户网站系统设计
  • 购物网站建设公建筑模板价格规格大全
  • 好玩的网站源码官方网站搭建要多少钱
  • 怎样创建购物网站做网站的变成语言哪类最简单
  • 建设规划展览馆网站的优势网站优化意义
  • 南京自适应网站wordpress企业授权
  • 郑州网站建设企业安全的网站建设公司
  • 苏州网站建设设计制作公司怎么样建设网站项目的目的是什么
  • 跨境电商自建站是什么wordpress 分类目录置顶
  • 企业官方网站认证黄山seo公司
  • 外贸wordpress主题seo外链发布软件
  • 分类信息网站营销潮州seo
  • 有什么可以做cad赚钱的网站宝塔wordpress
  • 新乡做网站的多吗徐州市鼓楼区建设局网站
  • 做网站赚钱的时代过去了吗seo在线培训机构
  • fms 视频网站建设中国八冶建设集团网站
  • 昔阳做网站公司个人介绍网页模板免费下载
  • 清廉桂林网站百度关键词优化服务
  • 仙桃网站网站建设wordpress分辨率
  • 已备案网站新增接入双滦网站建设
  • 建设网站用什么服务器网页设计作品赏析
  • PHP网站建设选择哪家好世界营销大师排名
  • discuz!网站模板崇明建设镇网站
  • 许昌市建设投资有限公司 网站做seo网站的公司哪家好
  • 大连网站建设意动科技图片编辑在线使用
  • 网站有哪些类型wordpress php配置文件
  • 松山湖网站建设公司宁波企业免费建站
  • 实训课建设网站步骤html网页特效