分区表的使用--mysql

最新推荐文章于 2022-10-18 14:38:40 发布
最新推荐文章于 2022-10-18 14:38:40 发布
阅读量787 收藏
点赞数
分类专栏： 分区+存储+索引+游标
本文详细介绍了MySQL中的分区表概念，包括四种主要的分区类型：RANGE、LIST、HASH及KEY分区，并提供了创建这些分区表的具体实例。此外，还讨论了如何进行分区的维护操作，例如新增、删除和合并分区等。

MySQL使用分区表的好处：

1，可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。

2，方便维护，通过删除分区来删除老的数据。

3，分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。

MySQL可以建立四种分区类型的分区：(也可说有五种)

          RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

·         LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

·         HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

·         KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

一般用得多的是range分区和list分区。

---------------------------------------------------------------------------------------------------------------------------------------

1、分区表类型
RANGE分区、LIST分区、HASH分区、KEY分区、子分区

二、分区表的创建
1、RANGE分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE employees (
    idINT NOT NULL,
    fnameVARCHAR(30),
    lnameVARCHAR(30),
    hiredDATE NOT NULL DEFAULT '1970-01-01',
    separatedDATE NOT NULL DEFAULT '9999-12-31',
    job_codeINT NOT NULL,
    store_idINT NOT NULL
)
PARTITION BYRANGE (store_id) (
    PARTITION p0VALUES LESS THAN (6),
    PARTITION p1VALUES LESS THAN (11),
    PARTITION p2VALUES LESS THAN (16),
    PARTITION p3VALUES LESS THAN MAXVALUE
);

2、 LIST分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE employees (
    idINT NOT NULL,
    fnameVARCHAR(30),
    lnameVARCHAR(30),
    hiredDATE NOT NULL DEFAULT '1970-01-01',
    separatedDATE NOT NULL DEFAULT '9999-12-31',
    job_codeINT,
    store_idINT
)
PARTITION BYLIST(store_id)
    PARTITION pNorthVALUES IN(3,5,6,9,17),
    PARTITION pEastVALUES IN(1,2,10,11,19,20),
    PARTITION pWestVALUES IN(4,12,13,14,18),
    PARTITION pCentralVALUES IN(7,8,15,16)
);

Note：LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

3、 HASH分区

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE employees (
    idINT NOT NULL,
    fnameVARCHAR(30),
    lnameVARCHAR(30),
    hiredDATE NOT NULL DEFAULT '1970-01-01',
    separatedDATE NOT NULL DEFAULT '9999-12-31',
    job_codeINT,
    store_idINT
)
PARTITION BYHASH(YEAR(hired))
PARTITIONS 4;

3.1、LINEAR HASH分区

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE employees (
    idINT NOT NULL,
    fnameVARCHAR(30),
    lnameVARCHAR(30),
    hiredDATE NOT NULL DEFAULT '1970-01-01',
    separatedDATE NOT NULL DEFAULT '9999-12-31',
    job_codeINT,
    store_idINT
)
PARTITION BYLINEAR HASH(YEAR(hired))
PARTITIONS 4;

Note：线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数

4、KEY分区

1
2
3
4
5
6
7
CREATE TABLE tk (
    col1INT NOT NULL,
    col2CHAR(5),
    col3DATE
)
PARTITION BYLINEAR KEY (col1)
PARTITIONS 3;

Note：
1）按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

2）“CREATE TABLE … PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

5、子分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE ts (id INT, purchasedDATE)
    PARTITIONBY RANGE(YEAR(purchased))
    SUBPARTITIONBY HASH(TO_DAYS(purchased))
    (
        PARTITION p0VALUES LESS THAN (1990)
        (
            SUBPARTITION s0
                DATA DIRECTORY ='/disk0/data'
                INDEXDIRECTORY = '/disk0/idx',
            SUBPARTITION s1
                DATA DIRECTORY ='/disk1/data'
                INDEXDIRECTORY = '/disk1/idx'
        ),
        PARTITION p1VALUES LESS THAN (2000)
        (
            SUBPARTITION s0
                DATA DIRECTORY ='/disk2/data'
                INDEXDIRECTORY = '/disk2/idx',
            SUBPARTITION s1
                DATA DIRECTORY ='/disk3/data'
                INDEXDIRECTORY = '/disk3/idx'
        ),
        PARTITION p2VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s0
                DATA DIRECTORY ='/disk4/data'
                INDEXDIRECTORY = '/disk4/idx',
            SUBPARTITION s1
                DATA DIRECTORY ='/disk5/data'
                INDEXDIRECTORY = '/disk5/idx'
        )
    );

Note:子分区是分区表中每个分区的再次分割，DATA DIRECTORY/INDEX DIRECTORY确定数据和索引的存储位置
-----------------------------------------------------------------------------------------------------------------------------



RANGE分区

这里以一个销售的业务来做测试

销售表有日期/商品/销售额三个字段

