Mysql的深入最佳化

標籤：完全   讀寫   事務   叢集   out   指令   分離   事物   技術   

ok，廢話不多說，上乾貨。

調優思路：

? 0.硬體最佳化

? 1.資料庫設計與規劃--以後再修改很麻煩，估計資料量，使用什麼儲存引擎

? 2.資料的應用--怎樣取資料，sql 語句的最佳化

? 3.磁碟 io 最佳化

? 4.mysql 服務最佳化--記憶體的使用，磁碟的使用

? 5.my.cnf 內參數的最佳化。

0.硬體最佳化

CPU—— 64 位元、高主頻、高緩衝，高平行處理能力

記憶體——大記憶體、主頻高，盡量不要用 SWAP

硬碟——15000轉、RAID5、raid10 。 SSD

網路——標配的千兆網卡，10G網卡，bond0，msyql伺服器儘可能和使用它的web伺服器在同一區域網路內，盡量避免諸如防火牆策略等不必要的開銷。 

1.資料庫設計與規劃（架構上的最佳化)

縱向拆解: 專機專用 

橫向拆解: 主從同步、負載平衡、高可用性設定組群，當單個 mysql 資料庫無法滿足日益增加的需求時，可以考慮在資料庫這個邏輯層面增加多台伺服器，以達到穩定、高效的效果。

2、查詢最佳化

a>建表時表結構要合理，每個表不宜過大；在任何情況下均應使用最精確的類型。例如，如果ID列用int是一個好主意，而用text類型則是個蠢辦法；TIME列酌情使用DATE或者DATETIME。

b>索引，建立合適的索引。

c>查詢時盡量減少邏輯運算（與運算、或運算、大於小於某值的運算）；

d>減少不當的查詢語句，不要查詢應用中不需要的列，比如說select * from 等操作。

e>減小事務包的大小；

f>將多個小的查詢適當合并成一個大的查詢，減少每次建立/關閉查詢時的開銷；

g>將某些過於複雜的查詢拆解成多個小查詢，和上一條恰好相反

h>建立和最佳化預存程序來代替大量的外部程式互動。

3，磁碟 io 規劃，io相關的技術

raid 技術：raid0或raid10 

4. mysql 服務最佳化（資料庫服務的最佳化)

保持每個表都不要太大，可以對大表做橫切和縱切：比如說我要取得某 ID 的 lastlogin， 完全可以做一張只有“ID和 “lastlog”的小表，而非幾十、幾百列資料的並排大表。

另外對一個有 1000 萬條記錄的表做更新比對 10 個 100 萬記錄的表做更新一般來的要慢

儲存引擎：

myisam 引擎，表級鎖，表級鎖開銷小，影響範圍大，適合讀多寫少的表，不支援事物。 表鎖定不存在死結

innodb 引擎，行級鎖，鎖定行的開銷要比鎖定全表要大。影響範圍小，適合寫操作比較頻繁的資料表。行級鎖可能存在死結。

 

5. my.cnf 內參數的最佳化 

對查詢進行緩衝，一般大致查詢都是如此：PHP發出查詢請求->資料庫收到指令對查詢語句進行分析->確定如何查詢->從磁碟中載入資訊->返回結果 

配置查詢快取：

vim /etc/my.cnf 添加：

[mysqld] #在此欄位中添加

query_cache_size = 32M

可以在資料庫中查看緩衝：

show status like ‘qcache%‘;

強制限制mysql 資源設定

您可以在mysqld中強制一些限制來確保系統負載不會導致資源耗盡的情況出現。

在設定檔中添加：

max_connections=500 

wait_timeout=10 

max_connect_errors = 100

在資料庫中用此命令就行驗證：show status like ‘max_used_connections‘;

MySQL呢，有太多的調節最佳化，我們要記住那麼多會很吃力，也沒有多大意義。其實，只要記住一部分就可以完全滿足要求。今天我就寫這麼多了，這些基本的最佳化可以解決一部分問題，如果資料很大，就需要做一些叢集保證更好的使用者體驗(讀寫分離，高可用，搭建快取資料庫)

Mysql的深入最佳化