MySQL 到底能不能online ddl index PG VS MYSQL

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2350人左右 1 + 2 + 3 + 4 +5 + 6）5群接近480 已经停止申请，新人将进入6群

上周一篇关于MySQL 拜托加个索引能和PG一样简单吗？只能gh-ost了 烦人，的帖子引起争议，主要因为我提到 POSTGRESQL 在线添加索引是非常方便的，MySQL 在这方面不咋地，首先说结果，我对MYSQL的知识需要更新，针对高版本得MYSQL是支持online ddl index，所以写这篇来纠正一下，但是有一些同学发了一些MYSQL 支持online ddl index 的绝对论甚至有人说mysql 5.x就可以onine ddl index，这点咱们也的纠正，对就是对，错就是错。

先说结论，高版本的MySQL可以进行online ddl index是在8.023版本以上的MySQL 而在MySQL 8.023 版本以下的MySQL没有默认使用 online ddl index 的功能或根本没有，使用create index on 的语句来建立索引，是会直接给表加锁，并且阻碍任何事务的运行，所以如果是8.023 的版本或以上的MYSQL那么我是错的，但在这个版本以下的mysql，我们是需要继续使用pt-osc, or gh-osc 工具来添加索引的（大表）。

给我反馈我说错的同学（感谢一位叫Mong的同学），当然还有其他在文章下面留言的同学，这位叫Mong的同学非常细心的对 ONLINE DDL INDEX 进行了工作和解释，并截图给我，同时他也发现版本的问题和即使支持也是有条件的支持，然后就有了此篇文章。的确从8.023版本已经默认引入了 online ddl add index 的功能。并且我也亲测在8.030上的确在存储过程疯狂的插入数据的情况下，建立索引删除都是OK 的，没有问题不会锁表，DML和index add drop之间是不存在锁表而导致DML无法进行的情况，所有说8.023 以后的版本不可以ONLINE DDL INDEX 是不对的。

那么8.023以上的版本有多少人再用，这是一个问题，同时有多少人知道这个事情，也是问题。另外8.023以下的版本，通过alter table 语句添加参数是否也可以进行在线的online ddl index  是需要通过 algorithm=inplace 和lock 的模式调整来降低添加索引锁定的时间，这点有多少人知道并且这和MYSQL8.023版本的online ddl index 是一回事吗？？？？？

让我不解的是上篇帖子出来，马上就有人反馈，不对MYSQL 5.7 就可以DDL INDEX ONLINE 的同学，拜托不要在人云亦云了The proof of the pudding is in the eating！

下面是证实在MYSQL8.030 在线加索引是否可以，答案是可以

mysql> 
mysql> CREATE PROCEDURE generate_data()
    -> BEGIN
    ->     DECLARE i INT DEFAULT 1;
    ->     DECLARE practice_time TIMESTAMP;
    -> 
    ->     CREATE TABLE generated_table (
    ->         id INT PRIMARY KEY AUTO_INCREMENT,
    ->         text_field VARCHAR(255),
    ->         practice_field TIMESTAMP
    ->     );
    ->     
    ->     SET practice_time = NOW();
    ->     
    ->     WHILE i          INSERT INTO generated_table (text_field, practice_field) VALU
    ->         SET practice_time = practice_time + INTERVAL 1 SECOND;
    ->         SET i = i + 1;
    ->     END WHILE;
    -> END //
Query OK, 0 rows affected (0.01 sec)

我们先建立一个存储过程，并且往里面添加1000万的数据。然后我在添加数据的过程中，我建立索引开是否能成功。参见下图，毫无疑问，这个版本的MySQL一点问题都没有。

除此以外，我们还应该有几个事情来说明即使在8.023版本以上的MYSQL ，online ddl index是有可能失败了，并且从上图大家也知道大致的ONLINE DDL 的原理是什么了，在创建索引的语句使用了更高的隔离级别,添加索引使用的是 RR,而不是当前的RC的isolation.

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 7881607
                 trx_state: RUNNING
               trx_started: 2024-04-16 14:11:48
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 112
                 trx_query: INSERT INTO generated_table (text_field, practice_field) VALUES (CONCAT('Text_', i), practice_time)
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 0
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
*************************** 2. row ***************************
                    trx_id: 7881467
                 trx_state: RUNNING
               trx_started: 2024-04-16 14:11:46
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 1
       trx_mysql_thread_id: 114
                 trx_query: create index idx_text_field on generated_table (text_field)
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 0
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
2 rows in set (0.00 sec)

那么咱们现在说说，即使在8.023版本以上的MYSLQ online ddl index 失败的可能性有什么？

1 表太大：对表太大，大表要进行online ddl index 是需要更大的内存也就是innodb buffer pool 的支持和磁盘空间tmpdir 或者innodb_tmpdir 文件系统的磁盘空间的支持，如果空间不足则无法支持，可能会导致操作失败。

2 innodb_online_alter_log_max_size  这个参数主要是在线添加索引的情况下设定在添加索引期间，对表所做的增删改查的记录，如果这个参数的值较小，则在online ddl index 的情况下，会导致DDL INDEX 的任务失败，并且回滚。这里默认值是128MB ，显然是有点保守了，可以设置的在大一点，尤其针对大表的情况并且这个表还在进行疯狂的 DML操作。

另外如果需要使用在线建立index online 的功能的情况下，建议使用8.027以上的版本（不要使用8.029）。因为在8.027 版本才引入了innodb_ddl_buffer_size 的功能，这个功能是专门为了在线DDL 定义操作的缓冲区的大小，默认1MB。在此之前这个变量是通过 innodb_sort_buffer_size 来做 DDL online 的索引在线的缓冲使用的。

