當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL-在线处理大表数据在线修改大表的表结构

發布時間：2025/3/21 数据库 19 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL-在线处理大表数据在线修改大表的表结构小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

文章目錄

生猛干貨
官方文檔
概述
示例
- 大表數據的分批處理
- 修改大表的表結構
- - 方案一 : 從表修改，主從切換
  - 方案二： pt-online-schema-change
搞定MySQL

生猛干貨

帶你搞定MySQL實戰，輕松對應海量業務處理及高并發需求，從容應對大場面試

官方文檔

https://dev.mysql.com/doc/

如果英文不好的話，可以參考 searchdoc 翻譯的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

概述

MySQL-獲取有性能問題SQL的方法_慢查詢 & 實時獲取

MySQL- SQL執行計劃 & 統計SQL執行每階段的耗時

上面兩篇文章我們知道了如何獲取有問題的SQL，以及如何統計SQL每個階段的耗時，這樣我們去優化的時候就更加有針對性。

這里我們列舉幾個例子，來看下如何具體的優化SQL

示例

大表數據的分批處理

分批處理大表的數據，特別是主從復制的MySQL集群，每處理一批最好留一點時間，給主從同步復制留一點時間。

舉個例子有個大表 1千萬數據，我們要修改其中的100萬，那么最好分多個批次來更新，每次5000或者1萬，根據自己服務器的性能合理的調整。

存過如下，根據自己的業務調整。

DELIMITER $$ USE `artisan` $$ DROP PROCEDURE IF EXISTS `p_delete_rows` $$ CREATE DEFINER=`root@192.168.18.131` PROCEDURE `p_delete_rows`() BEGINDECLARE v_rows INT;SET v_rows = 1;WHILE v_rows >0DO DELETE FROM t_test where id >= 10000 AND id <= 20000 LIMIT 5000;SELECT ROW_COUNT() INTO v_rows;SELECT SLEEP(5);END WHILE; END$$ DELIMITER;

修改大表的表結構

當一個表中的數據量很大的時候，我們對表中的列的字段類型進行修改，比如改變字段的寬度時會鎖表，從而影響業務。其二無法解決主從數據庫延遲的問題

方案一 : 從表修改，主從切換

現在從服務器上修改，然后主從切換。切換完以后在此修改新的從服務器。需要主從切換

方案二： pt-online-schema-change

主服務器上

Step1 : 建立一個新表，將大表的數據同步過去
Step2: 老表上建立觸發器，同步到新表
Step3：同步后老表上弄個排它鎖
Step4: 新表重命名，刪除老表

可以避免主從延遲，只不過操作復雜點，好在有第三方的工具可以使用 pt-online-schema-change

pt-online-schema-change 也是 percona-toolkit中的一個工具，如何安裝percona-toolkit，請查看前面的博文 https://blog.csdn.net/yangshangwei/article/details/104146374

pt-online-schema-change alters a table’s structure without blocking reads or
writes. Specify the database and table in the DSN.

用法

命令行里執行如下

pt-online-schema-change \ -- alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT '' " --user=xxxx --password=xxx D=數據庫名, t=表名 --charset=utf8 --execute

– alter : 要操作的DML語句，上面的內容為舉個例子改變字段長度
–user 用戶
–password
D 數據庫用戶
t 表名

實操一下

mysql> use artisan; No connection. Trying to reconnect... Connection id: 104 Current database: *** NONE ***Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changed mysql> show tables; +-------------------+ | Tables_in_artisan | +-------------------+ | checkTest | | t_innodb | | t_myisam | | t_order | | t_test | +-------------------+ 5 rows in set (0.29 sec)mysql> desc t_order; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | product | varchar(25) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)mysql> show create table t_order; +---------+------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------------------------------------------------------------------+ | t_order | CREATE TABLE `t_order` (`id` int(11) DEFAULT NULL,`product` varchar(25) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +---------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)mysql> alter table t_order modify product varchar(100) not null default '' ; ----如果用sql修改來改的話，就是上面的，這里不要執行哈。我們用pt-online-schema-change操作 ,用這個的話 alter table t_order 這些就不用了，只要后面的語句

退出 mysql客戶端，切換到命令行的模式

pt-online-schema-change – alter=" modify product varchar(100) not null default ‘’ " --user=root --password=artisan D=artisan, t=t_order --charset=utf8 --execute

發現了從庫，需要加 --nocheck-replication-filters

失敗了。。。。這個表建的時候沒建主鍵。加上后重新看下表定義

