使用MYSQL分区提升检索速度

随着数据量的增加，MYSQL中的检索速度会越来越慢。之前我们使用了很多方法来提高MYSQL的检索速度，比如通过使用ORDER BY语句提升搜索速度，增加新的索引字段提升搜索速度。最终实现了从800万条数据当中通过时间条件搜索只需要耗时200毫秒的成绩。然而，对于这样的速度，我们还是不够满意的。为了优化检索速度，我们将对数据进行分区处理。

由于互联网上关于MYSQL数据库分区的文章非常多，因此，本篇文章就不重复造轮子了。对于新表进行分区是非常方便的。对于老表来说，就需要额外的步骤了。主要步骤是：

• 根据目标数据表建立一张新表并分区
• 将目标数据表的数据导入新表
• 删除目标数据表
• 将新表的表名改为目标数据表

也许大家都有疑问，难道就不能直接在目标数据表上创建分区么？有网友说即使是在同一张表上创建分区，MYSQL中实际也是创建新表并做以上操作（虽然没有去考证这个说法，但是感觉通过创建新表并移除旧表的方法安全性相对较高）。创建新表并分区的语句是比较简单的，例如：

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_time` bigint(20) DEFAULT '0',
  `total` int(11) DEFAULT '0',
  KEY (`id`),
  KEY `start_time_index` (`start_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(start_time)
   (
    PARTITION p2011 VALUES LESS THAN (1325347200000),
    PARTITION p2012 VALUES LESS THAN (1356969600000),
    PARTITION p2013 VALUES LESS THAN (1388505600000),
    PARTITION p2014 VALUES LESS THAN (1420041600000),
    PARTITION p2015 VALUES LESS THAN (1451577600000),
    PARTITION p2016 VALUES LESS THAN (1483200000000),
    PARTITION p2017 VALUES LESS THAN (1514736000000),
    PARTITION p2018 VALUES LESS THAN (1546272000000),
    PARTITION p2019 VALUES LESS THAN (1577808000000),
    PARTITION p2020 VALUES LESS THAN (1609430400000),
    PARTITION p2021 VALUES LESS THAN (1640966400000),
    PARTITION p2022 VALUES LESS THAN (1672502400000),
    PARTITION p2023 VALUES LESS THAN (1704038400000),
    PARTITION p2024 VALUES LESS THAN (1735660800000),
    PARTITION p2025 VALUES LESS THAN (1767196800000)
    );

在分区的过程中，大家可能会碰到以下错误：

A PRIMARY KEY must include all columns in the table’s partitioning function。

这个是由于如果要在分区的表中创建主键（Primary Key），那么用于分区的键也必须包含在主键中（或者整张表不能有主键）。因此，如果我们要在上表中创建主键的话，必须需改为：

PRIMARY KEY (id, start_time) 

做完以上操作并把数据导入新表共用了约19分50秒。操作完成以后，发现之前搜索需要约0.235秒，现在尽然需要0.406秒。显然这样的结果是完全不能接受的。虽然网上有朋友说，分区以后必须用分区的字段查询速度才会有优势。然而以下是我做的一个小测试：

mysql> SELECT count(*) from ieds.mytable_copy where start_time>=1555286400000 and start_time<1560556800000 ;
+----------+
| count(*) |
+----------+
|  4523076 |
+----------+
1 row in set (5.61 sec)

mysql> SELECT count(*) from ieds.mytable where start_time>=1555286400000 and start_time<1560556800000 ;
+----------+
| count(*) |
+----------+
|  4523076 |
+----------+
1 row in set (1.83 sec)

同样两张表中第一张表用了start_time做分区，第二张表没有做分区。理论上应该第一张表更快才对，但是事实胜于雄辩，因此在当前情况下，使用分区提升检索速度是不实际的。