Chinaunix

标题: MySQL版《一周一议》之mysql sharding方案分享（积分已转账-2012-12-19） [打印本页]

作者: chinafenghao 时间: 2012-12-04 10:03
标题: MySQL版《一周一议》之mysql sharding方案分享（积分已转账-2012-12-19）
20积分已转账，请注意查收！

上周的话题比较轻松,主要讨论公司及DBA的人数配比有关系,一般看来DBA在研发团队的配比5%是比较合适的。
现在隆重宣布上周获得《锋利的SQL》的用户是：ruochen
其他参与讨论的用户到将至少获得20积分,积分是需要管理员手工加，加上后会发站内信的。

本周的话题是偏技术方面的。
大家都知道mysql是一个轻量级数据库,主流的INNODB引擎使用B+树和hash索引,hash索引是自适应的。由于INNODB是索引聚集表，如果当一个表的数据量达到上E，或者数据量达到几十个G的时候，性能就会下降的比较厉害。

针对这种情况,比较主流的一种处理方法就是Sharding.关于sharding更多的信息，推荐两篇博文给大家.
http://dbanotes.net/database/database_sharding.html 开源数据库Sharding技术
http://www.dedecms.com/knowledge ... 2012/0820/9172.html mysqlshareding可扩展设计

今天我们的话题就是mysql sharding方案分享。

1、分享你经历和处理过的mysql sharding方案,包括业务场景,架构,使用的软件,实施中的难点。
活动奖励:
1、每周会评选一位最活跃的用户，有一本mysql相关的书籍送出,本周送出的书籍是《Cassandra实战指南》。
2、由于是话题讨论，所以每位参加者都能获得适当的积分奖励。最低20分，最高不限,^_^想要赚分的朋友也可以来凑热闹哟。

作者: cenalulu 时间: 2012-12-04 10:25
本帖最后由 cenalulu 于 2012-12-07 11:39 编辑

先说说sharding的好处：
1. 表查询效率提升
2. 表运维代价降低
3. 服务器扩容便捷

如果单纯为了实现【1】其实sharding的可操作性还不如堆slave来的直接。
一般迫不得已sharding的情况主要有以下集中：
1. 单表数据库超大，已经超过服务器硬盘容量上限的1/2 （插槽全满）
2. 业务增长太快，每天疲于应付服务器扩容。
3. 大表更新需求很频繁，DDL的操作时间已经超过可接受范围

再说说sharding必须注意的：
1. sharding key一定要选的好，分布也要考虑周到。否则数据分布不均匀，或者add node导致大数据量漂移就是悲剧。
2. sharding不代表 availablity，可用性也要做好。一般双主绑在一起作为一个node比较靠谱。当然用dynamo的思路去做更好。

作者: huzi1986 时间: 2012-12-04 13:20
我来说一下吧

MySQL sharding 主要分为两种，分别为水平和垂直

一、业务场景
1、MMORPG网络游戏日志库：由于国家文化部门审计需要，需保留3个月的日志数据。过期数据可删除，采用的是程序按天自动分表，程序自动删除3个月之前的分表。这种好处就是可以有效节约日志数据库磁盘空间。查询某一天的玩家行为分析非常快，基本上不会用到同时查询多天的某个角色日志的情况

2、网页游戏平台数据库： MERGE引擎：通过 MERGE引擎可以把user_1 到user_n等几十个表连接起来进行汇总查询，程序一般通过一定的算法对 user_1 user_n进行DML操作
      1、触发器：通过相关算法对user表分拆为user_1 user_n表，在user_1 user_n表的dml操作前，触发器会对 user表进行变更到一张总表，便于后台进行数据分析。
      2、CRC32取模：通过CRC32算法取模对 user_1 user_n表，每次操作会进行两次（insert,update,delete)同时对 user_1 user_n  和 user_total表进行操作，user_total是汇总表。
   3、MySQL 分区表：zabbix监控平台 history 表和trends等相关表。由于history 表比较大，数据保留一个月，采用存储过程按天分区，脚本定期删除一定时间前的分区，有效保证数据库数据不被扩涨大快。trends表采用按月分区。

作者: huzi1986 时间: 2012-12-04 13:26
当然象大公司 sharding后都可能会有中间层。中小公司一般都可能采取水平或者垂直分表方式

至于架构方面

目前我还主要用的是 Master-Master、Master-Slave-Slave

其它还有 MMM ,MHA ,MM+KEEPALIVED等高可用。目前公司都还达不到这个要求，所以，还是主要停留单机和 MS MM这种常见的架构

作者: kerlion 时间: 2012-12-05 10:38
提示: 作者被禁止或删除内容自动屏蔽

作者: lloydm 时间: 2012-12-05 23:15
提示: 作者被禁止或删除内容自动屏蔽

