mysql水平分表和垂直分表和数据库分区

分表是分散数据库压力的好方法。

分表，最直白的意思，就是将一个表结构分为多个表，然后，可以再同一个库里，也可以放到不同的库。

当然，首先要知道什么情况下，才需要分表。个人觉得单表记录条数达到百万到千万级别时就要使用分表了。

1，分表的分类

1>纵向分表

将本来可以在同一个表的内容，人为划分为多个表。（所谓的本来，是指按照关系型数据库的第三范式要求，是应该在同一个表的。）

分表理由：根据数据的活跃度进行分离，（因为不同活跃的数据，处理方式是不同的）

案例：

对于一个博客系统，文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。所以，在进行数据库结构设计的时候，就应该考虑分表，首先是纵向分表的处理。

这样纵向分表后：

首先存储引擎的使用不同，冷数据使用MyIsam 可以有更好的查询数据。活跃数据，可以使用Innodb ,可以有更好的更新速度。

其次，对冷数据进行更多的从库配置，因为更多的操作时查询，这样来加快查询速度。对热数据，可以相对有更多的主库的横向分表处理。

其实，对于一些特殊的活跃数据，也可以考虑使用memcache ,redis

  之类的缓存，等累计到一定量再去更新数据库。或者mongodb 一类的nosql 数据库，这里只是举例，就先不说这个。

2>横向分表

字面意思，就可以看出来，是把大的表结构，横向切割为同样结构的不同表，如，用户信息表，user_1,user_2 等。表结构是完全一样，但是，根据某些特定的规则来划分的表，如根据用户ID来取模划分。

分表理由：根据数据量的规模来划分，保证单表的容量不会太大，从而来保证单表的查询等处理能力。

案例：同上面的例子，博客系统。当博客的量达到很大时候，就应该采取横向分割来降低每个单表的压力，来提升性能。例如博客的冷数据表，假如分为100个表，当同时有100万个用户在浏览时，如果是单表的话，会进行100万次请求，而现在分表后，就可能是每个表进行1万个数据的请求（因为，不可能绝对的平均，只是假设），这样压力就降低了很多很多。

坚信数据库的物理设计在对高级数据库的性能影响上远比其他因素重要。给大家说一下经过专家对Oracle的研究，他们解释了为什么拙劣的物理设计是数据库停机（无论是有计划的还是没计划的）背后的主要原因。但在这点上俺还是坚持DBA如果想要高性能的数据库就必须在数据库的物理设计上多思考的观点，这样才能减少响应时间使终端用户满意而不是引来骂声一片。

 

今天的文章是MySQL5.1的发布带来了设计超强动力数据库的强有力的武器，任何MySQL的DBA都应该尽快学习并使用它。俺觉得如果能很好滴使用这个5.1版带来的新特性，DBA可以使自己管理的VLDB（不知道什么是VLDB？告诉你，是好大好大的数据库的意思，Very Large DB）或数据仓库奇迹般的获得巨大的性能提升。

 

什么是数据库分区？

 

数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。

 

分区主要有两种形式：//这里一定要注意行和列的概念（row是行，column是列）

 

水平分区（Horizontal Partitioning） 这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。

 

举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。（注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份）

 

垂直分区（Vertical Partitioning） 这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。

 

举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

 

在数据库供应商开始在他们的数据库引擎中建立分区（主要是水平分区）时，DBA和建模者必须设计好表的物理分区结构，不要保存冗余的数据（不同表中同时都包含父表中的数据）或相互联结成一个逻辑父对象（通常是视图）。这种做法会使水平分区的大部分功能失效，有时候也会对垂直分区产生影响。

 

在MySQL 5.1中进行分区

 

     MySQL5.1中最激动人心的新特性应该就是对水平分区的支持了。这对MySQL的使用者来说确实是个好消息，而且她已经支持分区大部分模式：

 

         Range（范围）C 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。

 

         Hash（哈希）C 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。

 

         Key（键值）C 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

 

         List（预定义列表）C 这种模式允许系统通过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上运行通过。

 

如何进行实际分区

 

看看分区的实际效果吧。我们建立几个同样的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 命令支持返回分区表以及元数据的索引。

 

对其操作的命令和实现的维护功能有（比对全表的操作还多）：

 

ADD PARTITION

 

DROP PARTITION

 

COALESCE PARTITION

 

REORGANIZE PARTITION

 

ANALYZE PARTITION

 

CHECK PARTITION

 

OPTIMIZE PARTITION

 

REBUILD PARTITION

 

REPAIR PARTITION