MySQL数据库分区操作【RANGE】

客服平台，线上查询存在性能问题，为了解决或者说是缓解这个问题，除了加必要的索引，另外就是将表进行分区。

这里主要是针对既有的表进行分区，采用的是alter table xxx的方式，当然，也可以采用create table xxx partition by range（abc）的方式，都是可以的。两种方式，都验证和测试过，都可行！这里主要介绍alter的方式！

主要是因为alter的过程，遇到一点小小的问题，以备后查。

通过show create table 的方式查看我们的chat_message_history表，结构如下：

Table    Create Table
chat_message_history    CREATE TABLE `chat_message_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visitor_id` varchar(128) DEFAULT NULL,
  `visitor_name` varchar(255) DEFAULT NULL,
  `contentBlob` blob,
  `sender` varchar(32) DEFAULT NULL,
  `message_time` datetime DEFAULT NULL COMMENT '消息发送时间',
  `jobId` varchar(11) DEFAULT NULL,
  `robot_response` varchar(2000) DEFAULT NULL COMMENT '机器人的回复消息',
  `skill_group_id` varchar(32) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `new_skill_group_id` varchar(32) DEFAULT NULL,
  `channel` varchar(16) DEFAULT 'WXAPP' COMMENT '渠道',
  `message_id` varchar(64) DEFAULT NULL,
  `sessionId` varchar(50) DEFAULT NULL,
  `message_status` varchar(11) DEFAULT NULL,
  `error_message` varchar(255) DEFAULT NULL,
  `businessType` varchar(11) DEFAULT NULL COMMENT '1-欢迎语；',
  `pFlag` varchar(11) DEFAULT NULL COMMENT '消息的产品属性 1-微医保 2-微重疾',
  PRIMARY KEY (`id`),
  KEY `IDX_jobId` (`jobId`),
  KEY `idx_his_vis_ctdesc_key` (`visitor_id`,`skill_group_id`,`message_time`)
) DEFAULT CHARSET=utf8

然后就是alter table的方式添加分区，分区按照消息时间，大体是每个月一个分区：

alter table chat_message_history partition by range(to_days(message_time)) (
    partition p201708 values less than (to_days('2017-08-31')),
    partition p201709 values less than (to_days('2017-09-30')),
    partition p201710 values less than (to_days('2017-10-31')),    
    partition p201711 values less than (to_days('2017-11-30')),
    partition p201712 values less than (to_days('2017-12-31')),    
    partition p201801 values less than (to_days('2018-01-31')),     
    partition p201802 values less than (to_days('2018-02-30')),
    partition p201803 values less than (to_days('2018-03-31')),
    partition p201804 values less than (to_days('2018-04-30')),  
    partition p201805 values less than (to_days('2018-05-31')),
    partition p201806 values less than (to_days('2018-06-30')),  
    partition p201807 values less than (to_days('2018-07-31')),  
    partition p201808 values less than (to_days('2018-08-31')),  
    partition p201809 values less than (to_days('2018-09-30')),  
    partition p201810 values less than (to_days('2018-10-31')),  
    partition p201811 values less than (to_days('2018-11-30')),    
    partition p201812 values less than (to_days('2018-12-31')),  
    partition p201901 values less than (to_days('2019-01-31')),   
    partition p201902 values less than (to_days('2019-02-30')),
    partition p201903 values less than (to_days('2019-03-31')),  
    partition p201904 values less than (to_days('2019-04-30')),
    partition p201905 values less than (to_days('2019-05-31')),  
    partition p201906 values less than (to_days('2019-06-30')),
    partition p201907 values less than (to_days('2019-07-31')),  
    partition p201908 values less than (to_days('2019-08-31')),  
    partition p201909 values less than (to_days('2019-09-30')),
    partition p201910 values less than (to_days('2019-10-31')),    
    partition p201911 values less than (to_days('2019-11-30')),  
    partition p201912 values less than (to_days('2019-12-31')),   
    partition p202001 values less than (to_days('2020-01-31')),
    partition p202002 values less than (to_days('2020-02-30')),
    partition p202003 values less than (to_days('2020-03-31')),
    partition p202004 values less than (to_days('2020-04-30')),
    partition p202005 values less than (to_days('2020-05-31')),
    partition p202006 values less than (to_days('2020-06-30')),
    partition p202007 values less than (to_days('2020-07-31')),
    partition p202008 values less than (to_days('2020-08-31')),
    partition p202009 values less than (to_days('2020-09-30')),
    partition p202010 values less than (to_days('2020-10-31')),
    partition p202011 values less than (to_days('2020-11-30')),
    partition p202012 values less than (to_days('2020-12-31')),
    PARTITION p202XYZ VALUES LESS THAN (MAXVALUE));

