欢迎投稿

今日深度:

深入理解MySQL分区表的使用,

深入理解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分区表的基本入门教程

www.htsjk.Com true http://www.htsjk.com/Mysql/47820.html NewsArticle 深入理解MySQL分区表的使用, 目录 前言 什么需要分区表 分区的策略 分区表应用场景 分区表的限制 分区类型 RANGE分区 LIST分区 HASH分区 LINER HASH KEY分区 前言 当我们单表数据量比较大的...
评论暂时关闭