MySQL分区(mysql partition)

程序员文章站 2022-06-23 20:40:12

...

《 http://hi.baidu.com/jackbillow/blog/item/26effe7e637ca0300ed7da66.html》

一、概述

相信有很多人经常会问同样的一个问题：当 MySQL
的总记录数超过了100万后，会出现性能的大幅度下降吗？答案是肯定的，但是性能下降>的比率不一而同，要看系统的架构、应用程序、还有>包括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候，我最常见的回答>就是：分表，可以根据id区间或者时间先后顺序等多
种规则来分表。分表很容易，然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑，还包括将来的扩展性等。

在以前，一种解决方案就是使用 MERGE
类型，这是一个非常方便的做饭。架构和程序基本上不用做改动，不过，它的缺点是显见的：

只能在相同结构的 MyISAM 表上使用
无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索
它需要使用更多的文件描述符
读取索引更慢

这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了：

与单个磁盘或文件系统分区相比，可以存储更多的数据
很容易就能删除不用或者过时的数据
一些查询可以得到极大的优化
涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行
IO吞吐量更大

分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。

二、分区的类型

RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。参见18.2.1节，RANGE分区
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。参见18.2.2节，LIST分区
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。参见18.2.3节，HASH分区
KEY
分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。
参见18.2.4节，KEY分区

三、分区例子：

RANGE 类型

CREATE TABLE users (
                         uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                         name VARCHAR(30) NOT NULL DEFAULT '',
                         email VARCHAR(30) NOT NULL DEFAULT ''
                )
                PARTITION BY RANGE (uid) (
                         PARTITION p0 VALUES LESS THAN (3000000)
                         DATA DIRECTORY = '/data0/data'
                         INDEX DIRECTORY = '/data1/idx',
                         PARTITION p1 VALUES LESS THAN (6000000)
                         DATA DIRECTORY = '/data2/data'
                         INDEX DIRECTORY = '/data3/idx',
                         PARTITION p2 VALUES LESS THAN (9000000)
                         DATA DIRECTORY = '/data4/data'
                         INDEX DIRECTORY = '/data5/idx',
                         PARTITION p3 VALUES LESS THAN MAXVALUE         DATA DIRECTORY = '/data6/data'
                         INDEX DIRECTORY = '/data7/idx'
                );

在这里，将用户表分成4个分区，以每300万条记录为界限，每个分区都有自己独立的数据、索引文件的存放目录，与此同时，这些目录所在的>
物理磁盘分区可能也都是完全独立的，可以多大提高了磁盘IO吞吐量。

LIST 类型

CREATE TABLE category (
                         cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                         name VARCHAR(30) NOT NULL DEFAULT ''
                )
                PARTITION BY LIST (cid) (
                         PARTITION p0 VALUES IN (0,4,8,12)
                         DATA DIRECTORY = '/data0/data'
                         INDEX DIRECTORY = '/data1/idx',
                      
                         PARTITION p1 VALUES IN (1,5,9,13)
                         DATA DIRECTORY = '/data2/data'
                         INDEX DIRECTORY = '/data3/idx',
                      
                         PARTITION p2 VALUES IN (2,6,10,14)
                         DATA DIRECTORY = '/data4/data'
                         INDEX DIRECTORY = '/data5/idx',
                      
                         PARTITION p3 VALUES IN (3,7,11,15)
                         DATA DIRECTORY = '/data6/data'
                         INDEX DIRECTORY = '/data7/idx'
                );

分成4个区，数据文件和索引文件单独存放。

HASH 类型

CREATE TABLE users (
                         uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                         name VARCHAR(30) NOT NULL DEFAULT '',
                         email VARCHAR(30) NOT NULL DEFAULT ''
                )
                PARTITION BY HASH (uid) PARTITIONS 4 (
                         PARTITION p0
                         DATA DIRECTORY = '/data0/data'
                         INDEX DIRECTORY = '/data1/idx',
                         PARTITION p1
                         DATA DIRECTORY = '/data2/data'
                         INDEX DIRECTORY = '/data3/idx',
                         PARTITION p2
                         DATA DIRECTORY = '/data4/data'
                         INDEX DIRECTORY = '/data5/idx',
                         PARTITION p3
                         DATA DIRECTORY = '/data6/data'
                         INDEX DIRECTORY = '/data7/idx'
                );

