文档章节

MySQL分区表

BearCatYN
 BearCatYN
发布于 2015/11/26 14:26
字数 2230
阅读 170
收藏 11

当数据库数据量涨到一定数量时,性能就成为我们不能不关注的问题,如何优化呢? 常用的方式不外乎那么几种:

  1、分表,即把一个很大的表达数据分到几个表中,这样每个表数据都不多。 

    优点:提高并发量,减小锁的粒度

    缺点:代码维护成本高,相关sql都需要改动

  2、分区,所有的数据还在一个表中,但物理存储数据根据一定的规则存放在不同的文件中,文件也可以放到另外磁盘上

    优点:代码维护量小,基本不用改动,提高IO吞吐量

    缺点:表的并发程度没有增加

  3、拆分业务,这个本质还是分表。

    优点:长期支持更好

    缺点:代码逻辑重构,工作量很大

  当然,每种情况都有合适的应用场景,需要根据具体业务具体选择。由于分表和拆分业务和mysql本身关系不大属于业务层面,我们只说和数据库关系最紧密的方式:表分区。不过使用表分区有个前提就是你的数据库必须支持。那么,怎么知道我的数据库是否支持表分区呢 ? 请执行下面命令 

show plugins;  ---在mysql控制台中执行

据说5.4一下的版本是另外一个命令,不过我没有测试

 show variables like '%part%';

   数据库的表分区一般有两种方式:纵向和横向。纵向就是把表中不同字段分到不同数据文件中。横向是把表中前一部分数据放到一个文件中,另一部分数据放到一个文件中。mysql只支持后后一种方式,横向拆分。

1、创建分区表

   如果要使用表的分区优势,不但要数据库版本支持分区,关键要建分区表,这个表和普通表不一样,并且必须建表的时候就要指定分区,否则无法把普通表改成分区表。那么,如果创建一个分区表呢? 其他很简单,请看下面建表语句

CREATE TABLE `T_part` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY RANGE (f_id)(        -----指定分区方式
    PARTITION p0  VALUES less THAN (10),-- 分了两个区
    PARTITION p1  VALUES less THAN (20)
)

  上面语句建了一个“T_part”表,有两个字段f_id和f_name,并且根据RANGE方式把表分成两个区p0、p1,当f_id小于10放入p0分区,当f_id大于0小于20放入分区p1. 那么当f_id大于20的数据放入哪个分区呢? 你猜对了,insert语句会报错。

  看到了吧,创建分区表就这么简单!当然,你随时可以添加删除分区,不过要注意,删除分区的时候会把当前分区下所有数据都删除。

alter table T_part add partition(partition p2 values less than (MAXVALUE));  ---新增分区alter table T_part DROP partition p2; ----删除分区

2、表分区的几种方式

   mysql支持5种分区方式:RANGE分区、LIST分区、HASH分区、LINEAR HASH分区和KEY分区。每种分区都有自己的使用场景。

  1)RANGE分区:

    RANGE分区的表是通过如下一种方式进行分区的,每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。

    上面的例子就是RANGE分区.

  2)LIST分区:

    MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样,每个分区必须明确定义。它们的主要区别在于,LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值,而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。

CREATE TABLE `T_list` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION by list(f_id)
(
  PARTITION p0 VALUES in(1,2,3),  ----区间值不能重复
  PARTITION p1 VALUES in(4,5,6)
);

  3)HASH分区:

    HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中,MySQL 自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。

CREATE TABLE `T_hash` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY HASH(f_id) ---可以指定多列
PARTITIONS 4;---分区个数

    “expr”还可以是MySQL 中有效的任何函数或其他表达式,只要它们返回一个既非常数、也非随机数的整数。(换句话说,它既是变化的但又是确定的)。但是应当记住,每当插入或更新(或者可能删除)一行,这个表达式都要计算一次;这意味着非常复杂的表达式可能会引起性能问题,尤其是在执行同时影响大量行的运算(例如批量插入)的时候。最有效率的哈希函数是只对单个表列进行计算,并且它的值随列值进行一致地增大或减小,因为这考虑了在分区范围上的“修剪”。也就是说,表达式值和它所基于的列的值变化越接近,MySQL就可以越有效地使用该表达式来进行HASH分区。

  4)LINEAR HASH分区:

    MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-oftwo)运算法则,而常规 哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY” 子句中添加“LINEAR”关键字.

  5)KEY分区:

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

    KEY分区的语法和HASH语法类似,只是把关键字改成KEY。  

CREATE TABLE `T_key` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY LINEAR key(f_id)
PARTITIONS 3;

6)子分区:

    子分区的意思就是在分区的基础上再次分区。且每个分区必须有相同个数的子分区。

