通過sysbench的oltp_read_write測試來模擬業(yè)務(wù)壓力、以此來給指定的硬件環(huán)境配置一份比較合理的MySQL配置文件。
站在用戶的角度思考問題，與客戶深入溝通，找到山丹網(wǎng)站設(shè)計與山丹網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設(shè)計與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：成都網(wǎng)站設(shè)計、網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、主機域名、網(wǎng)頁空間、企業(yè)郵箱。業(yè)務(wù)覆蓋山丹地區(qū)。
環(huán)境介紹
硬件配置
請點擊輸入圖片描述
軟件環(huán)境
請點擊輸入圖片描述
優(yōu)化層級與指導(dǎo)思想
優(yōu)化層級
MySQL數(shù)據(jù)庫優(yōu)化可以在多個不同的層級進(jìn)行，常見的有：
SQL優(yōu)化
參數(shù)優(yōu)化
架構(gòu)優(yōu)化
本文重點關(guān)注：參數(shù)優(yōu)化
指導(dǎo)思想
日志先行 -- 一個事務(wù)能否成功提交的關(guān)鍵是日志是否成功落盤，與數(shù)據(jù)沒有太大的關(guān)系；也就是說對寫的優(yōu)化可以表述為各方面的資源向?qū)懖僮鲀A斜。
瓶頸分析 -- 通過show global status 的各個計數(shù)器的值基本上就能分析出當(dāng)前瓶頸所在，再結(jié)合一些簡單的系統(tǒng)層面的監(jiān)控工具如top iostat 就能明確瓶頸。
整體性能是“讀”“寫”之間的再平衡。
容量： 看硬件
InnoDB?最大容量64TB ，存儲引擎將 InnoDB 表 保存在一個 表空間內(nèi)（ 原始磁盤分區(qū)，由數(shù)個文件創(chuàng)建）。這樣， 表大小 能超過 單獨文件最大容量 。
MySQL 3.22( MyISAM )限制表大小 4GB ，最大表尺寸增加到65536TB（2567 – 1字節(jié)）。最大有效表尺寸通常是由 操作系統(tǒng) 對 文件大小限制 決定的， 不是 由MySQL內(nèi)部限制決定。
最多 20億個表 ，一個表允許定義1024列，每行的最大長度為8092字節(jié)（不包括文本和圖像類型的長度）；
阿里《Java 開發(fā)手冊》提出 單表行 500w 容量2GB ，才分庫分表
與 MySQL 配置及硬件 有關(guān)，實際記錄的條數(shù)無關(guān)。因為表 索引 裝載 到內(nèi)存，InnoDB buffer size 足夠 ，才能全加載進(jìn)內(nèi)存，查沒問題。達(dá)量級限時，導(dǎo)致 內(nèi)存無法存儲索引 ，產(chǎn)生磁盤 IO，性能下降。增加硬件配置解決。500w算折中
QPS在8400左右 ：400個線程并發(fā)，插入100萬條記錄（4核2.33G、3G內(nèi)存、SATA硬盤）
寫： 90-100M/S（機械硬盤，7200轉(zhuǎn)）預(yù)計kB_wrtn/s在90M左右
show variables like 'max_connections' ?mysql當(dāng)前最大連接數(shù)
set global max_connections=1000; ?設(shè)置當(dāng)前最大連接數(shù)為1000；mysql重啟時失效,需要長期生效在my.ini 添加 max_connections=1000
從業(yè)務(wù)使用場景出發(fā)，根據(jù)RDS套餐類型和線上實際訪問流量，來衡量性能指標(biāo)，以便方便對標(biāo)實際業(yè)務(wù)場景。
MySQL 5.7.21 Group Replication
MySQL 5.7.21 Group Replication with Consistent Read
同機房3節(jié)點、跨機房3節(jié)點
網(wǎng)絡(luò)異常：長時間延時0.5ms，長時間延時2ms，丟包0.01%
場景1、2的差異可以衡量 跨機房網(wǎng)絡(luò) 帶來的 性能損耗
場景3關(guān)注在 網(wǎng)絡(luò)質(zhì)量變化 時帶來的 性能變化
同機房3節(jié)點為 05 06 03????跨機房3節(jié)點為 05 06 01
機器部署：同IDC3臺（永順ys 03 05 06），跨IDC1臺（廣州gz 01）
同IDC RTT（06-05）：RTT min/avg/max/mdev =?0.051/0.059/0.070/0.010?ms
跨IDC RTT（01-05）：RTT min/avg/max/mdev =?0.739/0.749/0.810/0.027
跨IDC的網(wǎng)絡(luò)耗時是同 IDC的1.3倍 ，在設(shè)置 延遲0.5ms后 的網(wǎng)絡(luò)質(zhì)量：
同IDC RTT（06-05）：RTT min/avg/max/mdev =?0.507/0.564/0.617/0.037
跨IDC RTT（01-05）：RTT min/avg/max/mdev =?1.199/1.248/1.315/0.046
跨IDC的網(wǎng)絡(luò)耗時是 同IDC的2.2倍 ，在設(shè)置 延遲2ms后 的網(wǎng)絡(luò)質(zhì)量：
同IDC RTT（06-05）：RTT min/avg/max/mdev = 1.963/2.054/2.161/0.064 ms
跨IDC RTT（01-05）：RTT?min/avg/max/mdev = 2.642/2.732/2.835/0.076 ms
參考：;aliyun
數(shù)據(jù)千萬級別之多，占用的存儲空間也比較大，可想而知它不會存儲在一塊連續(xù)的物理空間上，而是鏈?zhǔn)酱鎯υ诙鄠€碎片的物理空間上?？赡軐τ陂L字符串的比較，就用更多的時間查找與比較，這就導(dǎo)致用更多的時間。
可以做表拆分，減少單表字段數(shù)量，優(yōu)化表結(jié)構(gòu)。
在保證主鍵有效的情況下，檢查主鍵索引的字段順序，使得查詢語句中條件的字段順序和主鍵索引的字段順序保持一致。
主要兩種拆分 垂直拆分，水平拆分。
垂直分表
也就是“大表拆小表”，基于列字段進(jìn)行的。一般是表中的字段較多，將不常用的， 數(shù)據(jù)較大，長度較長（比如text類型字段）的拆分到“擴展表“。 一般是針對 那種 幾百列的大表，也避免查詢時，數(shù)據(jù)量太大造成的“跨頁”問題。
垂直分庫針對的是一個系統(tǒng)中的不同業(yè)務(wù)進(jìn)行拆分，比如用戶User一個庫，商品Product一個庫，訂單Order一個庫。 切分后，要放在多個服務(wù)器上，而不是一個服務(wù)器上。為什么？ 我們想象一下，一個購物網(wǎng)站對外提供服務(wù)，會有用戶，商品，訂單等的CRUD。沒拆分之前， 全部都是落到單一的庫上的，這會讓數(shù)據(jù)庫的單庫處理能力成為瓶頸。按垂直分庫后，如果還是放在一個數(shù)據(jù)庫服務(wù)器上， 隨著用戶量增大，這會讓單個數(shù)據(jù)庫的處理能力成為瓶頸，還有單個服務(wù)器的磁盤空間，內(nèi)存，tps等非常吃緊。 所以我們要拆分到多個服務(wù)器上，這樣上面的問題都解決了，以后也不會面對單機資源問題。
數(shù)據(jù)庫業(yè)務(wù)層面的拆分，和服務(wù)的“治理”，“降級”機制類似，也能對不同業(yè)務(wù)的數(shù)據(jù)分別的進(jìn)行管理，維護(hù)，監(jiān)控，擴展等。 數(shù)據(jù)庫往往最容易成為應(yīng)用系統(tǒng)的瓶頸，而數(shù)據(jù)庫本身屬于“有狀態(tài)”的，相對于Web和應(yīng)用服務(wù)器來講，是比較難實現(xiàn)“橫向擴展”的。 數(shù)據(jù)庫的連接資源比較寶貴且單機處理能力也有限，在高并發(fā)場景下，垂直分庫一定程度上能夠突破IO、連接數(shù)及單機硬件資源的瓶頸。
水平分表
針對數(shù)據(jù)量巨大的單張表（比如訂單表），按照某種規(guī)則（RANGE,HASH取模等），切分到多張表里面去。 但是這些表還是在同一個庫中，所以庫級別的數(shù)據(jù)庫操作還是有IO瓶頸。不建議采用。
水平分庫分表
將單張表的數(shù)據(jù)切分到多個服務(wù)器上去，每個服務(wù)器具有相應(yīng)的庫與表，只是表中數(shù)據(jù)集合不同。 水平分庫分表能夠有效的緩解單機和單庫的性能瓶頸和壓力，突破IO、連接數(shù)、硬件資源等的瓶頸。
水平分庫分表切分規(guī)則
1. RANGE
從0到10000一個表，10001到20000一個表；
2. HASH取模
一個商場系統(tǒng)，一般都是將用戶，訂單作為主表，然后將和它們相關(guān)的作為附表，這樣不會造成跨庫事務(wù)之類的問題。 取用戶id，然后hash取模，分配到不同的數(shù)據(jù)庫上。
3. 地理區(qū)域
比如按照華東，華南，華北這樣來區(qū)分業(yè)務(wù)，七牛云應(yīng)該就是如此。
4. 時間
按照時間切分，就是將6個月前，甚至一年前的數(shù)據(jù)切出去放到另外的一張表，因為隨著時間流逝，這些表的數(shù)據(jù) 被查詢的概率變小，所以沒必要和“熱數(shù)據(jù)”放在一起，這個也是“冷熱數(shù)據(jù)分離”。
分庫分表后面臨的問題
事務(wù)支持
分庫分表后，就成了分布式事務(wù)了。如果依賴數(shù)據(jù)庫本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)，將付出高昂的性能代價； 如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)，又會造成編程方面的負(fù)擔(dān)。
跨庫join
只要是進(jìn)行切分，跨節(jié)點Join的問題是不可避免的。但是良好的設(shè)計和切分卻可以減少此類情況的發(fā)生。解決這一問題的普遍做法是分兩次查詢實現(xiàn)。在第一次查詢的結(jié)果集中找出關(guān)聯(lián)數(shù)據(jù)的id,根據(jù)這些id發(fā)起第二次請求得到關(guān)聯(lián)數(shù)據(jù)。
跨節(jié)點的count,order by,group by以及聚合函數(shù)問題
這些是一類問題，因為它們都需要基于全部數(shù)據(jù)集合進(jìn)行計算。多數(shù)的代理都不會自動處理合并工作。解決方案：與解決跨節(jié)點join問題的類似，分別在各個節(jié)點上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并。和join不同的是每個結(jié)點的查詢可以并行執(zhí)行，因此很多時候它的速度要比單一大表快很多。但如果結(jié)果集很大，對應(yīng)用程序內(nèi)存的消耗是一個問題。
數(shù)據(jù)遷移，容量規(guī)劃，擴容等問題
來自淘寶綜合業(yè)務(wù)平臺團(tuán)隊，它利用對2的倍數(shù)取余具有向前兼容的特性（如對4取余得1的數(shù)對2取余也是1）來分配數(shù)據(jù)，避免了行級別的數(shù)據(jù)遷移，但是依然需要進(jìn)行表級別的遷移，同時對擴容規(guī)模和分表數(shù)量都有限制?？偟脕碚f，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側(cè)面反映出了Sharding擴容的難度。
ID問題
一旦數(shù)據(jù)庫被切分到多個物理結(jié)點上，我們將不能再依賴數(shù)據(jù)庫自身的主鍵生成機制。一方面，某個分區(qū)數(shù)據(jù)庫自生成的ID無法保證在全局上是唯一的；另一方面，應(yīng)用程序在插入數(shù)據(jù)之前需要先獲得ID,以便進(jìn)行SQL路由.
一些常見的主鍵生成策略
UUID
使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由于UUID非常的長，除占用大量存儲空間外，最主要的問題是在索引上，在建立索引和基于索引進(jìn)行查詢時都存在性能問題。
Twitter的分布式自增ID算法Snowflake
在分布式系統(tǒng)中，需要生成全局UID的場合還是比較多的，twitter的snowflake解決了這種需求，實現(xiàn)也還是很簡單的，除去配置信息，核心代碼就是毫秒級時間41位 機器ID 10位 毫秒內(nèi)序列12位。
跨分片的排序分頁
一般來講，分頁時需要按照指定字段進(jìn)行排序。當(dāng)排序字段就是分片字段的時候，我們通過分片規(guī)則可以比較容易定位到指定的分片，而當(dāng)排序字段非分片字段的時候，情況就會變得比較復(fù)雜了。為了最終結(jié)果的準(zhǔn)確性，我們需要在不同的分片節(jié)點中將數(shù)據(jù)進(jìn)行排序并返回，并將不同分片返回的結(jié)果集進(jìn)行匯總和再次排序，最后再返回給用戶。
數(shù)據(jù)庫優(yōu)化一方面是找出系統(tǒng)的瓶頸,提高M(jìn)ySQL數(shù)據(jù)庫的整體性能,而另一方面需要合理的結(jié)構(gòu)設(shè)計和參數(shù)調(diào)整,以提高用戶的相應(yīng)速度,同時還要盡可能的節(jié)約系統(tǒng)資源,以便讓系統(tǒng)提供更大的負(fù)荷.
1. 優(yōu)化一覽圖
2. 優(yōu)化
筆者將優(yōu)化分為了兩大類,軟優(yōu)化和硬優(yōu)化,軟優(yōu)化一般是操作數(shù)據(jù)庫即可,而硬優(yōu)化則是操作服務(wù)器硬件及參數(shù)設(shè)置.
2.1 軟優(yōu)化
2.1.1 查詢語句優(yōu)化
1.首先我們可以用EXPLAIN或DESCRIBE(簡寫:DESC)命令分析一條查詢語句的執(zhí)行信息.
2.例:
顯示:
其中會顯示索引和查詢數(shù)據(jù)讀取數(shù)據(jù)條數(shù)等信息.
2.1.2 優(yōu)化子查詢
在MySQL中,盡量使用JOIN來代替子查詢.因為子查詢需要嵌套查詢,嵌套查詢時會建立一張臨時表,臨時表的建立和刪除都會有較大的系統(tǒng)開銷,而連接查詢不會創(chuàng)建臨時表,因此效率比嵌套子查詢高.
2.1.3 使用索引
索引是提高數(shù)據(jù)庫查詢速度最重要的方法之一,關(guān)于索引可以參高筆者M(jìn)ySQL數(shù)據(jù)庫索引一文,介紹比較詳細(xì),此處記錄使用索引的三大注意事項:
2.1.4 分解表
對于字段較多的表,如果某些字段使用頻率較低,此時應(yīng)當(dāng),將其分離出來從而形成新的表,
2.1.5 中間表
對于將大量連接查詢的表可以創(chuàng)建中間表,從而減少在查詢時造成的連接耗時.
2.1.6 增加冗余字段
類似于創(chuàng)建中間表,增加冗余也是為了減少連接查詢.
2.1.7 分析表,,檢查表,優(yōu)化表
分析表主要是分析表中關(guān)鍵字的分布,檢查表主要是檢查表中是否存在錯誤,優(yōu)化表主要是消除刪除或更新造成的表空間浪費.
1. 分析表: 使用 ANALYZE 關(guān)鍵字,如ANALYZE TABLE user;
2. 檢查表: 使用 CHECK關(guān)鍵字,如CHECK TABLE user [option]
option 只對MyISAM有效,共五個參數(shù)值:
3. 優(yōu)化表:使用OPTIMIZE關(guān)鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;
LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日志.,優(yōu)化表只對VARCHAR,BLOB和TEXT有效,通過OPTIMIZE TABLE語句可以消除文件碎片,在執(zhí)行過程中會加上只讀鎖.
2.2 硬優(yōu)化
2.2.1 硬件三件套
1.配置多核心和頻率高的cpu,多核心可以執(zhí)行多個線程.
2.配置大內(nèi)存,提高內(nèi)存,即可提高緩存區(qū)容量,因此能減少磁盤I/O時間,從而提高響應(yīng)速度.
3.配置高速磁盤或合理分布磁盤:高速磁盤提高I/O,分布磁盤能提高并行操作的能力.
2.2.2 優(yōu)化數(shù)據(jù)庫參數(shù)
優(yōu)化數(shù)據(jù)庫參數(shù)可以提高資源利用率,從而提高M(jìn)ySQL服務(wù)器性能.MySQL服務(wù)的配置參數(shù)都在my.cnf或my.ini,下面列出性能影響較大的幾個參數(shù).
2.2.3 分庫分表
因為數(shù)據(jù)庫壓力過大，首先一個問題就是高峰期系統(tǒng)性能可能會降低，因為數(shù)據(jù)庫負(fù)載過高對性能會有影響。另外一個，壓力過大把你的數(shù)據(jù)庫給搞掛了怎么辦？所以此時你必須得對系統(tǒng)做分庫分表 + 讀寫分離，也就是把一個庫拆分為多個庫，部署在多個數(shù)據(jù)庫服務(wù)上，這時作為主庫承載寫入請求。然后每個主庫都掛載至少一個從庫，由從庫來承載讀請求。
2.2.4 緩存集群
如果用戶量越來越大，此時你可以不停的加機器，比如說系統(tǒng)層面不停加機器，就可以承載更高的并發(fā)請求。然后數(shù)據(jù)庫層面如果寫入并發(fā)越來越高，就擴容加數(shù)據(jù)庫服務(wù)器，通過分庫分表是可以支持?jǐn)U容機器的，如果數(shù)據(jù)庫層面的讀并發(fā)越來越高，就擴容加更多的從庫。但是這里有一個很大的問題：數(shù)據(jù)庫其實本身不是用來承載高并發(fā)請求的，所以通常來說，數(shù)據(jù)庫單機每秒承載的并發(fā)就在幾千的數(shù)量級，而且數(shù)據(jù)庫使用的機器都是比較高配置，比較昂貴的機器，成本很高。如果你就是簡單的不停的加機器，其實是不對的。所以在高并發(fā)架構(gòu)里通常都有緩存這個環(huán)節(jié)，緩存系統(tǒng)的設(shè)計就是為了承載高并發(fā)而生。所以單機承載的并發(fā)量都在每秒幾萬，甚至每秒數(shù)十萬，對高并發(fā)的承載能力比數(shù)據(jù)庫系統(tǒng)要高出一到兩個數(shù)量級。所以你完全可以根據(jù)系統(tǒng)的業(yè)務(wù)特性，對那種寫少讀多的請求，引入緩存集群。具體來說，就是在寫數(shù)據(jù)庫的時候同時寫一份數(shù)據(jù)到緩存集群里，然后用緩存集群來承載大部分的讀請求。這樣的話，通過緩存集群，就可以用更少的機器資源承載更高的并發(fā)。
一個完整而復(fù)雜的高并發(fā)系統(tǒng)架構(gòu)中，一定會包含：各種復(fù)雜的自研基礎(chǔ)架構(gòu)系統(tǒng)。各種精妙的架構(gòu)設(shè)計.因此一篇小文頂多具有拋磚引玉的效果,但是數(shù)據(jù)庫優(yōu)化的思想差不多就這些了.
重慶分公司
重慶分公司