分成4个区，数据文件和索引文件单独存放。

KEY 类型

REATE TABLE users (
                         uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                         name VARCHAR(30) NOT NULL DEFAULT '',
                         email VARCHAR(30) NOT NULL DEFAULT ''
                )
                PARTITION BY KEY (uid) PARTITIONS 4 (
                         PARTITION p0
                         DATA DIRECTORY = '/data0/data'
                         INDEX DIRECTORY = '/data1/idx',
                      
                         PARTITION p1
                         DATA DIRECTORY = '/data2/data'
                         INDEX DIRECTORY = '/data3/idx',
                      
                         PARTITION p2
                         DATA DIRECTORY = '/data4/data'
                         INDEX DIRECTORY = '/data5/idx',
                      
                         PARTITION p3
                         DATA DIRECTORY = '/data6/data'
                         INDEX DIRECTORY = '/data7/idx'
                );

分成4个区，数据文件和索引文件单独存放。

子分区
子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。例如：

CREATE TABLE users (
                         uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                         name VARCHAR(30) NOT NULL DEFAULT '',
                         email VARCHAR(30) NOT NULL DEFAULT ''
                )
                PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(
                         PARTITION p0 VALUES LESS THAN (3000000)
                         DATA DIRECTORY = '/data0/data'
                         INDEX DIRECTORY = '/data1/idx',
                         PARTITION p1 VALUES LESS THAN (6000000)
                         DATA DIRECTORY = '/data2/data'
                         INDEX DIRECTORY = '/data3/idx'
                );

对 RANGE 分区再次进行子分区划分，子分区采用 HASH 类型。

或者

CREATE TABLE users (
                         uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                         name VARCHAR(30) NOT NULL DEFAULT '',
                         email VARCHAR(30) NOT NULL DEFAULT ''
                )
                PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(
                         PARTITION p0 VALUES LESS THAN (3000000)
                         DATA DIRECTORY = '/data0/data'
                         INDEX DIRECTORY = '/data1/idx',
                         PARTITION p1 VALUES LESS THAN (6000000)
                         DATA DIRECTORY = '/data2/data'
                         INDEX DIRECTORY = '/data3/idx'
                );

对 RANGE 分区再次进行子分区划分，子分区采用 KEY 类型。

四、分区管理

删除分区
```
ALERT TABLE users DROP PARTITION p0;
```
删除分区 p0。
重建分区
- RANGE 分区重建
```
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
```
  将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。
- LIST 分区重建
```
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
```
  将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。
- HASH/KEY 分区重建
```
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
```
  用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

新增分区

新增 RANGE 分区

ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
                    DATA DIRECTORY = '/data8/data'
                    INDEX DIRECTORY = '/data9/idx');

新增一个RANGE分区。

新增 HASH/KEY 分区
```
ALTER TABLE users ADD PARTITION PARTITIONS 8;
```
将分区总数扩展到8个。

好了，本次体验先到这里，更多详情请看 mysql 手册第18章。

MySQL分区(mysql partition)

一、概述

二、分区的类型

三、分区例子：

四、分区管理

MySQL5.7.23解压版安装教程图文详解

python 连接数据库mysql解压版安装配置及遇到问题

LAMP服务器性能优化技巧之Mysql优化

linux下Vps自动备份web和mysql数据库的脚本

为什么说MySQL单表数据不要超过500万行

Ubuntu安装MySql5.7

JBPM4.4安装(Tomcat-MySQL)方法

很简单的Eclipse连接MySQL数据库方法

python 对mysql新增，修改，删除操作

CentOS 7.x编译安装Nginx1.10.3+MySQL5.7.16+PHP5.2 5.3 5.4 5.5 5.6 7.0 7.1多版本全能环境