mysql優化的思路介紹-mysql教程-PHP中文網

這篇文章帶給大家的內容是關於mysql優化的思路介紹，有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

資料庫層面問題解決想法

一般應急調優的想法：
針對突然的業務辦理卡頓，無法進行正常的業務處理！需要立刻解決的場景！

1、show processlist

2、explain select id ,name from stu where name='clsn'; # ALL id name age sex
select id,name from stu where id=2-1 函数 结果集>30;
show index from table;

3、通过执行计划判断，索引问题(有没有、合不合理)或者语句本身问题

4、show status like '%lock%'; # 查询锁状态
kill SESSION_ID; # 杀掉有问题的session

登入後複製

常規調優思路：
針對業務週期性的卡頓，例如在每天 10-11 點業務特別慢，但是還能夠使用，過了這段時間就好了。

1、查看 slowlog，分析 slowlog，分析出查詢慢的語句。

2、依照一定優先級，進行一個一個的排查所有慢語句。

3、分析 top sql，進行 explain 調試，查看語句執行時間。

4、調整索引或語句本身。

系統層面

cpu方面：
vmstat、sar top、htop、nmon、mpstat

記憶體：
free 、ps -aux 、

IO 設備(磁碟、網路)：
iostat 、 ss 、 netstat 、 iptraf、iftop、lsof、

vmstat 指令說明：
Procs：r 顯示有多少進程正在等待CPU 時間。 b 顯示處於不可中斷的休眠的程序數量。在等待 I/O

Memory：swpd 顯示被交換到磁碟的資料區塊的數量。未被使用的資料區塊，使用者緩衝資料區塊，用於作業系統的資料區塊的數量

Swap：作業系統每秒從磁碟交換到記憶體和從記憶體交換到磁碟的資料區塊的數量。 s1 和 s0 最好是 0

Io：每秒從裝置中讀入 b1 的寫入到裝置 b0 的資料區塊的數量。反映了磁碟I/O

System：顯示了每秒發生中斷的數量(in)和上下文交換(cs)的數量

Cpu：顯示用於運行用戶代碼，系統代碼，空閒，等待I/O 的CPU 時間

iostat 指令說明
實例指令： iostat -dk 1 5
iostat -d -k -x 5 (查看裝置使用率(%util)和回應時間(await))

tps：該裝置每秒的傳輸次數。「一次傳輸」意思是「一次 I/O 請求」。多個邏輯請求可能會被合併為「一次 I/O 請求」。

iops ：硬體出廠的時候，廠商定義的一個每秒最大的 IO 次數,"一次傳輸"請求的大小是未知的。

kB_read/s：每秒從裝置(drive expressed)讀取的資料量；

KB_wrtn/s：每秒向裝置(drive expressed)寫入的資料量；

kB_read：讀取的總資料量；

kB_wrtn：寫入的總數資料量；這些單位都是Kilobytes。

系統層面問題解決方法

你認為到底負載高好，還是低好呢？
在實際的生產中，一般認為 cpu 只要不超過 90% 都沒什麼問題。

當然不排除下面這些特殊情況：
問題一：cpu 負載高，IO 負載低
記憶體不夠

磁碟效能差

SQL 問題- ----->去資料庫層，進一步排查sql 問題

IO 出問題了(磁碟到臨界了、raid 設計不好、raid 降級、鎖、在單位時間內tps 過高)

tps 過高: 大量的小資料IO、大量的全表掃描

問題二：IO 負載高，cpu 負載低
大量小的IO 寫入操作：

autocommit ，產生大量小IO

IO/PS 磁碟的一個定值，硬體出廠的時候，廠商定義的一個每秒最大的IO 次數。

大量大的IO 寫入操作

SQL 問題的幾率比較大

問題三：IO 和cpu 負載都很高
硬體不夠了或SQL 有問題

五、基礎最佳化

最佳化想法

