MySQL支持哪几类分区表?

Q

题目

MySQL支持哪几类分区表?

A

答案

表分区是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成,每个分区都是一个独立的对象。分区有利于管理大表,体现了“分而治之”的理念。一个表最多支持1024个分区。

在MySQL 5.6.1之前可以通过命令“show variables like '%have_partitioning%'”来查看MySQL是否支持分区。若have_partintioning的值为YES,则表示支持分区。从MySQL 5.6.1开始,该参数已经被去掉了,而是用SHOW PLUGINS来代替。若有partition行且STATUS列的值为ACTIVE,则表示支持分区,如下所示:

root@lhrdb> SHOW PLUGINS;

+----------------------------+----------+--------------------+---------+-------------+

| Name | Status | Type | Library | License |

+----------------------------+----------+--------------------+---------+-------------+

| partition | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |

+----------------------------+----------+--------------------+---------+-------------+

此外,也可以使用表INFORMATION_SCHEMA.PLUGINS来查询,如下所示:

mysql> SELECT PLUGIN_NAME AS NAME,

-> PLUGIN_VERSION AS VERSION,

-> PLUGIN_STATUS AS STATUS

-> FROM INFORMATION_SCHEMA.PLUGINS A

-> WHERE PLUGIN_TYPE = 'STORAGE ENGINE'

-> AND A.PLUGIN_NAME = 'partition';

+-----------+---------+--------+

| NAME | VERSION | STATUS |

+-----------+---------+--------+

| partition | 1.0 | ACTIVE |

+-----------+---------+--------+

MySQL支持的分区类型主要包括RANGE分区、LIST分区、HASH分区、KEY分区。分区表中对每个分区再次分割就是子分区(Subpartitioning),又称为复合分区。在MySQL 5.5中引入了COLUMNS分区,细分为RANGE COLUMNS和LIST COLUMNS分区。引人COLUMNS分区解决了MySQL 5.5版本之前RANGE分区和LIST分区只支持整数分区,从而导致需要額外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题。

KEY分区类似HASH分区,HASH分区允许使用用户自定义的表达式,但KEY分区不允许使用用户自定义的表达式。HASH仅支持整数分区,而KEY分区支持除了BLOB和TEXT的其他类型的列作为分区键。KEY分区语法为:

PARTITION BY KEY(EXP) PARTITIONS 4;//EXP是零个或多个字段名的列表

在进行KEY分区的时候,EXP可以为空,如果为空,那么默认使用主键作为分区键。若没有主键则会选择非空惟一键作为分区键。

MySQL允许分区键值为NULL,分区键可能是一个字段或者一个用户定义的表达式。一般情况下,MySQL在分区的时候会把NULL值当作零值或者一个最小值进行处理。需要注意以下几点:

lRANGE分区:NULL值被当作最小值来处理。

lLIST分区:NULL值必须出现在列表中,否则不被接受。

lHASH/KEY分区:NULL值会被当作零值来处理。

通过ALTER TABLE命令可以对分区进行添加、删除、重定义、合并、拆分等操作;通过INFORMATION_SCHEMA.PARTITIONS可以查询分区数、行数等信息;通过EXPLAIN PARTITIONS可以查看分区表的执行计划。

需要注意的是,MySQL的分库分表和表分区(Partitioning)的区别。分库分表是指把数据库中的数据物理地拆分到多个实例或多台机器上去。分表指的是通过一定规则,将一张表分解成多张不同的表。

表分区(Partitioning)可以将一张表的数据分别存储为多个文件。如果在写SQL的时候,遵从了分区规则,那么就能把原本需要遍历全表的工作转变为只需要遍历表里某一个或某些分区的工作。这样降低了查询对服务器的压力,提升了查询效率。如果分区表使用得当,那么也可以大规模地提升MySQL的服务能力。但是这种分区方式,一方面,在使用的时候必须遵从分区规则写SQL语句,如果不符合分区规则,性能反而会非常低下;另一方面,Partitioning的结果受到MySQL实例,或者说MySQL单实例的数据文件无法分布式存储的限制,不管怎么分区,所有的数据还是都在一个服务器上,没办法通过水平扩展物理服务的方法把压力分摊出去。

分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表。

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20171221B05NTD00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券