测试数据从2010年1月1日至2010年9月31日

以“月”为单位进行分区

初期分区定义

首先需要查看，当前数据库是否支持分区

mysql>SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+

| Variable_name     | Value |

 

 

 

 

 

+-------------------+-------+

| have_partitioning | YES   |

 

+-------------------+-------+

1 row in set (0.03 sec)

创建分区表，按照年月的方式分区。

mysql> CREATE TABLE sale_data (

    ->   sale_date  DATETIME NOT NULL,

 

    ->   sale_item  VARCHAR(2) NOT NULL ,

 

    ->   sale_money DECIMAL(10,2) NOT NULL

 

    -> )

 

    -> PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) (

 

    ->   PARTITION p201001 VALUES LESS THAN (201002),

 

    ->   PARTITION p201002 VALUES LESS THAN (201003),

 

    ->   PARTITION p201003 VALUES LESS THAN (201004),

 

    ->   PARTITION p201004 VALUES LESS THAN (201005),

 

    ->   PARTITION p201005 VALUES LESS THAN (201006),

 

    ->   PARTITION p201006 VALUES LESS THAN (201007),

 

    ->   PARTITION p201007 VALUES LESS THAN (201008),

 

    ->   PARTITION p201008 VALUES LESS THAN (201009),

 

    ->   PARTITION p201009 VALUES LESS THAN (201010),

 

    ->   PARTITION pcatchall VLAUES LESS THAN MAXVALUE

    -> );

 

Query OK, 0 rows affected (0.20 sec)

新增分区

mysql> ALTER TABLE sale_data

    ->   ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

 

