MySQL数据库性能优化之分区分表分库

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

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

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

分表的分类

**1、纵向分表**

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

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

案例：

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

这样纵向分表后：

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

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

其实，对于一些特殊的活跃数据，也可以考虑使用memcache ,redis之类的缓存，等累计到一定量再去更新数据库。或者mongodb 一类的nosql 数据库，这里只是举例，就先不说这个。

**2、横向分表**

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

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

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

延伸：为什么要分表和分区？

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去 *** 作它。

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候 *** 作的还是大表名字，db自动去组织分区的数据。

**MySQL分表和分区有什么联系呢？**

1、都能提高mysql的性高，在高并发状态下都有一个良好的表现。

2、分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

3、分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。

4、表分区相对于分表， *** 作方便，不需要创建子表。

我们知道对于大型的互联网应用，数据库单表的数据量可能达到千万甚至上亿级别，同时面临这高并发的压力。Master-Slave结构只能对数据库的读能力进行扩展，写 *** 作还是集中在Master中，Master并不能无限制的挂接Slave库，如果需要对数据库的吞吐能力进行进一步的扩展，可以考虑采用分库分表的策略。

**1、分表**

在分表之前，首先要选中合适的分表策略（以哪个字典为分表字段，需要将数据分为多少张表），使数据能够均衡的分布在多张表中，并且不影响正常的查询。在企业级应用中，往往使用org_id(组织主键)做为分表字段，在互联网应用中往往是userid。在确定分表策略后，当数据进行存储及查询时，需要确定到哪张表里去查找数据，

数据存放的数据表 = 分表字段的内容 % 分表数量

**2、分库**

分表能够解决单表数据量过大带来的查询效率下降的问题，但是不能给数据库的并发访问带来质的提升，面对高并发的写访问，当Master无法承担高并发的写入请求时，不管如何扩展Slave服务器，都没有意义了。我们通过对数据库进行拆分，来提高数据库的写入能力，即所谓的分库。分库采用对关键字取模的方式，对数据库进行路由。

数据存放的数据库=分库字段的内容%数据库的数量

**3、即分表又分库**

数据库分表可以解决单表海量数据的查询性能问题，分库可以解决单台数据库的并发访问压力问题。

当数据库同时面临海量数据存储和高并发访问的时候，需要同时采取分表和分库策略。一般分表分库策略如下：

中间变量 = 关键字%（数据库数量*单库数据表数量）

库 = 取整（中间变量/单库数据表数量）

表 = （中间变量%单库数据表数量）

实例：

1、分库分表

很明显，一个主表（也就是很重要的表，例如用户表）无限制的增长势必严重影响性能，分库与分表是一个很不错的解决途径，也就是性能优化途径，现在的案例是我们有一个1000多万条记录的用户表members,查询起来非常之慢，同事的做法是将其散列到100个表中，分别从members0到members99，然后根据mid分发记录到这些表中，牛逼的代码大概是这样子：

复制代码 代码如下:

<?php

for($i=0$i<100$i++ ){

//echo "CREATE TABLE db2.members{$i} LIKE db1.members

"

echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}

"

}

?>

2、不停机修改mysql表结构

同样还是members表，前期设计的表结构不尽合理，随着数据库不断运行，其冗余数据也是增长巨大，同事使用了下面的方法来处理：

先创建一个临时表：

/*创建临时表*/

CREATE TABLE members_tmp LIKE members

然后修改members_tmp的表结构为新结构，接着使用上面那个for循环来导出数据，因为1000万的数据一次性导出是不对的，mid是主键，一个区间一个区间的导，基本是一次导出5万条吧，这里略去了

接着重命名将新表替换上去：

/*这是个颇为经典的语句哈*/

RENAME TABLE members TO members_bak,members_tmp TO members

就是这样，基本可以做到无损失，无需停机更新表结构，但实际上RENAME期间表是被锁死的，所以选择在线少的时候 *** 作是一个技巧。经过这个 *** 作，使得原先8G多的表，一下子变成了2G多。

MySQL 5.5的发布带来了许多增强的功能，虽然已经报道了很多增强功能，如半同步复制，但大家却忽略了分区方面的增强，有时甚至还对其真正意义产生了误解，在这篇文章中，我们希望解释一下这些很酷的增强，特别是我们大多数人还没有完全理解的地方。51CTO向您推荐《MySQL数据库入门与精通教程》。

非整数列分区

任何使用过分区的人应该都遇到过不少问题，特别是面对非整数列分区时，MySQL 5.1只能处理整数列分区，如果你想在日期或字符串列上进行分区，你不得不使用函数对其进行转换。

MySQL 5.5中新增了两类分区方法，RANG和LIST分区法，同时在新的函数中增加了一个COLUMNS关键词。我们假设有这样一个表：

1.  CREATE TABLE expenses (  

2.    expense_date DATE NOT NULL,  

3.    category VARCHAR(30),  

4.    amount DECIMAL (10,3)  

5.  ) 

