MySQL索引及查詢優(yōu)化

1. ? ? 索引及查詢優(yōu)化

索引的類型

??普通索引:這是最基本的索引類型厌小,沒唯一性之類的限制衫嵌。

??唯一性索引:和普通索引基本相同牍汹,但所有的索引列值保持唯一性。

??主鍵:主鍵是一種唯一索引稚补,但必須指定為”PRIMARY KEY”童叠。

??全文索引:MYSQL從3.23.23開始支持全文索引和全文檢索。在MYSQL中课幕,全文索引的索引類型為FULLTEXT厦坛。全文索引可以在VARCHAR或者TEXT類型的列上創(chuàng)建。

大多數(shù)MySQL索引(PRIMARY KEY乍惊、UNIQUE杜秸、INDEX和FULLTEXT)使用B樹中存儲∪笠铮空間列類型的索引使用R-樹撬碟,MEMORY表支持hash索引。

單列索引和多列索引(復(fù)合索引)

索引可以是單列索引莉撇,也可以是多列索引小作。對相關(guān)的列使用索引是提高SELECT操作性能的最佳途徑之一。

多列索引:

MySQL可以為多個(gè)列創(chuàng)建索引稼钩。一個(gè)索引可以包括15個(gè)列。對于某些列類型达罗,可以索引列的左前綴坝撑,列的順序非常重要。

多列索引可以視為包含通過連接索引列的值而創(chuàng)建的值的排序的數(shù)組粮揉。一般來說巡李,即使是限制最嚴(yán)格的單列索引,它的限制能力也遠(yuǎn)遠(yuǎn)低于多列索引扶认。

最左前綴

多列索引有一個(gè)特點(diǎn)侨拦,即最左前綴(Leftmost Prefixing)。假如有一個(gè)多列索引為key(firstname lastname age)辐宾,當(dāng)搜索條件是以下各種列的組合和順序時(shí)狱从,MySQL將使用該多列索引:

firstname,lastname叠纹,age

firstname季研,lastname

firstname

也就是說,相當(dāng)于還建立了key(firstname lastname)和key(firstname)誉察。

索引主要用于下面的操作:

??快速找出匹配一個(gè)WHERE子句的行与涡。

??刪除行。當(dāng)執(zhí)行聯(lián)接時(shí),從其它表檢索行驼卖。

??對具體有索引的列key_col找出MAX()或MIN()值氨肌。由預(yù)處理器進(jìn)行優(yōu)化,檢查是否對索引中在key_col之前發(fā)生所有關(guān)鍵字元素使用了WHERE?key_part_#?=?constant酌畜。在這種情況下怎囚,MySQL為每個(gè)MIN()或MAX()表達(dá)式執(zhí)行一次關(guān)鍵字查找,并用常數(shù)替換它檩奠。如果所有表達(dá)式替換為常量桩了,查詢立即返回。例如:

SELECT MIN(key2), MAX (key2)??FROM?tb?WHERE?key1=10;

??如果對一個(gè)可用關(guān)鍵字的最左面的前綴進(jìn)行了排序或分組(例如埠戳,ORDER BY key_part_1,key_part_2)井誉,排序或分組一個(gè)表。如果所有關(guān)鍵字元素后面有DESC整胃,關(guān)鍵字以倒序被讀取颗圣。

??在一些情況中,可以對一個(gè)查詢進(jìn)行優(yōu)化以便不用查詢數(shù)據(jù)行即可以檢索值屁使。如果查詢只使用來自某個(gè)表的數(shù)字型并且構(gòu)成某些關(guān)鍵字的最左面前綴的列在岂,為了更快,可以從索引樹檢索出值蛮寂。

SELECT?key_part3?FROM?tb?WHERE?key_part1=1

有時(shí)MySQL不使用索引蔽午,即使有可用的索引。一種情形是當(dāng)優(yōu)化器估計(jì)到使用索引將需要MySQL訪問表中的大部分行時(shí)酬蹋。(在這種情況下及老,表掃描可能會更快些)。然而范抓,如果此類查詢使用LIMIT只搜索部分行骄恶,MySQL則使用索引,因?yàn)樗梢愿斓卣业綆仔胁⒃诮Y(jié)果中返回匕垫。

合理的建立索引的建議:

(1)? 越小的數(shù)據(jù)類型通常更好:越小的數(shù)據(jù)類型通常在磁盤僧鲁、內(nèi)存和CPU緩存中都需要更少的空間,處理起來更快象泵。

(2)? 簡單的數(shù)據(jù)類型更好:整型數(shù)據(jù)比起字符寞秃,處理開銷更小,因?yàn)樽址谋容^更復(fù)雜单芜。在MySQL中蜕该,應(yīng)該用內(nèi)置的日期和時(shí)間數(shù)據(jù)類型,而不是用字符串來存儲時(shí)間洲鸠;以及用整型數(shù)據(jù)類型存儲IP地址堂淡。

