深入理解MySQL分区表的使用,
目录
- 前言
- 什么需要分区表
- 分区的策略
- 分区表应用场景
- 分区表的限制
- 分区类型
- RANGE分区
- LIST分区
- HASH分区
- LINER HASH
- KEY分区
前言
当我们单表数据量比较大的时候,单表查询的IO较大。这个时候,我们是不是可以将表的数据分成多个文件,按照某个条件进行单文件的查询,这样避免了大量的IO操作。通过分而治之的思想,减少IO操作,提升查询效率。本文主要是讲述MySQL中分区表,看MySQL是如何实现分区表的。
什么需要分区表
MySQL从5.1版本开始支持分区功能,分区是将一个表的数据按照某种方式分别存储,比如按照时间上的月份,分成多个较小的,更容易管理的部分,但是逻辑上仍是一个表。还没出现分区表的时候,所有的数据都是存放在一个文件里面的,如果数据量太大,查询数据时总是避免不了需要大量io操作;使用分区表后,每个分区存放不同的数据。这样不但可以减少IO。还可以加快数据的访问;为了保证MySQL的性能,我们都建议MySQL单表不要太大,建议是:单表小于2GB,记录数小于1千万,十库百表。如果但行记录数非常小,那么记录数可以再偏大些,反之,可能记录数到百万级别就开始变慢了。那么,业务量在增长,数据到瓶颈了怎么办呢,除了使用分布式数据库,我们也可以自行分库分表,或者利用MySQL的分区功能实现。分区表的出现是为了分而治之的概念,分区表的用处非常大,只是现在还有很多人都不知道;将一个表设置为分区表后,会在数据文件.i的文件名加上#号,代表这是一个分区表;
分区的策略
对于大数据表,有两种策略进行分区:
1: 不使用索引:创建数据表时不增加索引,而是使用分区定位到所需要的数据行。只要你使用 WHERE 条件将查询切分到很小的分区范围,就已经足够了。这个时候需要通过数学方法计算查询的响应时间是否能够接受。当然,这里的假设是不会将数据放到内存中,而是全部数据都从磁盘读取。因此数据很快就会被其他查询覆盖,使用缓存没什么意义。这种情况一般用于大量数据表的基数是常规的。需要注意的是,需要限制分区数在几百。
2: 使用索引,并且隔离热区数据:如果除了热区数据外,大部分数据是不使用的,则可以将热区数据单独的分区,这个分区算上索引都能够加载到内存中。这个时候可以通过索引来优化性能,就像操作普通的数据表一样。
分区表应用场景
1: 表非常大以至于无法全部放在内存中,或者只在表的最后部分有热点数据,其他都是历史数据
2: 分区表的数据更容易维护,,能批量删除大量数据
3: 对一个独立分区进行优化、检查、修复等操作
4: 分区表的数据可以分布在不同的设备上,从未高效的利用多个硬件设备
5: 可以备份和恢复独立的分区
分区表的限制
一个表最多能有1024个分区,在5.7版本及以上可以有8196个分区。在早期MySQL中,分区表达式必须是整数或者整返回整数的表达式,
在MySQL5.5中,某些场景可以直接使用列来进行分区
- 分区表无法使用外检约束
- 最好不要去修改分区列
如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来;就像这样:
-- 创建分区必须包含所有主键 create table user_11( id bigint(20) not null , name varchar(20) , age int(3) not null , PRIMARY KEY (`id`,`age`) ) -- 创建分区 partition by range columns(id,age)( partition p00 values less than(6,30), -- 小于6的值在P0分区 partition p11 values less than(11,40), -- 小于11的值在p1分区 partition p22 values less than(16,50), -- 小于16的值在p2分区 partition p33 values less than (9999,9999) -- 大于9999的值在p3分区,或者用一个更大的值 ); -- 创建分区必须包含所有唯一键 create table user_22( id bigint(20) not null, name varchar(20) , age int(3) not null , unique key only_one_1(age,id ) ) -- 创建分区 partition by range columns(id,age)( partition p000 values less than(6,30), -- 小于6的值在P0分区 partition p111 values less than(11,40), -- 小于11的值在p1分区 partition p222 values less than(16,50), -- 小于16的值在p2分区 partition p333 values less than (9999,9999) -- 大于9999的值在p3分区,或者用一个更大的值 );
分区类型
RANGE分区
基于属于一个给定连续区间的列值,把多行分配给分区。
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) );
LIST分区
类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。 注释:在MySQL 5.1中,当使用LIST分区时,有可能只能匹配整数列表。
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, store_id INT );
HASH分区
基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num是一个非负的整数,它表示表将要被分割成分区的数量。
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, store_id INT ) PARTITION BY HASH(store_id) PARTITIONS 4;
LINER HASH
MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY”子句中添加“LINEAR”关键字。
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, store_id INT ) PARTITION BY LINEAR HASH(YEAR(hired)) PARTITIONS 4;
KEY分区
类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
CREATE TABLE tk ( col1 INT NOT NULL, col2 CHAR(5), col3 DATE ) PARTITION BY LINEAR KEY (col1) PARTITIONS 3;
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。
到此这篇关于深入理解MySQL分区表的使用的文章就介绍到这了,更多相关MySQL 分区表内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友!
您可能感兴趣的文章:- MySQL分区表的详细介绍
- MySQL分区表分区策略详解
- MySQL性能优化之分区表的使用详解
- MySQL分区表和分桶表的操作详解
- MySQL分区表管理命令汇总
- MySQL对数据表已有表进行分区表的实现
- Mysql临时表及分区表区别详解
- 详解MySQL分区表
- MySQL最佳实践之分区表基本类型
- MySQL分区表的基本入门教程