mysql数据表分区

分手后的思念是犯贱 2021-09-21 23:52 513阅读 0赞

**一、前沿知识**  
1、分区表类型  
RANGE分区、LIST分区、HASH分区、KEY分区、子分区

3、MySQL分区处理NULL值的方式  
MySQL中的分区在禁止空值（NULL）上没有进行处理，如果出现NULL，MySQL 把NULL视为0

**二、分区表的创建**  
1、RANGE分区

<table> 
 <tbody> 
 <tr> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>employees (</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id </code><code>INT</code> <code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>fname </code><code>VARCHAR</code><code>(30),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lname </code><code>VARCHAR</code><code>(30),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>hired </code><code>DATE</code> <code>NOT</code> <code>NULL</code> <code>DEFAULT</code> <code>'1970-01-01'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>separated </code><code>DATE</code> <code>NOT</code> <code>NULL</code> <code>DEFAULT</code> <code>'9999-12-31'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>job_code </code><code>INT</code> <code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>store_id </code><code>INT</code> <code>NOT</code> <code>NULL</code> <code>)</code> <code>PARTITION </code><code>BY</code> <code>RANGE (store_id) (</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION p0 </code><code>VALUES</code> <code>LESS THAN (6),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION p1 </code><code>VALUES</code> <code>LESS THAN (11),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION p2 </code><code>VALUES</code> <code>LESS THAN (16),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION p3 </code><code>VALUES</code> <code>LESS THAN MAXVALUE</code> <code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

2、 LIST分区

<table> 
 <tbody> 
 <tr> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>employees (</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id </code><code>INT</code> <code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>fname </code><code>VARCHAR</code><code>(30),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lname </code><code>VARCHAR</code><code>(30),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>hired </code><code>DATE</code> <code>NOT</code> <code>NULL</code> <code>DEFAULT</code> <code>'1970-01-01'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>separated </code><code>DATE</code> <code>NOT</code> <code>NULL</code> <code>DEFAULT</code> <code>'9999-12-31'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>job_code </code><code>INT</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>store_id </code><code>INT</code> <code>)</code> <code>PARTITION </code><code>BY</code> <code>LIST(store_id)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION pNorth </code><code>VALUES</code> <code>IN</code> <code>(3,5,6,9,17),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION pEast </code><code>VALUES</code> <code>IN</code> <code>(1,2,10,11,19,20),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION pWest </code><code>VALUES</code> <code>IN</code> <code>(4,12,13,14,18),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION pCentral </code><code>VALUES</code> <code>IN</code> <code>(7,8,15,16)</code> <code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

Note：LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

3、 HASH分区

3.1、LINEAR HASH分区

Note：线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数

4、KEY分区

<table> 
 <tbody> 
 <tr> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>tk (</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>col1 </code><code>INT</code> <code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>col2 </code><code>CHAR</code><code>(5),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>col3 </code><code>DATE</code> <code>) </code> <code>PARTITION </code><code>BY</code> <code>LINEAR </code><code>KEY</code> <code>(col1)</code> <code>PARTITIONS 3;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

2）“CREATE TABLE … PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

5、子分区

<table> 
 <tbody> 
 <tr> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>ts (id </code><code>INT</code><code>, purchased </code><code>DATE</code><code>)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION </code><code>BY</code> <code>RANGE(</code><code>YEAR</code><code>(purchased))</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>SUBPARTITION </code><code>BY</code> <code>HASH(TO_DAYS(purchased))</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION p0 </code><code>VALUES</code> <code>LESS THAN (1990)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>SUBPARTITION s0 </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>DATA DIRECTORY = </code><code>'/disk0/data'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INDEX</code> <code>DIRECTORY = </code><code>'/disk0/idx'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>SUBPARTITION s1 </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>DATA DIRECTORY = </code><code>'/disk1/data'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INDEX</code> <code>DIRECTORY = </code><code>'/disk1/idx'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION p1 </code><code>VALUES</code> <code>LESS THAN (2000)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>SUBPARTITION s0 </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>DATA DIRECTORY = </code><code>'/disk2/data'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INDEX</code> <code>DIRECTORY = </code><code>'/disk2/idx'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>SUBPARTITION s1 </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>DATA DIRECTORY = </code><code>'/disk3/data'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INDEX</code> <code>DIRECTORY = </code><code>'/disk3/idx'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>PARTITION p2 </code><code>VALUES</code> <code>LESS THAN MAXVALUE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>SUBPARTITION s0 </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>DATA DIRECTORY = </code><code>'/disk4/data'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INDEX</code> <code>DIRECTORY = </code><code>'/disk4/idx'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>SUBPARTITION s1 </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>DATA DIRECTORY = </code><code>'/disk5/data'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INDEX</code> <code>DIRECTORY = </code><code>'/disk5/idx'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

Note:子分区是分区表中每个分区的再次分割，DATA DIRECTORY/INDEX DIRECTORY确定数据和索引的存储位置

*  [oracle 建立分区表][oracle]
 *  [Oracle分区表管理][Oracle]
 *  [MySql分区表管理][MySql]
 *  [dul处理分区表][dul]
 *  [分区默认segment大小变化(64k—>8M)][segment_64k_8M]
 *  [创建sql server分区表][sql server]
 *  [DBMS\_STATS比较复杂参数][DBMS_STATS]
 *  [ORACLE 12C 支持multiple partitions同时操作][ORACLE 12C _multiple partitions]
 *  [Mysql Merge表][Mysql Merge]
 *  [ORACEL坏查询对象批量脚本][ORACEL]
 *  [\[MySQL异常恢复\]mysql ibd文件恢复][MySQL_mysql ibd]
 *  [oracle之数据导入][oracle 1]

[oracle]: https://www.xifenfei.com/2010/10/oracle-%E5%BB%BA%E7%AB%8B%E5%88%86%E5%8C%BA%E8%A1%A8.html
[Oracle]: https://www.xifenfei.com/2011/04/oracle%E5%88%86%E5%8C%BA%E8%A1%A8%E7%AE%A1%E7%90%86.html
[MySql]: https://www.xifenfei.com/2011/05/mysql%E5%88%86%E5%8C%BA%E7%AE%A1%E7%90%86.html
[dul]: https://www.xifenfei.com/2013/02/dul%E5%A4%84%E7%90%86%E5%88%86%E5%8C%BA%E8%A1%A8.html
[segment_64k_8M]: https://www.xifenfei.com/2013/08/%E5%88%86%E5%8C%BA%E9%BB%98%E8%AE%A4segment%E5%A4%A7%E5%B0%8F%E5%8F%98%E5%8C%9664k-8m.html
[sql server]: https://www.xifenfei.com/2011/04/%E5%88%9B%E5%BB%BAsql-server%E5%88%86%E5%8C%BA%E8%A1%A8.html
[DBMS_STATS]: https://www.xifenfei.com/2011/09/dbms_stats%E6%AF%94%E8%BE%83%E5%A4%8D%E6%9D%82%E5%8F%82%E6%95%B0.html
[ORACLE 12C _multiple partitions]: https://www.xifenfei.com/2012/12/oracle-12c-%E6%94%AF%E6%8C%81multiple-partitions%E5%90%8C%E6%97%B6%E6%93%8D%E4%BD%9C.html
[Mysql Merge]: https://www.xifenfei.com/2011/07/mysql-merge%E8%A1%A8.html
[ORACEL]: https://www.xifenfei.com/2014/09/oracel%E5%9D%8F%E6%9F%A5%E8%AF%A2%E5%AF%B9%E8%B1%A1%E6%89%B9%E9%87%8F%E8%84%9A%E6%9C%AC.html
[MySQL_mysql ibd]: https://www.xifenfei.com/2016/12/mysql-ibd-recovery.html
[oracle 1]: https://www.xifenfei.com/2010/08/oracle%E4%B9%8B%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%85%A5.html