如果你想使用MySQL 5.1中的分区类型，那你必须将类型转换成整数，需要使用一个额外的查找表，到了MySQL 5.5中，你可以不用再进行类型转换了，如：

1.  ALTER TABLE expenses  

2.  PARTITION BY LIST COLUMNS (category)  

3.  (  

4.    PARTITION p01 VALUES IN ( 'lodging', 'food'),  

5.    PARTITION p02 VALUES IN ( 'flights', 'ground transportation'),  

6.    PARTITION p03 VALUES IN ( 'leisure', 'customer entertainment'),  

7.    PARTITION p04 VALUES IN ( 'communications'),  

8.    PARTITION p05 VALUES IN ( 'fees')  

9.  ) 

这样的分区语句除了更加易读外，对数据的组织和管理也非常清晰，上面的例子只对category列进行分区。

在MySQL 5.1中使用分区另一个让人头痛的问题是date类型（即日期列），你不能直接使用它们，必须使用YEAR或TO_DAYS转换这些列，如：

1.  /* 在MySQL 5.1中*/  

2.  CREATE TABLE t2  

3.  (  

4.    dt DATE 

5.  )  

6.  PARTITION BY RANGE (TO_DAYS(dt))  

7.  (  

8.    PARTITION p01 VALUES LESS THAN (TO_DAYS('2007-01-01')),  

9.    PARTITION p02 VALUES LESS THAN (TO_DAYS('2008-01-01')),  

10.   PARTITION p03 VALUES LESS THAN (TO_DAYS('2009-01-01')),  

11.   PARTITION p04 VALUES LESS THAN (MAXVALUE)) 

12.  

13. SHOW CREATE TABLE t2 \G  

14. *************************** 1. row ***************************  

15.        Table: t2  

16. Create Table: CREATE TABLE `t2` (  

17.   `dt` date DEFAULT NULL 

18. ) ENGINE=MyISAM DEFAULT CHARSET=latin1  

19. /*!50100 PARTITION BY RANGE (TO_DAYS(dt))  

20. (PARTITION p01 VALUES LESS THAN (733042) ENGINE = MyISAM,  

21.  PARTITION p02 VALUES LESS THAN (733407) ENGINE = MyISAM,  

22.  PARTITION p03 VALUES LESS THAN (733773) ENGINE = MyISAM,  

23.  PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ 

看上去非常糟糕，当然也有变通办法，但麻烦确实不少。使用YEAR或TO_DAYS定义一个分区的确让人费解，查询时不得不使用赤裸列，因为加了函数的查询不能识别分区。

但在MySQL 5.5中情况发生了很大的变化，现在在日期列上可以直接分区，并且方法也很简单。

1.  /*在MySQL 5.5中*/  

2.  CREATE TABLE t2  

3.  (  

4.    dt DATE 

5.  )  

6.  PARTITION BY RANGE COLUMNS (dt)  

7.  (  

8.    PARTITION p01 VALUES LESS THAN ('2007-01-01'),  

9.    PARTITION p02 VALUES LESS THAN ('2008-01-01'),  

10.   PARTITION p03 VALUES LESS THAN ('2009-01-01'),  

11.   PARTITION p04 VALUES LESS THAN (MAXVALUE)) 

12.  

13. SHOW CREATE TABLE t2 \G  

14. *************************** 1. row ***************************  

15.        Table: t2  

16. Create Table: CREATE TABLE `t2` (  

17.   `dt` date DEFAULT NULL 

18. ) ENGINE=MyISAM DEFAULT CHARSET=latin1  

19. /*!50500 PARTITION BY RANGE  COLUMNS(dt)  

20. (PARTITION p01 VALUES LESS THAN ('2007-01-01') ENGINE = MyISAM,  

21.  PARTITION p02 VALUES LESS THAN ('2008-01-01') ENGINE = MyISAM,  

22.  PARTITION p03 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM,  

23.  PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */ 

在这里，通过函数定义和通过列查询之间没有冲突，因为是按列定义的，我们在定义中插入的值是保留的。

多列分区

COLUMNS关键字现在允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区，你可能在官方文档中已经看到了一些例子，如： 

1.  CREATE TABLE p1 (  

2.    a INT,  

3.    b INT,  

4.    c INT 

5.  )  

6.  PARTITION BY RANGE COLUMNS (a,b)  

7.  (  

8.    PARTITION p01 VALUES LESS THAN (10,20),  

9.    PARTITION p02 VALUES LESS THAN (20,30),  

10.   PARTITION p03 VALUES LESS THAN (30,40),  

11.   PARTITION p04 VALUES LESS THAN (40,MAXVALUE),  

12.   PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)  

13. ) 

14.  

15. CREATE TABLE p2 (  

16.   a INT,  

17.   b INT,  

18.   c INT 

19. )  

20. PARTITION BY RANGE COLUMNS (a,b)  

21. (  

22.   PARTITION p01 VALUES LESS THAN (10,10),  

23.   PARTITION p02 VALUES LESS THAN (10,20),  

24.   PARTITION p03 VALUES LESS THAN (10,30),  

25.   PARTITION p04 VALUES LESS THAN (10,MAXVALUE),  

26.   PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)  