Query OK, 0 rows affected (0.36 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

删除分区

--当删除了一个分区，也同时删除了该分区中所有的数据。

mysql> ALTER TABLE sale_data DROP PARTITION p201010;

Query OK, 0 rows affected (0.22 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

分区的合并

下面的SQL，将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3

 

mysql> ALTER TABLE sale_data

    ->   REORGANIZE PARTITION p201001,p201002,p201003,

 

    ->                        p201004,p201005,p201006,

 

    ->                        p201007,p201008,p201009 INTO

 

    -> (

 

    ->   PARTITION p2010Q1 VALUES LESS THAN (201004),

 

    ->   PARTITION p2010Q2 VALUES LESS THAN (201007),

 

    ->   PARTITION p2010Q3 VALUES LESS THAN (201010)

 

    -> );

 

Query OK, 0 rows affected (1.14 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

分区的拆分

下面的SQL，将p2010Q1 分区，拆分为s2009 与s2010 两个分区

 

mysql> ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO (

 

    ->     PARTITION s2009 VALUES LESS THAN (201001),

 

    ->     PARTITION s2010 VALUES LESS THAN (201004)

 

    -> );

 

Query OK, 0 rows affected (0.36 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

一个利用不同物理位置数据源做分区的例子：

CREATE TABLE ts (id INT, purchased DATE) 
    ENGINE=innodb 
    PARTITION BY RANGE(YEAR(purchased)) 
    SUBPARTITION BY HASH(id) 
    ( 
        PARTITION p0 VALUES LESS THAN (1990) 
        ( 
            SUBPARTITION s0                  //在大的分区下又有小的分区
            DATA DIRECTORY='/usr/local/mysql/data0'      //数据源
            INDEX DIRECTORY='/usr/local/mysql/index0',   //索引数据源
            SUBPARTITION s1 
            DATA DIRECTORY='/usr/local/mysql/data1' 
            INDEX DIRECTORY='/usr/local/mysql/index1' 
        ), 
        PARTITION p1 VALUES LESS THAN (MAXVALUE) 
        ( 
            SUBPARTITION s2 
            DATA DIRECTORY='/usr/local/mysql/data1' 
            INDEX DIRECTORY='/usr/local/mysql/index1', 
            SUBPARTITION s3 
            DATA DIRECTORY='/usr/local/mysql/data2' 
            INDEX DIRECTORY='/usr/local/mysql/index2' 
        ) 
    ); 

分区索引的局限：

1，所有分区都要使用同样的引擎。

2，分区表的每一个唯一索引必须包含由分区函数引用的列。

3，mysql能避免查询所有的分区，但仍然锁定了所有分区。

4，分区函数能使用的函数和表达式有限，例如函数有上面的4种。

5，分区不支持外键。

6，不能使用LOAD INDEX INTO CACHE

7，分区并不能总是改善性能，要进行性能评测。

例如可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据：

mysql> explain partitions select * from fenqubiao where day<'2011-09-12';

+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table     | partitions    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | fenqubiao | p_2010,p_2011 | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |

+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)


mysql分区表之三：MySQL分区建索引[转]
weixin_33804582的博客
03-08 1234
介绍 mysql分区后每个分区成了独立的文件，虽然从逻辑上还是一张表其实已经分成了多张独立的表，从“information_schema.INNODB_SYS_TABLES”系统表可以看到每个分区都存在独立的TABLE_ID,由于Innodb数据和索引都是保存在".ibd"文件当中（从INNODB_SYS_INDEXES系统表中也可以得到每个索引都是对应各自的分区(primary key和u...
MySQL分区表---自动增删管理分区
授人以鱼不如授之以渔
08-09 647
分区表管理脚本 功能：实现分区表的自动管理，支持按天或者按月创建分区或者删除分区，自测已经通过，分享给大家。 可直接运行，但是记得先在测试环境操作，不要直接上生产。 #!/bin/bash database_name=$1 table_prefix=$2 keep_time=$3 partition_rules=$4 #数据库用户 db_user='user' db_pass='pass' db_host='localhost' help(){ cat <<- EOF Desc:该脚本
Django 查漏补缺记录
weixin_30834783的博客
09-12 236
sql中的唯一索引 联合索引使用结论: 1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引. 2):条件列中只要条件相连在一起,以本文例子来说就是: last_name=’1′ and first_name=’1′与first_name=’1′ and last_name=’1′,无论前后,都会利用上联合索引. 3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二...
达梦数据库分区表注意事项
不定期分享数据库相关内容。
01-12 1691
注意以上语句与第一条SQL相比 多了DISABLE，即禁用主键约束，会创建失败，报错为：RANGE水平分区表[DMTEST2]不支持此修改操作。达梦数据库分区表创建时必须包括全部分区列，否则创建失败，且分区表不支持禁用主键约束。以上语句可以创建成功。注意，其中主键包含了分区列C3,C4。以上语句创建表将会报错，报错信息为“局部唯一索引必须包含全部分区列”
Mysql分区总结
chunda
08-05 927
Mysql分区种类 range分区 list分区 hash分区 key分区 子分区 使用以下命令查看当前的Mysql版本是否支持分区 SHOW VARIABLES LIKE '%partition%'; +-----------------------+-------+ | Variable_name | Value | +-------------
MySQL之KEY分区和LINEAR KEY分区
小马哥的博客
10-18 1273
MySQL之KEY分区和LINEAR KEY分区
mysql使用教程之分区表的使用方法(删除分区表)
12-15
MySQL使用分区表的好处： 1，可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。2，方便维护，通过删除分区来删除老的数据。3，分区数据可以被分布到不同的物理位置，可以做分布式有效...
mysql-server-mysql-5.7.36.tar.gz
09-16
6. **分区表优化**：对分区表的处理有了显著的提升，包括更灵活的分区策略和更高效的分区删除。 7. **安全性和认证**：5.7版本增强了安全特性，比如支持更强大的加密算法，改进了认证插件，如引入了caching_sha2_...
mysql-server-mysql-8.3.0.tar.gz
最新发布
03-28
6. **分区表改进**：分区表是大型数据库中常见的优化手段，新的版本可能扩展了分区策略，提升了处理大数据集的能力。 7. **通用表表达式（Common Table Expressions, CTE）**：这是一类临时结果集，可以用于复杂...
MySQL表分区
热门推荐
atwdy的博客
06-13 2万+
主要介绍mysql几种表分区的基本使用，包括range分区、list分区、hash分区、key分区、子分区和columns分区。
mysql 分区-key分区（五）
yinjinshui的博客
10-09 5011
key分区 按照key进行分区非常类似于按照hash进行分区，只不过hash分区允许使用用户自定义的表达式 与hash分区不用的是，key分区不允许使用用于自定义的表达式，需要使用mysql服务器提供的hash函数，不需要hash分区那样对字段整型进行转换， 同时hash分区只支持整数分区，而key分区支持使用出blob or text类型外的其他类型的列作为分区键。 和hash功能一样，不同的是...
mySQL分区：List分区、Hash分区、线性Hash分区、Key分区
dxyzhbb的博客
09-03 4498
1.1 List分区 List分区的SQL语句如下： create table shareniu_range1 ( id int not null, name varchar(50), age int ) partition by List(id) ( partition p0 values in (1,3,5), partition p1 values in (2,4,6), partitio...
MySQL性能优化(五)：分区
每天叫醒自己的不是闹铃 是娟 是梦 是理想。
06-10 3884
由于公司使用的是mysql数据库，在存储hive表数据结构时，数量已经达到了千万级别，需要进行分区存储，对这方面在之前的hive外部分区表有些了解，mysql是不是相同的道理，这里我在同事那里看到一些文章，作为学习使用； 一: 分区简介 分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影
MySQL分区（Partition）功能试验
liq330的专栏
02-12 2133
目录[概述][分区表和未分区表试验过程][分区命令详解][概述]自5.1开始对分区(Partition)有支持，6.0应比较稳定= 水平分区（根据列属性按行分）=举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。=== 水平分区的几种模式：===* Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个
【Mysql】mysql表分区3 —Hash分区
sdu_yujielei的专栏
02-27 2062
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。 要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITI
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值