线上直接修改大表的结构导致锁表业务无法操作，所以使用pt-online-schema-change工具在线修改表结构和添加索引，保证业务正常进行
详细使用说明：pt-online-schema-change
背景：业务场景表更或者是表结构设计不合理导致很多慢查询影响使用
pt-online-schema-change原理:
首先它会新建一张一模一样的表，表名一般是_new后缀
然后在这个新表执行更改字段操作
然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
最后将原表的数据拷贝到新表中，然后替换掉原表
下面以一个测试表为例，线上数据大约800w
1、查看表结构
mysql> desc message;
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| message_id | varchar(50) | NO | MUL | NULL | |
| recieveid | varchar(60) | NO | MUL | NULL | |
| receivename | varchar(100) | YES | | NULL | |
| sendid | varchar(100) | NO | | NULL | |
| sendname | varchar(100) | YES | | NULL | |
| subject | varchar(255) | NO | | NULL | |
| status | int(5) | NO | MUL | 0 | |
| sendtime | varchar(40) | NO | | NULL | |
| classid | int(10) | YES | MUL | NULL | |
| account | varchar(30) | NO | | NULL | |
| recievetimestamp | varchar(50) | NO | MUL | NULL | |
| replycontent | text | YES | | NULL | |
| replyuser_id | int(5) | YES | | NULL | |
| replytime | varchar(30) | YES | | NULL | |
| from_platform | int(5) | YES | | NULL | |
| realsite | varchar(10) | YES | | NULL | |
| read | int(1) | NO | | 0 | |
| messagepath | varchar(255) | NO | | NULL | |
| messagelevel | int(4) | NO | | 0 | |
| attachpath | varchar(255) | YES | | NULL | |
| attachname | varchar(255) | YES | | NULL | |
| send_attachpath | varchar(255) | NO | | NULL | |
| ordernum | varchar(100) | YES | MUL | NULL | |
| buyer | varchar(100) | YES | MUL | NULL | |
| seller | varchar(100) | YES | | NULL | |
| plaincontent | longtext | NO | | NULL | |
| hasAttachment | int(1) | NO | | NULL | |
| font | varchar(20) | YES | | NULL | |
| fontsize | int(2) | YES | | NULL | |
| type | int(11) | NO | | 0 | |
| ruleType | int(2) | NO | MUL | 1 | |
| isRemove | int(1) | NO | | 0 | |
| spu | varchar(10) | NO | | | |
| is_delete | int(2) | NO | | 0 | |
+------------------+--------------+------+-----+---------+----------------+
2、查看索引
mysql> show index from message;
+---------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| message | 0 | PRIMARY | 1 | id | A | 227 | NULL | NULL | | BTREE | | |
| message | 1 | recievetimestamp | 1 | recievetimestamp | A | 227 | NULL | NULL | | BTREE | | |
| message | 1 | status | 1 | status | A | 8 | NULL | NULL | | BTREE | | |
| message | 1 | buyer | 1 | buyer | A | 25 | NULL | NULL | YES | BTREE | | |
| message | 1 | buyer | 2 | seller | A | 56 | NULL | NULL | YES | BTREE | | |
| message | 1 | ordernum | 1 | ordernum | A | 227 | NULL | NULL | YES | BTREE | | |
| message | 1 | classid | 1 | classid | A | 32 | NULL | NULL | YES | BTREE | | |
| message | 1 | ruleType | 1 | ruleType | A | 2 | NULL | NULL | | BTREE | | |
| message | 1 | message_id | 1 | message_id | A | 227 | NULL | NULL | | BTREE | | |
| message | 1 | recieveid | 1 | recieveid | A | 32 | NULL | NULL | | BTREE | | |
+---------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
查看到时间搜索使用较多，但是实际保存数据是用的时间戳，senditme和recievetimestamp的类型为varchar是不合理的，所以改字段类型为int，注意如果表中存在null或者为空的值时需要先处理，不然可能会warning
3、使用pt-online-schema-change进行修改
1）安装pt-online-schema-change
下载地址：pt-online-schema-change
安装
yum install -y percona-toolkit-3.2.0-1.el6.x86_64.rpm
2）mysql创建一个有权限的用户进行操作
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
GRANT ALL ON *.* TO 'test'@'%';
SET PASSWORD FOR 'test'@'%' = PASSWORD('123456');
3)修改表结构
测试执行
/usr/bin/pt-online-schema-change --charset=utf8 --no-version-check --user=test --password=123456 --host=127.0.0.1 P=3306,D=test,t=message --alter "DROP INDEX recievetimestamp" --print --dry-run //测试
正式执行
/usr/bin/pt-online-schema-change --charset=utf8 --no-version-check --user=test --password=123456 --host=127.0.0.1 P=3306,D=test,t=message --alter "DROP INDEX recievetimestamp, MODIFY COLUMN sendtime int(10) NOT NULL DEFAULT 0 AFTER status,MODIFY COLUMN recievetimestamp int(10) NOT NULL DEFAULT 0 AFTER account,DROP INDEX buyer,ADD INDEX sendid(sendid) USING BTREE,ADD INDEX account(account, realsite) USING BTREE" --print --execute
执行结果：
4）查看执行后的表结构和索引
mysql> desc message;
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| message_id | varchar(50) | NO | MUL | NULL | |
| recieveid | varchar(60) | NO | MUL | NULL | |
| receivename | varchar(100) | YES | | NULL | |
| sendid | varchar(100) | NO | MUL | NULL | |
| sendname | varchar(100) | YES | | NULL | |
| subject | varchar(255) | NO | | NULL | |
| status | int(5) | NO | MUL | 0 | |
| sendtime | int(10) | NO | | 0 | |
| classid | int(10) | YES | MUL | NULL | |
| account | varchar(30) | NO | MUL | NULL | |
| recievetimestamp | int(10) | NO | | 0 | |
| replycontent | text | YES | | NULL | |
| replyuser_id | int(5) | YES | | NULL | |
| replytime | varchar(30) | YES | | NULL | |
| from_platform | int(5) | YES | | NULL | |
| realsite | varchar(10) | YES | | NULL | |
| read | int(1) | NO | | 0 | |
| messagepath | varchar(255) | NO | | NULL | |
| messagelevel | int(4) | NO | | 0 | |
| attachpath | varchar(255) | YES | | NULL | |
| attachname | varchar(255) | YES | | NULL | |
| send_attachpath | varchar(255) | NO | | NULL | |
| ordernum | varchar(100) | YES | MUL | NULL | |
| buyer | varchar(100) | YES | | NULL | |
| seller | varchar(100) | YES | | NULL | |
| plaincontent | longtext | NO | | NULL | |
| hasAttachment | int(1) | NO | | NULL | |
| font | varchar(20) | YES | | NULL | |
| fontsize | int(2) | YES | | NULL | |
| type | int(11) | NO | | 0 | |
| ruleType | int(2) | NO | MUL | 1 | |
| isRemove | int(1) | NO | | 0 | |
| spu | varchar(10) | NO | | | |
| is_delete | int(2) | NO | | 0 | |
+------------------+--------------+------+-----+---------+----------------+
mysql> show index from message;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| message | 0 | PRIMARY | 1 | id | A | 494 | NULL | NULL | | BTREE | | |
| message | 1 | status | 1 | status | A | 8 | NULL | NULL | | BTREE | | |
| message | 1 | ordernum | 1 | ordernum | A | 494 | NULL | NULL | YES | BTREE | | |
| message | 1 | classid | 1 | classid | A | 32 | NULL | NULL | YES | BTREE | | |
| message | 1 | ruleType | 1 | ruleType | A | 2 | NULL | NULL | | BTREE | | |
| message | 1 | message_id | 1 | message_id | A | 494 | NULL | NULL | | BTREE | | |
| message | 1 | recieveid | 1 | recieveid | A | 30 | NULL | NULL | | BTREE | | |
| message | 1 | sendid | 1 | sendid | A | 247 | NULL | NULL | | BTREE | | |
| message | 1 | account | 1 | account | A | 30 | NULL | NULL | | BTREE | | |
| message | 1 | account | 2 | realsite | A | 54 | NULL | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
不支持并发update主键或唯一索引,但是insert和delete没问题
扫一扫
185
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