(3)? 盡量避免NULL:應(yīng)該指定列為NOT NULL馋缅,除非你想存儲NULL。在MySQL中绢淀,含有空值的列很難進(jìn)行查詢優(yōu)化萤悴,因?yàn)樗鼈兪沟盟饕⑺饕慕y(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜皆的。你應(yīng)該用0覆履、一個(gè)特殊的值或者一個(gè)空串代替空值

這部分是關(guān)于索引和寫SQL語句時(shí)應(yīng)當(dāng)注意的一些瑣碎建議和注意點(diǎn)。

1.當(dāng)結(jié)果集只有一行數(shù)據(jù)時(shí)使用LIMIT 1

2.避免SELECT *费薄,始終指定你需要的列

從表中讀取越多的數(shù)據(jù)硝全,查詢會變得更慢。他增加了磁盤需要操作的時(shí)間楞抡,還是在數(shù)據(jù)庫服務(wù)器與WEB服務(wù)器是獨(dú)立分開的情況下伟众。你將會經(jīng)歷非常漫長的網(wǎng)絡(luò)延遲,僅僅是因?yàn)閿?shù)據(jù)不必要的在服務(wù)器之間傳輸召廷。

3.使用連接(JOIN)來代替子查詢(Sub-Queries)

連接(JOIN).. 之所以更有效率一些凳厢,是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作。

4.使用ENUM竞慢、CHAR而不是VARCHAR先紫,使用合理的字段屬性長度

5.盡可能的使用NOT NULL

6.固定長度的表會更快

7.拆分大的DELETEINSERT語句

8.查詢的列越小越快

Where條件

在查詢中,WHERE條件也是一個(gè)比較重要的因素筹煮,盡量少并且是合理的where條件是很重要的遮精,盡量在多個(gè)條件的時(shí)候,把會提取盡量少數(shù)據(jù)量的條件放在前面败潦,減少后一個(gè)where條件的查詢時(shí)間仑鸥。

有些where條件會導(dǎo)致索引無效:

??where子句的查詢條件里有!=变屁,MySQL將無法使用索引。

??where子句使用了Mysql函數(shù)的時(shí)候意狠,索引將無效粟关,比如:select * from tb where left(name, 4) = ‘xxx’

??使用LIKE進(jìn)行搜索匹配的時(shí)候,這樣索引是有效的:select * from tbl1 where name like ‘xxx%’环戈,而like ‘%xxx%’ 時(shí)索引無效

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末闷板,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子院塞,更是在濱河造成了極大的恐慌遮晚,老刑警劉巖,帶你破解...
    沈念sama閱讀 211,743評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件拦止,死亡現(xiàn)場離奇詭異县遣,居然都是意外死亡糜颠,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,296評論 3 385
  • 文/潘曉璐 我一進(jìn)店門萧求,熙熙樓的掌柜王于貴愁眉苦臉地迎上來其兴,“玉大人,你說我怎么就攤上這事夸政≡” “怎么了?”我有些...
    開封第一講書人閱讀 157,285評論 0 348
  • 文/不壞的土叔 我叫張陵守问,是天一觀的道長匀归。 經(jīng)常有香客問我,道長耗帕,這世上最難降的妖魔是什么穆端? 我笑而不...
    開封第一講書人閱讀 56,485評論 1 283
  • 正文 為了忘掉前任,我火速辦了婚禮兴垦,結(jié)果婚禮上徙赢,老公的妹妹穿的比我還像新娘。我一直安慰自己探越,他們只是感情好狡赐,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,581評論 6 386
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著钦幔,像睡著了一般枕屉。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上鲤氢,一...
    開封第一講書人閱讀 49,821評論 1 290
  • 那天搀擂,我揣著相機(jī)與錄音,去河邊找鬼卷玉。 笑死哨颂,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的相种。 我是一名探鬼主播威恼,決...
    沈念sama閱讀 38,960評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼寝并!你這毒婦竟也來了箫措?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 37,719評論 0 266
  • 序言:老撾萬榮一對情侶失蹤衬潦,失蹤者是張志新(化名)和其女友劉穎斤蔓,沒想到半個(gè)月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體镀岛,經(jīng)...
    沈念sama閱讀 44,186評論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡弦牡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,516評論 2 327
  • 正文 我和宋清朗相戀三年友驮,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片喇伯。...
    茶點(diǎn)故事閱讀 38,650評論 1 340
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡喊儡,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出稻据,到底是詐尸還是另有隱情艾猜,我是刑警寧澤,帶...
    沈念sama閱讀 34,329評論 4 330
  • 正文 年R本政府宣布捻悯,位于F島的核電站匆赃,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏今缚。R本人自食惡果不足惜算柳,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,936評論 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望姓言。 院中可真熱鬧瞬项,春花似錦、人聲如沸何荚。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,757評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽餐塘。三九已至妥衣,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間戒傻,已是汗流浹背税手。 一陣腳步聲響...
    開封第一講書人閱讀 31,991評論 1 266
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留需纳,地道東北人芦倒。 一個(gè)月前我還...
    沈念sama閱讀 46,370評論 2 360
  • 正文 我出身青樓,卻偏偏與公主長得像不翩,于是被迫代替她去往敵國和親熙暴。 傳聞我的和親對象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,527評論 2 349

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