作者: xike2002 时间: 2012-12-06 11:39
这个话题很好，大家可以好好讨论一下。

作者: xike2002 时间: 2012-12-06 11:55
下面和大家聊一聊mysql sharding，欢迎大家的建议和意见，大家一起学习！

一.背景
　　我们知道，当数据库中的数据量越来越大时，不论是读还是写，压力都会变得越来越大。采用MySQL Replication多master多slave方案，在上层做负载均衡，虽然能够一定程度上缓解压力。但是当一张表中的数据变得非常庞大时，压力还是非常大的。试想，如果一张表中的数据量达到了千万甚至上亿级别的时候，不管是建索引，优化缓存等，都会面临巨大的性能压力。
二.定义
　　数据sharding，也称作数据切分，或分区。是指通过某种条件，把同一个数据库中的数据分散到多个数据库或多台机器上，以减小单台机器压力。
三.分类
　　数据分区根据切分规则，可以分为两类：
　　(1)垂直分区：以表为单位，把不同的表分散到不同的数据库或主机上。特点是规则简单，实施方便，适合业务之间耦合度低的系统。
　　(2)水平分区：以行为单位，将同一个表中的数据按照某种条件拆分到不同的数据库或主机上。特点是相对复杂，适合单表巨大的系统。
　　在实际情况中，有的时候把垂直分区和水平分区结合使用。
四.注意事项
　　下面我们所说的分区，主要是指水平分区。
　　(1)在实施分区前，我们可以查看所安装版本的mysql是否支持分区：

　mysql> show variables like "%partition%";

　　如果支持则会显示：
      +-------------------+-------+
      | Variable_name    | Value |
      +-------------------+-------+
      | have_partitioning | YES |
      +-------------------+-------+
　　(2)分区适用于一个表的所有数据和索引;不能只对数据分区而不对索引分区，反之亦然，同时也不能只对表的一部分进行分区。
　　(3)分区类型:
　　RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
　　LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
　　HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。
　　KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
　　无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。
　　(4) MySQL提供了许多修改分区表的方式。添加、删除、重新定义、合并或拆分已经存在的分区是可能的。所有这些操作都可以通过使用ALTER TABLE 命令的分区扩展来实现.
　　(5) 可以对已经存在的表进行分区，直接使用alter table命令即可。

作者: laputa73 时间: 2012-12-06 16:09
关注。
云时代，数据库的分片还是很必要的。
先百度了一下什么sharding...汗
http://eddysheng.iteye.com/blog/461393
mysql的分区表，怎么拆分到不同的数据库或者主机上？
拆分以后，还能够用普通的查询吗？

sharding最主要的问题还是跨片的查询，这个有什么好的解决方案?

作者: kellyseeme123 时间: 2012-12-06 16:50
mysql没用过，不过oracle里面也有这个相同的东西
关注下～

作者: ylky_2000 时间: 2012-12-07 10:39
数据库真不懂，除了写几个selct语句 from where之外，其他只有概念了。
围观中，请教一个问题吧，mysql中如何跟踪mysql语句的执行呢?类似sql server的事件探测器。

作者: chinafenghao 时间: 2012-12-07 11:36
@ylky_2000
你google下 explain profiling的用法。

作者: cenalulu 时间: 2012-12-07 11:40
2楼更新了讨论内容～大家拍砖哈

作者: ylky_2000 时间: 2012-12-07 11:43
谢谢。提供信息。

chinafenghao 发表于 2012-12-07 11:36
@ylky_2000
你google下 explain profiling的用法。

作者: chinafenghao 时间: 2012-12-07 13:22
@cenalulu
赞同。
不过我觉得sharding对数据更新能力提升也是很关键的。

作者: cenalulu 时间: 2012-12-07 14:34
回复 15# chinafenghao

哦，对！当传统m-s架构中的m成为性能瓶颈的时候，也是sharding接入的最好时机

作者: pandorabag 时间: 2012-12-07 16:43

huzi1986 发表于 2012-12-04 13:26
当然象大公司 sharding后都可能会有中间层。中小公司一般都可能采取水平或者垂直分表方式

至于架构 ...

MM 你采用什么方式避免的双主自增id问题

作者: 唯吾顾家三少 时间: 2012-12-07 18:13
新人留爪，看到好多很好的回复啊，感谢大家的无私分享。

谢谢楼主真么好的活动了。谢谢大家无私的分享经验。

作者: pitonas 时间: 2012-12-08 14:29
这个话题很好

作者: je50 时间: 2012-12-08 18:15
mysql没用过

作者: iask 时间: 2012-12-09 13:21
回复 17# pandorabag

M1:
auto_increment_increment = 2
auto_increment_increment = 1

