mysql5.1通过分区（Partition）提升MySQL性能

一、概述
相信有很多人经常会问同样的一个问题：当 MySQL
的总记录数超过了100万后，会出现性能的大幅度下降吗？答案是肯定的，但是性能下降>的比率不一而同，
要看系统的架构、应用程序、还有>包 括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候，
我最常见的回答>就是：分表，可以根据id区间或者时间先后顺序等多种规则来分表。分表很容易，
然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑，还包括将来的扩展性等。
在以前，一种解决方案就是使用 MERGE类型，这是一个非常方便的做饭。架构和程序基本上不用做改动，
不过，它的缺点是显见的：只能在相同结构的 MyISAM 表上使用
无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索它需要使用更多的文件描述符
读取索引更慢这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了：
与单个磁盘或文件系统分区相比，可以存储更多的数据
很容易就能删除不用或者过时的数据一些查询可以得到极大的优化
涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行IO吞吐量更大
分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。
实际上，表的不同部分在不同的位置被存储为单独的表。
　　

通过分区（Partition）提升MySQL性能

什么是数据库分区？
　　数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。
虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据
读写的总量以缩减响应时间。分区主要有两种形式：
这里一定要注意行和列的概念（row是行，column是列）

1. 水平分区（Horizontal Partitioning）这种形式分区是对表的行进行分区，
通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）
或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。
（朋奕注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份）
　　
2. 垂直分区（Vertical Partitioning）这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列
被划分到特定的分区，每个分区都包含了其中的列所对应的行。
举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的
text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
在数据库供应商开始在他们的数据库引擎中建立分区（主要是水平分区）时，DBA和建模者必须设计好表的物理分区结构，
不要保存冗余的数据（不同表中同时都包含父表中的数据）或相互联结成一个逻辑父对象（通常是视图）。
这种做法会使水平分区的大部分功能失效，有时候也会对垂直分区产生影响。

在MySQL 5.1中进行分区
MySQL5.1中最激动人心的新特性应该就是对水平分区的支持了。这对MySQL的使用者来说确实是个好消息，
而且她已经支持分区大部分模式：
Range（范围）分区：基于属于一个给定连续区间的列值，把多行分配给分区。参见18.2.1节，RANGE分区
这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，
80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。
　　
Hash（哈希）分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。
这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码
不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。
这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。参见18.2.3节，HASH分区

Key（键值）分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，
且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。参见18.2.4节，KEY分区
上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
　　
List（预定义列表）分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配
一个离散值集合中的某个值来进行选择。参见18.2.2节，LIST分区
这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：
DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。
Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：
在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。
　　
分区带来的好处太多太多了，有多少？俺也不知道，自己猜去吧，要是觉得没有多少就别用，
反正俺也不求你用。不过在这里俺强调两点好处：

　　性能的提升（Increased performance） - 在扫描操作中，如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据，
它就能直接去扫描那些分区的数据，而不用浪费很多时间扫描不需要的地方了。需要举个例子？好啊，百万行的表划分为10个分区，
每个分区就包含十万行数据，那么查询分区需要的时间仅仅是全表扫描的十分之一了，很明显的对比。
同时对十万行的表建立索引的速度也会比百万行的快得多得多。如果你能把这些分区建立在不同的磁盘上，
这时候的I/O读写速度就“不堪设想”（没用错词，真的太快了，理论上100倍的速度提升啊，这是多么快的响应速度啊，
所以有点不堪设想了）了。
　　对数据管理的简化（Simplified data management） - 分区技术可以让DBA对数据的管理能力提升。通过优良的分区
，DBA可以简化特定数据操作的执行方式。例如：DBA在对某些分区的内容进行删除的同时能保证余下的分区的数据完整性
(这是跟对表的数据删除这种大动作做比较的)。
此外分区是由MySQL系统直接管理的，DBA不需要手工的去划分和维护。例如：这个例如没意思，不讲了，如果你是DBA
只要你划分了分区，以后你就不用管了就是了。

　　站在性能设计的观点上，俺们对以上的内容也是相当感兴趣滴。通过使用分区和对不同的SQL操作的匹配设计，
数据库的性能一定能获得巨大提升。下面咱们一起用用这个MySQL 5.1的新功能看看。
下面所有的测试都在Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM机器上（炫耀啊……），
Fedora Core 4和MySQL 5.1.6 alpha上运行通过。

