对 MySQL 表分区与 MERGE分表的个人理解

问题

最近数据库表数据接近3000万，查询性能有点慢

分析

对于MySQL数据库我已经做的：

• 经常查询的字段，已经建了索引，遵循左前缀原则。
• 表已经分区，按照数据时间戳划分，每年的数据一个分区。

那为什么还慢呢？排查发现，虽然分了区，但是有一个常用的查询语句，默认并没有带时间戳这个字段，分区是按时间戳来划分的，你又不带时间戳这个字段，那不就得全分区检索了吗？所以慢啊，分区并没有被有效利用。

就好像网上看到的一些例子，用日期分区后，想测试一下性能：

SELECT COUNT(*) FROM mytable;
SELECT * FROM mytable WHERE user_id = 34;

效果并不明显……废话，你查全部数据，并没有使用时间戳来筛选，当然是全分区检索了。

我认为表分区和编程语言里中的字典/Map是相似的，按某种规则建立起一个个存放数据桶，数据按照你给定的规则放进不同的桶里。依据 关键字 来确定数据在哪个桶里，这样就一下子过滤掉了大部分无效数据。在我的例子里，时间戳就是这个 关键字，如果你不带这个 关键字，那就和未分区一样，还是要在全部分区里查找。

在网上看到的例子，大多是按时间/日期来分区的，所以心里也有了一份执念——按时间划分区比较合理，每年/月一个区，多合适。
其实，这个要看你的实际场景，按时间不一定就是好的。
我所查询的数据，是按设备来查询的，时间戳不是必要条件，但设备ID通常是必要条件。
那么我就把分区改为按照设备ID划分，分为20个表:

ALTER TABLE mytable 
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `device_id`) 
PARTITION BY LIST (device_id % 20)
(PARTITION p0 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (4) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (5) ENGINE = InnoDB,
 PARTITION p5 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p6 VALUES IN (7) ENGINE = InnoDB,
 PARTITION p7 VALUES IN (8) ENGINE = InnoDB,
 PARTITION p8 VALUES IN (9) ENGINE = InnoDB,
 PARTITION p9 VALUES IN (10) ENGINE = InnoDB,
 PARTITION p10 VALUES IN (11) ENGINE = InnoDB,
 PARTITION p11 VALUES IN (12) ENGINE = InnoDB,
 PARTITION p12 VALUES IN (13) ENGINE = InnoDB,
 PARTITION p13 VALUES IN (14) ENGINE = InnoDB,
 PARTITION p14 VALUES IN (15) ENGINE = InnoDB,
 PARTITION p15 VALUES IN (16) ENGINE = InnoDB,
 PARTITION p16 VALUES IN (17) ENGINE = InnoDB,
 PARTITION p17 VALUES IN (18) ENGINE = InnoDB,
 PARTITION p18 VALUES IN (19) ENGINE = InnoDB,
 PARTITION p19 VALUES IN (0) ENGINE = InnoDB);

这样就可以每次查询的时候，先按分区规则定位到分区，只在分区中检索数据了，很快。

MySQL 表分区与 MERGE 分表的比较

MERGE 分表

在学习过程中，也看到了 MERGE 分表的方法。

• 只能使用MyISAM引擎，
• 需建立多个子表
• 建立一个总表，总表中把几个子表联合起来，定义数据插入到First/Last子表
• 子表、总表表结构必须相同
• 总表不存储数据
• 所有子表中的数据ID 不是唯一的，会重复（可建个专门生成ID的中间表解决）
• 插入、查询数据只需要操作总表

看到有人说 MERGE 分表比较简单，应用程序无需改动，只要操作总表即可。
是这样的，插入是没问题，但如果查询仍然操作总表的话，是不会提升性能的，因为还是要检索所有子表内的数据。
所以MERGE拆表，应该主要是做数据拆分，如果要提升查询性能，还是要改程序，直接定位要查询的子表，才能快。比较适合按日期划分子表的场景吧。

我想它的优点应该是灵活：

• 可以方便地增加子表
• 更新总表，把新的子表联合进去。因为总表不存储数据，所以表更新很容易。很容易就扩充了

表分区

表分区是真正的对上层应用程序隐形，不需要改动程序。
查询时带上关键字字段，大大缩小查询范围。我觉得它的缺点是不灵活：

• 插入更新会变慢，因为要确定分区
• 分区一旦创建就固定了，要想更改分区，会导致表内数据的重新分配调整。如果数据量很大，将花费巨量时间。
  先创建一个重新分区的新表，再把旧表的数据转移过去是个更好、更快的方案，转移时可以加上 id 筛选部分数据，这样就可以分次、分批转移了：

INSERT INTO mytable_new SELECT * FROM mytable WHERE id >= 1 AND id < 1000000;

转移成功后，再把新表、旧表重命名一下，就完成了表替换。

RENAME TABLE mytable TO mytable_old;
RENAME TABLE mytable_new TO mytable;

可以用 explain 来确认查询的分区

EXPLAIN SELECT * FROM mytable WHERE device_id = 36;

即使直接使用ID进行查询，加上关键字字段，也能缩小查询范围

EXPLAIN SELECT * FROM mytable WHERE id = 3672 AND device_id = 36;

上面如果不带device_id ，只带id查询，也很快，但仍然是在所有分区检索。带上device_id 就直接定位到了一个分区，更快。

如果查询条件中没带device_id，那分区就根本没有被利用到了。下面语句会在所有分区中筛选数据

SELECT * FROM mytable WHERE class_id = 32 AND ts BETWEEN 1593234321 AND 1594526324;

所以表分区后，上层应用程序查询时，也要使用 关键字 字段，使分区能够有效

总结

对于提升查询性能：
其实表分区就是数据库按既定规则帮你定位分区，缩小了查询范围。
而 MERGE 分表需要你在应用程序层面，按规则定位到子表，缩小查询范围。

对数据库的优化不只是数据库，还要『优化』使用它的人。数据库优化好了，某一个开发人员写了一个SQL语句，索引全部未命中，分区条件字段未使用……那好了，就是全表扫描，对于大表的查询SQL，还是要仔细斟酌，了解表结构、索引设计才能有好的效率，避免全表扫描。