深入MySQL優(yōu)化:提升數(shù)據(jù)庫性能的關(guān)鍵策略
MySQL優(yōu)化
從設(shè)計(jì)上,可根據(jù)需要:分庫分表、讀寫分離、冷熱分離、使用緩存、定期進(jìn)行數(shù)據(jù)清理。
從客戶端使用上,使用連接池、避免大事務(wù)、返回?cái)?shù)據(jù)多使用物理分頁。
從優(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ì)于某些文本字段來說,例如“省份”或者“性別”,我們可以將他們定義為ENUM(枚舉)類型。因?yàn)樵贛ySQL中,ENUM類型被當(dāng)做數(shù)值型數(shù)據(jù)來處理,而數(shù)值型數(shù)據(jù)被處理起來的速度要比文本類型要快得多。
從優(yōu)化查詢上,善用EXPLAIN查看SQL執(zhí)行計(jì)劃;使用連接(JOIN)來代替子查詢,減少在內(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è)字段沒有索引
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é)果集過大,超過了配置的范圍
Explain解釋
type列,連接類型。一個(gè)好的SQL語句至少要達(dá)到range級(jí)別。杜絕出現(xiàn)all級(jí)別。
key列,使用到的索引名。如果沒有選擇索引,值是NULL??梢圆扇?qiáng)制索引方式。
key_len列,索引長(zhǎng)度
rows列,掃描行數(shù)。該值是個(gè)預(yù)估值。
extra列,詳細(xì)說明。注意,常見的不太友好的值,如下:Using filesort,Using temporary。
如何解決大事務(wù)問題?
解決大事務(wù)問題常規(guī)的方法有:避免遠(yuǎn)程調(diào)用、避免一次性處理太多數(shù)據(jù)、覆蓋方法粒度盡量小。
具體到解決問題,需要考慮是什么、為什么、怎么辦三個(gè)方面。
是什么是要界定大事務(wù)。比如可以把大事務(wù)分成了三個(gè)級(jí)別,分別是1s以上,500ms以上和100ms以上。1s以上會(huì)嚴(yán)重影響數(shù)據(jù)庫性能,有很大概率會(huì)造成整體鏈路超時(shí),是重要緊急的。之前通過事務(wù)中不包含外部調(diào)用之后,偶爾會(huì)有100ms左右的大事務(wù),超過120ms的基本沒有。這些我們記錄在一個(gè)文檔中,在涉及其他相關(guān)修改時(shí),一起通過梳理邏輯優(yōu)化解決。
如果把RR改成RC需要注意哪些問題?
在現(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è)問題。在早期的mysql版本中,只有statement這種bin log格式,這時(shí)候,如果使用提交讀(Read Committed)、未提交讀(Read Uncommitted)這兩種隔離級(jí)別會(huì)因?yàn)榛胤彭樞蚨霈F(xiàn)與主庫數(shù)據(jù)不一致。MySQL是在5.1.5版本開始支持row的、在5.1.8版本中開始支持mixed,后面這兩種可以代替 statement格式。RC 隔離級(jí)別只支持row格式的binlog。如果指定了mixed作為 binlog 格式,那么如果使用RC,服務(wù)器會(huì)自動(dòng)使用基于row 格式的日志記錄。