利用一致性哈希水平拆分MySql单表

关注

转载最新推荐文章于 2026-08-02 12:27:18 发布 328 阅读 AI 写同款· GEO 优化 ›

springboot集成sharding-shardingsphere做分库分表（一）一般中小公司没有能力去开发自己的框架，只能使用开源的，我们这里也使用开源框架sharding-jdbc来实现我们的分库分表。 1、建表一般情况下，我们会去建10个库，每个库是10张表，我们这里为了方便测试，我只建2个库，每个库里建10张表。 -------------下面是fincenter00库----------------- CREATE TABLE `fincenter00`.`account_profile_00` ( `id` bigint NOT NULL AUTO_INC 阅读详情

Sharding（切片）不是一门新技术，而是一个相对简朴的软件理念，就是当我们的数据库单机无法承受高强度的i/o时，我们就考虑利用 sharding 来把这种读写压力分散到各个主机上去。

所以Sharding 不是一个某个特定数据库软件附属的功能，而是在具体技术细节之上的抽象处理，是Horizontal Partitioning 水平扩展(或横向扩展)的解决方案，其主要目的是为突破单节点数据库服务器的 I/O 能力限制，注意这里是突破单点数据库服务器的“I/O”能力。

在MySql 5.1 中增加了对单表的 PARTITION（分区）支持，可以把一张很大的单表通过 partition 分区成很多物理文件，避免每次操作一个大文件，可以对读写新能有所提升，下面是一个 partition 分区的例子。

一张游戏的日志表，有几千万行的数据，记录了接近一年的游戏物品获取日志，如果不对它进行 partition 分区存储，每次统计和分析日志都会消耗大量的时间。然后我们新建一张分区表，把老的日志数据导入到新的数据，统计分析的时间就会节约很多。

<div><div><span class="pln" style="color:#000000;">CREATE TABLE </span><span class="str" style="color:#0880;">`xxxxxxxx`</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`crttm`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`srvid`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`evtid`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`aid`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`rid`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`itmid`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`itmnum`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`gdtype`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`gdnum`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">     </span></div><div><span class="str" style="color:#0880;">`islmt`</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">int</span><span class="pun" style="color:#66660;">(</span><span class="lit" style="color:#06666;">11</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> NOT NULL</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">KEY </span><span class="str" style="color:#0880;">`crttm`</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">`crttm`</span><span class="pun" style="color:#66660;">),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">  KEY </span><span class="str" style="color:#0880;">`itemid`</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">`itmid`</span><span class="pun" style="color:#66660;">),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">  KEY </span><span class="str" style="color:#0880;">`srvid`</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">`srvid`</span><span class="pun" style="color:#66660;">),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">  KEY </span><span class="str" style="color:#0880;">`gdtype`</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">`gdtype`</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;"> ENGINE</span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;">myisam DEFAULT CHARSET</span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;">utf8  </span></div><div><span class="pln" style="color:#000000;">PARTITION BY RANGE </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">crttm</span><span class="pun" style="color:#66660;">)</span><span class="pln" style="color:#000000;">   </span></div><div><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201303 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2014-04-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201304 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2014-05-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201305 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2014-06-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201306 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2014-07-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201307 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2014-08-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201308 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2014-09-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201309 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2014-10-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201310 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2014-11-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201311 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2014-12-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201312 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2015-01-01'</span><span class="pun" style="color:#66660;">)),</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pln" style="color:#000000;">PARTITION p201401 VALUES LESS THAN </span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">unix_timestamp</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'2015-02-01'</span><span class="pun" style="color:#66660;">))</span><span class="pln" style="color:#000000;">  </span></div><div><span class="pun" style="color:#66660;">);</span><span class="pln" style="color:#000000;"> </span></div></div>

对于这种业务场景，使用 mysql 的 partition 就已经足够了，但是对于 i/o 非常频繁的大表，单机垂直升级也已经支撑不了，存储已经不是影响其性能的主要原因，这时候就要用到sharding了。

我们一般会将一张大表的唯一键作为 hash 的 key，比如我们想要水平拆分的是一张拥有3千万行数据的用户表，我们可以利用唯一的字段用户id作为拆分的依据，这样就可以依据如下的方式，将用户表水平拆分成3张，下面是伪代码，将老的用户数据导入到新的3个被水平拆分的数据库中。

<div><span class="kwd" style="color:#0088;">if</span><span class="pln" style="color:#000000;"> userId </span><span class="pun" style="color:#66660;">%</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">3</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">==</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">0</span><span class="pun" style="color:#66660;">:</span></div><div><span class="pln" style="color:#000000;">	</span><span class="com" style="color:#8800;">#insert data in user_table (</span><span style="font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,宋体;"><span class="com" style="color:#8800;">user_table_0 </span></span><span style="font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,宋体;"><span class="com" style="color:#8800;">databaseip: 127.0.0.1)</span></span></div><div><div><span class="com" style="color:#8800;">elif userId % 3 == 1:</span></div><div><span class="com" style="color:#8800;">	</span><span class="com" style="color:#8800;">#insert data in user_table (</span><span style="font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,宋体;"><span class="com" style="color:#8800;">user_table_1 </span></span><span style="font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,宋体;"><span class="com" style="color:#8800;">databaseip: 127.0.0.2)</span></span></div></div><div><span class="com" style="color:#8800;">else:</span></div><div><span class="com" style="color:#8800;">	</span><span class="com" style="color:#8800;">#insert data in user_table (</span><span style="font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,宋体;"><span class="com" style="color:#8800;">user_table_2 </span></span><span style="font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,宋体;"><span class="com" style="color:#8800;">databaseip: 127.0.0.3)</span></span></div>

我们还会对每一个被拆分的数据库，做一个双主 master 的副本集备份，至于backup，我们则可以使用 percona的cluster来解决。它是比 mysql m/s 或者 m/m 更靠谱的方案。

http://www.percona.com/software/percona-xtradb-cluster

所以最后拆分的拓扑图大致如下：

利用一致性哈希水平拆分MySql单表 - snoopyxdy - snoopyxdy的博客

随着我们的业务增长，数据涨到5千万了，慢慢的发现3个sharding不能满足我们的需求了，因为服务器紧张，所以这时候BOSS打算再加2个sharding，以后会慢慢加到10个sharding。

所以我们得在之前的3台sharding服务器上分别执行导入数据代码，将数据根据新的hash规则导入到每台sharding服务器上。几乎5千万行数据每行都移动了一遍，如果服务器够牛逼，Mysql每秒的插入性能能高达 2000/s，即使这样整个操作，都要让服务暂停8个小时左右。这时候DBA的脸色已经不好看了，他应该是已经通宵在导数据了。

那有没有一种更好的办法，让添加或者删除 sharding 节点对整个分片系统的数据迁移量降低呢？

我们可以利用一致性哈希算法，把用户id散列到各个 sharding 节点，这样就可以保证添加和删除节点数据迁移影响较小。关于什么是一致性哈性算法，参考我的另一篇博客：

http://snoopyxdy.blog.163.com/blog/static/601174402012722102446720/

这里介绍一个Node.js模块，hashring，github主页地址如下，上面有demo和api文档：

https://github.com/3rd-Eden/node-hashring

这是一个使用的demo代码，我翻译了注释，供大家参考：

<div><div><span class="com" style="color:#8800;">// 加载模块，返回HashRing的构造函数</span></div><div><span class="com" style="color:#8800;">var HashRing = require('hashring');</span></div><div>
</div><div><span class="com" style="color:#8800;">//实例化HashRing，这个例子中，我们把各个服务器均匀的添加了，没有设置权重</span></div><div><span style="font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,宋体;"><span class="com" style="color:#8800;">// 设置了最大的缓冲区 10000</span></span></div><div><span class="com" style="color:#8800;">var ring = new HashRing([</span></div><div><span class="com" style="color:#8800;">    '127.0.0.1',</span></div><div><span class="com" style="color:#8800;">    '127.0.0.2',</span></div><div><span class="com" style="color:#8800;">    '127.0.0.3', </span></div><div><span class="com" style="color:#8800;">    '127.0.0.4'</span></div><div><span class="com" style="color:#8800;">  ], 'md5', {</span></div><div><span class="com" style="color:#8800;">    'max cache size': 10000</span></div><div><span class="com" style="color:#8800;">  });</span></div><div>
</div><div><span class="com" style="color:#8800;">//我们获取这个字符串的服务器ip</span></div><div><span class="com" style="color:#8800;">var server = ring.get('foo bar banana'); // returns 127.0.0.x</span></div><div><span class="com" style="color:#8800;">console.log(server)</span></div><div>
</div><div><span class="com" style="color:#8800;">// 如果你想把数据冗余的存储在多个服务器上</span></div><div><span class="com" style="color:#8800;">ring.range('foo bar banana', 2).forEach(function forEach(server) {</span></div><div><span class="com" style="color:#8800;">  console.log(server); // do stuff with your server</span></div><div><span class="com" style="color:#8800;">});</span></div><div>
</div><div><span style="font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,宋体;"><span class="com" style="color:#8800;">// 对环上移除或新增加一台服务器</span></span></div><div><span class="com" style="color:#8800;">ring.add('127.0.0.7').remove('127.0.0.1');</span></div><div>
</div><div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> server </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> ring</span><span class="pun" style="color:#66660;">.</span><span class="kwd" style="color:#0088;">get</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'foo bar banana'</span><span class="pun" style="color:#66660;">);</span><span class="pln" style="color:#000000;"> </span><span class="com" style="color:#8800;">// returns 127.0.0.x</span></div><div><span class="com" style="color:#8800;">console.log(server)</span></div></div>

