數(shù)據(jù)庫(kù)使用上的優(yōu)化

1.查詢?cè)瓌t

不查>少查>索引查詢>普通查詢

1.1不查詢

沒(méi)有必要進(jìn)行查詢的則不要進(jìn)行查詢啸箫。如第一次查詢了客戶列表信息趟脂，列表中查詢顯示了客戶簡(jiǎn)單信息纷闺；則第二次查詢?cè)斍闀r(shí)不要再去查詢客戶信息金砍，能不查就不查

列表信息

查看時(shí)直接回顯

1.2少查詢

少查詢需要結(jié)合業(yè)務(wù)需求址否，業(yè)務(wù)上不經(jīng)常更新的數(shù)據(jù)可以少查詢餐蔬，如論壇的日活躍量/在線人數(shù)等∮痈剑或者如crm中角色的默認(rèn)菜單等樊诺，前端可以存cookie且設(shè)置較長(zhǎng)的有效期。

百度的產(chǎn)品列表

1.3索引查詢

索引查詢是優(yōu)化查詢的主體音同，任何查詢都應(yīng)該盡量向索引靠

1.4普通查詢

應(yīng)該給表建立簡(jiǎn)潔的索引樹词爬，盡量少進(jìn)行普通查詢

2. Explain的使用

explain是解釋一個(gè)語(yǔ)句的執(zhí)行計(jì)劃。語(yǔ)句的優(yōu)化可以在explain的結(jié)果中做出分析

explain解析語(yǔ)句

2.1 select_type权均，語(yǔ)句結(jié)構(gòu)

Simple顿膨，簡(jiǎn)單查詢，即基礎(chǔ)查詢

簡(jiǎn)單查詢

Primary(主句)叽赊，非簡(jiǎn)單查詢中的主體語(yǔ)句

SubQuery（非From子查詢）（5.7版本優(yōu)化很多子查詢恋沃，更能理解語(yǔ)句的執(zhí)行意義）

Derived（派生From子查詢）（5.7版本優(yōu)化很多子查詢，更能理解語(yǔ)句的執(zhí)行意義）

Primary和SubQuery

Primary和Derived

Union（聯(lián)合查詢）必指、UnionResult囊咏；

Primary、Union、UnionResult

2.2 table:真實(shí)表名梅割、表的別名霜第，表的派生名（子查詢結(jié)果），null（沒(méi)用到表）户辞；

2.3 patitions分區(qū)

2.4 type(關(guān)鍵因素):

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

type

2.4.1 system:特殊的const泌类，表中原本就只有一條數(shù)據(jù)，5.7已經(jīng)沒(méi)有該type

2.4.2 const:不變量底燎，查詢結(jié)果為單行如select * from student where id=1

const

2.4.3 eq_ref:查詢中使用索引作為查詢字段刃榨，這個(gè)索引是單一的，即主鍵或unique類型索引,多用在聯(lián)合查詢中

eq_ref的使用

2.4.4 ref:查詢中使用索引作為查詢字段书蚪，這個(gè)索引是非單一的喇澡，即noraml類型索引，聯(lián)合查詢使用

age是normal字段的索引

2.4.5 fulltext:全文索引(很少用)

2.4.6 ref_or_null:ref情況下殊校，索引值為null作為條件之一

age is null是條件之一

2.4.7 index_merge:多個(gè)索引結(jié)果合并出最終結(jié)果，如or

2個(gè)獨(dú)立索引的or組合條件查詢

2.4.8 unique_subquery：子查詢中的eq_ref（5.7版本中沒(méi)有找到合適結(jié)果读存，應(yīng)該已經(jīng)舍去）

2.4.9 index_subquery：子查詢中的ref（5.7版本中沒(méi)有找到合適結(jié)果为流，應(yīng)該已經(jīng)舍去）

2.4.10 range:索引有個(gè)范圍的查詢（主鍵專用）

主鍵專用

2.4.11 index:全索引查詢

普通的索引查詢

2.4.12 all:全表查詢

最慢的全表查詢

索引的使用過(guò)程中盡量不對(duì)對(duì)索引字段做函數(shù)處理，否則效果會(huì)變化

總結(jié)來(lái)說(shuō)：const>[eq_ref>ref>index_merge>]range>index>all

1.查詢中能確定單條的查詢則先查出來(lái)

某個(gè)值會(huì)根據(jù)條件確定让簿，則可以先查出來(lái)

2.索引范圍能確定的就確定掉

3.盡量使用索引查詢敬察，減少回行情況

4.聯(lián)合查詢中使用索引的性能順序是Primar|Unique>Noraml>merge

5.盡量避免耗時(shí)操作,帶有DISTINCT,UNION,ORDER BY的SQL語(yǔ)句，因?yàn)槿菀籽苌硬樵兓騯nion查詢

6.避免在WHERE子句中使用in尔当，not? in莲祸，or?或者h(yuǎn)aving，容易衍生子查詢

7.使用union代替臨時(shí)表椭迎，如果無(wú)需排除重復(fù)值或是操作集無(wú)重復(fù)則用UNION ALL锐帜，?UNION很明顯是需要比較重復(fù)性的

8. 盡量不要在建立的索引的數(shù)據(jù)列上進(jìn)行操作，如：計(jì)算畜号、IS NULL和IS NOT NULL缴阎、類型轉(zhuǎn)換、函數(shù)使用简软，like操作等

9.避免建立索引的列中使用空值蛮拔。

優(yōu)化示例：

student表（大數(shù)據(jù)量表）

student表結(jié)構(gòu)

teacher表

teacher表結(jié)構(gòu)

student_teacher關(guān)系表（大數(shù)據(jù)量表）

關(guān)系表

1.查找id為1222學(xué)生的老師id

a. explain可以看到該語(yǔ)句查詢關(guān)系表使用的是全表查詢

explain

b.未優(yōu)化的執(zhí)行效果，可以看到需要花費(fèi)半秒多時(shí)間才能查出

未優(yōu)化的執(zhí)行

c.優(yōu)化

explain看出語(yǔ)句慢是慢在關(guān)系表的查詢上痹升，則需要對(duì)該表進(jìn)行語(yǔ)句或結(jié)構(gòu)優(yōu)化建炫。

優(yōu)化手段1：因?yàn)樵摾袑W(xué)生對(duì)老師的關(guān)系是一對(duì)多的，所以student與student_teacher的關(guān)系是一對(duì)一疼蛾「氐可以根據(jù)需求情況認(rèn)為找到student_id=1222的記錄，這樣全表查詢就變?yōu)閏onst查詢

常量查詢

優(yōu)化后語(yǔ)句只要0.001s就查出來(lái)了

優(yōu)化手段2：本著索引不宜多加的情況，手段1是行得通的惋砂，但人為干預(yù)去預(yù)知單條數(shù)據(jù)在業(yè)務(wù)開發(fā)中是不夠現(xiàn)實(shí)的妒挎，這時(shí)就只能退而求其次，給關(guān)系表的student_id加索引

加索引

加索引后西饵，all查詢也變成了const查詢了酝掩，執(zhí)行結(jié)果也是0.001s就查出來(lái)了

2.查詢所有學(xué)生信息

a.explain查看，發(fā)現(xiàn)是全表查詢眷柔，因?yàn)榛匦辛?/p>

回行了

b.未優(yōu)化結(jié)果

4.069s

c.優(yōu)化期虾，手段就是避免回行，拿索引字段

只能有用的字段9