MySQL使用與優化總結

　　這篇文章總結了工作中用到MySQL的一些常見問題，解決方案；合適的使用場景和優化方案。

　儲存引擎的選擇：MyISAM vs InnoDB

    MyISAM：支援全文索引；使用表級鎖；讀併發效能好。

    InnoDB：支援事務和外來鍵；使用行級鎖；寫併發效能較好。

　　在實際應用場景中，我們一般都使用InnoDB作為預設的儲存引擎，除了支援事務和行鎖是比較重要的兩個原因外，其實MyISAM在實際應用場景中意義也不大，看看下面幾個原因：

• 全文索引完全可以（也應該）用第三方軟體來替代，比如：Sphinx；

• 讀效能高的特點完全可以用前端快取來替代，這已經是網際網路應用的標配了；

• 表級鎖在併發寫操作多時會嚴重影響讀操作（寫優先）；

　使用與優化

　　DB的優化

• 建立合適的索引：

  儘量讓所有查詢都走索引，這個效果是很明顯的。

• 表空間優化：

  在刪除或更新比較頻繁的表上，如果包含varchar,text之類的欄位，需要定期地執行表空間優化,optimaize table xxx，整理磁碟碎片，回收表資料和索引資料佔用的空閒空間;

• 配置引數優化：

  innodb_buffer_pool_size  innodb表資料和索引資料的記憶體緩衝大小，很關鍵，可以有效減少磁碟IO。
  innodb_flush_log_at_trx_commit 決定事務日誌怎麼記錄，這個對效能提升也很關鍵，線上下批量寫資料時可以考慮設定為0.或者寫操作頻繁但允許故障時丟失極少量資料的情況也可以考慮。
  query_cache 這個引數有些微妙，因為query cache在資料表中有任何資料修改時就會失效，對於寫操作頻繁的表來說，有可能還會降低效能。對於讀操作為主的表來說，效果還是很明顯的，但是通常場景下我們都依賴於前端快取，所以對於這個引數的設定來說，還要看具體業務場景。
  max_connections 控制併發連線數，不能太大，否則後果很嚴重。

• 拆分與擴容：

  庫拆分：一般是把同一例項上的資料庫分到多個例項上來分擔壓力（這種比較簡單，做一份複製，應用端改個ip就行），或者是把一個庫裡面的部分表單獨放到另一個例項庫中（這種比較麻煩，需要應用端配合修改程式）。
  表拆分：也分兩種，一種是把一些欄位的拆出到新表裡，比如按業務分，或者是像text之類的大欄位拆分。另一種是表記錄數太大，超出了單表承受能力，需要水平擴充套件到多張表。表拆分比較麻煩，都需要應用端配合修改程式。

　　SQL的優化

• 儘量用上索引，能用主鍵查詢最好了
• 儘量縮小掃描範圍，經典場景就是limit分頁偏移量的優化，其實在實際業務場景下很有很多類似的場景，我們完全可以按id號或者時間限制來顯著縮小查詢掃描範圍
• 儘量減少表連線查詢，最好是單表查詢（表連線可能用上臨時表，對DB消耗很大；而單表查詢可以快速返回，把計算操作放到前端應用去做，減少DB壓力）。如果前端併發沒有控制好的話，效能較差的表連線查詢可能會拖死DB
• 儘量做等值查詢，不等條件查詢和逆向查詢不走索引
• 用union替代or、in操作，後面兩個不走索引
• 不做前置模糊查詢，不走索引
• 排序和分組操作儘量在應用端做，減少DB的CPU壓力
• 在查詢列上不作函式運算：select concat('foo','bar') as str from xxx;

　　應用的優化

• 儘量不在DB端做運算，能在應用端做的事就不依賴DB
• text/blob之類的資料儘量不在DB中儲存，可以採用其它key/value型的儲存
• 大sql拆分成小sql查，不做表連線
• 用好連線池，減少連線開銷（這裡要注意連線池的空閒時間與資料庫空閒時間的配置）
• 有冷熱資料的場景，儘量均攤壓力
• 考慮讀寫分離（這裡要注意slave的延時，master寫頻繁的情況下，slave延時也是另人很頭疼的，對資料一致性敏感的應用場景是有隱患的）
• 最後提一下，preparedstatement，最大的作用是防止SQL隱碼攻擊。預編譯功能也可以嘗試使用，但是要開啟前端sql快取才好，這個還是看具體應用場景吧，大多數網際網路應用還沒到靠這個功能來提升效能的情況。

　簡單故障排查技巧

　　慢查詢排查

• 日誌檢視：slow.log，這個是mysql配置檔案裡設定的，要開啟。

• 實時檢視：select * from information_schema.processlist where time > 2;

  處理方式：

  • 慢查詢日誌分析工具：mysqlsla,mysqldumpslow等，對症下藥進行SQL優化；
  • 實時的慢查詢如果影響了應用的響應，可以直接kill掉查詢執行緒。執行kill [thread_id]即可。

　　Lock情況排查

　　確定資料庫有鎖住情況看兩個地方，在processlist中可以看到state那一列有lock相關的狀態，這裡只能看到一個狀態，最主要是通過下面這個命令來檢視show innodb engine status，這裡會顯示詳細的鎖和事務發生的資訊。至於怎麼解決，要看應用端怎麼來控制了。

　　Slave延時排查

　　在slave例項上執行show slave status檢視slave的狀態，主要關注以下三個：

Slave_IO_Running: Yes // 負責讀取binlog的執行緒是否正常執行
Slave_SQL_Running: Yes // 負責在slave上執行sql的執行緒是否正常執行
Seconds_Behind_Master: 0 // slave比master延時多長時間，單位：秒

　　如果出現IO和SQL執行緒狀態為No的情況，那說明slave同步已經停止了，可以通過Last_Error這個看到最近的錯誤。如果要恢復slave，一般兩種操作：一是重做slave，保證資料更準確；一種是跳過出錯的sql，stop slave;set global sql_slave_skip_counter=1;start slave;，這是跳過一條sql，也可跳過多條，這種方式可能導致slave資料不一致。

　監控

　　內建命令

status
show global status
show variables

　　外部監控

　　第三方的監控工具，可以提供圖形化的介面。cacti,ganglia等開源軟體都提供了監控mysql的外掛。

　簡單說說mysql高可用

　　兩種方式：

　　方式一：使用MySQL Cluster：讀擴充套件性好，寫效能會有一定下降。不是很成熟，線上慎用。

　　方式二：Master + Slave配合虛擬IP + LVS + keepalived實現簡單的高可用，這種方案的隱患就是：虛擬ip切換間隙會有短暫不可用；slave提升到master會有失敗的情況；

　最後

　　資料庫一般都儲存了應用的關鍵資料，可以說是一個公司產品的生命，所以資料的安全也非常重要，要做好許可權控制（嚴格控制許可權，儘量防止誤操作造成資料丟失），及時備份資料（異地，多機房），對於核心敏感資料還要做好保密工作。