问题现象

客户生产环境有一个每天一个分区的大表，经常频繁dml操作，而且没有主键，导致晚上跑批时，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)

复制