总结下Mysql分表分库的策略及应用

末蓝、 2023-10-17 15:39 48阅读 0赞

上月前面试某公司，对于mysql分表的思路，当时简要的说了下hash算法分表，以及discuz分表的思路，  
但是对于新增数据自增id存放的设计思想回答的不是很好(笔试+面试整个过程算是OK过了，因与个人预期的薪酬不太理想而忍痛放弃.)，在此再深究下mysql 分表优化之类的设计思路方案.先来闲扯下发文目的：

**为什么要分表和分区？**

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

**什么是分表？**

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

**什么是分区？**

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

**[mysql][]分表和分区有什么联系呢？**  
1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。  
2.分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。  
3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。  
4.表分区相对于分表，操作方便，不需要创建子表。

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

1.分表

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

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

2.分库

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

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

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

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

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

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

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

**先谈谈分表的几种方式：**

**1、mysql集群**

事实它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写压力。从而提升数据库性能。

**2、自定义规则分表**

大表可以按照业务的规则来分解为多个子表。通常为以下几种类型，也可自己定义规则。

![复制代码][copycode.gif]

1 Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
    2 Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
    3 Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
    4 List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。
    5 composite（复合模式） –以上模式的组合使用

![复制代码][copycode.gif]

以聊天信息表为例：

我事先建100个这样的表，message\_00,message\_01,message\_02……….message\_98,message\_99.然后根据用户的ID来判断这个用户的聊天信息放到哪张表里面，你可以用hash的方式来获得，可以用求余的方式来获得，方法很多，各人想各人的吧。下面用hash的方法来获得表名：

![复制代码][copycode.gif]

<?php
    function get_hash_table($table,$userid) {
     $str = crc32($userid);
     if($str<0){
      $hash = "0".substr(abs($str), 0, 1);
     }else{
      $hash = substr($str, 0, 2);
     }
     return $table."_".$hash;
    }   
    echo get_hash_table('message' , 'user18991');     //结果为message_10
    echo get_hash_table('message' , 'user34523');    //结果为message_13
    ?>

![复制代码][copycode.gif]

说明一下，上面的这个方法，告诉我们user18991这个用户的消息都记录在message\_10这张表里，user34523这个用户的消息都记录在message\_13这张表里，读取的时候，只要从各自的表中读取就行了。

优点：避免一张表出现几百万条数据，缩短了一条sql的执行时间

缺点：当一种规则确定时，打破这条规则会很麻烦，上面的例子中我用的hash算法是crc32，如果我现在不想用这个算法了，改用md5后，会使同一个用户的消息被存储到不同的表中，这样数据乱套了。扩展性很差。

3，利用merge存储引擎来实现分表

我觉得这种方法比较适合，那些没有事先考虑，而已经出现了得，数据查询慢的情况。这个时候如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了，现在一张表要分成几十张表，甚至上百张表，这样sql语句是不是要重写呢？举个例子，我很喜欢举例子

mysql>show engines;的时候你会发现mrg\_myisam其实就是merge。

![复制代码][copycode.gif]

mysql> CREATE TABLE IF NOT EXISTS `user1` (
     ->   `id` int(11) NOT NULL AUTO_INCREMENT,
     ->   `name` varchar(50) DEFAULT NULL,
     ->   `sex` int(1) NOT NULL DEFAULT '0',
     ->   PRIMARY KEY (`id`)
     -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
    Query OK, 0 rows affected (0.05 sec)   
    mysql> CREATE TABLE IF NOT EXISTS `user2` (
     ->   `id` int(11) NOT NULL AUTO_INCREMENT,
     ->   `name` varchar(50) DEFAULT NULL,
     ->   `sex` int(1) NOT NULL DEFAULT '0',
     ->   PRIMARY KEY (`id`)
     -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
    Query OK, 0 rows affected (0.01 sec)   
    mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0);
    Query OK, 1 row affected (0.00 sec)   
    mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);
    Query OK, 1 row affected (0.00 sec)   
    mysql> CREATE TABLE IF NOT EXISTS `alluser` (
     ->   `id` int(11) NOT NULL AUTO_INCREMENT,
     ->   `name` varchar(50) DEFAULT NULL,
     ->   `sex` int(1) NOT NULL DEFAULT '0',
     ->   INDEX(id)
     -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;
    Query OK, 0 rows affected, 1 warning (0.00 sec)   
    mysql> select id,name,sex from alluser;
    +----+--------+-----+
    | id | name   | sex |
    +----+--------+-----+
    |  1 | 张映    |   0 |
    |  1 | tank   |   1 |
    +----+--------+-----+
    2 rows in set (0.00 sec)   
    mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0);
    Query OK, 1 row affected (0.00 sec)   
    mysql> select id,name,sex from user2
     -> ;
    +----+-------+-----+
    | id | name  | sex |
    +----+-------+-----+
    |  1 | tank  |   1 |
    |  2 | tank2 |   0 |
    +----+-------+-----+
    2 rows in set (0.00 sec) 
    mysql> CREATE TABLE IF NOT EXISTS `user1` (  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  ->   `name` varchar(50) DEFAULT NULL,  ->   `sex` int(1) NOT NULL DEFAULT '0',  ->   PRIMARY KEY (`id`)  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.05 sec)  mysql> CREATE TABLE IF NOT EXISTS `user2` (  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  ->   `name` varchar(50) DEFAULT NULL,  ->   `sex` int(1) NOT NULL DEFAULT '0',  ->   PRIMARY KEY (`id`)  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.01 sec)  mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0); Query OK, 1 row affected (0.00 sec)  mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1); Query OK, 1 row affected (0.00 sec)  mysql> CREATE TABLE IF NOT EXISTS `alluser` (  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  ->   `name` varchar(50) DEFAULT NULL,  ->   `sex` int(1) NOT NULL DEFAULT '0',  ->   INDEX(id)  -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ; Query OK, 0 rows affected, 1 warning (0.00 sec)  mysql> select id,name,sex from alluser;
    +----+--------+-----+
    | id | name   | sex |
    +----+--------+-----+
    |  1 |  张映   |   0 |
    |  1 | tank   |   1 |
    +----+--------+-----+
    2 rows in set (0.00 sec)
    mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0); Query OK, 1 row affected (0.00 sec)  mysql> select id,name,sex from user2  -> ;
    +----+-------+-----+
    | id | name  | sex |
    +----+-------+-----+
    |  1 | tank  |   1 |
    |  2 | tank2 |   0 |
    +----+-------+-----+
    2 rows in set (0.00 sec)