27. ) 

同样还有PARTITION BY RANGE COLUMNS (a,b,c)等其它例子。由于我很长时间都在使用MySQL 5.1的分区，我对多列分区的含义不太了解，LESS THAN (10,10)是什么意思？如果下一个分区是LESS THAN (10,20)会发生什么？相反，如果是(20,30)又会如何？

所有这些问题都需要一个答案，在回答之前，他们需要更好地理解我们在做什么。

开始时可能有些混乱，当所有分区有一个不同范围的值时，实际上，它只是在表的一个列上进行了分区，但事实并非如此，在下面的例子中：

1.  CREATE TABLE p1_single (  

2.    a INT,  

3.    b INT,  

4.    c INT 

5.  )  

6.  PARTITION BY RANGE COLUMNS (a)  

7.  (  

8.    PARTITION p01 VALUES LESS THAN (10),  

9.    PARTITION p02 VALUES LESS THAN (20),  

10.   PARTITION p03 VALUES LESS THAN (30),  

11.   PARTITION p04 VALUES LESS THAN (40),  

12.   PARTITION p05 VALUES LESS THAN (MAXVALUE)  

13. ) 

它和前面的表p1不一样，如果你在表p1中插入（10,1,1），它将会进入第一个分区，相反，在表p1_single中，它将会进入第二个分区，其原因是(10,1)小于(10,10)，如果你仅仅关注第一个值，你还没有意识到你在比较一个元组，而不是一个单一的值。

现在我们来分析一下最难懂的地方，当你需要确定某一行应该放在哪里时会发生什么？你是如何确定类似(10,9) <(10,10)这种运算的值的？答案其实很简单，当你对它们进行排序时，使用相同的方法计算两条记录的值。

1.  a=10  

2.  b=9  

3.  (a,b) < (10,10) ?  

4.   

5.  # evaluates to:  

6.   

7.  (a < 10)  

8.  OR 

9.  ((a = 10) AND ( b < 10))  

10.  

11. # which translates to:  

12.  

13. (10 < 10)  

14. OR 

15. ((10 = 10) AND ( 9 < 10)) 

如果有三列，表达式会更长，但不会更复杂。你首先在第一个项目上测试小于运算，如果有两个或更多的分区与之匹配，接着就测试第二个项目，如果不止一个候选分区，那还需要测试第三个项目。

下图所显示的内容表示将遍历三条记录插入到使用以下代码定义的分区中：

(10,10),

(10,20),

(10,30),

(10, MAXVALUE)

 

 

图 2 元组比较。当第一个值小于分区定义的第一个范围时，那么该行将属于这里了。

 

 

图 3 元组比较。当第一个值等于分区定义的第一个范围，我们需要比较第二个项目，如果它小于第二个范围，那么该行将属于这里了。

 

 

图 4 元组比较。当第一个值和第二个值等于他们对应的范围时，如果元组不小于定义的范围，那么它就不属于这里，继续下一步。

 

 

图 5 元组比较。在下一个范围时，第一个项目是等于，第二个项目是小于，因此元组更小，那么该行就属于这里了。

在这些图的帮助下，我们对插入一条记录到多列分区表的步骤有了更深的了解，这些都是理论上的，为了帮助你更好地掌握新功能，我们再来看一个更高级一点的例子，对于比较务实的读者更有意义，下面是表的定义脚本：

1.  CREATE TABLE employees (  

2.    emp_no int(11) NOT NULL,  

3.    birth_date date NOT NULL,  

4.    first_name varchar(14) NOT NULL,  

5.    last_name varchar(16) NOT NULL,  

6.    gender char(1) DEFAULT NULL,  

7.    hire_date date NOT NULL 

8.  ) ENGINE=MyISAM  

9.  PARTITION BY RANGE  COLUMNS(gender,hire_date)  

10. (PARTITION p01 VALUES LESS THAN ('F','1990-01-01') ,  

11.  PARTITION p02 VALUES LESS THAN ('F','2000-01-01') ,  

12.  PARTITION p03 VALUES LESS THAN ('F',MAXVALUE) ,  

13.  PARTITION p04 VALUES LESS THAN ('M','1990-01-01') ,  

14.  PARTITION p05 VALUES LESS THAN ('M','2000-01-01') ,  

15.  PARTITION p06 VALUES LESS THAN ('M',MAXVALUE) ,  

16.  PARTITION p07 VALUES LESS THAN (MAXVALUE,MAXVALUE) 

和上面的例子不同，这个例子更好理解，第一个分区用来存储雇佣于1990年以前的女职员，第二个分区存储股用于1990-2000年之间的女职员，第三个分区存储所有剩下的女职员。对于分区p04到p06，我们策略是一样的，只不过存储的是男职员。最后一个分区是控制情况。

看完后你可能要问，我怎么知道某一行存储在那个分区中的？有两个办法，第一个办法是使用与分区定义相同的条件作为查询条件进行查询。

---