MySQL使用與優化總結

存儲引擎的選擇：MyISAM vs InnoDB

    MyISAM：支持全文索引；使用表級鎖；讀并發性能好。


InnoDB：支持事務和外鍵；使用行級鎖；寫并發性能較好。</code></pre> <p>在實際應用場景中，我們一般都使用InnoDB作為默認的存儲引擎，除了支持事務和行鎖是比較重要的兩個原因外，其實MyISAM在實際應用場景中意義也不大，看看下面幾個原因：</p>



全文索引完全可以（也應該）用第三方軟件來替代，比如：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會有失敗的情況；
最后
數據庫一般都存儲了應用的關鍵數據，可以說是一個公司產品的生命，所以數據的安全也非常重要，要做好權限控制（嚴格控制權限，盡量防止誤操作造成數據丟失），及時備份數據（異地，多機房），對于核心敏感數據還要做好保密工作。