接下来我们就要验证这种方式的可行性。

第一，假如我们有3万条数据，根据一致性哈希算法存储好了之后，这个算法是否能够较平均的将3万条数据分散到3台sharding服务器上。

第二，当数据量增加到5万，然后我们增加2台sharding服务器后，这个算法移动的数据量和最终每台服务器上的数据分布是如何的。

connHashStep1.js将3万用户数据通过一致性哈希算法存储在3台服务器上

<div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> </span><span class="typ" style="color:#66066;">HashRing</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">require</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'hashring'</span><span class="pun" style="color:#66660;">);</span></div><div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> ring </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">new</span><span class="pln" style="color:#000000;"> </span><span class="typ" style="color:#66066;">HashRing</span><span class="pun" style="color:#66660;">([</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.1'</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.2'</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.3'</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;"> </span></div><div><span class="pln" style="color:#000000;">  </span><span class="pun" style="color:#66660;">],</span><span class="pln" style="color:#000000;"> </span><span class="str" style="color:#0880;">'md5'</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">{</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'max cache size'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">10000</span></div><div><span class="pln" style="color:#000000;">  </span><span class="pun" style="color:#66660;">});</span></div><div>
</div><div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> record </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">{</span></div><div><span class="pln" style="color:#000000;">      </span><span class="str" style="color:#0880;">'127.0.0.1'</span><span class="pun" style="color:#66660;">:</span><span class="lit" style="color:#06666;">0</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.2'</span><span class="pun" style="color:#66660;">:</span><span class="lit" style="color:#06666;">0</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.3'</span><span class="pun" style="color:#66660;">:</span><span class="lit" style="color:#06666;">0</span></div><div><span class="pun" style="color:#66660;">};</span></div><div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> userMap </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">{}</span></div><div>
</div><div><span class="kwd" style="color:#0088;">for</span><span class="pun" style="color:#66660;">(</span><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;">=</span><span class="lit" style="color:#06666;">1</span><span class="pun" style="color:#66660;">;</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;"><=</span><span class="lit" style="color:#06666;">30000</span><span class="pun" style="color:#66660;">;</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;">++){</span></div><div><span class="pln" style="color:#000000;">	</span><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> userIdStr </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;">.</span><span class="pln" style="color:#000000;">toString</span><span class="pun" style="color:#66660;">();</span></div><div><span class="pln" style="color:#000000;">	</span><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> server </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> ring</span><span class="pun" style="color:#66660;">.</span><span class="kwd" style="color:#0088;">get</span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">userIdStr</span><span class="pun" style="color:#66660;">);</span></div><div><span class="pln" style="color:#000000;">	</span><span class="pln" style="color:#000000;">userMap</span><span class="pun" style="color:#66660;">[</span><span class="pln" style="color:#000000;">userIdStr</span><span class="pun" style="color:#66660;">]</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> server</span><span class="pun" style="color:#66660;">;</span></div><div><span class="pln" style="color:#000000;">	</span><span class="pln" style="color:#000000;">record</span><span class="pun" style="color:#66660;">[</span><span class="pln" style="color:#000000;">server</span><span class="pun" style="color:#66660;">]++;</span></div><div><span class="pun" style="color:#66660;">}</span></div><div>
</div><div><span class="pln" style="color:#000000;">console</span><span class="pun" style="color:#66660;">.</span><span class="pln" style="color:#000000;">log</span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">record</span><span class="pun" style="color:#66660;">);</span></div>

第一次利用一致性hash之后，每台服务器存储的用户数据。

<p style="margin-top:0px; margin-bottom:10px; padding-top:0px; padding-bottom:0px"><span class="pun" style="color:#66660;">{</span><span class="pln" style="color:#000000;"> </span><span class="str" style="color:#0880;">'127.0.0.1'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">9162</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;"> </span><span class="str" style="color:#0880;">'127.0.0.2'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">9824</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;"> </span><span class="str" style="color:#0880;">'127.0.0.3'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">11014</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">}</span></p>

connHashStep2.js将5万用户数据通过一致性哈希算法存储在3台服务器上，然后用户数据5万不改变，新增加2台sharding，查看新的5台sharding的用户数据存储情况以及计算移动的数据条数。

<div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> </span><span class="typ" style="color:#66066;">HashRing</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">require</span><span class="pun" style="color:#66660;">(</span><span class="str" style="color:#0880;">'hashring'</span><span class="pun" style="color:#66660;">);</span></div><div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> ring </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="kwd" style="color:#0088;">new</span><span class="pln" style="color:#000000;"> </span><span class="typ" style="color:#66066;">HashRing</span><span class="pun" style="color:#66660;">([</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.1'</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.2'</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.3'</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;"> </span></div><div><span class="pln" style="color:#000000;">  </span><span class="pun" style="color:#66660;">],</span><span class="pln" style="color:#000000;"> </span><span class="str" style="color:#0880;">'md5'</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">{</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'max cache size'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">10000</span></div><div><span class="pln" style="color:#000000;">  </span><span class="pun" style="color:#66660;">});</span></div><div>
</div><div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> record </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">{</span></div><div><span class="pln" style="color:#000000;">      </span><span class="str" style="color:#0880;">'127.0.0.1'</span><span class="pun" style="color:#66660;">:</span><span class="lit" style="color:#06666;">0</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.2'</span><span class="pun" style="color:#66660;">:</span><span class="lit" style="color:#06666;">0</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">    </span><span class="str" style="color:#0880;">'127.0.0.3'</span><span class="pun" style="color:#66660;">:</span><span class="lit" style="color:#06666;">0</span></div><div><span class="pun" style="color:#66660;">};</span></div><div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> userMap </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">{}</span></div><div><span class="pln" style="color:#000000;">  </span></div><div><span class="kwd" style="color:#0088;">for</span><span class="pun" style="color:#66660;">(</span><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;">=</span><span class="lit" style="color:#06666;">1</span><span class="pun" style="color:#66660;">;</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;"><=</span><span class="lit" style="color:#06666;">50000</span><span class="pun" style="color:#66660;">;</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;">++){</span></div><div><span class="pln" style="color:#000000;">	</span><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> userIdStr </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;">.</span><span class="pln" style="color:#000000;">toString</span><span class="pun" style="color:#66660;">();</span></div><div><span class="pln" style="color:#000000;">	</span><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> server </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> ring</span><span class="pun" style="color:#66660;">.</span><span class="kwd" style="color:#0088;">get</span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">userIdStr</span><span class="pun" style="color:#66660;">);</span></div><div><span class="pln" style="color:#000000;">	</span><span class="pln" style="color:#000000;">userMap</span><span class="pun" style="color:#66660;">[</span><span class="pln" style="color:#000000;">userIdStr</span><span class="pun" style="color:#66660;">]</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> server</span><span class="pun" style="color:#66660;">;</span></div><div><span class="pln" style="color:#000000;">	</span><span class="pln" style="color:#000000;">record</span><span class="pun" style="color:#66660;">[</span><span class="pln" style="color:#000000;">server</span><span class="pun" style="color:#66660;">]++;</span></div><div><span class="pun" style="color:#66660;">}</span></div><div>
</div><div><span class="pln" style="color:#000000;">console</span><span class="pun" style="color:#66660;">.</span><span class="pln" style="color:#000000;">log</span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">record</span><span class="pun" style="color:#66660;">);</span></div><div>
</div><div><span class="com" style="color:#8800;">//新增加2个sharding节点</span></div><div><span class="com" style="color:#8800;">var record2 = {</span></div><div><span class="com" style="color:#8800;">	</span><span class="com" style="color:#8800;">'127.0.0.1':0,</span></div><div><span class="com" style="color:#8800;">      '127.0.0.2':0,</span></div><div><span class="com" style="color:#8800;">      '127.0.0.3':0,</span></div><div><span class="com" style="color:#8800;">	</span><span class="com" style="color:#8800;">'127.0.0.4':0,</span></div><div><span class="com" style="color:#8800;">	</span><span class="com" style="color:#8800;">'127.0.0.5':0,</span></div><div><span class="com" style="color:#8800;">};</span></div><div><span class="com" style="color:#8800;">ring.add('127.0.0.4').add('127.0.0.5')</span></div><div>
</div><div><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> moveStep </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">0</span><span class="pun" style="color:#66660;">;</span></div><div><span class="kwd" style="color:#0088;">for</span><span class="pun" style="color:#66660;">(</span><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;">=</span><span class="lit" style="color:#06666;">1</span><span class="pun" style="color:#66660;">;</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;"><=</span><span class="lit" style="color:#06666;">50000</span><span class="pun" style="color:#66660;">;</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;">++){</span></div><div><span class="pln" style="color:#000000;">	</span><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> userIdStr </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> i</span><span class="pun" style="color:#66660;">.</span><span class="pln" style="color:#000000;">toString</span><span class="pun" style="color:#66660;">();</span></div><div><span class="pln" style="color:#000000;">	</span><span class="kwd" style="color:#0088;">var</span><span class="pln" style="color:#000000;"> server </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> ring</span><span class="pun" style="color:#66660;">.</span><span class="kwd" style="color:#0088;">get</span><span class="pun" style="color:#66660;">(</span><span class="pln" style="color:#000000;">userIdStr</span><span class="pun" style="color:#66660;">);</span></div><div><span class="pln" style="color:#000000;">	</span><span class="com" style="color:#8800;">//当用户的存储server改变，则计算移动</span></div><div><span class="com" style="color:#8800;">	</span><span class="com" style="color:#8800;">if(userMap[userIdStr] && userMap[userIdStr] != server){</span></div><div><span class="com" style="color:#8800;">		</span><span class="com" style="color:#8800;">userMap[userIdStr] = server;</span></div><div><span class="com" style="color:#8800;">		</span><span class="com" style="color:#8800;">moveStep++;</span></div><div><span class="com" style="color:#8800;">	</span><span class="com" style="color:#8800;">}</span></div><div><span class="com" style="color:#8800;">	</span><span class="com" style="color:#8800;">record2[server]++;</span></div><div><span class="com" style="color:#8800;">}</span></div><div><span class="com" style="color:#8800;">console.log(record2);</span></div><div><span class="com" style="color:#8800;">console.log('move step:'+moveStep);</span></div>

5万用户数据，存储在3台服务器上的数目：

<p style="margin-top:0px; margin-bottom:10px; padding-top:0px; padding-bottom:0px"><span class="pun" style="color:#66660;">{</span><span class="pln" style="color:#000000;"> </span><span class="str" style="color:#0880;">'127.0.0.1'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">15238</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;"> </span><span class="str" style="color:#0880;">'127.0.0.2'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">16448</span><span class="pun" style="color:#66660;">,</span><span class="pln" style="color:#000000;"> </span><span class="str" style="color:#0880;">'127.0.0.3'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">18314</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">}</span></p>

当我们sharding增加到5台，存储在5台服务器上的数目：

<div><span class="pun" style="color:#66660;">{</span><span class="pln" style="color:#000000;"> </span><span class="str" style="color:#0880;">'127.0.0.1'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">8869</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">  </span><span class="str" style="color:#0880;">'127.0.0.2'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">9972</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">  </span><span class="str" style="color:#0880;">'127.0.0.3'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">10326</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">  </span><span class="str" style="color:#0880;">'127.0.0.4'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">10064</span><span class="pun" style="color:#66660;">,</span></div><div><span class="pln" style="color:#000000;">  </span><span class="str" style="color:#0880;">'127.0.0.5'</span><span class="pun" style="color:#66660;">:</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">10769</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">}</span></div>

最终我们移动的用户数量：

<p style="margin-top:0px; margin-bottom:10px; padding-top:0px; padding-bottom:0px"><span class="pln" style="color:#000000;">move step</span><span class="pun" style="color:#66660;">:</span><span class="lit" style="color:#06666;">20833</span></p>

其实你会发现

<p style="margin-top:0px; margin-bottom:10px; padding-top:0px; padding-bottom:0px"><span class="lit" style="color:#06666;">20833</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">=</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">10064</span><span class="pln" style="color:#000000;"> </span><span class="pun" style="color:#66660;">+</span><span class="pln" style="color:#000000;"> </span><span class="lit" style="color:#06666;">10769</span><span class="pln" style="color:#000000;"> </span></p>

也就是说，我们只是将1-3节点的部分数据移动到了4,5节点，并没有多余的移动一行数据。根据上面的示例，如果是5千万数据，利用一致性哈希的算法，添加2个节点，仅需2-3小时就可以完成。

那么什么时候我们需要利用一致性哈希水平拆分数据库单表呢?

1、当我们拥有一个数据量非常大的单表，比如上亿条数据。

2、不仅数据量巨大，这个单表的访问读写也非常频繁，单机已经无法抗住 I/O 操作。

3、此表无事务性操作，如果涉及分布式事务是相当复杂的事情，在拆分此类表需要异常小心。

4、查询条件单一，对此表的查询更新条件常用的仅有1-2个字段，比如用户表中的用户id或用户名。

最后，这样的拆分也是会带来负面性的，当水平拆分了一个大表，不得不去修改应用程序或者开发db代理层中间件，这样会加大开发周期、难度和系统复杂性。

P.S 打算在公司试行这种方案，求大牛指点一二，看看有无错误和遗漏。

参考链接： http://lobert.iteye.com/blog/1955841

收录于