如何进行实际分区

三、分区例子：　
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 类型。

　　看看分区的实际效果吧。我们建立几个同样的MyISAM引擎的表，包含日期敏感的数据，
但只对其中一个分区。分区的表（表名为part_tab）我们采用Range范围分区模式，通过年份进行分区：

mysql> Create TABLE part_tab
    ->      ( c1 int default NULL,
    -> c2 varchar(30) default NULL,
    -> c3 date default NULL
    ->
    ->      ) engine=myisam
    ->      PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
    ->      PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
    ->      PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
    ->      PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
    ->      PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
    ->      PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
    ->      PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.00 sec)

　　注意到了这里的最后一行吗？这里把不属于前面年度划分的年份范围都包含了，这样才能保证数据不会出错，
大家以后要记住啊，不然数据库无缘无故出错你就爽了。那下面我们建立没有分区的表（表名为no_part_tab）：

mysql> create table no_part_tab
    -> (c1 int(11) default NULL,
    -> c2 varchar(30) default NULL,
    -> c3 date default NULL) engine=myisam;
Query OK, 0 rows affected (0.02 sec)
　　下面咱写一个存储过程（感谢Peter Gulutzan给的代码，
如果大家需要Peter Gulutzan的存储过程教程的中文翻译也可以跟我要，chenpengyi◎gmail.com），
它能向咱刚才建立的已分区的表中平均的向每个分区插入共8百万条不同的数据。填满后，咱就给没分区的克隆表中插入相同的数据：

mysql> delimiter //
mysql> Create PROCEDURE load_part_tab()
    -> begin
    -> declare v int default 0;
    ->          while v < 8000000
    -> do
    -> insert into part_tab
    -> values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
    -> set v = v + 1;
    -> end while;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call load_part_tab();
Query OK, 1 row affected (8 min 17.75 sec)
mysql> insert into no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

　　表都准备好了。咱开始对这两表中的数据进行简单的范围查询吧。先分区了的，
后没分区的，跟着有执行过程解析（MySQL Explain命令解析器），可以看到MySQL做了什么：

mysql> select count(*) from no_part_tab where
    -> c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (38.30 sec)
 
mysql> select count(*) from part_tab where
    -> c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (3.88 sec)
 
mysql> explain select count(*) from no_part_tab where
    -> c3 > date '1995-01-01' and c3 < date '1995-12-31'/G
*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: no_part_tab
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8000000
        Extra: Using where
1 row in set (0.00 sec)
 
mysql> explain partitions select count(*) from part_tab where
    -> c3 > date '1995-01-01' and c3 < date '1995-12-31'/G
*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: part_tab
   partitions: p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 798458
        Extra: Using where
1 row in set (0.00 sec)

　　从上面结果可以容易看出，设计恰当表分区能比非分区的减少90％的响应时间。
而命令解析Explain程序也告诉我们在对已分区的表的查询过程中仅对第一个分区进行了扫描，其他都跳过了。
哔厉吧拉，说阿说……反正就是这个分区功能对DBA很有用拉，特别对VLDB和需要快速反应的系统。

对Vertical Partitioning的一些看法

　　虽然MySQL 5.1自动实现了水平分区，但在设计数据库的时候不要轻视垂直分区。
虽然要手工去实现垂直分区，但在特定场合下你会收益不少的。例如在前面建立的表中，
VARCHAR字段是你平常很少引用的，那么对它进行垂直分区会不会提升速度呢？咱们看看测试结果：

mysql> desc part_tab;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | YES |     | NULL    |       |
| c2    | varchar(30) | YES |     | NULL    |       |
| c3    | date        | YES |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
 
