MySQL版《一周一议》之mysql sharding方案分享（积分已转账-2012-12-19）

chinafenghao

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

先说说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

我来说一下吧

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

当然 象大公司 sharding后都可能会有中间层。中小公司 一般都可能采取 水平或者垂直分表方式

至于 架构方面

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

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

xike2002

这个话题很好，大家可以好好讨论一下。

xike2002

下面和大家聊一聊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

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

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

kellyseeme123

mysql没用过，不过oracle里面也有这个相同的东西
关注下～