三十七、MySQL基础系列笔记之MySQL分区(二)HASH/KEY/子分区

HASH分区

HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。

MySQL支持两种HASH分区，即常规HASH分区和线性HASH分区（LINEAR HASH）。常规HASH使用的是取模算法；线性HASH分区使用的是一个线性的2的幂的运算法则。

常规HASH使用

常规HASH使用PARTITION BY HASH(EXPR) PARTITIONS num子句对分区类型、分区键和分区个数进行定义；使用PARTITION BY LINEAR创建线性HASH分区。

EXPR条件为 某列值或一个基于某列值返回一个整数值的表达式；

num是一个非负的整数，表示分割成分区的数量，默认为1.

使用常规HASH分区案例

<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"># 为demo_emp表使用store_id创建4个hash分区</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">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo_emp(</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">20</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">hash</span>(store_id) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partitions</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</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> demo_emp <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">567</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.01</span> sec)</span>

计算保存的分区位置：如store_id=567，select它会被保存到哪个分区？n=mod(234,4)的结果就对应保存的存储。4代表partions所创建的4个分区。

线性HASH使用与计算

线性HASH使用的是partition by linear。

<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"># 线性HASH</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">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo_emp(</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">20</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">hash</span>(store_id) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partitions</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span>;</span>

计算线性HASH分区：假设保存记录的分区编号为N，num为非负整数(表示分割成分区的数量)，那么分区编号N通过如下步骤得到：

步骤一：计算一个大于等于num的2的幂，其值可以通过公式V=Power(2,Ceiling(Log(2,num)))得到。

如：创建的表中设置了4个分区，即num=4,下面计算num的2的幂。

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">V = power(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>, ceiling(log(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>,num)))</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> = power(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>,ceiling(log(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span>)))</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> = power(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>,ceiling(<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"> = power(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</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(209, 154, 102) !important">4</span></span>

步骤二：计算分区：公式 N=F(column_list) & (V-1)

如：可知我们在步骤一时计算出了V=4，现在计算sotre_id=567对应的N值。

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">N = F(column_list) & (V-<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(209, 154, 102) !important">567</span> & (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span>-<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(209, 154, 102) !important">3</span></span>

步骤二计算出了store_id=567所对应存储的分区，若是计算出store_id>=4(即设置的分区时)，需要通过步骤三来计算存储入哪个分区

步骤三：当 N >= num时，设置V=ceiling(V/2)，设置N=N & (V-1)

由于 store_id=567，即N=3<4,因此该记录会被存储第三个分区中。

需要知道的是，当线性HASH的分区个数是2的N次幂时，线性HASH分区的结果与常规HASH分区的结果是一致的。

线性分区的优点：对分区维护(增、删、合并、拆分分区)处理时，速度更快；缺点点，对比常规的HASH分区（取模）时，线性各个分区之间数据的分布不太均衡。

KEY分区

key分区使用mysql服务器提供的hash函数；key分区支持使用除blob和text外其他类型的列作为分区键。

key分区使用partition by key(expr)子句来创建一个key分区表；expr是0个或多个字段名的列表。

案例：创建一个基于job字段分区的表

<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_emp02(</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">20</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">    job <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">20</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">key</span>(job) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partitions</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</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">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_emp02 <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(152, 195, 121) !important">'it'</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.01</span> sec)</span>

key分区有如下几种情况：

1）key分区可以不指定分区键，默认使用主键作为分区键。

<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> demo01(</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 style="box-sizing: border-box;color: rgb(198, 120, 221) !important">primary</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">key</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">20</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">key</span>() <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partitions</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span>;</span>

2）没有主键时，则选择非空唯一键作为分区键。

<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> demo02(</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">20</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">unique</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">key</span> (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> <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">key</span>() <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partitions</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span>;</span>

3）若无主键又无唯一键，则必须指定分区键，否则报错。

<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> demo03(</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><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">20</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">key</span>() <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partitions</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span>;</span>

子分区

子分区（Subpartitioning）是分区表中对每个分区的再次分割，又被称为复合分区（Composite Partitioning）。

MySQL5.7之后可以通过对RANGE或LIST分区表再次进行子分区。子分区既可以使用HASH分区，也可以使用KEY分区。

子分区语法：

<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><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>(分区字段)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">subpartition <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">hash</span>(分区字段)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">subpartitions num (</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">values</span> less than (<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">value</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">values</span> less than (<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">value</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">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>

释义：

分区字段：表示要按照哪个字段进行分区，可以是一个字段名也可以是对某个字段进行表达式运算，如year(create_time);

分区名称：要保证分区名称的不同；

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">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo_emp03(</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><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    create_time <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">date</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>(<span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">year</span>(create_time))</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">subpartition <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">hash</span>(to_days(create_time))</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">subpartitions <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><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">1970</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">2010</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(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>

表demo_emp03有3个range分区p0、p1、p2，这三个range分区又被分割成了2个子分区。实际情况是整张表被分成了6个分区。

MySQL分区对NULL值的处理

MySQL允许在分区键中使用NULL,分区键可以是一个字段也可以是一个表达式。一般情况下，MySQL的分区把NULL当作0值或一个最小值处理。

需要注意的是：RANGE分区中，null值被当作最小值处理；LIST分区中，null值必须出现在枚举列表中，否则不被接受；HASH/KEY分区中，null值被当作0值处理。

<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> demo01(</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><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">5</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>(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">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> less than(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</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(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><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">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> demo01 <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">null</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !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">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.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"></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"># 确认null是否被当作最小值处理</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">select</span> partition_name part,partition_expression expr,partition_description descr,table_rows </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">from</span> information_schema<span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">.partitions</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">where</span> table_schema=<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">schema</span>() <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">and</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table_name</span>=<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'demo01'</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+------+------+----------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| part | expr | descr    | TABLE_ROWS |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+------+------+----------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p0   | <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`id`</span> | -<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">10</span>      |          <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">| p1   | <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`id`</span> | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span>        |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p2   | <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`id`</span> | <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">MAXVALUE</span> |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+------+------+----------+------------+</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(209, 154, 102) !important">3</span> rows <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

期待和优秀的你一起同行!

2021年01月16日