有哪些方法可以改善 MySQL 的 IO 瓶頸問題

通過sysbench的oltp_read_write測試來模擬業務壓力、以此來給指定的硬件環境配置一份比較合理的MySQL配置文件。

站在用戶的角度思考問題，與客戶深入溝通，找到山丹網站設計與山丹網站推廣的解決方案，憑借多年的經驗，讓設計與互聯網技術結合，創造個性化、用戶體驗好的作品，建站類型包括：成都網站設計、網站建設、企業官網、英文網站、手機端網站、網站推廣、主機域名、網頁空間、企業郵箱。業務覆蓋山丹地區。

環境介紹

硬件配置

請點擊輸入圖片描述

軟件環境

請點擊輸入圖片描述

優化層級與指導思想

優化層級

MySQL數據庫優化可以在多個不同的層級進行，常見的有：

SQL優化

參數優化

架構優化

本文重點關注：參數優化

指導思想

日志先行 -- 一個事務能否成功提交的關鍵是日志是否成功落盤，與數據沒有太大的關系；也就是說對寫的優化可以表述為各方面的資源向寫操作傾斜。

瓶頸分析 -- 通過show global status 的各個計數器的值基本上就能分析出當前瓶頸所在，再結合一些簡單的系統層面的監控工具如top iostat 就能明確瓶頸。

整體性能是“讀”“寫”之間的再平衡。

(16)mysql瓶頸 & MGR和一致性讀 性能測試

容量： 看硬件

InnoDB?最大容量64TB ，存儲引擎將 InnoDB 表 保存在一個 表空間內（ 原始磁盤分區，由數個文件創建）。這樣， 表大小 能超過 單獨文件最大容量 。

MySQL 3.22( MyISAM )限制表大小 4GB ，最大表尺寸增加到65536TB（2567 – 1字節）。最大有效表尺寸通常是由 操作系統 對 文件大小限制 決定的， 不是 由MySQL內部限制決定。

最多 20億個表 ，一個表允許定義1024列，每行的最大長度為8092字節（不包括文本和圖像類型的長度）；

阿里《Java 開發手冊》提出 單表行 500w 容量2GB ，才分庫分表

與 MySQL 配置及硬件 有關，實際記錄的條數無關。因為表 索引 裝載 到內存，InnoDB buffer size 足夠 ，才能全加載進內存，查沒問題。達量級限時，導致 內存無法存儲索引 ，產生磁盤 IO，性能下降。增加硬件配置解決。500w算折中

QPS在8400左右 ：400個線程并發，插入100萬條記錄（4核2.33G、3G內存、SATA硬盤）

寫： 90-100M/S（機械硬盤，7200轉）預計kB_wrtn/s在90M左右

show variables like 'max_connections' ?mysql當前最大連接數

set global max_connections=1000; ?設置當前最大連接數為1000；mysql重啟時失效,需要長期生效在my.ini 添加 max_connections=1000

從業務使用場景出發，根據RDS套餐類型和線上實際訪問流量，來衡量性能指標，以便方便對標實際業務場景。

MySQL 5.7.21 Group Replication

MySQL 5.7.21 Group Replication with Consistent Read

同機房3節點、跨機房3節點

網絡異常：長時間延時0.5ms，長時間延時2ms，丟包0.01%

場景1、2的差異可以衡量 跨機房網絡 帶來的 性能損耗

場景3關注在 網絡質量變化 時帶來的 性能變化

同機房3節點為 05 06 03????跨機房3節點為 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的網絡耗時是同 IDC的1.3倍 ，在設置 延遲0.5ms后 的網絡質量：

同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的網絡耗時是 同IDC的2.2倍 ，在設置 延遲2ms后 的網絡質量：

同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

Mysql某個表有近千萬數據，CRUD比較慢，如何優化？

數據千萬級別之多，占用的存儲空間也比較大，可想而知它不會存儲在一塊連續的物理空間上，而是鏈式存儲在多個碎片的物理空間上。可能對于長字符串的比較，就用更多的時間查找與比較，這就導致用更多的時間。

可以做表拆分，減少單表字段數量，優化表結構。

在保證主鍵有效的情況下，檢查主鍵索引的字段順序，使得查詢語句中條件的字段順序和主鍵索引的字段順序保持一致。

主要兩種拆分 垂直拆分，水平拆分。

垂直分表

也就是“大表拆小表”，基于列字段進行的。一般是表中的字段較多，將不常用的， 數據較大，長度較長（比如text類型字段）的拆分到“擴展表“。 一般是針對 那種 幾百列的大表，也避免查詢時，數據量太大造成的“跨頁”問題。

