https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html 参考《高性能》MySQL、《MySQL技术内幕-Innodb》

概述

概念

分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。

水平分区垂直分区

MySQL数据库支持的分区类型为水平分区（同一表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（同一表中不同列的记录分配到不同的物理文件中）。

局部分区索引

MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引存放在一个对象中。目前MySQL并不支持全局分区

  1. mysql> use pdemo;
  2. Database changed
  4. mysql> show tables;
  5. +-----------------+
  6. | Tables_in_pdemo |
  7. +-----------------+
  8. | members         |
  9. +-----------------+
  10. 1 row in set (0.00 sec)
  12. ###查看当前表是否示分区表
  13. mysql> show table status\G
  14. *************************** 1. row ***************************
  15.            Name: members
  16.          Engine: InnoDB
  17.         Version: 10
  18.      Row_format: Dynamic
  19.            Rows: 6
  20.  Avg_row_length: 16384
  21.     Data_length: 98304
  22. Max_data_length: 0
  23.    Index_length: 0
  24.       Data_free: 0
  25.  Auto_increment: NULL
  26.     Create_time: 2021-06-01 16:44:10
  27.     Update_time: 2021-06-01 17:09:32
  28.      Check_time: NULL
  29.       Collation: utf8_general_ci
  30.        Checksum: NULL
  31.  Create_options: partitioned
  32.         Comment:
  33. 1 row in set (0.00 sec)
  35. mysql>

语法

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分，因此下面创建分区的SQL语句会产生错误。

mysql> create table t1(
  c1 int not null,
  c2 char(3) not null,
  c3 int not null unique
)
partition by hash(c1)
partitions 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

//如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列
mysql> CREATE TABLE members (
    ->     firstname VARCHAR(25) NOT NULL,
    ->     lastname VARCHAR(25) NOT NULL,
    ->     username VARCHAR(16) NOT NULL,
    ->     email VARCHAR(35),
    ->     joined DATE NOT NULL
    -> )
    -> PARTITION BY RANGE( YEAR(joined) ) (
    ->     PARTITION p0 VALUES LESS THAN (2018),
    ->     PARTITION p1 VALUES LESS THAN (2019),
    ->     PARTITION p2 VALUES LESS THAN (2020),
    ->     PARTITION p3 VALUES LESS THAN (2021),
    ->     PARTITION p4 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.23 sec)


insert into pdemo.members values ('one','aa','aaone','aaone@ww.com','2017-06-01'),
                                 ('two','bb','bbtwo','bbtwo@ww.com','2018-05-01'),
                                 ('three','cc','ccthree','ccthree@ww.com','2019-04-01'),
                                 ('four','dd','ddfour','ddfour@ww.com','2020-04-01'),
                                 ('five','ee','eefive','eefive@ww.com','2021-04-01'),
                                 ('six','ff','ffsix','ffsix@ww.com','2019-09-09');

EXPLAIN PARTITIONS SELECT * FROM members\G


select * from members partition(p2);

分区类型

RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。

mysql> create table t(id int)
    -> partition by range(id)(
    -> partition p0 values less than (10),
    -> partition p1 values less than(20));
Query OK, 0 rows affected (0.12 sec)

####查看磁盘上的表文件
C:\ProgramData\MySQL\MySQL Server 5.7\Data\pdemo 的目录

2021/06/03  09:28    <DIR>          .
2021/06/03  09:28    <DIR>          ..
2021/06/01  16:39                61 db.opt
2021/06/03  09:28            98,304 t#p#p0.ibd
2021/06/03  09:28            98,304 t#p#p1.ibd
2021/06/03  09:28             8,556 t.frm


####插入数据
mysql> insert into t select 9;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 10;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 15;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0


###通过查询information_schema下的partitions查看t表各分区的情况
select * from information_schema.partitions where table_schema = database() and table_name='t'\G

LIST分区：和RANGE分区类型，只是LIST分区面向的是离散的值。
HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数
KEY分区：根据MySQL数据库提供的哈希函数来进行分区。