上述SQL执行报错：

ERROR 1566 (HY000): Not allowed to use NULL value in VALUES LESS THAN

仔细查看，上述的LESS THAN后面没有NULL的值啊，都是写的很明确的年月日进行获取天数来得到分界线的啊。。。最后研究下to_days（expr）函数，

官方文档：

TO_DAYS(date)

Given a date date, returns a day number (the number of days since year 0).

我怀疑是因为我给定的每年的2月份的年月日信息不合法，验证一下：

mysql> select TO_DAYS('2017-02-30');
+-----------------------+
| TO_DAYS('2017-02-30') |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2017-02-30' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

结合上述错误提示，将分区SQL语句调整一下如下：

alter table chat_message_history partition by range(to_days(message_time)) (
    partition p201708 values less than (to_days('2017-09-01')),
    partition p201709 values less than (to_days('2017-10-01')),
    partition p201710 values less than (to_days('2017-11-01')),    
    partition p201711 values less than (to_days('2017-12-01')),
    partition p201712 values less than (to_days('2018-01-01')),    
    partition p201801 values less than (to_days('2018-02-01')),     
    partition p201802 values less than (to_days('2018-03-01')),
    partition p201803 values less than (to_days('2018-04-01')),
    partition p201804 values less than (to_days('2018-05-01')),  
    partition p201805 values less than (to_days('2018-06-01')),
    partition p201806 values less than (to_days('2018-07-01')),  
    partition p201807 values less than (to_days('2018-08-01')),  
    partition p201808 values less than (to_days('2018-09-01')),  
    partition p201809 values less than (to_days('2018-10-01')),  
    partition p201810 values less than (to_days('2018-11-01')),  
    partition p201811 values less than (to_days('2018-12-01')),    
    partition p201812 values less than (to_days('2019-01-01')),  
    partition p201901 values less than (to_days('2019-02-01')),   
    partition p201902 values less than (to_days('2019-03-01')),
    partition p201903 values less than (to_days('2019-04-01')),  
    partition p201904 values less than (to_days('2019-05-01')),
    partition p201905 values less than (to_days('2019-06-01')),  
    partition p201906 values less than (to_days('2019-07-01')),
    partition p201907 values less than (to_days('2019-08-01')),  
    partition p201908 values less than (to_days('2019-09-01')),  
    partition p201909 values less than (to_days('2019-10-01')),
    partition p201910 values less than (to_days('2019-11-01')),    
    partition p201911 values less than (to_days('2019-12-01')),  
    partition p201912 values less than (to_days('2020-01-01')),   
    partition p202001 values less than (to_days('2020-02-01')),
    partition p202002 values less than (to_days('2020-03-01')),
    partition p202003 values less than (to_days('2020-04-01')),
    partition p202004 values less than (to_days('2020-05-01')),
    partition p202005 values less than (to_days('2020-06-01')),
    partition p202006 values less than (to_days('2020-07-01')),
    partition p202007 values less than (to_days('2020-08-01')),
    partition p202008 values less than (to_days('2020-09-01')),
    partition p202009 values less than (to_days('2020-10-01')),
    partition p202010 values less than (to_days('2020-11-01')),
    partition p202011 values less than (to_days('2020-12-01')),
    partition p202012 values less than (to_days('2021-01-01')),
    PARTITION p202XYZ VALUES LESS THAN (MAXVALUE));

执行后还是报错：

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

这个错误是说，分区函数里面，主键必须包含所有的用于建立分区的列。我这里分区，是按照message_time进行分区，所以，这里将message_time和既有的id主键建立联合主键。SQL如下（先删除既有的id主键，再建联合主键）：

alter table chat_message_history drop primary key,add primary key (`id`,`message_time`);

再次执行创建分区的SQL：

