mysql分区的二，mysql分区类型

mysql分区的二，mysql分区类型

什么是分表，从表面意思上看呢，就是把一张表分成N多个小表，具体请看mysql分表的3种方法

什么是分区，分区呢就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，具体请参考mysql分区功能详细介绍，以及实例

MySQL 55的发布带来了许多增强的功能，虽然已经报道了很多增强功能，如半同步复制，但大家却忽略了分区方面的增强，有时甚至还对其真正意义产生了误解，在这篇文章中，我们希望解释一下这些很酷的增强，特别是我们大多数人还没有完全理解的地方。51CTO向您推荐《MySQL数据库入门与精通教程》。

非整数列分区

任何使用过分区的人应该都遇到过不少问题，特别是面对非整数列分区时，MySQL 51只能处理整数列分区，如果你想在日期或字符串列上进行分区，你不得不使用函数对其进行转换。

MySQL 55中新增了两类分区方法，RANG和LIST分区法，同时在新的函数中增加了一个COLUMNS关键词。我们假设有这样一个表：

1  CREATE TABLE expenses (  

2    expense_date DATE NOT NULL,  

3    category VARCHAR(30),  

4    amount DECIMAL (10,3)  

5  ); 

如果你想使用MySQL 51中的分区类型，那你必须将类型转换成整数，需要使用一个额外的查找表，到了MySQL 55中，你可以不用再进行类型转换了，如：

1  ALTER TABLE expenses  

2  PARTITION BY LIST COLUMNS (category)  

3  (  

4    PARTITION p01 VALUES IN ( 'lodging', 'food'),  

5    PARTITION p02 VALUES IN ( 'flights', 'ground transportation'),  

6    PARTITION p03 VALUES IN ( 'leisure', 'customer entertainment'),  

7    PARTITION p04 VALUES IN ( 'communications'),  

8    PARTITION p05 VALUES IN ( 'fees')  

9  ); 

这样的分区语句除了更加易读外，对数据的组织和管理也非常清晰，上面的例子只对category列进行分区。

在MySQL 51中使用分区另一个让人头痛的问题是date类型（即日期列），你不能直接使用它们，必须使用YEAR或TO_DAYS转换这些列，如：

1  / 在MySQL 51中/  

2  CREATE TABLE t2  

3  (  

4    dt DATE 

5  )  

6  PARTITION BY RANGE (TO_DAYS(dt))  

7  (  

8    PARTITION p01 VALUES LESS THAN (TO_DAYS('2007-01-01')),  

9    PARTITION p02 VALUES LESS THAN (TO_DAYS('2008-01-01')),  

10   PARTITION p03 VALUES LESS THAN (TO_DAYS('2009-01-01')),  

11   PARTITION p04 VALUES LESS THAN (MAXVALUE));  

12  

13 SHOW CREATE TABLE t2 \G  

14  1 row   

15        Table: t2  

16 Create Table: CREATE TABLE `t2` (  

17   `dt` date DEFAULT NULL 

18 ) ENGINE=MyISAM DEFAULT CHARSET=latin1  

19 /!50100 PARTITION BY RANGE (TO_DAYS(dt))  

20 (PARTITION p01 VALUES LESS THAN (733042) ENGINE = MyISAM,  

21  PARTITION p02 VALUES LESS THAN (733407) ENGINE = MyISAM,  

22  PARTITION p03 VALUES LESS THAN (733773) ENGINE = MyISAM,  

23  PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) / 

看上去非常糟糕，当然也有变通办法，但麻烦确实不少。使用YEAR或TO_DAYS定义一个分区的确让人费解，查询时不得不使用赤裸列，因为加了函数的查询不能识别分区。

但在MySQL 55中情况发生了很大的变化，现在在日期列上可以直接分区，并且方法也很简单。

1  /在MySQL 55中/  

2  CREATE TABLE t2  

3  (  

4    dt DATE 

5  )  

6  PARTITION BY RANGE COLUMNS (dt)  

