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

小小工匠

发布于 2021-08-17 11:15:37

4K0

文章被收录于专栏：小工匠聊架构小工匠聊架构

官方文档

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`()
BEGIN
		DECLARE v_rows INT;
    SET v_rows = 1;
    WHILE  v_rows >0
		DO 
				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://cloud.tencent.com/developer/article/1862775

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 -A

Database 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

[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
Found 1 slaves:
artisan -> 192.168.18.132:socket
Will check slave lag on:
artisan -> 192.168.18.132:socket
Replication filters are set on these hosts:
  artisan
    replicate_do_db = artisan
Please read the --check-replication-filters documentation to learn how to solve this problem.
[root@artisan ~]#

发现了从库，需要加 --nocheck-replication-filters

[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, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_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.
The new table `artisan`.`_t_order_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
Please check you have at least one UNIQUE and NOT NULLABLE index.
2020-02-03T11:47:25 Dropping new table...
2020-02-03T11:47:25 Dropped new table OK.
`artisan`.`t_order` was not altered.
[root@artisan ~]#

失败了。。。。这个表建的时候没建主键。加上后重新看下表定义

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, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_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 ~]#

从库没有这个表，。。。建下吧。

然后再来一次 ,

[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, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_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:03:27 Creating triggers...
2020-02-03T12:03:27 Created triggers OK.
2020-02-03T12:03:27 Copying approximately 1 rows...
Replica artisan is stopped.  Waiting.
Replica artisan is stopped.  Waiting.

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, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_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 ~]#

成功了。

看下表结构的变化