垂直分庫針對的是一個系統中的不同業務進行拆分，比如用戶User一個庫，商品Product一個庫，訂單Order一個庫。 切分后，要放在多個服務器上，而不是一個服務器上。為什么？ 我們想象一下，一個購物網站對外提供服務，會有用戶，商品，訂單等的CRUD。沒拆分之前， 全部都是落到單一的庫上的，這會讓數據庫的單庫處理能力成為瓶頸。按垂直分庫后，如果還是放在一個數據庫服務器上， 隨著用戶量增大，這會讓單個數據庫的處理能力成為瓶頸，還有單個服務器的磁盤空間，內存，tps等非常吃緊。 所以我們要拆分到多個服務器上，這樣上面的問題都解決了，以后也不會面對單機資源問題。

數據庫業務層面的拆分，和服務的“治理”，“降級”機制類似，也能對不同業務的數據分別的進行管理，維護，監控，擴展等。 數據庫往往最容易成為應用系統的瓶頸，而數據庫本身屬于“有狀態”的，相對于Web和應用服務器來講，是比較難實現“橫向擴展”的。 數據庫的連接資源比較寶貴且單機處理能力也有限，在高并發場景下，垂直分庫一定程度上能夠突破IO、連接數及單機硬件資源的瓶頸。

水平分表

針對數據量巨大的單張表（比如訂單表），按照某種規則（RANGE,HASH取模等），切分到多張表里面去。 但是這些表還是在同一個庫中，所以庫級別的數據庫操作還是有IO瓶頸。不建議采用。

水平分庫分表

將單張表的數據切分到多個服務器上去，每個服務器具有相應的庫與表，只是表中數據集合不同。 水平分庫分表能夠有效的緩解單機和單庫的性能瓶頸和壓力，突破IO、連接數、硬件資源等的瓶頸。

水平分庫分表切分規則

1. RANGE

從0到10000一個表，10001到20000一個表；

2. HASH取模

一個商場系統，一般都是將用戶，訂單作為主表，然后將和它們相關的作為附表，這樣不會造成跨庫事務之類的問題。 取用戶id，然后hash取模，分配到不同的數據庫上。

3. 地理區域

比如按照華東，華南，華北這樣來區分業務，七牛云應該就是如此。

4. 時間

按照時間切分，就是將6個月前，甚至一年前的數據切出去放到另外的一張表，因為隨著時間流逝，這些表的數據 被查詢的概率變小，所以沒必要和“熱數據”放在一起，這個也是“冷熱數據分離”。

分庫分表后面臨的問題

事務支持

分庫分表后，就成了分布式事務了。如果依賴數據庫本身的分布式事務管理功能去執行事務，將付出高昂的性能代價； 如果由應用程序去協助控制，形成程序邏輯上的事務，又會造成編程方面的負擔。

跨庫join

只要是進行切分，跨節點Join的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。解決這一問題的普遍做法是分兩次查詢實現。在第一次查詢的結果集中找出關聯數據的id,根據這些id發起第二次請求得到關聯數據。

跨節點的count,order by,group by以及聚合函數問題

這些是一類問題，因為它們都需要基于全部數據集合進行計算。多數的代理都不會自動處理合并工作。解決方案：與解決跨節點join問題的類似，分別在各個節點上得到結果后在應用程序端進行合并。和join不同的是每個結點的查詢可以并行執行，因此很多時候它的速度要比單一大表快很多。但如果結果集很大，對應用程序內存的消耗是一個問題。

數據遷移，容量規劃，擴容等問題

來自淘寶綜合業務平臺團隊，它利用對2的倍數取余具有向前兼容的特性（如對4取余得1的數對2取余也是1）來分配數據，避免了行級別的數據遷移，但是依然需要進行表級別的遷移，同時對擴容規模和分表數量都有限制?？偟脕碚f，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側面反映出了Sharding擴容的難度。

ID問題

一旦數據庫被切分到多個物理結點上，我們將不能再依賴數據庫自身的主鍵生成機制。一方面，某個分區數據庫自生成的ID無法保證在全局上是唯一的；另一方面，應用程序在插入數據之前需要先獲得ID,以便進行SQL路由.

一些常見的主鍵生成策略

UUID

使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由于UUID非常的長，除占用大量存儲空間外，最主要的問題是在索引上，在建立索引和基于索引進行查詢時都存在性能問題。

Twitter的分布式自增ID算法Snowflake

在分布式系統中，需要生成全局UID的場合還是比較多的，twitter的snowflake解決了這種需求，實現也還是很簡單的，除去配置信息，核心代碼就是毫秒級時間41位 機器ID 10位 毫秒內序列12位。

跨分片的排序分頁

一般來講，分頁時需要按照指定字段進行排序。當排序字段就是分片字段的時候，我們通過分片規則可以比較容易定位到指定的分片，而當排序字段非分片字段的時候，情況就會變得比較復雜了。為了最終結果的準確性，我們需要在不同的分片節點中將數據進行排序并返回，并將不同分片返回的結果集進行匯總和再次排序，最后再返回給用戶。

超詳細MySQL數據庫優化

數據庫優化一方面是找出系統的瓶頸,提高MySQL數據庫的整體性能,而另一方面需要合理的結構設計和參數調整,以提高用戶的相應速度,同時還要盡可能的節約系統資源,以便讓系統提供更大的負荷.

