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

AustinDatabases

发布于 2024-06-04 15:55:51

930

发布于 2024-06-04 15:55:51

MySQL MySQL 怎么那么烦人，好用，好用个屁。用过数据库吗说好用，加个索引都那么的费劲。说到这里我很想用宋丹丹老师那句，怎么那么烦人来结束此篇文章，在给MYSQL一个大白眼，和SQL SERVER ORACLE 比不了，和 PG也比不了，人家仨都有在线索引的技术，虽然SQL SERVER 黑，必须买企业版，但人家有。MySQL 呵呵

为了大表加索引的事情，可让MySQL的使用者们操心，gh-ost 是一种针对MySQL 在线加索引的一种可实现的方案，对比PT-OSC工具他最好的部分在于没有对于原表有相关的变化，通过二进制流的方式来捕获对于表的更改，异步的应用到虚拟表中，在将数据追齐的情况下，切换虚拟表和实体表之间的方案。

同时从GA 上来看 GH-OST 软件本身已经踢出了 MYSQL5.5. 和 MYSQL5.6的支持，目前最新版本仅仅支持 MYSQL 5.7 和8.0 的MYSQL数据库。

首选需要确认的是，gh-ost 对于在线添加索引是可以满足这个需求的，他主要的工作步骤也是监听BINLOG ，讲BINLOG 中与这个表作用的DML 进行重放，重放到新建的临时表中，临时表已经将索引建立好，在全量同步数据后，就将增量的数据也写入到临时表，当发现业务不繁忙的时候，将针对这个表进行切换，将临时表切换成正式表。

这里我们用一个事例来验证这个事情，以及会遇到的问题

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


delimiter $$
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=187635267;
SET ageVal=100;
SET i=1;
WHILE i < 1000000 DO
  INSERT INTO t_user(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+i)%30,NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END $$

delimiter ;
call proc_batch_insert();

先建立表，然后通过存储过程来对数据表快速插入数据，以为插入数据的速度比较快，同时由于插入的数据的方式，此时我们运行 gh-ost 暂时无法进行正常的工作，具体情况参加下图。

