相信在提到对于 MySQL 千万级大表做优化时,兄弟萌脑海中第一个想法就是:分库分表,但除非单表数据未来会一直不断上涨,否则不要一开始就考虑分库分表,拆分会带来逻辑、部署、运维的各种复杂度。事实上很多时候 MySQL 单表的性能依然有不少优化空间。比如:使用 MySQL 分区表。
但兄弟萌千万别把分区表和分库分表搞混淆了,分库分表区别于分区的是:分区一般都是放在单机里的,用的比较多的是时间范围分区,方便归档。分库分表和分区并不冲突,可以结合使用。
日常开发中,我们经常会遇到大表的情况,所谓大表就是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长、性能低下,如果涉及联合查询的情况下,性能会更加糟糕。对表进行分区,目的就是减少数据库的负担,提高数据库的效率,提高表的 CRUD 效率。
对于MySQL分区表,相信很多兄弟萌也没接触过,下面我们来聊聊 MySQL 分区表,Let’s Go!
分区表概念
针对表分区,简单的说就是将一个大表按照 MySQL 提供的几种方式,分成几个小表。
分区是将数据分段划分在多个位置存放,可以是同一块磁盘,也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。应用程序读写时操作的还是大表名字,数据库系统自动去组织分区的数据。
MySQL 数据库支持的分区类型为水平分区(指将同一个表中不同行的记录分配到不同的物理文件中),并不支持垂直分区(指将同一表中不同列的记录分配到不同的物理文件中)
MySQL 5.1 以后新增了表分区(Partition)的功能,它的好处有:
- 可以存储更多的数据
- 一些查询可以得到极大的优化
- 删除分区或它的数据是容易的,因为它不影响其它表
- 对大表的维护更快、更容易,因为数据分布在不同的逻辑文件上
- 涉及到 SUM( )/COUNT( ) 等聚合函数时,可以并行进行 全表扫描变成了分区扫描,提高 IO 效率
可能还是有兄弟无法体会到分区带来的好处,下面我们举一个直观的例子:
比如我们班有 39 个同学,去食堂窗口打饭菜,只开一个窗口,打菜和打饭都是在这个窗口,39 个人依次排队,效率肯定很低。
对食堂窗口进行分区,打饭开一个窗口,打菜开一个窗口,对于打菜我们再细分一下,因为有的同学喜欢吃素菜,有的同学喜欢吃肉菜,我们把打菜窗口分为肉菜窗口和素菜窗口,这样效率肯定大幅度提升。
虽然分区了,但对外只有一个食堂,不会随着窗口的变多食堂变多,学生都去这个食堂吃饭。
分区类型
-
范围分区(Range Partition)
通常是使用频率最高的分区,如:按月份或年份划分,这样的数据保存均匀性比较好。
-
哈希分区(Hash Partition)
如果数据不是那么容易进行划分,就可以选择哈希分区。通过某种策略(hash、range 等)将数据均匀插入到不同的块。
-
列表分区(List Partition)
当明确指定了根据某字段的某个具体值进行分区,采用这种方式。
-
复合分区
侧重于数据归档,将上述三个组合起来使用。根据业务需求的数据分布来选择合适的组合。
分区表创建
CREATE TABLE table_name(
....
)
partition by RANGE|LIST|HASH(table_column)(
PARTITION p0....
PARTITION p1....
PARTITION p2....
)
范围表分区
CREATE TABLE table_name(