mysql> alter table part_tab drop column c2;
Query OK, 8000000 rows affected (42.20 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
 
mysql> desc part_tab;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES |     | NULL    |       |
| c3    | date    | YES |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> select count(*) from part_tab where
    -> c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (0.34 sec)

　　在设计上去掉了VARCHAR字段后，
不止是你，俺也发现查询响应速度上获得了另一个90％的时间节省。所以大家在设计表的时候，
一定要考虑，表中的字段是否真正关联，又是否在你的查询中有用？

补充说明

　　这么简单的文章肯定不能说全MySQL 5.1 分区机制的所有好处和要点（虽然对自己写文章水平很有信心），
下面就说几个感兴趣的：

　　* 支持所有存储引擎(MyISAM, Archive, InnoDB, 等等)
　　* 对分区的表支持索引，包括本地索引local indexes，对其进行的是一对一的视图镜像，假设一个表有十个分区，
那么其本地索引也包含十个分区。
　　* 关于分区的元数据Metadata的表可以在INFORMATION_SCHEMA数据库中找到，表名为PARTITIONS。
　　* All SHOW 命令支持返回分区表以及元数据的索引。
　　* 对其操作的命令和实现的维护功能有（比对全表的操作还多）：

　　o ADD PARTITION
　　o Drop PARTITION
　　o COALESCE PARTITION
　　o REORGANIZE PARTITION
　　o ANALYZE PARTITION
　　o CHECK PARTITION
　　o OPTIMIZE PARTITION
　　o REBUILD PARTITION
　　o REPAIR PARTITION

　　站在性能主导的观点上来说，MySQL 5.1的分区功能能给数据性能带来巨大的提升的同时减轻DBA的管理负担
，如果分区合理的话。如果需要更多的资料可以去http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
或 http://forums.mysql.com/list.php?106获得相关资料。

四、分区管理
删除分区
　
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章。

TIPS：
Php+mysql处理大容量数据存储
在一个php的论坛上看到了这样一个贴子，索引将答案贴到这里让大家看看，希望对大家有帮助
--------------------------------------------------------------------------------
当数据库很大如10万条，1gb大小时，怎样搜索才高效？
请问版主，我现在在调试一个论坛，向数据库中写入了10万多条信息，此时主要的内容都集中在一个表上，有近1gb大小。
搜索该论坛时很慢，特别是全文搜索时，即使只用
select * from `cdb_posts` where message=本破烂陈旧的小册子从怀中取出递给我

也很慢，在台式机上甚至根本运行不完。但我听说sun公司的论坛（下面有近百个分论坛）进行全文搜索时也不超过20秒，我们能否做到。

我运行搜索程序时，cpu和内存占用率都很低，只有硬盘灯一直在闪，我总认为是程序不够高效，是不是可以把程序写出类似“多线程”，以提高效率。

请版主和各路大侠指教！
谢谢。

--------------------------------------------------------------------------------
有人建议它用oracle
我觉得有一点大家要注意，那就是如果你让oracle执行一个 select * from xxxx where xxx = xxxxxxxxxxxxxx;
这个表有1g的时候绝对不会比mysql快上多少。
现在的这么多流行的数据库系统上执行相同的操作时不可能快上很多倍的。
关键在于你的数据库设计。
我在做一个商业信息的搜索引擎，在做的过程中得到一些经验。如下：

1。 建立摘要表
2。 对于那些数字形式且经常查询的数据一定要建立索引，我不记得在哪里看到一篇贴子说什么：“不要用那垃圾的索引”.郁闷了n久。这种贴子都有。。哎。
建议： 在where中的用到的尺寸不大的condition一般都要索引。尺寸大的单独表中存储且要用like查询的单独表中存储。
3。 对于有很多结果的时候不要select *... ,先select id.... 然后取得id后进行分页控制，取出当前页中的id到数组。。 再执行select * from xxx where id in(implode(,, $id数组);
如果得到的数据还要进行join，请在这条select 语句中来进行。因为这样数据库进行join的选择因子会小很多。
4。对于要全文检索的数据和现在的数据分离出来。以数据库设计中第二范式的形式表示.在单独表中存储和数据主键对应的信息.
5。给表加上一个primary , int(smallint、tinyint 看要多少条数据).
6。在做好系统后用explain select .... 对系统中耗时的语句进行分析。看看mysql是如何处理这条语句的。
7。如果有可能，把信息分表存储（也可分布在不同的数据库服务器上）
本文转载自『北漂石头的博客』
http://www.niutian365.com/blog/
更多精彩内容,欢迎访问北漂石头的博客!