mysql> show create table t_order; +---------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | t_order | CREATE TABLE `t_order` (`id` int(11) NOT NULL,`product` varchar(25) DEFAULT NULL,PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)mysql>

再次執行

[root@artisan ~]# pt-online-schema-change --alter="modify product varchar(100) not null default ''" --user=root --password=artisan D=artisan,t=t_order --charset=utf8 --execute --nocheck-replication-filters Found 1 slaves: artisan -> 192.168.18.132:socket Will check slave lag on: artisan -> 192.168.18.132:socket Operation, tries, wait:analyze_table, 10, 1copy_rows, 10, 0.25create_triggers, 10, 1drop_triggers, 10, 1swap_tables, 10, 1update_foreign_keys, 10, 1 Altering `artisan`.`t_order`... Creating new table... Created new table artisan._t_order_new OK. Altering new table... Altered `artisan`.`_t_order_new` OK. 2020-02-03T11:50:12 Creating triggers... 2020-02-03T11:50:12 Created triggers OK. 2020-02-03T11:50:12 Copying approximately 1 rows... 2020-02-03T11:50:12 Dropping triggers... 2020-02-03T11:50:12 Dropped triggers OK. 2020-02-03T11:50:12 Dropping new table... 2020-02-03T11:50:12 Dropped new table OK. `artisan`.`t_order` was not altered. 2020-02-03T11:50:12 Error copying rows from `artisan`.`t_order` to `artisan`.`_t_order_new`: DBD::mysql::db selectrow_hashref failed: Table 'artisan.t_order' doesn't exist [for Statement "EXPLAIN SELECT * FROM `artisan`.`t_order` WHERE 1=1"] at /usr/bin/pt-online-schema-change line 6002. [root@artisan ~]#

從庫沒有這個表，。。。建下吧。

然后再來一次 ,

Replica artisan is stopped. Waiting. …

從庫同步停止了，重啟下吧，要是重啟后

重新設置下同步點。

mysql> stop slave; Query OK, 0 rows affected (0.00 sec)mysql> change master to master_host='192.168.18.131', master_user='artisan4syn', master_password='artisan', master_log_file='mysql-bin.000050', master_logog_pos=14342; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master_logog_pos=14342' at line 1 mysql> change master to master_host='192.168.18.131', master_user='artisan4syn' ,master_password='artisan' ,master_log_file='mysql-bin.000050' ,master_log_pos=14342; Query OK, 0 rows affected, 2 warnings (0.04 sec)mysql> mysql> start slave; Query OK, 0 rows affected (0.04 sec)mysql>

再看

[root@artisan ~]# pt-online-schema-change --alter="modify product varchar(100) not null default ''" --user=root --password=artisan D=artisan,t=t_order --charset=utf8 --execute --nocheck-replication-filters Found 1 slaves: artisan -> 192.168.18.132:socket Will check slave lag on: artisan -> 192.168.18.132:socket Operation, tries, wait:analyze_table, 10, 1copy_rows, 10, 0.25create_triggers, 10, 1drop_triggers, 10, 1swap_tables, 10, 1update_foreign_keys, 10, 1 Altering `artisan`.`t_order`... Creating new table... Created new table artisan.__t_order_new OK. Altering new table... Altered `artisan`.`__t_order_new` OK. 2020-02-03T12:09:13 Creating triggers... 2020-02-03T12:09:13 Created triggers OK. 2020-02-03T12:09:13 Copying approximately 1 rows... Replica artisan is stopped. Waiting. Replica artisan is stopped. Waiting. Replica artisan is stopped. Waiting. Replica artisan is stopped. Waiting. Replica artisan is stopped. Waiting. Replica artisan is stopped. Waiting. Replica artisan is stopped. Waiting. Replica artisan is stopped. Waiting. Replica artisan is stopped. Waiting. 2020-02-03T12:13:40 Copied rows OK. 2020-02-03T12:13:40 Analyzing new table... 2020-02-03T12:13:40 Swapping tables... 2020-02-03T12:13:40 Swapped original and new tables OK. 2020-02-03T12:13:40 Dropping old table... 2020-02-03T12:13:40 Dropped old table `artisan`.`_t_order_old` OK. 2020-02-03T12:13:40 Dropping triggers... 2020-02-03T12:13:40 Dropped triggers OK. Successfully altered `artisan`.`t_order`. [root@artisan ~]#

成功了。

看下表結構的變化

搞定MySQL

總結

以上是生活随笔為你收集整理的MySQL-在线处理大表数据在线修改大表的表结构的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： MySQL- SQL执行计划统计SQ
下一篇： MySQL-count(*) 和 not