三十六、MySQL基础系列笔记之MySQL分区(一)LIST/RANGE/COLUMNS

作者: 温新

分类: 【MySql】

阅读: 2523

时间: 2021-01-14 16:00:47

什么是MySQL分区与分表

分区：分区是指根据一定的规则把一个表分解成多个更小的、更容易管理的区块。

分表：把一个冗余在单库中的单个表拆分为多个与之相关的逻辑业务子表，如用户表与用户信息表，每一个表都有.myd数据文件、.myi索引文件、.frm表结构文件。

分库：将存储在单个物理存储区域的数据库分布到多个物理存储区域。

为什么使用分区

分区有利于管理非常大的表，采用'分而治之'的逻辑。

分区采用了分区键(PartitionKey)的概念，分区键用于根据某个区间值、特定值列表或HASH函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成一些小对象。

MySQL分区的优点：存储更多数据、优化查询、删除数据、查询

  • (存储更多数据)与单个磁盘文件或文件系统分区相比，分区可以存储更多数据；
  • (优化查询)分区利于优化查询。如where子句查询中包含分区条件时，可只扫描必要的一个或多个分区表来提高查询效率；
  • (删除数据)对于已经过期或不需要保存数据，可通过删除与这些数据有关的分区来快速删除数据；
  • (查询)跨多个磁盘来分散数据查询，以获得更到的查询吞吐量。

分区类型

  • RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区；
  • LIST分区：基于枚举出的值列表分区；
  • COLUMNS分区：既可以多列以可以是非整数；
  • HASH分区：基于给定的分区个数，把数据取模分配到不同的分区；
  • KEY分区：类似于HASH分区，但使用MySQL提供的哈希函数；
  • 子分区：也叫复合分区或组合分区，即在主分区下再做一层分区，将数据再次分割。

查看是否开启分区

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> * <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">from</span> information_schema<span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">.plugins</span> <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">\G</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">           PLUGIN_NAME: mysqlx</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">        PLUGIN_VERSION: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.0</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">         PLUGIN_STATUS: ACTIVE</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">           PLUGIN_TYPE: DAEMON</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">   PLUGIN_TYPE_VERSION: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">80020.0</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">        PLUGIN_LIBRARY: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">NULL</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">PLUGIN_LIBRARY_VERSION: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">NULL</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">         PLUGIN_AUTHOR: Oracle Corporation</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    PLUGIN_DESCRIPTION: X <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Plugin</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">for</span> MySQL</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">        PLUGIN_LICENSE: GPL</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">           LOAD_OPTION: <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">ON</span></span>

若PLUGIN_STATUS为ACTIVE则说明开启了分区。

MySQL中MERGE、CSV、FEDERATED这三种引擎不支持创建分区表。同一个分区表的所有分区必须使用同一个存储引擎且分区数量不超过8192个。

使用分区

RAGGE分区

RANGE分区的表是利用取值范围将数据分成分区且是不可重叠的连续区间。

使用VALUES LESS THAN操作符进行分区定义。

案例：创建员工表并按照商店ID(store_id)进行range分区。

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`emp`</span>(</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> id <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">not</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">null</span>,</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    name <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">varchar</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">30</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    store_id <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">not</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">null</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">by</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">range</span> (store_id) (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p0 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">15</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p1 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">35</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p2 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">55</span>)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">);</span>

释义：

1-14的员工将插入到p0;

15-34的员工将插入到p1;

35-54的员工将插入到p2;

若>=55则报错。

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> emp (id,name,store_id) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'lisi'</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># store_id>=55，则报错</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> emp (id,name,store_id) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'lisi'</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">55</span>);  </span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">ERROR <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1526</span> (HY000): <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Table</span> has <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">no</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">for</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">value</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">55</span></span>

VALUES LESS THAN MAXVALUE解决插入大于分区_storeid报错问题

可以看到，插入store_id=55的员工，报错了。使用VALUES LESS THAN MAXVALUE设置大于指定的最高值，下面为表添加新的分区：

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">alter</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> emp <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">add</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> (<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p3 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">maxvalue</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 重新插入</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> emp (id,name,store_id) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'lisi'</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">55</span>); </span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> affected (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>
RANGE分区适用的情况
  • 删除过期数据。使用alter table emp drop partition 分区来删除对应分区的数据。如对于有百万记录的表来说，删除分区远比delete有效；
  • 经常运行包含分区键的查询。如查询sore_id=2的数据，mysql不会全表扫描，而是直接到p0分区中去扫描查询。

List分区

List分区是建立在离散的值列表告诉数据库特定的值属于哪个分区。

RANGE与List的区别：List分区属于一个枚举列表的值的集合；RANGE分区是从属于一个连续区间的值的集合。

使用

List分区使用PARTITION BY LIST(EXPR)子句来实现；使用PARTITION BY LIST COLUMUNS创建非整数分区。

EXPR条件：EXPR是某列值或一个基于某列值返回一个整数的表达式，然后通过VALUES IN(value_list)的方式来定义分区；

value_list是一个使用逗号分隔的整数列表。

如下案例:

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo_art(</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> id <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">not</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">null</span>,</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    arttime <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">date</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">not</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">null</span>,</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    cate_id <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">by</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">list</span>(cate_id) (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p0 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">10</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p1 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">5</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">9</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p2 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">);</span>

注意其格式为

<span style="color: rgb(0, 176, 80);">create table 表名 () partition by list(条件)(分区值1,...);</span><br></br>

案例结果

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 插入cate_id为1的值，在分区的范围中，插入成功</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> demo_art <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span>,now(),<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> affected, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> warning (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.01</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 插入cate_id为3的值没有在分区中列表，因此插入失败</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> demo_art <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span>,now(),<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">3</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">ERROR <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1526</span> (HY000): <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Table</span> has <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">no</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">for</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">value</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">3</span></span>

注意：values in (1,10)指的并不是区间范围，而是这两个值。

COLUMNS分区

COLUMNS是为了解决RANGE与LIST分区只支持整数的分区而引入的分区类型。COLUMNS可以细分为RANGE COLUMNS和LIST COLUMNS分区，两者都支持整数、日期时间、字符串三大类型数据。

COLUMNS支持的类型如下 ：

整数类型：tinyint、smallint、mediumint、int、bigint

日期日间类型：date、datetime

字符串类型：char、varchar、binary、varbinary

我是小白，期待和优秀的你一起同行!

小白

2021年01月15日

请登录后再评论