mysql索引優(yōu)化

1.mysql 的存儲形式是b+tree 典格。

2.使用索引時機(jī)

1.哪些情況需要創(chuàng)建索引

1).主鍵自動建立唯一索引

2).頻繁作為查詢查詢條件的字段應(yīng)該創(chuàng)建索引

3).查詢中與其它表關(guān)聯(lián)的字段,外鍵關(guān)系建立索引

4).頻繁更新的字段不適合創(chuàng)建索引

5).where條件里用不到的字段不創(chuàng)建索引

6).單鍵/組合索引的選擇問題(在高并發(fā)下傾向創(chuàng)建組合索引)

7).查詢中排序的字段妹窖,排序字段若通過索引去訪問將大大提高排序速度

8).查詢中統(tǒng)計(jì)或者分組字段

2.哪些情況不要創(chuàng)建索引

1).表記錄太少

2).經(jīng)常增刪改的表(因?yàn)椴粌H要保存數(shù)據(jù)啊奄,還要保存一下索引文件)

3).數(shù)據(jù)重復(fù)且分布平均的表字段渐苏,因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。

注意:如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容菇夸,為它建立索引就沒有太大的實(shí)際效果琼富。


二張表

性能優(yōu)化:left join是由左邊決定的,左邊一定都有庄新,所以右邊是我們的關(guān)鍵點(diǎn)鞠眉,建立索引要建右邊邊。當(dāng)然如果索引在左邊摄咆,可以用右連接凡蚜。

1

2

select * from atable?

left join btable? on atable.aid=btable.bid;? // 最好在bid上建索引

結(jié)論:

盡可能減少Join語句中的NestedLoop的循環(huán)次數(shù):“永遠(yuǎn)用小結(jié)果集驅(qū)動大的結(jié)果集”

避免索引失效

2.最佳左前綴法則:如果索引了多列,要尊守最左前綴法則吭从,指的是查詢從索引的最左前列開始并且不跳過索引中的列朝蜘。

3.不在索引列上做任何操作(計(jì)算、函數(shù)涩金、(自動or手動)類型轉(zhuǎn)換)谱醇,會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。

4.存儲引擎不能使用索引中范圍條件右邊的列步做。

如這樣的sql:?select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引副渴,只有username和age會生效,phone的索引沒有用到全度。

5.盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列致))煮剧,如select age from user減少select *

6.mysql在使用不等于(!= 或者 <>)的時候無法使用索引會導(dǎo)致全表掃描。

7.is null, is not null 也無法使用索引将鸵。

8.like 以通配符開頭(‘%abc..’)mysql索引失效會變成全表掃描的操作勉盅。

所以最好用右邊like 'abc%'。如果兩邊都要用顶掉,可以用select age from user where username like '%abc%',其中age是索引列

假如index(a,b,c),?where a=3 and b like 'abc%' and c=4草娜,a能用,b能用痒筒,c不能用

9.字符串不加單引號索引失效

10.少用or宰闰,用它來連接時會索引失效

11.盡量避免子查詢茬贵,而用join

一般性建議

對于單鍵索引,盡量選擇針對當(dāng)前query過濾性更好的索引

在選擇組合索引的時候移袍,當(dāng)前Query中過濾性最好的字段在索引字段順序中解藻,位置越靠前越好

在選擇組合索引的時候,盡量選擇可以能夠包含當(dāng)前query中的where子句中更多字段的索引

盡可能通過分析統(tǒng)計(jì)信息和調(diào)整query的寫法來達(dá)到選擇合適索引的目的咐容。

left/right join注意

1).on與 where的執(zhí)行順序

ON 條件(“A LEFT JOIN B ON 條件表達(dá)式”中的ON)用來決定如何從 B 表中檢索數(shù)據(jù)行舆逃。如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會被使用蚂维。僅在匹配階段完成以后戳粒,WHERE 子句條件才會被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾虫啥。

所以我們要注意:在使用Left (right) join的時候蔚约,一定要在先給出盡可能多的匹配滿足條件,減少Where的執(zhí)行涂籽。如:

2).注意ON 子句和 WHERE 子句的不同

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

mysql> SELECT * FROM product LEFT JOIN product_details

? ? ? ON (product.id = product_details.id)

? ? ? AND product_details.id=2;

+----+--------+------+--------+-------+

| id | amount | id? | weight | exist |

+----+--------+------+--------+-------+

|? 1 |? ? 100 | NULL |? NULL |? NULL |

|? 2 |? ? 200 |? ? 2 |? ? 22 |? ? 0 |

|? 3 |? ? 300 | NULL |? NULL |? NULL |

|? 4 |? ? 400 | NULL |? NULL |? NULL |

+----+--------+------+--------+-------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM product LEFT JOIN product_details

? ? ? ON (product.id = product_details.id)

? ? ? WHERE product_details.id=2;

+----+--------+----+--------+-------+

| id | amount | id | weight | exist |

+----+--------+----+--------+-------+

|? 2 |? ? 200 |? 2 |? ? 22 |? ? 0 |

+----+--------+----+--------+-------+

1 row in set (0.01 sec)

  從上可知苹祟,第一條查詢使用 ON 條件決定了從 LEFT JOIN的 product_details表中檢索符合的所有數(shù)據(jù)行。第二條查詢做了簡單的LEFT JOIN评雌,然后使用 WHERE 子句從 LEFT JOIN的數(shù)據(jù)中過濾掉不符合條件的數(shù)據(jù)行树枫。


