三国演义开篇一语道破:合久必分!MySQL的使用亦是如此,面对应用中越来越庞大的数据量,最时髦的解决方案是Shard,不过它的复杂性并不是每个程序员都能驾驭的,如果把架构的演变比作生命的进化,那么Shard可以看做是哺乳动物,很多架构最后之所以失败,就是因为它们步子迈得太大,想从原始生命直接进化成哺乳动物,殊不知这中间还有爬行动物等必经阶段。
在MySQL没有支持Partition之前,如果想把数据分而治之,可以使用MySQL提供的Merge的引擎,例子:
先建立两个结构一样的MyISAM表:
CREATE TABLE foo_1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATETIME
) ENGINE=MyISAM;
CREATE TABLE foo_2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATETIME
) ENGINE=MyISAM;
再建立MERGE表:
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATETIME
) ENGINE=MERGE UNION=(foo_1, foo_2) INSERT_METHOD=LAST;
对调用者而言,MERGE表就像一个UNION语句一样,这样确实很方便,不过它有很多弱点:
首先,它不是完全透明的,需要有若干基础表(foo_1,foo_2)的存在,而且基础表必须是MyISAM表类型,另外,对于MERGE来说,不支持约束,比如上面的foo表定义中,虽然把id定义为主键,但是如果我们在foo_1和foo_2分别插入一个相同id的话,foo表也不会报错。
言归正传!MySQL从5.1.3开始支持Partition,你可以使用如下命令来确认你的版本是否支持Partition:
mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
MySQL支持RANGE,LIST,HASH,KEY分区类型,其中以RANGE最为常用:
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT,
created DATETIME,
PRIMARY KEY(id, created)
) ENGINE=INNODB PARTITION BY RANGE (TO_DAYS(created)) (
PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')),
PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01'))
)
即便创建完分区,也可以在后期管理,比如说添加一个新的分区:
ALTER TABLE foo ADD PARTITION (
PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2011-01-01'))
)
或者删除一个分区:
ALTER TABLE FOO DROP PARTITION foo_3;
通过检索information_schema数据库,能看到我们刚刚创建的分区信息:
SELECT * FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL
此时,打开MySQL的数据目录(SHOW VARIABLES LIKE 'datadir'):
如果MySQL配置设置了innodb file per table为ON的话,由于上面定义的是INNODB,则会发现:
foo#p#foo_1.ibd
foo#p#foo_2.ibd
如果创建的是MyISAM表类型的话,则会发现:
foo#P#foo_1.MYD
foo#P#foo_1.MYI
foo#P#foo_2.MYD
foo#P#foo_2.MYI
由此可知通过分区,MySQL会把数据保存到不同的数据文件里,同时索引也是分区的,相对未分区的表来说,分区后单独的数据文件和索引文件的大小都明显降低,效率则明显提升。为了验证这一点,我们做如下实验:
INSERT INTO `foo` (`id`, `created`) VALUES
(1, '2008-01-02 00:00:00'),
(2, '2009-01-02 00:00:00');
然后执行SQL:
EXPLAIN PARTITIONS SELECT * FROM foo WHERE created = '2008-01-02';
会看到MySQL仅仅在foo_1分区执行这条查询。理论上效率肯定会快一些,至于具体多少,就看数据量了。实际应用分区的时候,我们还可以通过DATA DIRECTORY和INDEX DIRECTORY选项把不同的分区分散到不同的磁盘上,从而进步一提高系统的IO吞吐量。
重要提示:使用分区功能之后,相关查询最好都用EXPLAIN PARTITIONS过一遍,确认分区是否生效。
到底应该采用哪种分区类型呢?通常来说使用range类型是个不错的选择,不过也不尽然,比如说在主从结构中,主服务器由于很少使用SELECT查询,所以在主服务器上使用range类型的分区通常并没有太大意义,此时使用hash类型的分区相对更好一些,假设使用PARTITION BY HASH(id) PARTITIONS 10,那么当插入新数据时,会根据id把数据平均分散到各个分区上,由于文件小,所以效率高,更新操作会变得更快。
到底应该按哪个字段来分区呢?通常来说按时间字段分区是个不错的选择,不过还是应该按需求而定,通常有很多种划分应用的方式,比如说按时间,或者按用户,哪种用的多,就选哪种来分区。如果使用主从结构的话,还可能用的更灵活些,有的从服务器使用时间分区,有的从服务器使用用户分区,不过如此一来,当执行查询时,程序里应该负责选择正确的从服务器去查询,写个MySQL Proxy脚本应该可以透明实现。
分区虽然很爽,但目前的实现还有很多限制:
主键或者唯一索引必须包含分区字段:如PRIMARY KEY(id, created),不过对INNODB来说,大主键不爽。
很多时候,使用了分区就不要再使用主键,否则可能影响性能。
只能通过int类型的字段或者返回int类型的表达式来分区:通常使用YEAR或TO_DAYS等函数。
每个表最多1024个分区:不可能无限制的扩展分区,而且过度使用分区往往会消耗大量系统内存。
采用分区的表不支持外键:相关的约束逻辑必须通过程序来实现。
希望看了上面的简单介绍,大家可以明白应该如何使用分区功能了,不要仅仅把眼光放在Shard等流行技术之上,而忽视了原本使用更简单的Partition,恐龙虽然仅仅是爬行动物,却统治了地球长达千万年,比作为哺乳动物的人类统治地球的时间长得多。
MySQL5.5优化了分区功能,具体信息参考:A deep look at MySQL 5.5 partitioning enhancements。
分享到:
相关推荐
MYSQL 通过分区(Partition)提升MySQL性能,看清楚,不要浪费分数,是MYSQL,不是SQL SERVER2000,有需要的可以下载.
MySQL Partition Manager 是雅虎开源的 MySQL 分区管理器。它可以帮助你以最小的配置自动创建、维护、清除分区。 标签:MySQL
mysql表分区的建立,索引的建立,原理说明,还有就是实例的演示
Paragon Partition Manager 是一套磁盘管理软件,是目前为止最好用的磁盘管理工具之一,能够优化磁盘使应用程序和系统速度变得更快,不损失磁盘数据下调整分区大小,对磁盘进行分区,并可以在不同的分区以及分区之间...
图解Partition_Manager9.0把D盘空间转给C盘
Powerquest Partition Magic后被norton收购改叫Norton Partition Magic,最新版8.05有5年没更新。 而 Paragon Partition Manager 公司名是 Paragon。版本更新比较块,各个WinPE系统里面基本有它的7.0 Server版,不过...
partition by 使用说明
Paragon Partition Manager是一个类似于PQ PartitionMagic的磁盘分区工具集,是一套磁盘管理软件,PartitionMagic的最佳替代品!有着直觉的图形使用介面和支持鼠标操作。 【软件功能】 主要功能包括:能够不损失...
说到这里,大家首先想到的工具八成是PQMagic,不过在这里我给大家介绍的是Paragon公司的Partition Manager 7.0。之所以介绍它,是因为这个工具比PQMagic对中文操作系统的支持要好的多,安全系数比较高,功能也丝毫不...
如果想在已经建好的表上进行分区,如果使用alter添加分区的话,mysql会提示错误: 代码如下: ERROR 1505 <HY000> Partition management on a not partitioned table is not possible 正确的方法是新建一个具有分区...
本文介绍如何通过添加Informatica partition进行Session调优,包括判断Session瓶颈,选择partition类型
Partition Bad Disk(硬盘坏道扇区修复工具) 绿色破解版是一款非常优秀的可以帮助你将硬盘中的坏道扇区自动的彻底扫描检测出来,然后隐藏隔离起来的磁盘工具软件,使得新创建的分区可以不受坏道扇区的影响!...
本版本为Partition Wizard Server Edition服务器版,支持用于windows server2k/2003/2008/2008 r2服务器系统。支持32\64位系统。功能包括:对磁盘进行分区、重建MBR,初始化转换为MBR磁盘、使用GPT初始化磁盘、...
ORACLE数据库中PARTITION的用法[文].pdf
Paragon Partition Manager 是一套磁盘管理软件,是目前为止最好用的磁盘管理工具之一,能够优化磁盘使应用程序和系统速度变得更快,不损失磁盘数据下调整分区大小,对磁盘进行分区,并可以在不同的分区以及分区之间...
DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_IN...
As Partition Magic alternative, EASEUS Partition Master freeware is an ALL-IN-ONE partition software and the most convenient hard disk partition manager toolkit including Partition Manager, Disk & ...
Paragon Partition Manager 11
Active@ Partition Recovery for Windows - will help you if non-system partition is lost, i.e. you can boot Windows, install and run the software from under Windows to recover deleted or damaged ...
Oracle Partition分区详细总结.pdf