MySQL優(yōu)化

從設(shè)計(jì)上，可根據(jù)需要：分庫(kù)分表、讀寫分離、冷熱分離、使用緩存、定期進(jìn)行數(shù)據(jù)清理。

從客戶端使用上，使用連接池、避免大事務(wù)、返回?cái)?shù)據(jù)多使用物理分頁(yè)。

從優(yōu)化MySQL配置文件上，調(diào)整MySQL配置文件中的參數(shù)，如緩沖區(qū)大小、最大連接數(shù)等，以適應(yīng)應(yīng)用程序的需要。

從優(yōu)化表結(jié)構(gòu)上，使用合適的存儲(chǔ)引擎；避免使用大型或不必要的列，并盡可能使用小型數(shù)據(jù)類型；盡量把字段設(shè)置為NOT NULL；對(duì)于某些文本字段來(lái)說(shuō)，例如“省份”或者“性別”，我們可以將他們定義為ENUM(枚舉)類型。因?yàn)樵贛ySQL中，ENUM類型被當(dāng)做數(shù)值型數(shù)據(jù)來(lái)處理，而數(shù)值型數(shù)據(jù)被處理起來(lái)的速度要比文本類型要快得多。

從優(yōu)化查詢上，善用EXPLAIN查看SQL執(zhí)行計(jì)劃；使用連接(JOIN)來(lái)代替子查詢，減少在內(nèi)存中創(chuàng)建臨時(shí)表；盡量用union all代替union減少排序；利用小表去驅(qū)動(dòng)大表，減少嵌套循環(huán)中的循環(huán)次數(shù)，以減少 IO總量及CPU運(yùn)算的次數(shù)；善用索引。 

查詢?cè)谑裁磿r(shí)候不走索引、索引失效 

1.不滿足走索引的條件，常見的情況有 

1.1 不滿足最左匹配原則 

1.2 查詢條件使用了函數(shù) 

1.3 or操作有一個(gè)字段沒(méi)有索引 

1.4 使用like條件以%開頭 

1.5 顯式或隱式類型轉(zhuǎn)換導(dǎo)致索引失效 

1.6 存在范圍查詢，比如between、>、<等條件時(shí)，會(huì)造成后面的索引字段失效 

2.走索引效率低于全表掃描，常見的情況有 

2.1 查詢條件對(duì)null做判斷，而null的值很多   

2.2 一個(gè)字段區(qū)分度很小，比如性別、狀態(tài) 

3.需要回表的查詢結(jié)果集過(guò)大，超過(guò)了配置的范圍 

Explain解釋 

type列，連接類型。一個(gè)好的SQL語(yǔ)句至少要達(dá)到range級(jí)別。杜絕出現(xiàn)all級(jí)別。 

key列，使用到的索引名。如果沒(méi)有選擇索引，值是NULL?？梢圆扇?qiáng)制索引方式。 

key_len列，索引長(zhǎng)度

rows列，掃描行數(shù)。該值是個(gè)預(yù)估值。 

extra列，詳細(xì)說(shuō)明。注意，常見的不太友好的值，如下：Using filesort，Using temporary。 

如何解決大事務(wù)問(wèn)題？ 

解決大事務(wù)問(wèn)題常規(guī)的方法有：避免遠(yuǎn)程調(diào)用、避免一次性處理太多數(shù)據(jù)、覆蓋方法粒度盡量小。

具體到解決問(wèn)題，需要考慮是什么、為什么、怎么辦三個(gè)方面。

是什么是要界定大事務(wù)。比如可以把大事務(wù)分成了三個(gè)級(jí)別，分別是1s以上，500ms以上和100ms以上。1s以上會(huì)嚴(yán)重影響數(shù)據(jù)庫(kù)性能，有很大概率會(huì)造成整體鏈路超時(shí)，是重要緊急的。之前通過(guò)事務(wù)中不包含外部調(diào)用之后，偶爾會(huì)有100ms左右的大事務(wù)，超過(guò)120ms的基本沒(méi)有。這些我們記錄在一個(gè)文檔中，在涉及其他相關(guān)修改時(shí)，一起通過(guò)梳理邏輯優(yōu)化解決。

如果把RR改成RC需要注意哪些問(wèn)題？

在現(xiàn)代互聯(lián)網(wǎng)場(chǎng)景下，其實(shí)很少需要在一個(gè)事務(wù)中對(duì)同一行數(shù)據(jù)讀取兩次的情況，RR隔離級(jí)別實(shí)用性不高，而且因?yàn)榧恿碎g隙鎖和Next-Key Lock，性能有所降低，死鎖概率也相對(duì)較高。在歷史上，RR確實(shí)解決了mysql主從復(fù)制時(shí)的一個(gè)問(wèn)題。在早期的mysql版本中，只有statement這種bin log格式，這時(shí)候，如果使用提交讀(Read Committed)、未提交讀(Read Uncommitted)這兩種隔離級(jí)別會(huì)因?yàn)榛胤彭樞蚨霈F(xiàn)與主庫(kù)數(shù)據(jù)不一致。MySQL是在5.1.5版本開始支持row的、在5.1.8版本中開始支持mixed，后面這兩種可以代替 statement格式。RC 隔離級(jí)別只支持row格式的binlog。如果指定了mixed作為 binlog 格式，那么如果使用RC，服務(wù)器會(huì)自動(dòng)使用基于row 格式的日志記錄。