整合营销服务商

电脑端+手机端+微信端=数据同步管理

免费咨询热线:

MySQL表分区

MySQL可以通过SHOW 语句的输出来确定MySQL 是否支持分区。

mysql> SHOW PLUGINS;+------------+----------+| Name       | Status +------------+----------+| partition  | ACTIVE +------------+----------

也可以通过.来查看

mysql> SELECT    ->     PLUGIN_NAME as Name,    ->     PLUGIN_VERSION as Version,    ->     PLUGIN_STATUS as Status    -> FROM INFORMATION_SCHEMA.PLUGINS    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';+--------------------+| Name              | Status |+--------------------+---------+--------+| partition          |ACTIVE |+--------------------+---------+--------+

如果在输出中看到 的列为则说明MySQL支持分区功能。

1、MySQL分区概述

分区是根据一定的规则,把一个表分解成多个更小的、更容易管理的部分。分区表逻辑上是一个表,但实际上可能有N个物理分区对象组成,每个分区都是一个独立的对象,可以独立处理。每个分区中既存放了数据又存放了索引。

MySQL数据库支持的常用分区类型:

不论何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的组成部分。

2、MySQL分区类型2.1 range分区

range分区,是最常用的分区类型。

示例:

# a.创建分区表CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT NOT NULL,    store_id INT NOT NULL)PARTITION BY RANGE (store_id) (    PARTITION p0 VALUES LESS THAN (6),    PARTITION p1 VALUES LESS THAN (11),    PARTITION p2 VALUES LESS THAN (16),    PARTITION p3 VALUES LESS THAN (21));

查看表在磁盘的物理文件,启用分区之后,表变成由建立分区时的各个分区ibd文件组成。

mysql 空间数据类型_mysql enum类型_mysql 类型 decimal

分区表物理文件

# b.插入数据insert into employeesvalues(2, 'Mitchell', 'Wilson', '2020-04-19', '2020-04-19', 2,2);# c.查看数据select * from employees;# d.查看分区信息select table_name,partition_name,table_rowsfrom information_schema.partitionswhere table_schema=database()    and table_name='employees';

range分区表信息

2.2 list分区

list分区和range分区类似mysql 空间数据类型,只是分区列的值是离散的,而非连续的。

示例:

mysql 空间数据类型_mysql enum类型_mysql 类型 decimal

# a.建表drop table if exists t_columns_range;CREATE TABLE t_columns_range (    a INT,    b datetime)PARTITION BY range columns(b)(PARTITION p0 values less than ('2019-01-01'),PARTITION p1 values less than ('2020-01-01'));# b.插入数据insert into t_columns_range values(1,'2018-01-01'),(2,'2019-01-01');# c.查看数据select * from t_columns_range;# d.查看分区信息select table_name,partition_name,table_rowsfrom information_schema.partitionswhere table_schema=database()       and table_name='t_columns_range';

2.3 hash分区

hash分区的目的是将数据均匀分布到预先定义的各个分区中。

示例:

# a.建表drop table if exists t_hash;CREATE TABLE t_hash (col1 INT, col2 CHAR(5), col3 DATE)    PARTITION BY HASH( YEAR(col3) )    PARTITIONS 4;# b.写入数据insert into t_hash values(1,'a','2020-01-01'),(2,'b','2021-01-02'),(3,'c','2022-01-03'),(4,'a','2023-01-01');# c.查看数据SELECT * FROM t_hash;# d.查看分区信息select table_name,partition_name,table_rowsfrom information_schema.partitionswhere table_schema=database()    and table_name='t_hash';

2.4 key分区

key分区和hash分区相似,key分区使用MySQL数据库提供的函数进行分区。

示例:

# a.建表drop table if exists t_key;CREATE TABLE t_key (    id INT NOT NULL,    name VARCHAR(20),    UNIQUE KEY (id))PARTITION BY KEY()PARTITIONS 2;# b.插入数据insert into t_key values(1,'a'),(2,'b'),(3,'c'),(4,'a');# c.查看数据SELECT * FROM t_key;# d.查看分区信息select table_name,partition_name,table_rowsfrom information_schema.partitionswhere table_schema=database()       and table_name='t_key';

2.5 分区

分区是在RANGE和LIST 分区的变体,可以直接使用非整型的数据进行分区,而range/list/hash/key这四种分区的分区条件必须是整型。

示例:

# a.建表CREATE TABLE ts (id INT, purchased DATE)    PARTITION BY RANGE( YEAR(purchased) )    SUBPARTITION BY HASH( TO_DAYS(purchased) )    SUBPARTITIONS 2 (        PARTITION p0 VALUES LESS THAN (1990),        PARTITION p1 VALUES LESS THAN (2000),        PARTITION p2 VALUES LESS THAN MAXVALUE    );

list分区

mysql enum类型_mysql 空间数据类型_mysql 类型 decimal

# a.建columns list分区表CREATE TABLE customers_1 (    first_name VARCHAR(25),    last_name VARCHAR(25),    street_1 VARCHAR(30),    street_2 VARCHAR(30),    city VARCHAR(15),    renewal DATE)PARTITION BY LIST COLUMNS(city) (    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo'));

2.6 子分区

子分区是分区表中每个分区的进一步分区,MySQL数据库允许在RANGE和 LIST分区上再进行HASH或KEY分区。

示例:

# a.建表CREATE TABLE ts (id INT, purchased DATE)    PARTITION BY RANGE( YEAR(purchased) )    SUBPARTITION BY HASH( TO_DAYS(purchased) )    SUBPARTITIONS 2 (        PARTITION p0 VALUES LESS THAN (1990),        PARTITION p1 VALUES LESS THAN (2000),        PARTITION p2 VALUES LESS THAN MAXVALUE    );

表ts有3个RANGE 分区。这些分区中的每个分区p0,p1和p2进一步分为2个子分区。实际上,整个表分为多个 3 * 2 = 6分区。

示例:

# a.建表CREATE TABLE ts (id INT, purchased DATE)    PARTITION BY RANGE( YEAR(purchased) )    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (        PARTITION p0 VALUES LESS THAN (1990) (            SUBPARTITION s0,            SUBPARTITION s1        ),        PARTITION p1 VALUES LESS THAN (2000) (            SUBPARTITION s2,            SUBPARTITION s3        ),        PARTITION p2 VALUES LESS THAN MAXVALUE (            SUBPARTITION s4,            SUBPARTITION s5        )    );

使用子句明确定义子分区mysql 空间数据类型, 以指定各个子分区的选项。