三十七、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日