1.永遠(yuǎn)小表驅(qū)動大表

在java程序里,兩個for循環(huán)景东,循環(huán)次數(shù)不管誰先誰后都是兩者次數(shù)相乘砂轻。

但在mysql的語句中,一定要小表驅(qū)動大表斤吐,因?yàn)樾”砀鶰ysql連接和釋放數(shù)量少

如in與exists

1

2

3

4

5

6

7

8

9

10

11

12

13

select * from A where id in (select id form B)

等價于

for select id from B

for select * from A where A.id=B.id

結(jié)論:當(dāng)B表的數(shù)據(jù)集必須小于A表的數(shù)據(jù)集時搔涝,用in優(yōu)于exists。

select * from A where exists (select 1 from B where B.id=A.id) // 這里的1用任何常量都行

等價于

for select * from A

for select * from B where B.id=A.id

結(jié)論:當(dāng)A表的數(shù)據(jù)集必須小于B表的數(shù)據(jù)集時和措,用in索引優(yōu)化分析 | liucw's blog鏈接1優(yōu)于庄呈。

注意:A表與B表的ID字段應(yīng)建立索引

exists通用語法select ... from table where exists (subquery)

該語法可以理解為:將主查詢的數(shù)據(jù),放到子查詢中做條件驗(yàn)證派阱,根據(jù)驗(yàn)證結(jié)果(true或false)來決定主查詢的數(shù)據(jù)結(jié)果是否得以保留诬留。

提示:

1).exists(subquery)只返回true或false, 因此子查詢中select *也可以是select 1或select 'X', 官方說法是實(shí)際執(zhí)行會忽略select 清單贫母,因此沒有區(qū)別文兑。

2).exists 子查詢的實(shí)際執(zhí)行過程可能經(jīng)過了優(yōu)化而不是我們理解上的逐條對比,如果擔(dān)憂效率問題颁独,可進(jìn)行實(shí)際檢驗(yàn)彩届。

3).exists 子查詢往往也可以用條件表達(dá)式、其他子查詢或者JOIN來替代誓酒,何種最優(yōu)需要具體問題具體分析樟蠕。


鏈接1

鏈接2

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末贮聂,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子寨辩,更是在濱河造成了極大的恐慌吓懈,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,807評論 6 518
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件靡狞,死亡現(xiàn)場離奇詭異耻警,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī)甸怕,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,284評論 3 399
  • 文/潘曉璐 我一進(jìn)店門甘穿,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人梢杭,你說我怎么就攤上這事温兼。” “怎么了武契?”我有些...
    開封第一講書人閱讀 169,589評論 0 363
  • 文/不壞的土叔 我叫張陵募判,是天一觀的道長。 經(jīng)常有香客問我咒唆,道長届垫,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 60,188評論 1 300
  • 正文 為了忘掉前任全释,我火速辦了婚禮装处,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘恨溜。我一直安慰自己符衔,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,185評論 6 398
  • 文/花漫 我一把揭開白布糟袁。 她就那樣靜靜地躺著判族,像睡著了一般。 火紅的嫁衣襯著肌膚如雪项戴。 梳的紋絲不亂的頭發(fā)上形帮,一...
    開封第一講書人閱讀 52,785評論 1 314
  • 那天,我揣著相機(jī)與錄音周叮,去河邊找鬼辩撑。 笑死,一個胖子當(dāng)著我的面吹牛仿耽,可吹牛的內(nèi)容都是我干的合冀。 我是一名探鬼主播,決...
    沈念sama閱讀 41,220評論 3 423
  • 文/蒼蘭香墨 我猛地睜開眼项贺,長吁一口氣:“原來是場噩夢啊……” “哼君躺!你這毒婦竟也來了峭判?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 40,167評論 0 277
  • 序言:老撾萬榮一對情侶失蹤棕叫,失蹤者是張志新(化名)和其女友劉穎林螃,沒想到半個月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體俺泣,經(jīng)...
    沈念sama閱讀 46,698評論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡疗认,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,767評論 3 343
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了伏钠。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片横漏。...
    茶點(diǎn)故事閱讀 40,912評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖贝润,靈堂內(nèi)的尸體忽然破棺而出绊茧,到底是詐尸還是另有隱情铝宵,我是刑警寧澤打掘,帶...
    沈念sama閱讀 36,572評論 5 351
  • 正文 年R本政府宣布,位于F島的核電站鹏秋,受9級特大地震影響尊蚁,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜侣夷,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,254評論 3 336
  • 文/蒙蒙 一横朋、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧百拓,春花似錦琴锭、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,746評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至蓖捶,卻和暖如春地回,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背俊鱼。 一陣腳步聲響...
    開封第一講書人閱讀 33,859評論 1 274
  • 我被黑心中介騙來泰國打工刻像, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人并闲。 一個月前我還...
    沈念sama閱讀 49,359評論 3 379
  • 正文 我出身青樓细睡,卻偏偏與公主長得像,于是被迫代替她去往敵國和親帝火。 傳聞我的和親對象是個殘疾皇子溜徙,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,922評論 2 361

推薦閱讀更多精彩內(nèi)容