基于以上的内容，1 online ddl index 在mysql 高版本是可以的，至少应该是8.023版本以上，但完善的等到8.027 以上的版本。2 如果你是之下的版本，那么你的继续和笔者一样，在大表添加索引的时候继续业障，烦人。

注明：实际上MySQL 可以进行online ddl index  lock=none 也是在MySQL 8.015 后开始的，所以，MySQL ddl index online 在低版本大表还是要借助工具的，否则工具的产生原因是什么？？？

MySQL 拜托加个索引能和PG一样简单吗？只能gh-ost了 烦人

置顶文章：MySQL 拜托加个索引能和PG一样简单吗？只能gh-ost了 烦人
临时工访谈：NoSQL 大有前景，MongoDB DBA 被裁员后谋求新职位
临时工访谈：问金融软件开发总监  哪些业务不用传统数据库
PolarDB for PostgreSQL  有意思吗？有意思呀PolarDB  Serverless POC测试中有没有坑与发现的疑问
PolarDB 数据库架构 测试 serverless 后的 三字真言  稳定，灵活，省钱（的用对地方）

往期热门文章：

临时工说：如果DBA大龄被裁员了怎么办？

临时工说：炮轰阿里云MongoDB司令部 低质高价技术差 你是要疯！！！！

MySQL 的SQL引擎很差吗？由一个同学提出问题引出的实验

临时工访谈：从国产数据库 到 普罗大众的产品 ！与在美国创业软件公司老板对话

PostgreSQL 如何通过工具来分析PG 内存泄露

MySQL 的SQL引擎很差吗？由一个同学提出问题引出的实验
临时工访谈：我很普通，但我也有生存的权利，大龄程序员 求职贴PolarDB  Serverless POC测试中有没有坑与发现的疑问
临时工访谈：PolarDB  Serverless  发现“大”问题了  之 灭妖记 续集
临时工访谈：庙小妖风大-PolarDB 组团镇妖 之 他们是第一临时工说: 快速识别 “海洋贝壳类” 数据库方法速递临时工说：国产 数据库 销售人员  图鉴临时工说：DBA 是不是阻碍国产数据库发展的毒瘤 ，是不是？从国产DB老专家的一条留言开始 （其实更好看的是文章下方的留言）

感谢 老虎刘 刘老师 对 5月20日 SQL 问题纠正贴 ---PostgreSQL 同一种SQL为什么这样写会提升45%性能

PostgreSQL 同一种SQL为什么这样写会提升45%性能 --程序员和DBA思维方式不同决定

MongoDB 不是软柿子，想替换就替换

PostgreSQL  熊灿灿一句话够学半个月 之 KILL -9

MongoDB  挑战传统数据库聚合查询，干不死他们的

临时工说：国内数据库企业存活   “三板斧”

临时工访谈：庙小妖风大-PolarDB 组团镇妖 之 他们是第一  （阿里云组团PK笔者实录）

临时工访谈：金牌 “女” 销售从ORACLE 转到另类国产数据库 到底  为什么？

临时工访谈：无名氏意外到访-- 也祝你好运（管理者PUA DBA现场直播）

临时工说：搞数据库 光凭的是技术，那DBA的死多少次？

PostgreSQL  分组查询可以不进行全表扫描吗？速度提高上千倍？临时工说：分析当前经济形势下 DBA 被裁员的根因
PostgreSQL PG_DUMP 工作失败了怎么回事及如何处理MySQL 八怪（高老师）现场解决问题实录
PostgreSQL 为什么也不建议 RR隔离级别，MySQL别笑临时工访谈：OceanBase上海开大会，我们四个开小会 OB 国产数据库破局者
临时工说：OceanBase 到访，果然数据库的世界很卷，没边临时工访谈：恶意裁员后，一个国产数据库企业程序员的心声
临时工说：上云后给 我一个 不裁 DBA的理由PolarDB for PostgreSQL  有意思吗？有意思呀PostgreSQL   玩PG我们是认真的，vacuum 稳定性平台我们有了临时工说：裁员裁到 DBA 咋办  临时工教你 套路1 2 3PolarDB  搞那么多复杂磁盘计费的东西，抽筋了吗？临时工说：OceanBase 到访，果然数据库的世界很卷，没边MONGODB  ---- Austindatabases  历年文章合集MYSQL  --Austindatabases 历年文章合集POSTGRESQL --Austindatabaes 历年文章整理POLARDB  -- Ausitndatabases 历年的文章集合PostgreSQL  查询语句开发写不好是必然，不是PG的锅
SQL SERVER 如何实现UNDO REDO  和PostgreSQL 有近亲关系吗
MongoDB 2023纽约 MongoDB 大会 -- 我们怎么做的新一代引擎 SBE Mongodb 7.0双擎力量（译）
MongoDB 2023年度纽约 MongoDB 年度大会话题 -- MongoDB 数据模式与建模
MongoDB  双机热备那篇文章是  “毒”
MongoDB   会丢数据吗？在次补刀MongoDB  双机热备
临时工说：从人性的角度来分析为什么公司内MySQL 成为少数派，PolarDB 占领高处
POLARDB  到底打倒了谁  PPT 分享 （文字版）
PostgreSQL  字符集乌龙导致数据查询排序的问题，与 MySQL 稳定 "PG不稳定"
PostgreSQL  Patroni 3.0 新功能规划 2023年 纽约PG 大会 （音译）

Austindatabases 公众号，主要围绕数据库技术（PostgreSQL, MySQL, Mongodb, Redis, SqlServer，PolarDB, Oceanbase 等）和职业发展，国外数据库大会音译,国外大型IT信息类网站文章翻译，等，希望能和您共同发展。截止今天共发布