mysql5.6在线ddl

日期：2023-09-30 03:53

-->

Innodb 性能提升：

　　--用户可以添加索引并执行标准表更改，同时数据库仍可用于应用程序更新。
　　支持在线操作（添加索引、修改表）：

示例一：在线添加索引：
打开一个会话，在italk库下的data_userinfo表上创建索引（该表有70万条数据左右）：
mysql>>create index idx_groupid on data_userinfo(groupid);
查询正常，0行受影响（9.26秒）
记录：0重复：0警告：0
同时在另一个会话中，对表执行更新操作：
mysql>>update data_userinfo set status=1 where id=22;
查询正常，1行受影响（0.02秒）
匹配的行：1更改：1警告：0
更新操作立即返回结果，在之前的版本中，由于索引创建和alter table都是表级锁，所以update只能等待添加索引的语句完成后才能执行。 show full processlist status 显示为：
等待表元数据锁 |更新 data_userinfo 设置 status=2 其中 id=22

Alter表添加索引形式：
mysql>>alter table data_userinfo添加索引idx_groupid(groupid);
查询正常，0 行受影响（12.77 秒）
记录：0 重复：0 警告：0
mysql>>update data_userinfo set status=2 where id=23;
查询正常，1 行受影响（0.05 秒）
匹配的行：1 更改：1 警告：0

示例2：在线添加列字段：
mysql>>alter table data_userinfo addage int unsigned not null default 0;
查询正常，0行受影响（57.71秒）
记录： 0 重复项：0 警告：0
mysql>>update data_userinfo set status=1 where id=23;
查询正常，1 行受影响（0.01 秒）
匹配行：1 已更改：1 警告：0

示例 3：更改列字段的数据类型：
更改列的数据类型（需要大量时间，并且需要重建表的所有行）：
以及所有行表中的数据将被重建
查询OK，受影响的668200行（1分35.54秒）

对大表执行DDL操作时，可以通过以下步骤判断操作执行快还是慢：
1.克隆原始表
2的表结构。使用原来小表的部分数据来填充克隆表
3。对克隆表
4执行DDL操作。检查受影响的行是 0 还是非 0。如果非0，则表示该DDL操作会重建表中的所有行数据，这意味着您需要规划一个业务低峰的时间或者在
slave

上执行DDL操作

性能基准

可以通过在最新版本5.6及更早版本中对更大的innodb表执行相同的alter操作来测试在线DDL操作的相对性能
mysql--root@localhost:italk 17:45:09 >>设置old_alter_table=1； -------------旧模式设置
查询正常，0行受影响（0.00秒）

mysql--root@localhost:italk 17:45:30>>修改表data_userinfo添加索引idx_groupid(groupid);
查询OK，668200行受影响（42.05秒）------------通过复制表数据来执行表复制。受影响的行数为所有行
记录：668200 重复：0 警告：0

mysql--root@localhost:italk 17:46:28>>设置old_alter_table=0; -------------设置最新模式
查询OK，0行受影响（0.00秒）

mysql--root@localhost:italk 17:46:54>>修改表data_userinfo添加索引idx_groupid(groupid);
查询正常，0 行受影响（10.67 秒）--------- ----就地更改就地更改而不影响行数据
记录：0 重复：0 警告：0

通过时间消耗，我们可以看到运行时间大大减少，性能显着提升。

新的算法选项和锁定选项已添加到 5.6 的 alter table 语法中：
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
你可以使用这两个选项来控制你的DDL操作是就地模式还是老式的复制表模式

思考问题：

1。为什么索引删除的这么快？

答：删除索引时，只是在辅助索引的空间上做一个可用标记。并删除表的索引定义，而不创建新表。

2。 innodb中索引存储在哪里

分析innodb会发现索引存在于新的page中，并且没有与数据一起存储在同一个pageh中。辅助索引页存储索引行的值和主键的值。

3。如何保证添加索引和在线更新数据都不会受到影响？

答：在MySQL 5.6中，row_log是用来保证数据一致性的，双方不受影响。

在线添加索引处理流程

确定是否可以使用 inplace 执行 DML（无需创建新表）

开始线上运营前期准备工作

　　　　(1.修改表的数据字典信息

　　　　(2.等待所有后台线程停止操作此表

　　　　(3.上线过程中，原表允许读写，所以DML操作的数据需要记录在row_log中，而不是更新到索引上

开始真正的在线添加索引操作

　　　　(1.读取聚集索引，排序，插入到新索引中，并将行日志中的记录变化更新到新索引中

　　　　(2.插入新索引后，重新应用这段时间row_log中记录的记录变化

　　　　(3.新索引将暂时锁定

-->

最近发表

猜你喜欢

mysql5.6在线ddl

性能基准

思考问题：

在线添加索引处理流程

相关文章