7  (  

8    PARTITION p01 VALUES LESS THAN ('2007-01-01'),  

9    PARTITION p02 VALUES LESS THAN ('2008-01-01'),  

10   PARTITION p03 VALUES LESS THAN ('2009-01-01'),  

11   PARTITION p04 VALUES LESS THAN (MAXVALUE));  

12  

13 SHOW CREATE TABLE t2 \G  

14  1 row   

15        Table: t2  

16 Create Table: CREATE TABLE `t2` (  

17   `dt` date DEFAULT NULL 

18 ) ENGINE=MyISAM DEFAULT CHARSET=latin1  

19 /!50500 PARTITION BY RANGE  COLUMNS(dt)  

20 (PARTITION p01 VALUES LESS THAN ('2007-01-01') ENGINE = MyISAM,  

21  PARTITION p02 VALUES LESS THAN ('2008-01-01') ENGINE = MyISAM,  

22  PARTITION p03 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM,  

23  PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) / 

在这里，通过函数定义和通过列查询之间没有冲突，因为是按列定义的，我们在定义中插入的值是保留的。

多列分区

COLUMNS关键字现在允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区，你可能在官方文档中已经看到了一些例子，如： 

1  CREATE TABLE p1 (  

2    a INT,  

3    b INT,  

4    c INT 

5  )  

6  PARTITION BY RANGE COLUMNS (a,b)  

7  (  

8    PARTITION p01 VALUES LESS THAN (10,20),  

9    PARTITION p02 VALUES LESS THAN (20,30),  

10   PARTITION p03 VALUES LESS THAN (30,40),  

11   PARTITION p04 VALUES LESS THAN (40,MAXVALUE),  

12   PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)  

13 );  

14  

15 CREATE TABLE p2 (  

16   a INT,  

17   b INT,  

18   c INT 

19 )  

20 PARTITION BY RANGE COLUMNS (a,b)  

21 (  

22   PARTITION p01 VALUES LESS THAN (10,10),  

23   PARTITION p02 VALUES LESS THAN (10,20),  

24   PARTITION p03 VALUES LESS THAN (10,30),  

25   PARTITION p04 VALUES LESS THAN (10,MAXVALUE),  

26   PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)  

27 ) 

同样还有PARTITION BY RANGE COLUMNS (a,b,c)等其它例子。由于我很长时间都在使用MySQL 51的分区，我对多列分区的含义不太了解，LESS THAN (10,10)是什么意思？如果下一个分区是LESS THAN (10,20)会发生什么？相反，如果是(20,30)又会如何？

所有这些问题都需要一个答案，在回答之前，他们需要更好地理解我们在做什么。

开始时可能有些混乱，当所有分区有一个不同范围的值时，实际上，它只是在表的一个列上进行了分区，但事实并非如此，在下面的例子中：

1  CREATE TABLE p1_single (  

2    a INT,  

3    b INT,  

4    c INT 

5  )  

6  PARTITION BY RANGE COLUMNS (a)  

7  (  

8    PARTITION p01 VALUES LESS THAN (10),  

9    PARTITION p02 VALUES LESS THAN (20),  

10   PARTITION p03 VALUES LESS THAN (30),  

11   PARTITION p04 VALUES LESS THAN (40),  

12   PARTITION p05 VALUES LESS THAN (MAXVALUE)  

13 ); 

它和前面的表p1不一样，如果你在表p1中插入（10,1,1），它将会进入第一个分区，相反，在表p1_single中，它将会进入第二个分区，其原因是(10,1)小于(10,10)，如果你仅仅关注第一个值，你还没有意识到你在比较一个元组，而不是一个单一的值。

现在我们来分析一下最难懂的地方，当你需要确定某一行应该放在哪里时会发生什么？你是如何确定类似(10,9) < (10,10)这种运算的值的？答案其实很简单，当你对它们进行排序时，使用相同的方法计算两条记录的值。

1  a=10  

2  b=9  

3  (a,b) < (10,10)   

4   

5  # evaluates to:  

6   

7  (a < 10)  

8  OR 

9  ((a = 10) AND ( b < 10))  

10  

