mysql分表

一、概述 分表是个目前算是比较炒的比较流行的概念,特别是在大负载的情况下,分表是一个良好分散数据库压力的好方法。 首先要了解为什么要分表,分表的好处是什么。我们先来大概了解以下一个数据库执行SQL的过程: 接收到SQL --> 放入SQL执行队列--> 使用分析器分解SQL --> 按照分析结果进行数据的提取或者修改--> 返回处理结果 当然,这个流程图不一定正确,这只是我自己主观意识上这么我认为。那么这个处理过程当中,最容易出现问题的是什么?就是说,如果前一个SQL没有执行完毕的话,后面的SQL是不会执行的,因为为了保证数据的完整性,必须对数据表文件进行锁定,包括共享锁和独享锁两种锁定。共享锁是在锁定的期间,其它线程也可以访问这个数据文件,但是不允许修改操作,相应的,独享锁就是整个文件就是归一个线程所有,其它线程无法访问这个数据文件。一般MySQL中最快的存储引擎MyISAM,它是基于表锁定的,就是说如果一锁定的话,那么整个数据文件外部都无法访问,必须等前一个操作完成后,才能接收下一个操作,那么在这个前一个操作没有执行完成,后一个操作等待在队列里无法执行的情况叫做阻塞,一般我们通俗意义上叫做“锁表”。 锁表直接导致的后果是什么?就是大量的SQL无法立即执行,必须等队列前面的SQL 全部执行完毕才能继续执行。这个无法执行的SQL就会导致没有结果,或者延迟严重,影响用户体验。 特别是对于一些使用比较频繁的表,比如SNS系统中的用户信息表、论坛系统中的帖子表等等,都是访问量大很大的表,为了保证数据的快速提取返回给用户,必须使用一些处理方式来解决这个问题,这个就是我今天要聊到的分表技术。 分表技术顾名思义,就是把若干个存储相同类型数据的表分成几个表分表存储,在提取数据的时候,不同的用户访问不同的表,互不冲突,减少锁表的几率。比如,目前保存用户分表有两个表,一个是user_1表,还有一个是user_2 表,两个表保存了不同的用户信息,user_1 保存了前10万的用户信息,user_2保存了后10万名用户的信息,现在如果同时查询用户heiyeluren1 和heiyeluren2 这个两个用户,那么就是分表从不同的表提取出来,减少锁表的可能。 我下面要讲述的两种分表方法我自己都没有实验过,不保证准确能用,只是提供一个 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 思路。下面关于分表的例子我假设是在一个贴吧系统的基础上来进行处理和构建的。 (如果没有用过贴吧的用户赶紧Google一下) 二、基于基础表的分表处理 这个基于基础表的分表处理方式大致的思想就是:一个主要表,保存了所有的基本信息,如果某个项目需要找到它所存储的表,那么必须从这个基础表中查找出对应的表 名等项目,好直接访问这个表。如果觉得这个基础表速度不够快,可以完全把整个基础表保存在缓存或者内存中,方便有效的查询。 我们基于贴吧的情况,构建假设如下的3张表: 1. 贴吧版块表: 保存贴吧中版块的信息 2. 贴吧主题表:保存贴吧中版块中的主题信息,用于浏览 3. 贴吧回复表:保存主题的原始内容和回复内容 “贴吧版块表”包含如下字段: 版块ID board_id int(10) 版块名称board_name char(50) 子表ID table_id smallint(5) 产生时间created datetime “贴吧主题表”包含如下字段: 主题ID topic_id int(10) 主题名称topic_name char(255) 版块ID board_id int(10) 创建时间created datetime “贴吧回复表”的字段如下: 回复ID reply_id int(10) 回复内容reply_text text 主题ID topic_id int(10) 版块ID board_id int(10) 创建时间created datetime 那么上面保存了我们整个贴吧中的表结构信息,三个表对应的关系是: 版块--> 多个主题 主题--> 多个回复 那么就是说,表文件大小的关系是: 版块表文件< 主题表文件< 回复表文件 所以基本可以确定需要对主题表和回复表进行分表,已增加我们数据检索查询更改时候的速度和性能。 看了上面的表结构,会明显发现,在“版块表”中保存了一个"table_id"字段,这个字段就是用于保存一个版块对应的主题和回复都是分表保存在什么表里的。 比如我们有一个叫做“PHP”的贴吧,board_id是1,子表ID也是1,那么这条记录就是: board_id | board_name | table_id | created 1 | PHP | 1 | 2007-01-19 00:30:12 相应的,如果我需要提取“PHP”吧里的所有主题,那么就必须按照表里保存的table_id 来组合一个存储了主题的表名称,比如我们主题表的前缀是“topic_”,那么组合出来“PHP”吧对应的主题表应该是:“topic_1”,那么我们执行: 基于Hash算法的分表处理 我们知道Hash表就是通过某个特殊的Hash算法计算出的一个值,这个值必须是惟一的,并且能够使用这个计算出来的值查找到需要的值,这个叫做哈希表。 我们在分表里的hash算法跟这个思想类似:通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名,然后访问相应的表。 继续拿上面的贴吧来说,每个贴吧有版块名称和版块ID,那么这两项值是固定的,并且是惟一的,那么我们就可以考虑通过对这两项值中的一项进行一些运算得出一个目标表的名称。 现在假如我们针对我们这个贴吧系统,假设系统最大允许1亿条数据,考虑每个表保存100万条记录,那么整个系统就不超过100个表就能够容纳。按照这个标准,我们假设在贴吧的版块ID上进行hash,获得一个key值,这个值就是我们的表名,然后访问相应的表。 我们构造一个简单的hash算法: function get_hash($id){ $str = bin2hex($id); $hash = substr($str, 0, 4); if (strlen($hash)<4){ $hash = str_pad($hash, 4, "0"); } return $hash; } 算法大致就是传入一个版块ID值,然后函数返回一个4位的字符串,如果字符串长度不够,使用0进行补全。 比如:get_hash(1),输出的结果是“3100”,输入:get_hash(23819),得到的结果是:3233,那么我们经过简单的跟表前缀组合,就能够访问这个表了。那么我们需要访问ID为1的内容时候哦,组合的表将是:topic_3100、reply_3100,那么就可以直接对目标表进行访问了。 当然,使用hash算法后,有部分数据是可能在同一个表的,这一点跟hash表不同,hash 表是尽量解决冲突,我们这里不需要,当然同样需要预测和分析表数据可能保存的表名。 如果需要存储的数据更多,同样的,可以对版块的名字进行hash操作,比如也是上面的二进制转换成十六进制,因为汉字比数字和字母要多很多,那么重复几率更小,但是可能组合成的表就更多了,相应就必须考虑一些其它的问题。 归根结底,使用hash 方式的话必须选择一个好的hash算法,才能生成更多的表,然 数据查询的更迅速。 【优点hash算法直接得出目标表名称,效率很高】通过 【劣势】扩展性比较差,选择了一个hash算法,定义了多少数据量,以后只能在这个数据量上跑,不能超过过这个数据量,可扩展性稍差 四、其它问题 1. 搜索问题 现在我们已经进行分表了,那么就无法直接对表进行搜索,因为你无法对可能系统中已经存在的几十或者几百个表进行检索,所以搜索必须借助第三方的组件来进行,比如Lucene作为站内搜索引擎是个不错的选择。 2. 表文件问题 我们知道MySQL的MyISAM引擎每个表都会生成三个文件,*.frm、*.MYD、*.MYI 三个文件,分表用来保存表结构、表数据和表索引。Linux下面每个目录下的文件数量最好不要超过1000个,不然检索数据将更慢,那么每个表都会生成三个文件,相应的如果分表超过300个表,那么将检索非常慢,所以这时候就必须再进行分,比如在进行数据库的分离。 使用基础表,我们可以新增加一个字段,用来保存这个表保存在什么数据。使用Hash 的方式,我们必须截取hash值中第几位来作为数据库的名字。这样,完好的解决这个问题。 二、一,先说一下为什么要分表 当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。 根据个人经验,mysql执行一个sql的过程如下: 1,接收到sql;2,把sql放到排队队列中;3,执行sql;4,返回执行结果。在这个执行过程中最花时间在什么地方呢?第一,是排队等待的时间,第二,sql的执行时间。其实这二个是一回事,等待的同时,肯定有sql在执行。所以我们要缩短sql的执行时间。 mysql中有一种机制是表锁定和行锁定,为什么要出现这种机制,是为了保证数据的完整性,我举个例子来说吧,如果有二个sql都要修改同一张表的同一条数据,这个时候怎么办呢,是不是二个sql都可以同时修改这条数据呢?很显然mysql对这种情况的处理是,一种是表锁定(myisam存储引擎),一个是行锁定(innodb存储引擎)。表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。如果数据太多,一次执行的时间太长,等待的时间就越长,这也是我们为什么要分表的原因。 二,分表 1,做mysql集群,例如:利用mysql cluster ,mysql proxy,mysql replication,drdb等等 有人会问mysql集群,根分表有什么关系吗?虽然它不是实际意义上的分表,但是它启到了分表的作用,做集群的意义是什么呢?为一个数据库减轻负担,说白了就是减少sql排队队列中的sql的数量,举个例子:有10个sql请求,如果放在一个数据库服务器的排队队列中,他要等很长时间,如果把这10个sql请求,分配到5个数据库服务器的排队队列中,一个数据库服务器的队列中只有2个,这样等待时间是不是大大的缩短了呢?这已经很明显了。所以我把它列到了分表的范围以内,我做过一些mysql的集群: linux mysql proxy 的安装,配置,以及读写分离 mysql replication 互为主从的安装及配置,以及数据同步 优点:扩展性好,没有多个分表后的复杂操作(php代码) 缺点:单个表的数据量还是没有变,一次操作所花的时间还是那么多,硬件开销大。 2,预先估计会出现大数据量并且访问频繁的表,将其分为若干个表 这种预估大差不差的,论坛里面发表帖子的表,时间长了这张表肯定很大,几十万,几百万都有可能。聊天室里面信息表,几十个人在一起一聊一个晚上,时间长了,这张表的数据肯定很大。像这样的情况很多。所以这种能预估出来的大数据量表,我们就事先分出个N个表,这个N是多少,根据实际情况而定。以聊天信息表为例: 我事先建100个这样的表, message_00,message_01,message_02……….message_98,message_99.然后根据用户的ID 来判断这个用户的聊天信息放到哪张表里面,你可以用hash的方式来获得,可以用求余的方式来获得,方法很多,各人想各人的吧。下面用hash的方法来获得表名: 查看复制打印? < ?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 ?> < ?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 ?> 说明一下,上面的这个方法,告诉我们user18991这个用户的消息都记录在message_10这张表里,user34523这个用户的消息都记录在message_13这张表里,读取的时候,只要从各自的表中读取就行了。 优点:避免一张表出现几百万条数据,缩短了一条sql的执行时间 缺点:当一种规则确定时,打破这条规则会很麻烦,上面的例子中我用的hash算法是crc32,如果我现在不想用这个算法了,改用md5后,会使同一个用户的消息被存储到不同的表中,这样数据乱套了。扩展性很差。 3,利用merge存储引擎来实现分表 我觉得这种方法比较适合,那些没有事先考虑,而已经出现了得,数据查询慢的情况。这个时候如果要把已有的大数据量表分开比较痛苦,最痛苦的事就是改代码,因为程序里面的sql语句已经写好了,现在一张表要分成几十张表,甚至上百张表,这样sql 语句是不是要重写呢?举个例子,我很喜欢举子 mysql>show engines;的时候你会发现mrg_myisam其实就是merge。 查看复制打印? 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) 从上面的操作中,我不知道你有没有发现点什么?假如我有一张用户表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不起作用,我试了一下可以起作用的。晕一个先 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) 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结合的方式