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

AustinDatabases

发布于 2024-06-05 10:23:45

23500

代码可运行

文章被收录于专栏：AustinDatabasesAustinDatabases

运行总次数：0

代码可运行

上周一篇关于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 <= 10000000 DO
    ->         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)

mysql> 
mysql> DELIMITER ;
mysql> 
mysql> CALL generate_data();

我们先建立一个存储过程，并且往里面添加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 在低版本大表还是要借助工具的，否则工具的产生原因是什么？？？

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-06-04，如有侵权请联系 cloudcommunity@tencent.com 删除

数据