11 # which translates to:  

12  

13 (10 < 10)  

14 OR 

15 ((10 = 10) AND ( 9 < 10)) 

如果有三列，表达式会更长，但不会更复杂。你首先在第一个项目上测试小于运算，如果有两个或更多的分区与之匹配，接着就测试第二个项目，如果不止一个候选分区，那还需要测试第三个项目。

下图所显示的内容表示将遍历三条记录插入到使用以下代码定义的分区中：

(10,10),

(10,20),

(10,30),

(10, MAXVALUE)

图 2 元组比较。当第一个值小于分区定义的第一个范围时，那么该行将属于这里了。

图 3 元组比较。当第一个值等于分区定义的第一个范围，我们需要比较第二个项目，如果它小于第二个范围，那么该行将属于这里了。

图 4 元组比较。当第一个值和第二个值等于他们对应的范围时，如果元组不小于定义的范围，那么它就不属于这里，继续下一步。

图 5 元组比较。在下一个范围时，第一个项目是等于，第二个项目是小于，因此元组更小，那么该行就属于这里了。

在这些图的帮助下，我们对插入一条记录到多列分区表的步骤有了更深的了解，这些都是理论上的，为了帮助你更好地掌握新功能，我们再来看一个更高级一点的例子，对于比较务实的读者更有意义，下面是表的定义脚本：

1  CREATE TABLE employees (  

2    emp_no int(11) NOT NULL,  

3    birth_date date NOT NULL,  

4    first_name varchar(14) NOT NULL,  

5    last_name varchar(16) NOT NULL,  

6    gender char(1) DEFAULT NULL,  

7    hire_date date NOT NULL 

8  ) ENGINE=MyISAM  

9  PARTITION BY RANGE  COLUMNS(gender,hire_date)  

