千萬級的大表！MySQL這樣最佳化更好

標籤：ide   日誌   AC   解決方案   水平   分區   二進位檔案   恢複   SQ   

對於一個千萬級的大表，現在可能更多的是億級資料量，很多人第一反應是各種切分，可結果總是事半功倍，或許正是我們最佳化順序的不正確。下面我們來談談怎樣的最佳化順序可以讓效果更好。

MySQL資料庫一般都是按照下面的步驟去演化，成本也是由低到高:

1/ SQL最佳化

1. 避免使用select *

• 返回結果過多，降低查詢的速度；

• 過多的返回結果，增加資料轉送量；

2. 可確定返回記錄數的，盡量增加limit n；

3. 盡量少用like查詢，會導致索引失效；

2/ 軟體最佳化

1. 選擇合理的引擎

• MyISAM索引循序存取方法，支援全文索引，非事務安全，不支援外鍵，會加表級鎖；

• InnoDB事務型儲存引擎，加行鎖，支援復原，崩潰恢複，ACID事務控制；

2. 正確使用索引

• 結合適的列表建立索引；

• 索引值應該不相同，唯一值時效果最好，大量重複效果很差；

• 不能濫用索引，索引佔用空間；

• 使用短索引，存的索引多，消耗IO更小，能提高尋找速度；

3. 欄位盡量設定成NOT NULL

• NULL占空間，對於Java和OC強型別的，容易千萬APP閃退；

4. MySQL分區表

3/ 硬體最佳化

1. Linux核心用記憶體開緩衝儲存資料；

2. 增加應用緩衝，例如Memcached、Redis讀寫效能非常高；

3. 用SSD代替機械硬碟

• 日誌和資料分開儲存，日誌順序讀寫 - 機械硬碟，資料隨機讀寫 - SSD；

4. SSD+SATA混合儲存，對熱資料緩衝，例如：FlashCache；

4/ 架構最佳化

1. 讀寫分離

• 可以把資料庫讀和寫拆開，對應主從伺服器，主伺服器寫操作、從伺服器是讀操作；

• 讀是一些機器，寫是一些機器，二進位檔案的主從複製，延遲解決方案；

• 主伺服器寫操作的同時，同步到從伺服器，保持資料完整性——主從複製；

2. 垂直分割

• 根據模組的耦合度，將一個大的系統分為多個小的系統，也就是分布式系統；

• 欄位分成多個表；

3. 水平分割

• 分表：資料分成多個表，拆分後的每張表的表頭相同；

• 分庫：類型方案有Cobar（阿里開源，無更新）、MyCat（基於Cobar）；

總結

盡我們所能去最佳化SQL吧！它成本最低，卻又是一項費時費力的活，需要在技術與業務都熟悉的情況下，用心去最佳化才能做到最優，最佳化後效果也是立竿見影的！

千萬級的大表！MySQL這樣最佳化更好