MySQL版《一周一議》之mysql sharding方案分享（積分已轉(zhuǎn)賬-2012-12-19）

chinafenghao

20積分已轉(zhuǎn)賬，請(qǐng)注意查收！

上周的話題比較輕松,主要討論公司及DBA的人數(shù)配比有關(guān)系,一般看來(lái)DBA在研發(fā)團(tuán)隊(duì)的配比5%是比較合適的。
現(xiàn)在隆重宣布上周獲得《鋒利的SQL》的用戶是：ruochen
其他參與討論的用戶到將至少獲得20積分,積分是需要管理員手工加，加上后會(huì)發(fā)站內(nèi)信的。

本周的話題是偏技術(shù)方面的。
大家都知道m(xù)ysql是一個(gè)輕量級(jí)數(shù)據(jù)庫(kù),主流的INNODB引擎使用B+樹和hash索引,hash索引是自適應(yīng)的。由于INNODB是索引聚集表，如果當(dāng)一個(gè)表的數(shù)據(jù)量達(dá)到上E，或者數(shù)據(jù)量達(dá)到幾十個(gè)G的時(shí)候，性能就會(huì)下降的比較厲害。

針對(duì)這種情況,比較主流的一種處理方法就是Sharding.關(guān)于sharding更多的信息，推薦兩篇博文給大家.
http://dbanotes.net/database/database_sharding.html 開源數(shù)據(jù)庫(kù)Sharding技術(shù)
http://www.dedecms.com/knowledge ... 2012/0820/9172.html mysqlshareding可擴(kuò)展設(shè)計(jì)


今天我們的話題就是mysql sharding方案分享。

1、分享你經(jīng)歷和處理過(guò)的mysql sharding方案,包括業(yè)務(wù)場(chǎng)景,架構(gòu),使用的軟件,實(shí)施中的難點(diǎn)。
活動(dòng)獎(jiǎng)勵(lì):
1、每周會(huì)評(píng)選一位最活躍的用戶，有一本mysql相關(guān)的書籍送出,本周送出的書籍是《Cassandra實(shí)戰(zhàn)指南》。
2、由于是話題討論，所以每位參加者都能獲得適當(dāng)?shù)姆e分獎(jiǎng)勵(lì)。最低20分，最高不限,^_^想要賺分的朋友也可以來(lái)湊熱鬧喲。

cenalulu

先說(shuō)說(shuō)sharding的好處：
1. 表查詢效率提升
2. 表運(yùn)維代價(jià)降低
3. 服務(wù)器擴(kuò)容便捷

如果單純?yōu)榱藢?shí)現(xiàn)【1】其實(shí)sharding的可操作性還不如堆slave來(lái)的直接。
一般迫不得已sharding的情況主要有以下集中：
1. 單表數(shù)據(jù)庫(kù)超大，已經(jīng)超過(guò)服務(wù)器硬盤容量上限的1/2 （插槽全滿）
2. 業(yè)務(wù)增長(zhǎng)太快，每天疲于應(yīng)付服務(wù)器擴(kuò)容。
3. 大表更新需求很頻繁，DDL的操作時(shí)間已經(jīng)超過(guò)可接受范圍

再說(shuō)說(shuō)sharding必須注意的：
1. sharding key一定要選的好，分布也要考慮周到。否則數(shù)據(jù)分布不均勻，或者add node導(dǎo)致大數(shù)據(jù)量漂移就是悲劇。
2. sharding不代表 availablity，可用性也要做好。一般雙主綁在一起作為一個(gè)node比較靠譜。當(dāng)然用dynamo的思路去做更好。

huzi1986

我來(lái)說(shuō)一下吧

MySQL sharding 主要分為兩種，分別為水平 和垂直

一、業(yè)務(wù)場(chǎng)景
    1、MMORPG網(wǎng)絡(luò)游戲日志庫(kù)：由于國(guó)家文化部門審計(jì)需要，需保留3個(gè)月的日志數(shù)據(jù)。過(guò)期數(shù)據(jù)可刪除，采用的是程序按天自動(dòng)分表，程序自動(dòng)刪除3個(gè)月之前的分表。這種好處就是可以有效節(jié)約日志數(shù)據(jù)庫(kù)磁盤空間。查詢某一天的玩家行為分析非�？�，基本上不會(huì)用到同時(shí)查詢多天的某個(gè)角色日志的情況

    2、網(wǎng)頁(yè)游戲平臺(tái)數(shù)據(jù)庫(kù)： MERGE引擎 ：通過(guò) MERGE引擎可以把user_1 到user_n等幾十個(gè)表 連接起來(lái)進(jìn)行匯總查詢，程序一般通過(guò)一定的算法對(duì) user_1 user_n進(jìn)行DML操作
        1、觸發(fā)器：通過(guò)相關(guān)算法對(duì)user表分拆 為user_1 user_n表，在user_1 user_n表的dml操作前，觸發(fā) 器會(huì)對(duì) user表進(jìn)行變更 到一張總表，便于后臺(tái)進(jìn)行數(shù)據(jù)分析。
        2、CRC32取模：通過(guò)CRC32算法取模 對(duì) user_1 user_n表，每次操作會(huì)進(jìn)行兩次（insert,update,delete)同時(shí)對(duì) user_1 user_n  和 user_total表進(jìn)行操作，user_total是匯總表。
     3、MySQL 分區(qū)表：zabbix監(jiān)控平臺(tái) history 表 和trends等相關(guān)表。由于history 表比較大，數(shù)據(jù)保留一個(gè)月，采用存儲(chǔ)過(guò)程按天分區(qū)，腳本定期刪除一定時(shí)間前的分區(qū)，有效保證數(shù)據(jù)庫(kù)數(shù)據(jù)不被擴(kuò)漲大快。trends表采用按月分區(qū) 。