M2:
auto_increment_increment = 2
auto_increment_increment = 2

作者: G8bao7 时间: 2012-12-09 19:51
谢谢lz和同学们的分享，支持一下！

鉴于自己目前公司业务数据量比较小，虽然也进行了简单sharding(ms)，但由于数据量远不够形成压力，希望有同学能分享下实际业务sharding碰到的问题

作者: bellszhu 时间: 2012-12-09 21:56
不太清楚sharding的概念。。。
sharding和散表的区别？？
因为我只散过表

作者: chinafenghao 时间: 2012-12-10 10:29
回复 23# bellszhu

参见一楼帖子的文章链接。

作者: huzi1986 时间: 2012-12-10 15:13
回复 17# pandorabag

目前所有使用双主的架构中，一般都使用 HA+MM，只有一个实例同时提供写功能。

作者: frogoscar 时间: 2012-12-10 20:39
学习了，看了网上几篇东西才知道 sharding这个概念，落后了啊。之前只是用过数据库。。。。。。。

作者: fanzhilin515 时间: 2012-12-11 02:51
新手，混点分用用。
mysql不是很懂，mark下

作者: zhangshengdong 时间: 2012-12-11 12:44
现在是越来越懒了，我把个人认为的mysql sharding写一下。

1.Master(W/R)    单实例读写master服务器的时代
2.Master (W) + Slaves (R) 下一步，慢慢地就发展到了Mater和Slave的时代，因为单实例无法承载应用的所有请求。所以，把所有的write queries(INSERT/UPDATE/DELETE)
  给予master，把所有或者近乎所有的 read queries给予slave。
3.Vertical Partitioning （垂直拆分）

*继续往后发展之后，可能不只是读请求超过负载，就连写请求也会超过负载。
*当出现write-heavy applications时候，我们该怎么办？
*使用Vertical Partitioning,把一些重业务分离出来（按库分离或者按表分离）。分别单独放在一个服务器，分担读写分离的请求。分散Top Master的写请求

基于上述的架构，会碰到的瓶颈：
（1):become harder and harder
  (2): Lose some JOIN-functionality
  (3):  if table grew so rapidly ，so
the performance of the host  wasn’t guranteed any more。
怎么办？
加更多的salve？
买更贵的服务器？
就算你怎么加，它都会hit limit。

4.当上述又得不到解决的时候，horizontal partitioning到来了。

*前面讨论的瓶颈，以及后时代的web数据管理。基于上述的理论解决。而这个早在1996年多人大型游戏online（MMO (Massive Multiplayer Online) Games world）就已经开始积极使用。

基于上图描述，根据上述的算法，针对一张photos表的分割，根据"%10”的算法，连接10台服务器的哪一台进行处理。（其实是有图片的，就是一个表根据"%10"的方法，分布到不同的服务器中），这个要从程序方面调用来解决了。

图我就不贴了，如果有兴趣的，我可以把ppt到时候贴出来。

作者: zhangshengdong 时间: 2012-12-11 12:51
多半做sharding都是用replication来做。拆库。拆表的话，应该跟应用层相关了。我个人认为的。

作者: visician 时间: 2012-12-11 19:04
好话题，关注

作者: fengfeng919 时间: 2012-12-11 21:32
我做准备找方法来做这个事情，前两天还发了一个求帮助的帖子！还请版主顶一下我这个需求，有经验的老师给点建议。

我目前的情况是多个数据库，所有表都使用innodb引擎共享表空间模式，其中有一张表目前已经多大于3千万条数据了，现在老大提出的直接办法是分表，当然也就是水平分表了，由于前端我们要根据ID去写和读，所以希望分表后能够按照顺序写表，例如第一张表300万条数据，然后从301就开始到第二张表去写，怎么样能够自动的去实现呢，默认的merge表好像是随机的而且不保证多张表的ID唯一。
我目前自己的想法是我手动建立好10张表，程序每次写完数据能够把最后一个ID覆盖写到一张临时表，然后我系统里脚步实时去查询这个临时表ID，如果大于300就创建第二张表，以此类推，不知道这样是否可行。

作者: mickey_51 时间: 2012-12-11 21:52

很好我也顶一顶

作者: 灵魂守卫者 时间: 2012-12-14 11:27
不错，支持一下

作者: fengfeng919 时间: 2012-12-14 12:54
版主们，给点建议呀？？？

作者: chinafenghao 时间: 2012-12-17 09:28
@fengfeng919
你这种分区方法基本不太可行哈。用脚本控制数据库，很不靠谱。
你要描述你的数据库瓶颈,大家根据你的瓶颈来指定方案，不一定要你老大说那种。他说的水平分表方式基本不可能在不修改业务代码的前提下完成。

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)