客服平台，线上查询存在性能问题，为了解决或者说是缓解这个问题，除了加必要的索引，另外就是将表进行分区。
这里主要是针对既有的表进行分区，采用的是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，数据量大了，采用分区，或者加索引，可以缓解眼前的问题，但是，随着时间推移，若查询数据量不做限制，最终还是会出现查询响应非常慢的问题。所以，建议采用数据分割或者说是表拆分的方式，基于一定的业务场景或者需要进行，可以保证系统的高可用性。
扫一扫
433
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