mysql> alter table chat_message_history partition by range(to_days(message_time)) (
    -> partition p201708 values less than (to_days('2017-09-01')),
    -> partition p201709 values less than (to_days('2017-10-01')),
    -> partition p201710 values less than (to_days('2017-11-01')),    
    -> partition p201711 values less than (to_days('2017-12-01')),
    -> partition p201712 values less than (to_days('2018-01-01')),    
    -> partition p201801 values less than (to_days('2018-02-01')),     
    -> partition p201802 values less than (to_days('2018-03-01')),
    -> partition p201803 values less than (to_days('2018-04-01')),
    -> partition p201804 values less than (to_days('2018-05-01')),  
    -> partition p201805 values less than (to_days('2018-06-01')),
    -> partition p201806 values less than (to_days('2018-07-01')),  
    -> partition p201807 values less than (to_days('2018-08-01')),  
    -> partition p201808 values less than (to_days('2018-09-01')),  
    -> partition p201809 values less than (to_days('2018-10-01')),  
    -> partition p201810 values less than (to_days('2018-11-01')),  
    -> partition p201811 values less than (to_days('2018-12-01')),    
    -> partition p201812 values less than (to_days('2019-01-01')),  
    -> partition p201901 values less than (to_days('2019-02-01')),   
    -> partition p201902 values less than (to_days('2019-03-01')),
    -> partition p201903 values less than (to_days('2019-04-01')),  
    -> partition p201904 values less than (to_days('2019-05-01')),
    -> partition p201905 values less than (to_days('2019-06-01')),  
    -> partition p201906 values less than (to_days('2019-07-01')),
    -> partition p201907 values less than (to_days('2019-08-01')),  
    -> partition p201908 values less than (to_days('2019-09-01')),  
    -> partition p201909 values less than (to_days('2019-10-01')),
    -> partition p201910 values less than (to_days('2019-11-01')),    
    -> partition p201911 values less than (to_days('2019-12-01')),  
    -> partition p201912 values less than (to_days('2020-01-01')),   
    -> partition p202001 values less than (to_days('2021-02-01')),
    -> partition p202002 values less than (to_days('2020-03-01')),
    -> partition p202003 values less than (to_days('2020-04-01')),
    -> partition p202004 values less than (to_days('2020-05-01')),
    -> partition p202005 values less than (to_days('2020-06-01')),
    -> partition p202006 values less than (to_days('2020-07-01')),
    -> partition p202007 values less than (to_days('2020-08-01')),
    -> partition p202008 values less than (to_days('2020-09-01')),
    -> partition p202009 values less than (to_days('2020-10-01')),
    -> partition p202010 values less than (to_days('2020-11-01')),
    -> partition p202011 values less than (to_days('2020-12-01')),
    -> partition p202012 values less than (to_days('2021-01-01')),
    -> PARTITION p202XYZ VALUES LESS THAN (MAXVALUE));
Query OK, 0 rows affected (1.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

这回成功了，真是折腾！！！

现在就要来验证一下，我们的分区是否起到作用了。主要是进行对比呗，先看没有建立分区的SQL查询：

mysql> explain select * from chat_message_history where message_time > '2017-12-01' and message_time < '2018-01-01';
+----+-------------+----------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | chat_message_history | ALL  | NULL          | NULL | NULL    | NULL | 5103176 | Using where |
+----+-------------+----------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

涉及到表扫描行数是5103176，这个表一共530W行记录，这里就扫描了510W行，够可以的。。。

那么，加了分区后呢？请看下面的SQL查询：

mysql> explain select * from chat_message_history where message_time > '2017-12-01' and message_time < '2018-01-01';
+----+-------------+----------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | chat_message_history | ALL  | NULL          | NULL | NULL    | NULL | 829848 | Using where |
+----+-------------+----------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

这回查询扫描的行数，就变成了80多万行了，少了不少啊！

从这次分区看，分区查询和不分区查询，影响到的扫描行数还是挺明显的。

总结一下：

1，MySQL数据量达到几百万后，多表联合查询时，性能极其不稳定，这个是我们线上系统的真实写照，几天内，两次查询导致数据库连接数耗尽，这次600个连接，全部占用，导致系统不可用！

2，数据量大了，采用分区，或者加索引，可以缓解眼前的问题，但是，随着时间推移，若查询数据量不做限制，最终还是会出现查询响应非常慢的问题。所以，建议采用数据分割或者说是表拆分的方式，基于一定的业务场景或者需要进行，可以保证系统的高可用性。

MySQL数据库分区操作【RANGE】

相关推荐