1. 優化一覽圖

2. 優化

筆者將優化分為了兩大類,軟優化和硬優化,軟優化一般是操作數據庫即可,而硬優化則是操作服務器硬件及參數設置.

2.1 軟優化

2.1.1 查詢語句優化

1.首先我們可以用EXPLAIN或DESCRIBE(簡寫:DESC)命令分析一條查詢語句的執行信息.

2.例:

顯示:

其中會顯示索引和查詢數據讀取數據條數等信息.

2.1.2 優化子查詢

在MySQL中,盡量使用JOIN來代替子查詢.因為子查詢需要嵌套查詢,嵌套查詢時會建立一張臨時表,臨時表的建立和刪除都會有較大的系統開銷,而連接查詢不會創建臨時表,因此效率比嵌套子查詢高.

2.1.3 使用索引

索引是提高數據庫查詢速度最重要的方法之一,關于索引可以參高筆者MySQL數據庫索引一文,介紹比較詳細,此處記錄使用索引的三大注意事項:

2.1.4 分解表

對于字段較多的表,如果某些字段使用頻率較低,此時應當,將其分離出來從而形成新的表,

2.1.5 中間表

對于將大量連接查詢的表可以創建中間表,從而減少在查詢時造成的連接耗時.

2.1.6 增加冗余字段

類似于創建中間表,增加冗余也是為了減少連接查詢.

2.1.7 分析表,,檢查表,優化表

分析表主要是分析表中關鍵字的分布,檢查表主要是檢查表中是否存在錯誤,優化表主要是消除刪除或更新造成的表空間浪費.

1. 分析表: 使用 ANALYZE 關鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關鍵字,如CHECK TABLE user [option]

option 只對MyISAM有效,共五個參數值:

3. 優化表:使用OPTIMIZE關鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日志.,優化表只對VARCHAR,BLOB和TEXT有效,通過OPTIMIZE TABLE語句可以消除文件碎片,在執行過程中會加上只讀鎖.

2.2 硬優化

2.2.1 硬件三件套

1.配置多核心和頻率高的cpu,多核心可以執行多個線程.

2.配置大內存,提高內存,即可提高緩存區容量,因此能減少磁盤I/O時間,從而提高響應速度.

3.配置高速磁盤或合理分布磁盤:高速磁盤提高I/O,分布磁盤能提高并行操作的能力.

2.2.2 優化數據庫參數

優化數據庫參數可以提高資源利用率,從而提高MySQL服務器性能.MySQL服務的配置參數都在my.cnf或my.ini,下面列出性能影響較大的幾個參數.

2.2.3 分庫分表

因為數據庫壓力過大，首先一個問題就是高峰期系統性能可能會降低，因為數據庫負載過高對性能會有影響。另外一個，壓力過大把你的數據庫給搞掛了怎么辦？所以此時你必須得對系統做分庫分表 + 讀寫分離，也就是把一個庫拆分為多個庫，部署在多個數據庫服務上，這時作為主庫承載寫入請求。然后每個主庫都掛載至少一個從庫，由從庫來承載讀請求。

2.2.4 緩存集群

如果用戶量越來越大，此時你可以不停的加機器，比如說系統層面不停加機器，就可以承載更高的并發請求。然后數據庫層面如果寫入并發越來越高，就擴容加數據庫服務器，通過分庫分表是可以支持擴容機器的，如果數據庫層面的讀并發越來越高，就擴容加更多的從庫。但是這里有一個很大的問題：數據庫其實本身不是用來承載高并發請求的，所以通常來說，數據庫單機每秒承載的并發就在幾千的數量級，而且數據庫使用的機器都是比較高配置，比較昂貴的機器，成本很高。如果你就是簡單的不停的加機器，其實是不對的。所以在高并發架構里通常都有緩存這個環節，緩存系統的設計就是為了承載高并發而生。所以單機承載的并發量都在每秒幾萬，甚至每秒數十萬，對高并發的承載能力比數據庫系統要高出一到兩個數量級。所以你完全可以根據系統的業務特性，對那種寫少讀多的請求，引入緩存集群。具體來說，就是在寫數據庫的時候同時寫一份數據到緩存集群里，然后用緩存集群來承載大部分的讀請求。這樣的話，通過緩存集群，就可以用更少的機器資源承載更高的并發。

一個完整而復雜的高并發系統架構中，一定會包含：各種復雜的自研基礎架構系統。各種精妙的架構設計.因此一篇小文頂多具有拋磚引玉的效果,但是數據庫優化的思想差不多就這些了.

標題名稱：mysql瓶頸怎么處理 mysql性能瓶頸是什么
本文URL：http://www.yijiale78.com/article10/hgoogo.html

成都網站建設公司_創新互聯，為您提供建站公司、關鍵詞優化、移動網站建設、網站設計公司、網站建設、網站內鏈

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