2024-04-16 04:51:27 DEBUG Iteration complete: no further range to iterate
2024-04-16 04:51:27 INFO Row copy complete
Copy: 4674/4674 100.0%; Applied: 21767; Backlog: 0/1000; Time: 37s(total), 37s(copy); streamer: mysql-bin.000016:307859899; Lag: 0.02s, HeartbeatLag: 0.04s, State: migrating; ETA: due
2024-04-16 04:51:27 INFO Copy: 4674/4674 100.0%!;(MISSING) Applied: 21767; Backlog: 0/1000; Time: 37s(total), 37s(copy); streamer: mysql-bin.000016:307859899; Lag: 0.02s, HeartbeatLag: 0.04s, State: migrating; ETA: due
2024-04-16 04:51:27 DEBUG checking for cut-over postpone
2024-04-16 04:51:27 DEBUG checking for cut-over postpone: complete
2024-04-16 04:51:27 INFO Grabbing voluntary lock: gh-ost.94.lock
2024-04-16 04:51:27 INFO Setting LOCK timeout as 6 seconds
2024-04-16 04:51:27 DEBUG ApplyDMLEventQueries() applied 1 events in one transaction
2024-04-16 04:51:27 INFO Looking for magic cut-over table
2024-04-16 04:51:27 INFO Creating magic cut-over table `test`.`_t_user_del`
2024-04-16 04:51:27 DEBUG ApplyDMLEventQueries() applied 1 events in one transaction
2024-04-16 04:51:27 DEBUG ApplyDMLEventQueries() applied 2 events in one transaction
2024-04-16 04:51:27 DEBUG ApplyDMLEventQueries() applied 2 events in one transaction
2024-04-16 04:51:27 INFO Magic cut-over table created
2024-04-16 04:51:27 INFO Locking `test`.`t_user`, `test`.`_t_user_del`
2024-04-16 04:51:27 DEBUG ApplyDMLEventQueries() applied 2 events in one transaction
2024-04-16 04:51:27 INFO Tables locked
2024-04-16 04:51:27 INFO Session locking original & magic tables is 94
2024-04-16 04:51:27 INFO Writing changelog state: AllEventsUpToLockProcessed:1713257487927341252
2024-04-16 04:51:27 DEBUG ApplyDMLEventQueries() applied 1 events in one transaction
2024-04-16 04:51:27 INFO Intercepted changelog state AllEventsUpToLockProcessed
2024-04-16 04:51:27 INFO Handled changelog state AllEventsUpToLockProcessed
2024-04-16 04:51:27 INFO Waiting for events up to lock
2024-04-16 04:51:27 DEBUG ApplyDMLEventQueries() applied 1 events in one transaction
2024-04-16 04:51:27 DEBUG Getting nothing in the write queue. Sleeping...
2024-04-16 04:51:27 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1713257487927341252
2024-04-16 04:51:27 INFO Done waiting for events up to lock; duration=5.108675ms
# Migrating `test`.`t_user`; Ghost table is `test`.`_t_user_gho`
# Migrating 192.168.198.120:3306; inspecting 192.168.198.120:3306; executing on mysql830
# Migration started at Tue Apr 16 04:50:50 -0400 2024
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle 
# Serving on unix socket: /tmp/gh-ost.test.t_user.sock
Copy: 4674/4674 100.0%; Applied: 21777; Backlog: 0/1000; Time: 37s(total), 37s(copy); streamer: mysql-bin.000016:307866710; Lag: 0.02s, HeartbeatLag: 0.07s, State: migrating; ETA: due
2024-04-16 04:51:27 INFO Copy: 4674/4674 100.0%!;(MISSING) Applied: 21777; Backlog: 0/1000; Time: 37s(total), 37s(copy); streamer: mysql-bin.000016:307866710; Lag: 0.02s, HeartbeatLag: 0.07s, State: migrating; ETA: due
2024-04-16 04:51:27 INFO Setting RENAME timeout as 3 seconds
2024-04-16 04:51:27 INFO Session renaming tables is 89
2024-04-16 04:51:27 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`t_user` to `test`.`_t_user_del`, `test`.`_t_user_gho` to `test`.`t_user`
2024-04-16 04:51:27 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2024-04-16 04:51:27 INFO Checking session lock: gh-ost.94.lock
2024-04-16 04:51:27 INFO Connection holding lock on original table still exists
2024-04-16 04:51:27 INFO Will now proceed to drop magic table and unlock tables
2024-04-16 04:51:27 INFO Dropping magic cut-over table
2024-04-16 04:51:27 INFO Releasing lock from `test`.`t_user`, `test`.`_t_user_del`
2024-04-16 04:51:27 INFO Tables unlocked
2024-04-16 04:51:27 INFO Looking for magic cut-over table
2024-04-16 04:51:27 INFO Tables renamed
2024-04-16 04:51:27 INFO Lock & rename duration: 34.154777ms. During this time, queries on `t_user` were blocked
2024-04-16 04:51:27 INFO Writing changelog state: Migrated
2024-04-16 04:51:27 DEBUG done streaming events
2024-04-16 04:51:27 DEBUG Done streaming
[2024/04/16 04:51:27] [info] binlogsyncer.go:180 syncer is closing...
[2024/04/16 04:51:27] [info] binlogsyncer.go:864 kill last connection id 90
[2024/04/16 04:51:27] [info] binlogsyncer.go:210 syncer is closed
2024-04-16 04:51:27 INFO Closed streamer connection. err=<nil>
2024-04-16 04:51:27 INFO Dropping table `test`.`_t_user_ghc`
2024-04-16 04:51:27 INFO Table dropped
2024-04-16 04:51:27 INFO Am not dropping old table because I want this operation to be as live as possible. If you insist I should do it, please add `--ok-to-drop-table` next time. But I prefer you do not. To drop the old table, issue:
2024-04-16 04:51:27 INFO -- drop table `test`.`_t_user_del`
2024-04-16 04:51:27 INFO Done migrating `test`.`t_user`
2024-04-16 04:51:27 INFO Removing socket file: /tmp/gh-ost.test.t_user.sock
2024-04-16 04:51:27 INFO Tearing down inspector
2024-04-16 04:51:27 INFO Tearing down applier
2024-04-16 04:51:27 DEBUG Tearing down...
2024-04-16 04:51:27 INFO Tearing down streamer
2024-04-16 04:51:27 INFO Tearing down throttler
2024-04-16 04:51:27 DEBUG Tearing down...
# Done

从上面操作的过程来说，在gh-ost进行切换的过程中

1 至少要copy 完现有表的数据到临时表

2 开始判断监听的BINLOG 应用到临时表和日志产生数据的之间的延迟时间

3 通过心跳时间以及复制延迟来判断是否满足进行切换

4 然后系统将会对于要进行变更的表加锁，保证数据的一致性，这里会设定一个锁定的时间，并且记录BINLOG 在上锁时的日志的位置。

5 进行切换，将原表变为指定新的表名，然后将临时表转换为生产表的表名。

6 整体的任务完成

gh-ost --aliyun-rds=true --debug --initially-drop-ghost-table --initially-drop-old-table --allow-on-master --alter="ADD INDEX idx_name (name);" --assume-master-host="192.168.198.120" --host="192.168.198.120" --port=3306 --password="test" --database="test" --table="t_user" --tungsten --user="test" --assume-rbr --execute

需要说明命令中的问题

1 如果是在云上进行相关数据库的操作，如果是阿里云云请选择--aliyun-rds ，经过和阿里云的客服确认 POALRDB 也支持 gh-ost 产品。

2 allow-on-master 这个参数是允许在主节点上进行DDL的操作

3 --tungsten 确定是在主从复制环境中进行的DDL操作

4 --assume-rbr 确认使用行复制的的模式来操作

5 --assume-master-host 指定主节点的地址

在进行添加索引钱，如果表较大，则会开始大量的靠背原来的数据到新表，这个会比较耗时，和耗费空间，这点还请注意。

以上为实例操作，如果想了解具体的命令与功能，请参考官方文档