![复制代码][copycode.gif]

从上面的操作中，我不知道你有没有发现点什么？假如我有一张用户表user，有50W条数据，现在要拆成二张表user1和user2，每张表25W条数据，

INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000
    INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000

这样我就成功的将一张user表，分成了二个表，这个时候有一个问题，代码中的sql语句怎么办，以前是一张表，现在变成二张表了，代码改动很大，这样给程序员带来了很大的工作量，有没有好的办法解决这一点呢？办法是把以前的user表备份一下，然后删除掉，上面的操作中我建立了一个alluser表，只把这个alluser表的表名改成user就行了。但是，不是所有的mysql操作都能用的

a，如果你使用 alter table 来把 merge 表变为其它表类型，到底层表的映射就被丢失了。取而代之的，来自底层 myisam 表的行被复制到已更换的表中，该表随后被指定新类型。

b，网上看到一些说replace不起作用，我试了一下可以起作用的。晕一个先

![复制代码][copycode.gif]

mysql> UPDATE alluser SET sex=REPLACE(sex, 0, 1) where id=2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0   
    mysql> select * from alluser;
    +----+--------+-----+
    | id | name   | sex |
    +----+--------+-----+
    |  1 | 张映    |   0 |
    |  1 | tank   |   1 |
    |  2 | tank2  |   1 |
    +----+--------+-----+
    3 rows in set (0.00 sec) 
    mysql> UPDATE alluser SET sex=REPLACE(sex, 0, 1) where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0  mysql> select * from alluser;
     +----+--------+-----+
     | id | name   | sex |
     +----+--------+-----+
     |  1 | 张映    |   0 |
     |  1 | tank   |   1 |
     |  2 | tank2  |   1 |
     +----+--------+-----+
     3 rows in set (0.00 sec)

![复制代码][copycode.gif]

c，一个 merge 表不能在整个表上维持 unique 约束。当你执行一个 insert，数据进入第一个或者最后一个 myisam 表（取决于 insert\_method 选项的值）。mysql 确保唯一键值在那个 myisam 表里保持唯一，但不是跨集合里所有的表。

d,当你创建一个 merge 表之时，没有检查去确保底层表的存在以及有相同的机构。当 merge 表被使用之时，mysql 检查每个被映射的表的记录长度是否相等，但这并不十分可靠。如果你从不相似的 myisam 表创建一个 merge 表，你非常有可能撞见奇怪的问题。

c和d在网上看到的，没有测试，大家试一下吧。

优点：扩展性好，并且程序代码改动的不是很大

缺点：这种方法的效果比第二种要差一点

**三、总结一下**

上面提到的三种方法，我实际做过二种，第一种和第二种。第三种没有做过，所以说的细一点。哈哈。做什么事都有一个度，超过个度就过变得很差，不能一味的做数据库服务器集群，硬件是要花钱买的，也不要一味的分表，分出来1000表，mysql的存储归根到底还以文件的形势存在硬盘上面，一张表对应三个文件，1000个分表就是对应3000个文件，这样检索起来也会变的很慢。我的建议是

方法1和方法2结合的方式来进行分表  
方法1和方法3结合的方式来进行分表

我的二个建议适合不同的情况，根据个人情况而定，我觉得会有很多人选择方法1和方法3结合的方式

未完待续.............

无论从事什么行业，只要做好两件事就够了，一个是你的专业、一个是你的人品，专业决定了你的存在，人品决定了你的人脉，剩下的就是坚持，用善良專業和真诚赢取更多的信任。不忘初心 方得始终！

来源:http://www.cnblogs.com/phpper/p/6937896.html

[Link 1]: http://www.2cto.com/database/
[mysql]: http://www.2cto.com/database/MySQL/
[Link 2]: http://lib.csdn.net/base/mysql
[copycode.gif]: http://common.cnblogs.com/images/copycode.gif