經(jīng)常用mysql,也都是自己設計表結(jié)構(gòu),寫sql,但是由于業(yè)務規(guī)模小,對sql性能要求不高,所以很少注意到這一塊,但是最近DBA出了一個慢sql報表,才發(fā)現(xiàn)自己寫的都是慢sql,因此通過讀書思灰、查資料對這一塊做了一些了解,整理一下玷犹。
影響數(shù)據(jù)庫性能的相關(guān)因素
1. 系統(tǒng)架構(gòu)對數(shù)據(jù)庫性能的影響
數(shù)據(jù)庫中存放的數(shù)據(jù)是否真正適合在數(shù)據(jù)庫中存放？一般情況下有三種數(shù)據(jù)不適合存放在數(shù)據(jù)庫中,二 進制多媒體數(shù)據(jù)洒疚，超大文本數(shù)據(jù)歹颓，流水隊列數(shù)據(jù)(需要頻繁的insert,update,delete)坯屿。
是否合理的利用了cache?尤其是對于訪問頻繁但變更少的數(shù)據(jù)應當有chache緩解數(shù)據(jù)庫壓力。
2. 表結(jié)構(gòu)對數(shù)據(jù)庫性能的影響
- 在表結(jié)構(gòu)設計時就應該結(jié)合業(yè)務考慮到避免昂貴操作和性能優(yōu)化巍扛。
3. query語句對數(shù)據(jù)庫的影響
- 對于同一份數(shù)據(jù),當以不同方式去尋找其中某一部分內(nèi)容的時候,須要讀取 的數(shù)據(jù)量可能會有天壤之別,所消耗的資源自然也是區(qū)別甚大领跛。
其中第一點需要在整個系統(tǒng)的架構(gòu)設計時考慮合適的技術(shù)選型和性能優(yōu)化點, 此處不進行詳敘, 下面主要對表結(jié)構(gòu)設計和query語句優(yōu)化需要注意的點做簡單的梳理。
表結(jié)構(gòu)設計優(yōu)化策略
1. 適度冗余, 減少頻繁查詢的join操作
join操作本身就比較耗時,而且mysql對于復雜的join操作容易出現(xiàn)不合理的執(zhí)行計劃,因此對于更新不頻繁但是查詢頻繁的其他表中的數(shù)據(jù)可以適當冗余存儲在查詢主表中,比如大多數(shù)查詢博客的時候都同時需要其作者名, 而一片博客的作者名幾乎很少會修改, 因此博客表中可以冗余存儲作者名, 避免每次都需要和用戶表作join操作, 提高博客的查詢性能撤奸。
2. 大字段垂直拆分
大多數(shù)情況下大字段的訪問都不是很頻繁, 但由于其大, 往往帶來較大的IO開銷, 因此通過將其拆分出去, 可以在訪問其他字段時大大降低IO訪問, 從而提高性能吠昭。比如博客表, 其中博客內(nèi)容就屬于大字段,而且相對于博客標題,摘要,作者,發(fā)布時間,類型等字段來說, 訪問頻率也相對低許多(只有當用戶看到前面這些字段并且對這篇博客產(chǎn)生興趣的時候才會查看博客內(nèi)容),因此就可以單獨拆分出去。其實不止是大字段,所有訪問不頻繁的字段都可以和大字段一起拆分出去胧瓜。
3. 大表基于類型進行分拆
一個表中存儲的數(shù)據(jù)雖然都屬于同一類, 但是很多情況下也有細分類型, 有時候不同類型的讀寫比例,訪問頻率可能都有很大差別, 因此將這些差別較大的類型單獨拆分出一個表, 不僅不同類型的數(shù)據(jù)查詢相互不受拖累,而且也更有利用cache矢棚。
4. 選擇合適的數(shù)據(jù)類型
選擇更小的數(shù)據(jù)類型, 可以使查詢相同數(shù)據(jù)需要的IO資源降低,如果是索引字段,也能更好的利用索引。
對于數(shù)字的存儲,盡可能使用整數(shù)存儲,可以使數(shù)據(jù)的處理更為高效府喳。
對于時間類型, 如果不需要存儲1970年之前的數(shù)據(jù), 盡可能采用TIMESTAMP類型(只能存儲1970年以后的時間,但是僅需要4字節(jié),其他類型均需要8字節(jié))幻妓。
query語句優(yōu)化
1. 基本原則
重點優(yōu)化高并發(fā)的查詢, 對于高并發(fā)的查詢每次節(jié)省一點資源,對整個系統(tǒng)來說也會帶來很大的收益
使用EXPLAIN 和 PROFILING, 使用EXPLAIN可以看到一條sql語句在當前狀態(tài)的數(shù)據(jù)庫中的執(zhí)行計劃, 而PROFILING則可以看到一條sql語句具體耗費的資源情況,從而找到性能瓶頸。
只取出自己需要的column, 尤其是在需要排序的query中, 取出的column越少傳輸數(shù)據(jù)量就越小,從而也就有更好的性能,在mysql4.1之后,排序時會將所需的所有column取出,在排序區(qū)排好以后返回給客戶端,因此如果將不需要的column也取出,就是對內(nèi)存的浪費劫拢。
永遠用小結(jié)果集驅(qū)動大結(jié)果集, mysql的join都是通過嵌套循環(huán)來實現(xiàn)的湾宙。驅(qū)動結(jié)果集越大,所需要循環(huán) 就越多, 那么被驅(qū)動表的訪問次數(shù)自然也就越多, 而每次訪問被驅(qū)動表,即使需要的IO很少,循環(huán)次數(shù)多了, 總量也不可能小, 而且每次循環(huán)都不能避免消耗CPU, 所以CPU運算量也會增加扒吁。
盡可能在索引中完成排序, 利用索引進行排序操作, 主要是利用了索引的有序性。在通過索引進行檢索 的過程中，就已經(jīng)得到了有序的數(shù)據(jù)訪問順序, 依次讀取結(jié)果數(shù)據(jù)后就不須要進行排序操作,進而避免了此操作借浊。
僅僅使用最有效的查詢條件,避免mysql作出不理想的執(zhí)行計劃版保。
盡可能避免復雜join和子查詢, 當并發(fā)量比較高的時候, 容易發(fā)生鎖爭用和死鎖拐揭。
2. 關(guān)于索引
主要針對InnoDB引擎的B-Tree索引做一些探究, B-Tree索引通過B+樹(一種Balance Tree結(jié)構(gòu)),所有實際需要的數(shù)據(jù)都存放于Leaf Node, 而索引則存放在Branch Node娶吞。對于主鍵索引而言,其Leaf Node中存儲的就是表的實際數(shù)據(jù)(不僅包括主鍵,而且包括其他數(shù)據(jù)), 這個表的數(shù)據(jù)按照主鍵有序排列。而普通索引的Leaf Node中則存放的是索引鍵和主鍵值, 因此在InnoDB中通過主鍵訪問數(shù)據(jù)效率是非常高的, 而如果通過 普通索引來訪的話, InnoDB首先通過普通索引的相關(guān)信息及相應的索引鍵檢索到Leaf Node, 再通過Leaf Node中存放的主鍵值和主鍵索引來獲取相應的數(shù)據(jù)行牵寺。索引可以節(jié)省IO次數(shù), 但是也會增加數(shù)據(jù)量和修改操作的復雜度, 可以根據(jù)一下幾個原則判斷是否為一個字段創(chuàng)建索引:
較為頻繁的作為查詢條件的字段應當建索引
唯一性太差的字段不適合單獨創(chuàng)建建索引
更新非常頻繁字段不適合創(chuàng)建索引
不會出現(xiàn)在where子句中的字段不適合創(chuàng)建索引
join操作內(nèi)驅(qū)表使用的字段應該建索引, join語句會根據(jù)內(nèi)驅(qū)表的join字段進行循環(huán)查詢,因此在改字段上加索引可以有效提高join語句的性能
需要用來做排序(ORDER BY)悍引、分組(GROUP BY)、 去重(DISTINCT)的字段可以考慮建索引,排序操作創(chuàng)建索引的原理在基本原則中已經(jīng)說明, mysql的分組操作也可以利用索引,而且分組操作默認會進行排序(如果對順序不關(guān)心,可以通過在整個語句后面添加一個以null排序的[ORDER BY null]子句來顯式禁止排序提高性能)
