Mysql分区

1. http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html
2. 查看是否支持分区：SHOW VARIABLES LIKE '%partition%';
3. 分区语句CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=INNODB PARTITION BY HASH(MONTH(tr_date)) PARTITIONS 6; 
4. ------------------------------------------------------网上
5. 水平分区（Horizontal Partitioning） 这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
6. 垂直分区（Vertical Partitioning） 这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
   举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。 
7. Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。 
   Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。 
   Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。 
   List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。 
   Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。
8. 分区带来的好处太多太多了，有多少？俺也不知道，自己猜去吧，要是觉得没有多少就别用，反正俺也不求你用。不过在这里俺强调两点好处：
   性能的提升（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上运行通过。
   
   如何进行实际分区
   看看分区的实际效果吧。我们建立几个同样的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 

9. MySQL数据库的优化和使用是一件很发杂的事情，那么我们如何让数据库变得更大更好呢?本文将介绍如何通过分区和负载均衡解决方案让你的MySQL变得更大更好。

   　　更大的MySQL

   　　增加更多的MySQL实例是提高应用程序响应速度的有效方法，如果你的服务器有多颗CPU，充足的内存和快速的硬盘，这些资源有相当一部分处于闲置状态，那么在这种情况下，在服务器上可以同时运行多个MySQL实例，因为MySQL默认情况下只有一个进程和多个会话线程，因此它真正能利用的最大硬件资源是有限的。

   　　如果你的服务器已经快饱和了，那么必须增加服务器，不管你的多个MySQL实例是在一台服务器上，还是在多台服务器上，你都需要为应用程序配置一个方法让它知道该将查询发送给哪台服务器，如果是要修改数据，那就应该将指令发送到主数据库实例，如果仅仅是查询操作，那么随便发给任何一个从数据库实例即可。

   　　1、数据分区和水平分区

   　　因为许多Web应用程序是通过会话来识别用户的，通过会话将它们分配到不同的从数据库实例显得很有道理。例如A-G，H-O，P-Z数据库实例可能在工作，这时可以通过用户名的哈希值，或userid将用户分配到不同的服务器上，这就是所谓的分区键，选择分区键时需要慎重决定，因为它会影响到你如何构建从数据库实例，主要是考虑如何让这些服务器平均承担工作负载，如果选择得不好，假设从数据库倒掉，也可能会引起数据中断。

   　　如果正采用这种分区，你需要决定程序运行时使用哪个数据库，这可以通过一个中间层如MySQL代理来实现，虽然它还处于Alpha阶段，但它的思想很好，并且已经有很多人将其用于生产环境，它运行在服务器上，响应端口3306上的请求，然后将这些查询通过高速语言如lua实现的某些逻辑转发给后端适当的服务器。

   　　其次你也可以在应用程序中指定将查询发到哪些服务器，这也是最灵活的方法，你可以完全控制整个决策过程，你也可以使用master_pos_wait检查从数据库实例，看看它们是否有足够的计算资源。还有你使用的编程语言或Web框架可能也会提供这方面的支持，如果你还不清楚，可以查询它们的文档。

   　　你还可以研究一下Continuent Tungsten，DBIx::DBCluster for Perl以及SQLRelay，它们支持许多不同的编程语言和数据库。同样，CMS如Drupal也支持多种只读的从数据库，你只需要启用这个功能即可。

   　　使用这种架构需要考虑的另一个事情是，是否要使用主数据库实例，以及何时使用，一般说来，所有插入，更新和删除操作都应放在主数据库实例上完成，所有的查询操作都放在从数据库实例上完成。例如，如果某个用户对博客文章发表了注释，此时如果直接使用从数据库，可能无法完成，因为MySQL复制架构会存在滞后，此时从数据库中可能还没有那篇博文。

   　　检查过时数据是一个更好的方法，如果你有报告查询在夜间运行，这种方法可能工作得很好，你只需要确保复制赶得上进度即可。

   　　另一个方法是通过版本号跟踪数据库变更，在读取数据之前确定数据是否是最新的版本。

   　　最后，MySQL提供了一个函数master_pos_wait，它可以确定从数据库更新到哪个时间点了。

   　　2、功能分区

   　　你可能已经使用到功能分区，使用功能分区时，需要创建一个生产数据库的副本用于不同目的，如其中一个用于数据仓库和报告，另一个用于文本搜索等。

   　　通过负载均衡使MySQL变得更好

   　　如果你的从数据库已经有些只读数据，你可能需要实现负载均衡，将流量平均分配到各个从数据库，实现方法有多种，如随机分配，最少连接法，响应速度最快法，或某种加权平均法，虽然某些硬件负载均衡设备可以提供负载均衡功能，但它们往往是设计用于均衡网络流量，并没有提供数据库相关的均衡功能。

   　　幸运的是有很多软件解决方案，LVS项目就是一个不错的候选，它已经发展得相当成熟稳定，它提供了类似DNS轮询的负载均衡算法，但是在IP层实现的，速度非常快。此外，也有很多项目是建立在LVS基础之上的，包括wackamole，它是基于对等网络的，因此不会发生单点故障，还有一个值得推荐的项目是ultramonkey。

   　　小结

   　　MySQL提供了许多高级特性可以实现无限制的规模扩展，视不同应用环境有不同的最佳解决方案，因此需要在用于生产数据库之前，最好先对各种解决方案进行充分了解，并尽量搭建与生产环境负载相当的测试平台进行测试。