Mysql数据库分区字符串匹配

1、简介:

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

2、判断mysql是否支持分区:

可以执行以下语句:

show variables like '%partition%';

如果没有看到变量 have_partition_engine 的值为 YES 则说明当前mysql的版本就不支持分区。

mysql从5.1开始支持分区功能。注:执行 SELECT VERSION() 查看数据库版本;

但是如果你使用mysql的5.6版本，同样会出现empty的结果。这又是怎么回事呢，难道mysql的5.6版本不支持分区了吗？不是的，5.6依然支持分区，只不过将上面的验证方式抛弃了，改用：

show plugins;

可以看到下图中 PARTITION 是 ACTIVE ,那么也是支持的!

3、分区表的限制因素

（1）、一个表最多只能有1024个分区。

（2）、MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

（3）、如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

（4）、分区表中无法使用外键约束。

（5）、MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

4、MySQL支持的分区类型有哪些？

（1）、RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

（2）、LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

（3）、HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

（4）、KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

(5)、COLUMNS分区:COLUMN分区是5.5开始引入的分区功能，只有RANGE COLUMN和LIST COLUMN这两种分区；支持整形、日期、字符串；RANGE和LIST的分区方式非常的相似。

5、我的需求

需要根据数据库表中的一个varchar类型的值进行分区, 使用到了 COLUMNS分区

ALTER TABLE 表名 PARTITION BY RANGE COLUMNS(需要匹配的字段)(

PARTITION 命名的分区名称 VALUES LESS THAN ('f'),

PARTITION 命名的分区名称 VALUES LESS THAN ('sb')

)

THAN() 括号中的内容是字母 , 第一个'f' 代表>f , 即匹配的字段内容首字母顺序处于"f"之前;

若是需要匹配的字段内容首字母相同 , 则顺延至使用第二个字母进行匹配 ;

例如 "safety"和"sequip" ,要匹配第一个字符串则使用"sb" , 第一个字母"s"匹配顺延至使用第二个字母来匹配,依次类推;

分区执行语句

alter table t_insured_info_copy1 PARTITION BY RANGE COLUMNS(channel_code)(

PARTITION pedu VALUES LESS THAN ('f'), ## 匹配字符串 edu

PARTITION psafety values LESS THAN ('sb'), ## 匹配字符串 safety

PARTITION psequip values LESS THAN ('sf'), ## 匹配字符串 sequip

PARTITION ptour values LESS THAN ('u'), ## 匹配字符串 tour

PARTITION pmax VALUES LESS THAN ( MAXVALUE )

)

在使用范围COLUNS时，MAXVALUE也可能出现在多个小于值子句中的同一列中。但是，连续分区定义中各个列的极限值应该是递增的，不应该定义超过一个分区，其中MAXVALUE用作所有列值的上限，并且这个分区定义应该出现在分区列表的最后……小于子句的值。此外，您不能在多个分区定义中使用MAXVALUE作为第一列的极限值。如前所述，对于范围列分区，也可以使用非整数列作为分区列;

执行完成之后执行以下语句查看分区是否成功

SELECT * from t_insured_info_copy1 PARTITION(命名的分区名称)

执行语句

SELECT * from t_insured_info_copy1 PARTITION(psafety)

查看结果

至此顺利将数据库表分区;

6、移除表的分区

ALTER TABLE tablename

REMOVE PARTITIONING ;

注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

7、结束

初次编写文章,若是有错误或者误导之处还请谅解,可联系作者指出问题,谢谢!

官方文档:

地址:https://dev.mysql.com/doc/refman/5.7/en/partitioning-columns-range.html