10 (PARTITION p01 VALUES LESS THAN ('F','1990-01-01') ,  

11  PARTITION p02 VALUES LESS THAN ('F','2000-01-01') ,  

12  PARTITION p03 VALUES LESS THAN ('F',MAXVALUE) ,  

13  PARTITION p04 VALUES LESS THAN ('M','1990-01-01') ,  

14  PARTITION p05 VALUES LESS THAN ('M','2000-01-01') ,  

15  PARTITION p06 VALUES LESS THAN ('M',MAXVALUE) ,  

16  PARTITION p07 VALUES LESS THAN (MAXVALUE,MAXVALUE) 

和上面的例子不同，这个例子更好理解，第一个分区用来存储雇佣于1990年以前的女职员，第二个分区存储股用于1990-2000年之间的女职员，第三个分区存储所有剩下的女职员。对于分区p04到p06，我们策略是一样的，只不过存储的是男职员。最后一个分区是控制情况。

看完后你可能要问，我怎么知道某一行存储在那个分区中的？有两个办法，第一个办法是使用与分区定义相同的条件作为查询条件进行查询。

*** 作步骤：

1、查看当前MySQL数据库是否支持分区；

2、判断当前数据库版本是否安装了分区插件；

3、创建数据库表并建立表分区，利用命令，结果发现报错；

4、修改创建数据库表主键设置，将其去掉，再次运行命令；

5、查看某张表是不是分区表；

6、查看具体数据库下的某张表的分区情况；

7、解决问题。

ql代码

#这里使用HASH表分区，mysql会根据HASH字段来自动分配数据到不同的表分区，这种情况适用于没有表分区规则但是有需要分表来进行查询优化的情况。这里根据id字段hash规则创建2个表分区

CREATE TABLE `creater_bak` (

`id` int(11) NOT NULL,

`name` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY HASH(id) PARTITIONS 2

创建完成后开始导入原表数据：

Sql代码

insert into creater_bak select from creater;

导入以后的新表数据就是分布在不同的2个表分区中了。

如果数据量非常大，觉得预设的表分区数量太少，那么可以新增表分区，mysql会自动重新分配：

Sql代码

#这里新增8个表分区，加上新建表时候的2个，一共10个表分区了

ALTER TABLE `creater_bak` ADD PARTITION PA

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，MYI索引文件，frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去 *** 作它。

随着业务的不断发展，数据库中的数据会越来越多，相应地，单表的数据量也会越到越大，大到一个临界值，单表的查询性能就会下降。

这个临界值，并不能一概而论，它与硬件能力、具体业务有关。

虽然在很多 MySQL 运维规范里，都建议单表不超过 500w、1000w。

但实际上，我在生产环境，也见过大小超过 2T，记录数过亿的表，同时，业务不受影响。

单表过大时，业务通常会考虑两种拆分方案：水平切分和垂直切分。

水平切分，拆分的维度是行，一般会根据某种规则或算法将表中的记录拆分到多张表中。

拆分后的表既可在一个实例，也可在多个不同实例中。如果是后者，又会涉及到分布式事务。

垂直切分，拆分的维度是列，一般是将列拆分到多个业务模块中。这种拆分更多的是上层业务的拆分。

从改造的复杂程度来说，前者小于后者。

所以，在单表数据量过大时，业界用得较多的还是水平拆分。

常见的水平拆分方案有：分库分表、分区表。

虽然分库分表是一个比较彻底的水平拆分方案，但一方面，它的改造需要一定的时间；另一方面，它对开发的能力也有一定的要求。相对来说，分区表就比较简单，也无需业务改造。

很多人可能会认为 MySQL 的优势在于 OLTP 应用，对于 OLAP 应用就不太适合，所以，也不太推荐分区表这种偏 OLAP 的特性。

但实际上，对于某些业务类型，还是比较适合使用分区表的，尤其是那些有明显冷热数据之分，且数据的冷热与时间相关的业务。

下面，我们看看分区表的优点：

遗憾的是，MySQL 分区表不支持并行查询。理论上，当一个查询涉及到多个分区时，分区与分区之间应进行并行查询，这样才能充分利用多核 CPU 资源。

但 MySQL 并不支持，包括早期的官方文档，也提到了这个问题，也将这个功能的实现放到了优先级列表中。

在 MySQL 57 中，对于分区表，有个很重大的更新，即 InnoDB 存储引擎原生支持了分区，无需再通过 ha_partition 接口来实现。

所以，在 MySQL 57 中，如果要创建基于 MyISAM 存储引擎的分区表，会提示 warning 。

而在 MySQL 80 中，则更为彻底，server 层移除了 ha_partition 接口代码。

如果要使用分区表，只能使用支持原生分区的存储引擎。在 MySQL 80 中，就只有 InnoDB。

这就意味着，在 MySQL 80 中，如果要创建 MyISAM 分区表，基本上就不可能了。

这也从另外一个角度说明了为什么生产上不建议使用 MyISAM 表。

在使用分区表时，大家常常会碰到下面这个报错。

即分区键必须是主键的一部分。

上面的 opr 是一张 *** 作流水表。其中，opr_no 是 *** 作流水号，一般都会被设置为主键，opr_date 是 *** 作时间。基于 *** 作时间来进行分区，是一个常见的分区场景。

为了突破这个限制，可将 opr_date 作为主键的一部分。

但是这么创建，又会带来一个新的问题，即对于同一个 opr_no ，可插入到不同分区中。如下所示：

这实际上违背了业务对于 opr_no 的唯一性要求。

既然这样，有的童鞋会建议给 opr_no 添加个唯一索引，But，现实是残酷的。

即便是添加唯一索引，分区键也必须包含在唯一索引中。

总而言之，对于 MySQL 分区表，无法从数据库层面保证非分区列在表级别的唯一性，只能确保其在分区内的唯一性。

这也是 MySQL 分区表所为人诟病的地方之一。

但实际上，这个锅让 MySQL 背并不合适，对于 Oracle 索引组织表（ InnoDB 即是索引组织表），同样也有这个限制。

Oracle 官方文档（ >

以上就是关于mysql分区的二，mysql分区类型全部的内容，包括:mysql分区的二，mysql分区类型、mysql5.6.20如何开启分区功能、mysql如何查看分区情况等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！