huzi1986

當(dāng)然 象大公司 sharding后都可能會(huì)有中間層。中小公司 一般都可能采取 水平或者垂直分表方式

至于 架構(gòu)方面

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

其它還有 MMM ,MHA ,MM+KEEPALIVED等高可用。目前公司都還達(dá)不到這個(gè)要求，所以，還是主要停留單機(jī) 和 MS  MM這種常見的架構(gòu)

xike2002

這個(gè)話題很好，大家可以好好討論一下。

xike2002

下面和大家聊一聊mysql sharding，歡迎大家的建議和意見，大家一起學(xué)習(xí)！

一.背景
　　我們知道，當(dāng)數(shù)據(jù)庫(kù)中的數(shù)據(jù)量越來(lái)越大時(shí)，不論是讀還是寫，壓力都會(huì)變得越來(lái)越大。采用MySQL Replication多master多slave方案，在上層做負(fù)載均衡，雖然能夠一定程度上緩解壓力。但是當(dāng)一張表中的數(shù)據(jù)變得非常龐大時(shí)，壓力還是 非常大的。試想，如果一張表中的數(shù)據(jù)量達(dá)到了千萬(wàn)甚至上億級(jí)別的時(shí)候，不管是建索引，優(yōu)化緩存等，都會(huì)面臨巨大的性能壓力。
二.定義
　　數(shù)據(jù)sharding，也稱作數(shù)據(jù)切分，或分區(qū)。是指通過(guò)某種條件，把同一個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)分散到多個(gè)數(shù)據(jù)庫(kù)或多臺(tái)機(jī)器上，以減小單臺(tái)機(jī)器壓力。
三.分類
　　數(shù)據(jù)分區(qū)根據(jù)切分規(guī)則，可以分為兩類：
　　(1)垂直分區(qū)：以表為單位，把不同的表分散到不同的數(shù)據(jù)庫(kù)或主機(jī)上。特點(diǎn)是規(guī)則簡(jiǎn)單，實(shí)施方便，適合業(yè)務(wù)之間耦合度低的系統(tǒng)。
　　(2)水平分區(qū)：以行為單位，將同一個(gè)表中的數(shù)據(jù)按照某種條件拆分到不同的數(shù)據(jù)庫(kù)或主機(jī)上。特點(diǎn)是相對(duì)復(fù)雜，適合單表巨大的系統(tǒng)。
　　在實(shí)際情況中，有的時(shí)候把垂直分區(qū)和水平分區(qū)結(jié)合使用。
四.注意事項(xiàng)
　　下面我們所說(shuō)的分區(qū)，主要是指水平分區(qū)。
　　(1)在實(shí)施分區(qū)前，我們可以查看所安裝版本的mysql是否支持分區(qū)：

　mysql> show variables like "%partition%";

　　如果支持則會(huì)顯示：
        +-------------------+-------+
        | Variable_name     | Value |
        +-------------------+-------+
        | have_partitioning | YES   |
        +-------------------+-------+
　　(2)分區(qū)適用于一個(gè)表的所有數(shù)據(jù)和索引;不能只對(duì)數(shù)據(jù)分區(qū)而不對(duì)索引分區(qū)，反之亦然，同時(shí)也不能只對(duì)表的一部分進(jìn)行分區(qū)。
　　(3)分區(qū)類型:
　　RANGE 分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。
　　LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。
　　HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。
　　KEY 分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。
　　無(wú)論使用何種類型的分區(qū)，分區(qū)總是在創(chuàng)建時(shí)就自動(dòng)的順序編號(hào)，且從0開始記錄。當(dāng)有一新行插入到一個(gè)分區(qū)表中時(shí)，就是使用這些分區(qū)編號(hào)來(lái)識(shí)別正確的分區(qū)。
　　(4) MySQL提供了許多修改分區(qū)表的方式。添加、刪除、重新定義、合并或拆分已經(jīng)存在的分區(qū)是可能的。所有這些操作都可以通過(guò)使用ALTER TABLE 命令的分區(qū)擴(kuò)展來(lái)實(shí)現(xiàn).
　　(5) 可以對(duì)已經(jīng)存在的表進(jìn)行分區(qū)，直接使用alter table命令即可。

laputa73

關(guān)注。
云時(shí)代，數(shù)據(jù)庫(kù)的分片還是很必要的。
先百度了一下什么sharding...汗
http://eddysheng.iteye.com/blog/461393
mysql的分區(qū)表，怎么拆分到不同的數(shù)據(jù)庫(kù)或者主機(jī)上？
拆分以后，還能夠用普通的查詢嗎？

sharding最主要的問(wèn)題還是跨片的查詢，這個(gè)有什么好的解決方案?

kellyseeme123

mysql沒用過(guò)，不過(guò)oracle里面也有這個(gè)相同的東西
關(guān)注下～