资料：《Mysql技术内幕》第四章第9节

一、分区表

查看是否开启分区 mysql> show plugins \G; ……. Name: partition Status: ACTIVE Type: STORAGE ENGINE Library: NULL License: GPL

分区就是将一个表或者索引物分解为多个更小、更可管理的部分。就数据库的应用而言，从逻辑上看就只有一个表或者一个索引，但在物理上表或者索引可能存储在多个物理分区中。每个分区都是独立的对象，可以独自处理，也可以作为整个逻辑表的一部分进行处理。

Mysql 中并不是所有的存储引擎都支持分区，常见支持分区的有 MyISAM、InnoDB、NDB等。

Mysql支持以下辑中类型的分区

  • RANGE 分区
    行数据基于属于一个给定连续区间的列值放入分区。
  • LIST 分区
    和 RNAGE 分区类似，只是 LIST 分区面向的是离散的值。
  • HASH 分区
    根据用户自定义的表达式的返回值进行分区，返回值不能为负数。
  • KEY 分区
    根据 MYSQL 数据库提供的哈希函数来进行分区。

不论创建何种类型的分区，如果表中存在主键或者唯一索引时，分区列必须是唯一搜因的一个组成部分。

二、RANGE 分区案例

在项目中更多还是使用的 RANGE 分区。

RANGE 分区主要用于日期列的分区，存储的数据可以根据年月来存放。
实战中分区列更多使用int存储时间，如果分区列使用的date类型的数据，优化器只能针对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。

step1、创建表指定分区并初始化数据

  1. CREATE TABLE `order_partion` (
  2.     `id` INT NOT NULL AUTO_INCREMENT,
  3.     `partition_key` INT NOT NULL,
  4.     `amt` DECIMAL ( 5 ) NULL,
  5.     PRIMARY KEY ( `id`, `partition_key` )) 
  6.     PARTITION BY RANGE ( partition_key ) PARTITIONS 5 (
  7.         PARTITION part0
  8.         VALUES
  9.             LESS THAN ( 202201 ),
  10.             PARTITION part1
  11.         VALUES
  12.             LESS THAN ( 202202 ),
  13.             PARTITION part2
  14.         VALUES
  15.             LESS THAN ( 202203 ),
  16.             PARTITION part3
  17.         VALUES
  18.             LESS THAN ( 202204 ),
  19.             PARTITION part4
  20.     VALUES
  21.         LESS THAN ( 202205 ));
  26.     INSERT INTO `seata_order`.`order_partion` (`id`, `partition_key`, `amt`) VALUES ('1', '202201', '1000');
  27. INSERT INTO `seata_order`.`order_partion` (`id`, `partition_key`, `amt`) VALUES ('2', '202202', '900');
  28. INSERT INTO `seata_order`.`order_partion` (`id`, `partition_key`, `amt`) VALUES ('3', '202203', '1200');

step2、分区查询测试

a、不按照分区字段进行查询

不按照分区字段进行查询，会导致查询直接从所有分区中查询，可能导致性能问题

EXPLAIN PARTITIONS SELECT * FROM order_partion
image.png

b、按照分区字段进行查询

查询时，严格按照分区字段查询，使得每次查询都在指定的分区内查询结果，从而达到分区效果。

EXPLAIN PARTITIONS SELECT * FROM order_partion WHERE partition_key = '202201'
image.png

三、分区前后数据库区别

从逻辑上分区前后的表并没有本质上的区别，其区别在于数据的物理存储。

分区前后数据文件物理存储对比

orders 表未进行分区 order 表进行了分区

如何获取 mysql 数据存储路径（SHOW VARIABLES LIKE '%datadir%';）
image.png

[root@mine_test seata_order]# ll 总用量 728 -rw-r-----. 1 systemd-bus-proxy ssh_keys 65 10月 31 01:29 db.opt -rw-r-----. 1 systemd-bus-proxy ssh_keys 8632 2月 22 00:59 order_partion.frm -rw-r-----. 1 systemd-bus-proxy ssh_keys 98304 2月 22 00:59 order_partion#P#part0.ibd -rw-r-----. 1 systemd-bus-proxy ssh_keys 98304 2月 22 00:59 order_partion#P#part1.ibd -rw-r-----. 1 systemd-bus-proxy ssh_keys 98304 2月 22 00:59 order_partion#P#part2.ibd -rw-r-----. 1 systemd-bus-proxy ssh_keys 98304 2月 22 00:59 order_partion#P#part3.ibd -rw-r-----. 1 systemd-bus-proxy ssh_keys 98304 2月 22 00:59 order_partion#P#part4.ibd -rw-r-----. 1 systemd-bus-proxy ssh_keys 8802 10月 31 01:29 orders.frm -rw-r-----. 1 systemd-bus-proxy ssh_keys 98304 10月 31 08:07 orders.ibd -rw-r-----. 1 systemd-bus-proxy ssh_keys 8840 10月 31 01:29 undo_log.frm -rw-r-----. 1 systemd-bus-proxy ssh_keys 114688 10月 31 08:07 undo_log.ibd

从上面可以看出，有2中类型的文件，.frm文件和.ibd文件

  • frm文件：表结构文件
  • ibd文件：InnoDB中，索引和数据都在同个文件.ibdata

通过对比，分区表 order比未分区表orders多了 *.ibd文件，几个分区对应几个 *.ibd文件。

四、分区对性能的影响

分区并不能从根本上解决单数据库存储瓶颈，

分区将一张表分割成多个物理分区进行数据存储，如果走分区键，会提升读的效率，如果不走分区键会将所有分区都执行一次，造成多次逻辑 IO。

所以在使用了分区表后，编写 SQL 时需要注意SQL的执行查询了几个分区，否则分区带来不是性能的提升而是灾难。