CREATE TABLE `T_part` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(F_ID)
SUBPARTITIONS 2
(
    PARTITION p0     VALUES         less THAN (10),
    PARTITION p1    VALUES        less THAN (20)
)

    上面语句的意思是,建立两个range分区,每个分区根据hash有分别有两个子分区,实际上整个表分成2×2=4个分区。当然,要详细定义每个分区属性也是可以的

CREATE TABLE `T_part` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(F_ID)
(
    PARTITION p0     VALUES less THAN (10)
    (
        SUBPARTITION s0 
            DATA DIRECTORY = '/disk0/data' 
            INDEX DIRECTORY = '/disk0/idx',
        SUBPARTITION s1 
            DATA DIRECTORY = '/disk1/data' 
            INDEX DIRECTORY = '/disk1/idx'
    ),
    PARTITION p1    VALUES less THAN (20)
    (
        SUBPARTITION s2
            DATA DIRECTORY = '/disk0/data' 
            INDEX DIRECTORY = '/disk0/idx',
        SUBPARTITION s3 
            DATA DIRECTORY = '/disk1/data' 
            INDEX DIRECTORY = '/disk1/idx'
    )
)

    这样可以对每个分区指定具体存储磁盘。前提磁盘是存在的。

  

  MySQL 中的分区在禁止空值(NULL)上没有进行处理,无论它是一个列值还是一个用户定义表达式的值。一般而言,在这种情况下MySQL 把NULL视为0。如果你希望回避这种做法,你应该在设计表时不允许空值;最可能的方法是,通过声明列“NOT NULL”来实现这一点。


本文转载自:http://www.cnblogs.com/freeton/p/4265228.html

BearCatYN
粉丝 26
博文 158
码字总数 11947
作品 0
朝阳
程序员
私信 提问
MySQL下的表分区简述

MySQL表分区就是把一张表根据设定好的条件下把表切分成若干个小表相互之间,在MySQL的5.1版本以后就开始支持表分区的功能,在使用表的分区后会使MySQL中大表在平时查询统计时性能提升。使用M...

往事_Jim_遗
2017/11/10
0
0
MySQL学习——分区表

MySQL存储引擎现状及发展趋势 MySQL官网有这样一句话: As of MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is removed in MySQL 8.0, when th......

沈渊
2017/09/03
0
0
MySQL表分区优势及分类简析 MySQL DBA学习

表分区是将一个表的数据按照一定规则水平划分成不同的逻辑块,并分别进行物理存储,这个规则就叫做分区函数,可以有不同的分区规则。通过show plugins语句查看当前MySQL是否支持表分区功能。...

mo默瑶
2018/05/24
0
0
mysql分表,分区的区别和联系

一,什么是mysql分表,分区 什么是分表,从表面意思上看呢,就是把一张表分成N多个小表,具体请看mysql分表的3种方法 什么是分区,分区呢就是把一张表的数据分成N多个区块,这些区块可以在同...

haorizi
2013/03/11
200
1
mysql分表,分区的区别和联系

mysql分表,分区的区别和联系 一,什么是mysql分表,分区 什么是分表,从表面意思上看呢,就是把一张表分成N多个小表,具体请看mysql分表的3种方法 什么是分区,分区呢就是把一张表的数据分成...

henosteven
2012/08/27
2.6K
0

没有更多内容

加载失败,请刷新页面

加载更多

一、docker 入坑(win10和Ubuntu 安装)

前言 终究还是绕不过去了,要学的知识真的是太多了,好在我们还有时间,docker 之前只闻其声,不曾真正的接触过,现在docker 越来越火,很多公司也都开始使用了。所以对于我们程序员而言,又...

quellanan2
27分钟前
4
0
AutoCompleteTextView

小技巧按菜单键 当菜单打开之前会调用onMenuOpened(int featereId,Menu menu),可以重写这个方法,弹出对话框或者Popmenu 再布局中添加控件AutoCompleteTextView. <AutoCompleteTextVie...

逆天游云
31分钟前
4
0
谷歌软件商店:推出5美元会员 可用数百个软件

腾讯科技讯,谷歌和苹果是全球两大智能手机操作系统的运营者,两家公司旗下分别拥有占据行业垄断地位的谷歌软件商店和苹果软件商店。据外媒最新消息,手机软件商店的商业模式正在发生一些变化...

linuxCool
53分钟前
3
0
RocketMQ 多副本前置篇:初探raft协议

Raft协议是分布式领域解决一致性的又一著名协议,主要包含Leader选举、日志复制两个部分。 温馨提示: 本文根据raft官方给出的raft动画进行学习,其动画展示地址:http://thesecretlivesofda...

中间件兴趣圈
53分钟前
3
0
elasticsearch 6.8.0 添加认证

1. 修改elasticsearch-6.8.0/config/elasticsearch.yml 最后添加一行:xpack.security.enabled: true 2. 初始化用户和密码 ./bin/elasticsearch-setup-passwords interactive 我这里初始化为......

coord
55分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部