Heim >Datenbank >MySQL-Tutorial >[MySQL 5.1 体验]MySQL 分区
作/译者:叶金荣(Email: ),来源:http://imysql.cn 一、概述 相信有很多人经常会问同样的一个问题:当 MySQL 的总记录数超过了100万后,会出现性能的大幅度下降吗?答案是肯定的,但是性能下降的比率不一而同,要看系统的架构、应用程序、还有包 括索引、
作/译者:叶金荣(Email: ),来源:http://imysql.cn
相信有很多人经常会问同样的一个问题:当 MySQL
的总记录数超过了100万后,会出现性能的大幅度下降吗?答案是肯定的,但是性能下降>的比率不一而同,要看系统的架构、应用程序、还有>包 括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候,我最常见的回答>就是:分表,可以根据id区间或者时间先后顺序等多
种规则来分表。分表很容易,然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑,还包括将来的扩展性等。
在以前,一种解决方案就是使用 MERGE
类型,这是一个非常方便的做饭。架构和程序基本上不用做改动,不过,它的缺点是显见的:
这个时候,MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了:
分区允许可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。
CREATE TABLE users (<br> uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,<br> name VARCHAR(30) NOT NULL DEFAULT '',<br> email VARCHAR(30) NOT NULL DEFAULT ''<br>)<br>PARTITION BY RANGE (uid) (<br> PARTITION p0 VALUES LESS THAN (3000000)<br> DATA DIRECTORY = '/data0/data'<br> INDEX DIRECTORY = '/data1/idx',<br><br> PARTITION p1 VALUES LESS THAN (6000000)<br> DATA DIRECTORY = '/data2/data'<br> INDEX DIRECTORY = '/data3/idx',<br><br> PARTITION p2 VALUES LESS THAN (9000000)<br> DATA DIRECTORY = '/data4/data'<br> INDEX DIRECTORY = '/data5/idx',<br><br> PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data' <br> INDEX DIRECTORY = '/data7/idx'<br>);<br>
在这里,将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录,与此同时,这些目录所在的>
物理磁盘分区可能也都是完全独立的,可以多大提高了磁盘IO吞吐量。
CREATE TABLE category (<br> cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,<br> name VARCHAR(30) NOT NULL DEFAULT ''<br>)<br>PARTITION BY LIST (cid) (<br> PARTITION p0 VALUES IN (0,4,8,12)<br> DATA DIRECTORY = '/data0/data' <br> INDEX DIRECTORY = '/data1/idx',<br> <br> PARTITION p1 VALUES IN (1,5,9,13)<br> DATA DIRECTORY = '/data2/data'<br> INDEX DIRECTORY = '/data3/idx',<br> <br> PARTITION p2 VALUES IN (2,6,10,14)<br> DATA DIRECTORY = '/data4/data'<br> INDEX DIRECTORY = '/data5/idx',<br> <br> PARTITION p3 VALUES IN (3,7,11,15)<br> DATA DIRECTORY = '/data6/data'<br> INDEX DIRECTORY = '/data7/idx'<br>); <br>
分成4个区,数据文件和索引文件单独存放。
CREATE TABLE users (<br> uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,<br> name VARCHAR(30) NOT NULL DEFAULT '',<br> email VARCHAR(30) NOT NULL DEFAULT ''<br>)<br>PARTITION BY HASH (uid) PARTITIONS 4 (<br> PARTITION p0<br> DATA DIRECTORY = '/data0/data'<br> INDEX DIRECTORY = '/data1/idx',<br><br> PARTITION p1<br> DATA DIRECTORY = '/data2/data'<br> INDEX DIRECTORY = '/data3/idx',<br><br> PARTITION p2<br> DATA DIRECTORY = '/data4/data'<br> INDEX DIRECTORY = '/data5/idx',<br><br> PARTITION p3<br> DATA DIRECTORY = '/data6/data'<br> INDEX DIRECTORY = '/data7/idx'<br>);<br>
分成4个区,数据文件和索引文件单独存放。
REATE TABLE users (<br> uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,<br> name VARCHAR(30) NOT NULL DEFAULT '',<br> email VARCHAR(30) NOT NULL DEFAULT ''<br>)<br>PARTITION BY KEY (uid) PARTITIONS 4 (<br> PARTITION p0<br> DATA DIRECTORY = '/data0/data'<br> INDEX DIRECTORY = '/data1/idx',<br> <br> PARTITION p1<br> DATA DIRECTORY = '/data2/data' <br> INDEX DIRECTORY = '/data3/idx',<br> <br> PARTITION p2 <br> DATA DIRECTORY = '/data4/data'<br> INDEX DIRECTORY = '/data5/idx',<br> <br> PARTITION p3 <br> DATA DIRECTORY = '/data6/data'<br> INDEX DIRECTORY = '/data7/idx'<br>); <br>
分成4个区,数据文件和索引文件单独存放。
CREATE TABLE users (<br> uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,<br> name VARCHAR(30) NOT NULL DEFAULT '',<br> email VARCHAR(30) NOT NULL DEFAULT ''<br>)<br>PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(<br> PARTITION p0 VALUES LESS THAN (3000000)<br> DATA DIRECTORY = '/data0/data'<br> INDEX DIRECTORY = '/data1/idx',<br><br> PARTITION p1 VALUES LESS THAN (6000000)<br> DATA DIRECTORY = '/data2/data'<br> INDEX DIRECTORY = '/data3/idx'<br>);<br>
对 RANGE 分区再次进行子分区划分,子分区采用 HASH 类型。
或者
CREATE TABLE users (<br> uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,<br> name VARCHAR(30) NOT NULL DEFAULT '',<br> email VARCHAR(30) NOT NULL DEFAULT ''<br>)<br>PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(<br> PARTITION p0 VALUES LESS THAN (3000000)<br> DATA DIRECTORY = '/data0/data'<br> INDEX DIRECTORY = '/data1/idx',<br><br> PARTITION p1 VALUES LESS THAN (6000000)<br> DATA DIRECTORY = '/data2/data'<br> INDEX DIRECTORY = '/data3/idx'<br>);<br>
对 RANGE 分区再次进行子分区划分,子分区采用 KEY 类型。
ALERT TABLE users DROP PARTITION p0;<br>
删除分区 p0。
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));<br>
将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));<br>
将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;<br>
用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)<br>DATA DIRECTORY = '/data8/data'<br>INDEX DIRECTORY = '/data9/idx');<br>
新增一个RANGE分区。
ALTER TABLE users ADD PARTITION PARTITIONS 8;<br>
将分区总数扩展到8个。
好了,本次体验先到这里,更多详情请看
target="_blank">MySQL 手册第18章。