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

本文链接：https://blog.csdn.net/liuhuayang/article/details/139338238

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，（共2240人左右 1 + 2 + 3 + 4 +5）新人奖直接分配到6群 5群430 停止自由申请

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 指定主节点的地址

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

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

置顶文章：

临时工访谈：问金融软件开发总监哪些业务不用传统数据库

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

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

SQL SERVER 我没有消失，SQL SERVER下一个版本是2025 (功能领先大多数数据库）

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

PolarDB Serverless POC测试中有没有坑与发现的疑问 (大妖复仇记前传)

往期热门文章：

临时工访谈：我很普通，但我也有生存的权利，大龄程序员求职贴

临时工：读书笔记--重读老书《品悟性能优化》与什么是合并设计

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

临时工说：DBA 是不是阻碍国产数据库发展的毒瘤，是不是？从国产DB老专家的一条留言开始

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

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

临时工访谈：PolarDB Serverless 发现“大”问题了之灭妖记续集

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

临时工说: 快速识别 “海洋贝壳类” 数据库方法速递

临时工说：国产数据库销售人员图鉴

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

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

临时工访谈：庙小妖风大-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 3

PolarDB 搞那么多复杂磁盘计费的东西，抽筋了吗？

临时工说：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信息类网站文章翻译，等，希望能和您共同发展。
截止今天工发布 1152篇文章