Mysql 切分规则及其相应机制简介

标签： mysql 规则简介 | 发表时间：2012-01-04 21:52 | 作者：

出处：http://www.iteye.com

前段时间看了一下Mysql数据库切分方面的知识，感觉某些点还挺有技巧的，决定小小的对自己所看的知识点进行一下总结。

随着企业的不断发展，需要存储的数据不断加大，以往把数据放在单一的数据库中的做法越来越成为存取数的瓶颈，因此就对数据库提出了以下的一些要求：

1、为了降低单台数据库负载，引入数据库切分。

2、考虑到大多数数据库操作大都“多读少写”，因此，在数据库中引入“读写分离”机制。

3、为了数据库容灾，在数据库中引入备库的思想。

基于以上需要mysql数据库可以从以下几方面入手进行改造：

一、数据库水平切分

考虑一个情况，一个表中现在有5000w条数据，在这个表中增加（insert）一条新的数据，insert完毕后，数据库会针对这张表重新建立索引，5000w行数据建立索引的系统开销是不容忽视的。因此为了解决以上问题，我们引入了第一个数据切分的方式：数据库内的水平切分。这种切分方式是对数据通过一系列的切分规则，将数据分布到一个数据库的不同表中，比如将article分为article_001,article_002等子表，若干个子表水平拼合有组成了逻辑上一个完整的article表。但是这种方式仍然将读取限定在了一台数据库，因此由此产生了另外一种切分：物理上的水平切分，即将数据分布到不同的DB服务器上，通过路由规则路由访问特定的数据库，这样一来每次访问面对的就不是单台服务器了，而是N台服务器，这样就可以降低单台机器的负载压力。

那通过哪些方法能够将数据库拆分呢，总结了下，主要有几下几点：

1、按号段分：即按照某一个字段的区间来分库、分表，如用户表，可按照Id为1——100的用户存放在 DB1,Id为101——200的用户存放在 DB2。这样做的优点是具有方便的迁移性，可以方便的将数据库进行拆分；但缺点是数据分布不均。

2、hash取模分：将数据按照某种规则进行hash运算，结果作为存到哪个库、哪个表的依据。这种分库、分表的优点是数据可以分布的很均匀；但这种方式的缺点同样突出，首先是数据迁移时会很麻烦，同时由于数据分配很均衡也就导致了数据并不能按照机器性能分摊。

3、在认证库中保存数据库配置：即创建一个中心库，存放了数据路由的规则，每条数据到底存放在什么位置，中心库中都能够有记录。这种方式优点是灵活性强，具有一对一的关系；但缺点是每次查询之前都要多一次查询，性能大打折扣。

二、数据库的读写分离策略

由于数据库大多是“多读少写”的，同时由于写数据存在锁表的机制，因此在数据库集群内可以采用“读写分离”的机制来提高性能。具体做法是：在数据库集群内部制定一台数据库为master，另外几台为slave，并都与master通信，对数据库所有的写操作都在master端发生，而其他slave与master共同来负责读的任务，当master端写完数据后将数据传递给slave（其实是slave向master请求），这样就能做到数据的一致性。利用这种机制分散了对数据库读的负载，同时也能够控制写数据时锁表对数据库的影响。其中把数据从master端复制到slave端的操作由mysql的replication机制保证。

mysql的replication机制：

replication机制的步骤如下图所示：

1、首先master在其配置中打开bin log，然后slave通过i/o线程链接到master，请求master传递bin log日志，同时请求中包括了此次需要的日志的起始标志位。

2、master收到请求后从接收到的标志位开始给slave返回内容，同时返回此次日志发送到达的记录位置，作为一下次slave请求的起始标志位。

3、slave将得到的内容记录在relay log中，同时受到的起始位置信心记录在mysql的master-info文件中。

4、另外开启一个线程，解析日志，并执行，这样master端的数据都都同步到了slave端。

其中3、4步骤是同时执行。因此记录到relay log的信息是不断的执行，然后擦除的，所以中继日志的开销很小，并通常将其存入OS的缓存中，。

但是复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

那从master端到底是复制什么内容到slave端了，一般有两种类型：

1、基于语句的复制：指日志中记录的是master端执行过的写数据库的sql语言，然后slave获取到这些sql语言后再次在自己的数据库中执行一次。这样做的好处整个复制去slave端的内容数据量小，而且清晰易懂。但由于sql中存在着now（）等函数，带有这种函数的sql传递到slave端执行时now（）值早已变化了，因此对着这种情况此种复制方法需要特殊处理。

2、基于记录的复制：指日志中记录的是实际数据的改变，然后复制到slave端后只需执行相应的改变即可，这样的好处是对于某些语句高效，缺点是日志不直观。

e.g 1 :

mysql> INSERT INTO summary_table(col1, col2, sum_col3) values SELECT col1, col2, sum(col3) FROM enormous_table GROUP BY col1, col2;

对于这种sql语句，显然第二种复制方式对于slave数据库来说更为高效。

e.g 2 :

mysql> UPDATE enormous_table SET col1 = 0;

而对着这种sql语言，第一种复制方式就只用执行一次sql语句即可，而第二种方式需要依次改变 enormous_table表的每条记录，因此第一种更为高效。

其实，仔细想想，以上的replication机制会存在问题，假设，当master端已经更新了数据A，但是还没有来得及传递到slave端，而此时两个读请求同时请求到master和slave的数据A，这样两个请求获取到得数据A将是不同的，这样就会产生“脏数据”的问题。这个问题是读写分离中遇到的最常见的问题之一，为了解决这个问题，可以采用mysql提供的proxy机制。

mysql提供的proxy机制：

proxy是一个位于client端和mysql的server端的小程序，是一个连接池，用lua脚本实现。

proxy的处理过程对于client端透明，client只需要把链接数据库的ip和端口改成proxy服务器的就行。

1、解决读写分离：client端请求时， proxy 截取出当前的请求是insert、update还是select，然后把 insert和update请求发送到Master中，把select请求发送到Slave中，这样就解决了读写分离的问题。

2、同步延迟：在master端增加只含有一个字段的自增表，当master端有数据更新时便向自增表更新记录。同时slave端也存在一个自增表，当Client通过Proxy进行数据读取时，Proxy可以先向Master和Slave的 Count_table表发送查询请求，当二者的数据相同时，Proxy可以认定 Master和Slave的数据状态是一致的，然后把select请求发送到Slave服务器上，否则就发送到Master上。如下图所示：