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单实例的数据文件无法分布式存储的限制,不管怎么分区,所有的数据还是都在一个服务器上,没办法通过水平扩展物理服务的方法把压力分摊出去。

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

本文来自企鹅号 - DBA宝典媒体

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏后端技术探索

一次非常有意思的sql优化经历

发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。

9910
来自专栏极客慕白的成长之路

MySQL从安装到使用

Columns 列;Indexes 索引;Views 视图;Events 事件;Fields 字段;

10640
来自专栏跟着阿笨一起玩NET

SQL好的写法

13520
来自专栏互联网开发者交流社区

数据定义: CREATE、DROP、ALTER

16220
来自专栏杨建荣的学习笔记

Oracle和MySQL竟然可以这么写这样的SQL?(r12笔记第99天)

今天看到Franck Pachot‏ 发了一个Twitter,意思是Oracle里的SQL还能这么写。猛一看确实让人有些意外。 ? 禁不住诱惑,自己也尝试了一番...

30350
来自专栏后端技术探索

一次非常有意思的sql优化经历

发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。

9220
来自专栏撸码那些事

MySQL——索引实现原理

在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

9820
来自专栏帘卷西风的专栏

关于mysql存储过程创建动态表名及参数处理

转载请注明出处:帘卷西风的专栏(http://blog.csdn.net/ljxfblog) 

25130
来自专栏程序你好

正确使用索引和Explain工具,MySQL性能提升实例

可以有多种不同层次的技术提高应用程序性能,但是通常我们首先关注的是数据库方面——这是最常见的性能瓶颈。数据库的性能可以改善吗?我们如何衡量,到底什么需要性能改进...

22230
来自专栏JavaQ

MySQL索引实战经验总结

MySQL索引对数据检索的性能至关重要,盲目的增加索引不仅不能带来性能的提升,反而会消耗更多的额外资源,本篇总结了一些MySQL索引实战经验。 索引是用于快速查...

36980

扫码关注云+社区

领取腾讯云代金券