mysql 为分区表添加主键
适用范围
MYSQL 5.7 +
问题概述
客户生产环境有一个每天一个分区的大表，经常频繁update操作，而且没有主键，导致晚上跑批时，slave延迟很大，基本在10个小时左右。
问题原因
为了优化该表，我们给的建议是将历史数据分离出去，并通过创建主键索引提长效率，而客户表结构上并无合适的字段添加组合索引(分区表必须包含分区字段)，所以我们建议通过添加自增主键+分区键来创建组合索引。
解决方案
测试步骤如下：
1.创建测试表
mysql> CREATE TABLE `test` (
`name` VARCHAR(50),
`purchased` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005),
PARTITION p4 VALUES LESS THAN (2010),
PARTITION p5 VALUES LESS THAN (2015)
);
Query OK, 0 rows affected, 1 warning (0.19 sec)
mysql> desc test ;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| purchased | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO `test` VALUES
('desk organiser', '2003-10-15'),
('alarm clock', '1997-11-05'),
('chair', '2009-03-10'),
('bookcase', '1989-01-10'),
('exercise bike', '2014-05-09'),
('sofa', '1987-06-05'),
('espresso maker', '2011-11-22'),
('aquarium', '1992-08-04'),
('study desk', '2006-09-16'),
( 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from test ;
+----------------+------------+
| name | purchased |
+----------------+------------+
| bookcase | 1989-01-10 |
| sofa | 1987-06-05 |
| aquarium | 1992-08-04 |
| alarm clock | 1997-11-05 |
| lava lamp | 1998-12-25 |
| desk organiser | 2003-10-15 |
| chair | 2009-03-10 |
| study desk | 2006-09-16 |
| exercise bike | 2014-05-09 |
| espresso maker | 2011-11-22 |
+----------------+------------+
10 rows in set (0.00 sec)
2.添加字段
mysql> alter table test add id int;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test ;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| purchased | date | YES | | NULL | |
| id | int | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from test ;
+----------------+------------+------+
| name | purchased | id |
+----------------+------------+------+
| bookcase | 1989-01-10 | NULL |
| sofa | 1987-06-05 | NULL |
| aquarium | 1992-08-04 | NULL |
| alarm clock | 1997-11-05 | NULL |
| lava lamp | 1998-12-25 | NULL |
| desk organiser | 2003-10-15 | NULL |
| chair | 2009-03-10 | NULL |
| study desk | 2006-09-16 | NULL |
| exercise bike | 2014-05-09 | NULL |
| espresso maker | 2011-11-22 | NULL |
+----------------+------------+------+
10 rows in set (0.00 sec)
3.备份表
[root@s2ahuoracle02 run]# mysqldump -uroot -proot -P3333 --socket=/u01/mysql8e/data/run/mysql3333.sock --set-gtid-purged=OFF --no-create-info pardb test > backup3.sql
mysql> rename table test to test_bak;
Query OK, 0 rows affected (0.09 sec)
4.创建带主的表结构
mysql> CREATE TABLE `test` (
`id` INT AUTO_INCREMENT,
`name` VARCHAR(50),
`purchased` DATE ,
PRIMARY KEY(id, purchased)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005),
PARTITION p4 VALUES LESS THAN (2010),
PARTITION p5 VALUES LESS THAN (2015)
);
Query OK, 0 rows affected, 1 warning (0.17 sec)
mysql>
mysql> desc test;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| purchased | date | NO | PRI | NULL | |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from test ;
Empty set (0.00 sec)
5.修改备份脚本
由于备份原表的字段顺序，与新表的字段顺序不一致，此时我们需要调整备份脚本的字段顺便。添字段顺序 (name,purchased,id) 到备份的脚本中。vi backup3.sql
6.将数据导入到表中
mysql> source backup3.sql
mysql> select * from test ;
+----+----------------+------------+
| id | name | purchased |
+----+----------------+------------+
| 1 | bookcase | 1989-01-10 |
| 2 | sofa | 1987-06-05 |
| 3 | aquarium | 1992-08-04 |
| 4 | alarm clock | 1997-11-05 |
| 5 | lava lamp | 1998-12-25 |
| 6 | desk organiser | 2003-10-15 |
| 7 | chair | 2009-03-10 |
| 8 | study desk | 2006-09-16 |
| 9 | exercise bike | 2014-05-09 |
| 10 | espresso maker | 2011-11-22 |
+----+----------------+------------+
10 rows in set (0.01 sec)