定位問題點:
硬體--> 系統--> 應用-- > 資料庫--> 架構(高可用、讀寫分離、分庫分錶)

處理方向：
明確優化目標、效能與安全的折中、防患未然

#硬體最佳化

主機方面：
根據資料庫類型，主機CPU 選擇、記憶體容量選擇、磁碟選擇

平衡記憶體和磁碟資源

隨機的I/O 和順序的I/O

主機RAID 卡的BBU(Battery Backup Unit)關閉

cpu 的選擇：
cpu 的兩個關鍵因素：核數、主頻

根據不同的業務類型選擇：

cpu 密集型：計算比較多，OLTP 主頻很高的cpu、核數還要多

IO 密集型：查詢比較，OLAP 核數要多，主頻不一定高的

內存的選擇：
OLAP 類型數據庫，需要更多內存，和數據獲取量級有關。

OLTP 類型資料一般記憶體是 cpu 核心數量的 2 倍到 4 倍，沒有最佳實踐。

儲存方面：
依據儲存資料種類的不同，選擇不同的儲存裝置

設定合理的RAID 等級(raid 5、raid 10、熱備磁碟)

對與作業系統來講，不需要太特殊的選擇，最好做好冗餘(raid1)(ssd、sas 、sata)

raid 卡：主機raid 卡選擇：
實現作業系統磁碟的冗餘(raid1)

平衡記憶體和磁碟資源

隨機的I/O 和順序的I/O

主機RAID 卡的BBU(Battery Backup Unit)要關閉

網路設備方面：
使用流量支援更高的網路設備(交換器、路由器、網路線、網路卡、HBA 卡)

注意：以上這些規劃應該在初始設計系統時就應該考慮好。

伺服器硬體最佳化

1、實體狀態燈：

2、自備管理設備：遠端控制卡(FENCE 設備：ipmi ilo idarc )，開關機、硬體監控。

3、第三方的監控軟體、設備(snmp、agent)對實體設施進行監控

4、儲存設備：自帶的監控平台。 EMC2(hp 收購了)，日立(hds)，IBM 低端OEM hds，高端存儲是自己技術，華為存儲

系統優化

##Cpu：

基本上不需要調整，在硬體選擇方面下功夫即可。

記憶體：

基本上不需要調整，在硬體選擇方面下功夫即可。

SWAP：

MySQL 盡量避免使用 swap。阿里雲的伺服器中預設swap 為0

IO ：

raid、no lvm、 ext4 或xfs、ssd、IO 調度策略

Swap 調整(不使用swap 分割區)

這個參數決定了Linux 是傾向於使用swap，還是傾向於釋放檔案系統cache。在記憶體緊張的情況下，數值越低越傾向於釋放檔案系統 cache。當然，這個參數只能減少使用 swap 的機率，並不能避免 Linux 使用 swap。

修改 MySQL 的設定參數 innodb_flush_method，開啟 O_DIRECT 模式。這種情況下，InnoDB 的 buffer pool 會直接繞過檔案系統 cache 來存取磁碟，但是redo log 依舊會使用檔案系統 cache。值得注意的是，Redo log 是覆寫模式的，即使使用了檔案系統的cache，也不會佔用太多

IO 調度策略：

Linux 系統核心參數最佳化：

使用者限制參數(MySQL 可以不設定以下配置)：

業務應用程式與資料庫應用獨立,防火牆：iptables、selinux 等其他無用服務(關閉)：

安裝圖形介面的伺服器不要啟動圖形介面runlevel 3,另外，思考將來我們的業務是否真的需要MySQL，還是使用其他種類的資料庫。用資料庫的最高境界就是不用資料庫。

六、資料庫最佳化

SQL 最佳化方向：
執行計畫、索引、SQL 改寫

架構最佳化方向：

高可用架構、高效能架構、分庫分錶

調整：

實例整體(進階最佳化，擴充)

連結層(基礎最佳化)