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

1.查詢原則

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

1.1不查詢

沒有必要進(jìn)行查詢的則不要進(jìn)行查詢。如第一次查詢了客戶列表信息，列表中查詢顯示了客戶簡單信息；則第二次查詢詳情時(shí)不要再去查詢客戶信息，能不查就不查

列表信息

查看時(shí)直接回顯

1.2少查詢

少查詢需要結(jié)合業(yè)務(wù)需求，業(yè)務(wù)上不經(jīng)常更新的數(shù)據(jù)可以少查詢，如論壇的日活躍量/在線人數(shù)等?；蛘呷鏲rm中角色的默認(rèn)菜單等，前端可以存cookie且設(shè)置較長的有效期。

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

1.3索引查詢

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

1.4普通查詢

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

2. Explain的使用

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

explain解析語句

2.1 select_type，語句結(jié)構(gòu)

Simple，簡單查詢，即基礎(chǔ)查詢

簡單查詢

Primary(主句)，非簡單查詢中的主體語句

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

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

Primary和SubQuery

Primary和Derived

Union（聯(lián)合查詢）、UnionResult；

Primary、Union、UnionResult

2.2 table:真實(shí)表名、表的別名，表的派生名（子查詢結(jié)果），null（沒用到表）；

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)沒有該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版本中沒有找到合適結(jié)果，應(yīng)該已經(jīng)舍去）

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

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

主鍵專用

2.4.11 index:全索引查詢

普通的索引查詢

2.4.12 all:全表查詢

最慢的全表查詢

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

總結(jié)來說：const>[eq_ref>ref>index_merge>]range>index>all

1.查詢中能確定單條的查詢則先查出來

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

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

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

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

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

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

7.使用union代替臨時(shí)表，如果無需排除重復(fù)值或是操作集無重復(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可以看到該語句查詢關(guān)系表使用的是全表查詢

explain

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

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

c.優(yōu)化

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

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

常量查詢

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

優(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就查出來了

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

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

回行了

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

4.069s

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

只能有用的字段9