優化mysql從哪些思路入手

下文主要給大家帶來優化mysql從哪些思路入手，希望這些內容能夠帶給大家實際用處，這也是我編輯優化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、調整索引或語句本身。

1. 系統層面

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。

1. 系統層面問題解決辦法

你認為到底負載高好，還是低好呢？
在實際的生產中，一般認為 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 存在問題

五、基礎優化

1. 優化思路

定位問題點:
硬件 --> 系統 --> 應用 --> 數據庫 --> 架構(高可用、讀寫分離、分庫分表)

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

1. 硬件優化

主機方面：
根據數據庫類型，主機 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. 服務器硬件優化

1、物理狀態燈：

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

3、第三方的監控軟件、設備(snmp、agent)對物理設施進行監控

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

1. 系統優化

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 調度策略：

1. 系統參數調整

Linux 系統內核參數優化：

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

1. 應用優化

業務應用和數據庫應用獨立,防火墻：iptables、selinux 等其他無用服務(關閉)：

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

六、數據庫優化
SQL 優化方向：
執行計劃、索引、SQL 改寫

架構優化方向：
高可用架構、高性能架構、分庫分表

1. 數據庫參數優化

調整：
實例整體(高級優化，擴展)

連接層(基礎優化)
設置合理的連接客戶和連接方式

對于以上關于優化mysql從哪些思路入手，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。