mysql數(shù)據(jù)庫(kù)索引相關(guān)

一 介紹

什么是索引？

索引在MySQL中也叫做“鍵”，是存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。索引對(duì)于良好的性能
非常關(guān)鍵，尤其是當(dāng)表中的數(shù)據(jù)量越來(lái)越大時(shí)，索引對(duì)于性能的影響愈發(fā)重要。
索引優(yōu)化應(yīng)該是對(duì)查詢性能優(yōu)化最有效的手段了。索引能夠輕易將查詢性能提高好幾個(gè)數(shù)量級(jí)。
索引相當(dāng)于字典的音序表，如果要查某個(gè)字，如果不使用音序表，則需要從幾百頁(yè)中逐頁(yè)去查。

（建索引是讓mysql提供的一種數(shù)據(jù)結(jié)構(gòu)）

索引相關(guān)見(jiàn)解

索引是應(yīng)用程序設(shè)計(jì)和開(kāi)發(fā)的一個(gè)重要方面。若索引太多，應(yīng)用程序的性能可能會(huì)受到影響。而索引太少，對(duì)查詢性能又會(huì)產(chǎn)生影響，要找到一個(gè)平衡點(diǎn)，這對(duì)應(yīng)用程序的性能至關(guān)重要。一些開(kāi)發(fā)人員總是在事后才想起添加索引----我一直認(rèn)為，這源于一種錯(cuò)誤的開(kāi)發(fā)模式。如果知道數(shù)據(jù)的使用，從一開(kāi)始就應(yīng)該在需要處添加索引。開(kāi)發(fā)人員往往對(duì)數(shù)據(jù)庫(kù)的使用停留在應(yīng)用的層面，比如編寫(xiě)SQL語(yǔ)句、存儲(chǔ)過(guò)程之類(lèi)，他們甚至可能不知道索引的存在，或認(rèn)為事后讓相關(guān)DBA加上即可。DBA往往不夠了解業(yè)務(wù)的數(shù)據(jù)流，而添加索引需要通過(guò)監(jiān)控大量的SQL語(yǔ)句進(jìn)而從中找到問(wèn)題，這個(gè)步驟所需的時(shí)間肯定是遠(yuǎn)大于初始添加索引所需的時(shí)間，并且可能會(huì)遺漏一部分的索引。當(dāng)然索引也并不是越多越好，我曾經(jīng)遇到過(guò)這樣一個(gè)問(wèn)題：某臺(tái)MySQL服務(wù)器iostat顯示磁盤(pán)使用率一直處于100%，經(jīng)過(guò)分析后發(fā)現(xiàn)是由于開(kāi)發(fā)人員添加了太多的索引，在刪除一些不必要的索引之后，磁盤(pán)使用率馬上下降為20%?？梢?jiàn)索引的添加也是非常有技術(shù)含量的。

二、索引的原理

索引的目的在于提高查詢效率。

本質(zhì)：通過(guò)不斷的縮小想要獲取數(shù)據(jù)的范圍來(lái)篩選出最終想要的結(jié)果，同時(shí)把隨機(jī)的事件變成順序的事件，也就是，有了這種索引機(jī)制，我們可以總是用同一種查找方式來(lái)鎖定數(shù)據(jù)。

三、 索引的數(shù)據(jù)結(jié)構(gòu)

需要這種數(shù)據(jù)結(jié)構(gòu)能夠做些什么，其實(shí)很簡(jiǎn)單，那就是：每次查找數(shù)據(jù)時(shí)把磁盤(pán)IO次數(shù)控制在一個(gè)很小的數(shù)量級(jí)，最好是常數(shù)數(shù)量級(jí)。那么我們就想到如果一個(gè)高度可控的多路搜索樹(shù)是否能滿足需求呢？就這樣，b+樹(shù)應(yīng)運(yùn)而生（B+樹(shù)是通過(guò)二叉查找樹(shù)，再由平衡二叉樹(shù)，B樹(shù)演化而來(lái)）。

如上圖，是一顆b+樹(shù)，關(guān)于b+樹(shù)的定義可以參見(jiàn)B+樹(shù)，這里只說(shuō)一些重點(diǎn)，淺藍(lán)色的塊我們稱(chēng)之為一個(gè)磁盤(pán)塊，可以看到每個(gè)磁盤(pán)塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），如磁盤(pán)塊1包含數(shù)據(jù)項(xiàng)17和35，包含指針P1、P2、P3，P1表示小于17的磁盤(pán)塊，P2表示在17和35之間的磁盤(pán)塊，P3表示大于35的磁盤(pán)塊。真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如17、35并不真實(shí)存在于數(shù)據(jù)表中。

b+樹(shù)性質(zhì)

1、索引字段要盡量的小

2、索引的最左匹配特性

三、常見(jiàn)的索引

索引分類(lèi):

1、普通索引INDEX：加速查找

　　唯一索引：
2、-主鍵索引PRIMARY KEY：加速查找+約束（不為空、不能重復(fù)）
3、-唯一索引UNIQUE:加速查找+約束（不能重復(fù)）
4、-組合索引

　　組合索引是將n個(gè)列組合成一個(gè)索引

5、聯(lián)合索引：

??? -PRIMARY KEY(id,name):聯(lián)合主鍵索引
??? -UNIQUE(id,name):聯(lián)合唯一索引
??? -INDEX(id,name):聯(lián)合普通索引

除此之外還有全文索引，即FULLTEXT，但其實(shí)對(duì)于全文搜索，我們并不會(huì)使用MySQL自帶的該索引，而是會(huì)選擇第三方軟件如Sphinx，專(zhuān)門(mén)來(lái)做全文搜索。

二、索引類(lèi)型

　　索引主要包括hash和btree兩大類(lèi)型，我們?cè)趧?chuàng)建索引時(shí)可以為其指定索引類(lèi)型。其中hash類(lèi)型的索引：查詢單條快，范圍查詢慢；btree類(lèi)型的索引：b+樹(shù)，層數(shù)越多，數(shù)據(jù)量指數(shù)級(jí)增長(zhǎng)（我們就用它，因?yàn)閕nnodb默認(rèn)支持它）

#不同的存儲(chǔ)引擎支持的索引類(lèi)型也不一樣 InnoDB 支持事務(wù)，支持行級(jí)別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； MyISAM 不支持事務(wù)，支持表級(jí)別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory 不支持事務(wù)，支持表級(jí)別鎖定，支持 B-tree、Hash 等索引，不支持 Full-text 索引； NDB 支持事務(wù)，支持行級(jí)別鎖定，支持 Hash 索引，不支持 B-tree、Full-text 等索引； Archive 不支持事務(wù)，支持表級(jí)別鎖定，不支持 B-tree、Hash、Full-text 等索引；

三、創(chuàng)建與刪除索引

1、在創(chuàng)建表時(shí)創(chuàng)建索引

create table t1(
??? id int,
??? name char(5),
??? age int,
??? unique key uni_name(name),??? # uni_name 為索引名 ??? ?
??? index index_age(age),???????? # index_age 為索引名，不需要key
??? primary key(id)?????????????? # primary 不需要起索引名，起了也不顯示 ?? ?
???? );

2、創(chuàng)建完表后為其添加索引

3、刪除索引

?

drop index indx_id on t3;alter table t3 drop primary key;

　　上述第一個(gè)刪除語(yǔ)法中，因primary key 沒(méi)有名字，所以刪除方式為：drop index ‘primary’ on t3，其他有名字的索引刪除方式為：drop index 索引名 on 表名

?

四、測(cè)試索引

　　按照如下sql語(yǔ)句創(chuàng)建表s1，后續(xù)所有測(cè)試均基于此表：

#1. 準(zhǔn)備表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) );#2. 創(chuàng)建存儲(chǔ)過(guò)程，實(shí)現(xiàn)批量插入記錄 delimiter $$ #聲明存儲(chǔ)過(guò)程的結(jié)束符號(hào)為$$ create procedure auto_insert1() BEGINdeclare i int default 1;while(i<3000000)doinsert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));set i=i+1;end while; END$$ #$$結(jié)束 delimiter ; #重新聲明分號(hào)為結(jié)束符號(hào)#3. 查看存儲(chǔ)過(guò)程 show create procedure auto_insert1\G #4. 調(diào)用存儲(chǔ)過(guò)程 call auto_insert1();

1、加索引可以加快查詢效率，但是會(huì)降低寫(xiě)的效率

?五、正確使用索引

　　但我們必須知道，并不是說(shuō)我們創(chuàng)建了索引就一定會(huì)加快查詢速度，若想利用索引達(dá)到預(yù)想的提高查詢速度的效果，我們?cè)谔砑铀饕龝r(shí)，必須遵循以下問(wèn)題。

1、范圍問(wèn)題，或者說(shuō)條件不明確，條件中出現(xiàn)這些符號(hào)或關(guān)鍵字：>、>=、<、<=、!= 、between...and...、like

　　大于 小于

?

不等于

between...and

?

?like

?

?2、盡量選擇區(qū)分度高的字段作為索引，區(qū)分度是指的字段中數(shù)據(jù)的重復(fù)性，越重復(fù)，區(qū)分度變低

?

我們編寫(xiě)存儲(chǔ)過(guò)程為表s1批量添加記錄，name字段的值均為egon，也就是說(shuō)name這個(gè)字段的區(qū)分度很低（gender字段也是一樣的，我們稍后再搭理它）回憶b+樹(shù)的結(jié)構(gòu)，查詢的速度與樹(shù)的高度成反比，要想將樹(shù)的高低控制的很低，需要保證：在某一層內(nèi)數(shù)據(jù)項(xiàng)均是按照從左到右，從小到大的順序依次排開(kāi)，即左1<左2<左3<...而對(duì)于區(qū)分度低的字段，無(wú)法找到大小關(guān)系，因?yàn)橹刀际窍嗟鹊?#xff0c;毫無(wú)疑問(wèn)，還想要用b+樹(shù)存放這些等值的數(shù)據(jù)，只能增加樹(shù)的高度，字段的區(qū)分度越低，則樹(shù)的高度越高。極端的情況，索引字段的值都一樣，
那么b+樹(shù)幾乎成了一根棍。本例中就是這種極端的情況，name字段所有的值均為'egon'#現(xiàn)在我們得出一個(gè)結(jié)論：為區(qū)分度低的字段建立索引，索引樹(shù)的高度會(huì)很高，然而這具體會(huì)帶來(lái)什么影響呢？？？#1：如果條件是name='xxxx',那么肯定是可以第一時(shí)間判斷出'xxxx'是不在索引樹(shù)中的（因?yàn)闃?shù)中所有的值均為'egon’），所以查詢速度很快#2：如果條件正好是name='egon',查詢時(shí)，我們永遠(yuǎn)無(wú)法從樹(shù)的某個(gè)位置得到一個(gè)明確的范圍，只能往下找，往下找，往下找。。。這與全表掃描的IO次數(shù)沒(méi)有多大區(qū)別，所以速度很慢

?

3、索引字段不可以參與計(jì)算

?\

?

?

??在左邊條件成立但是索引字段的區(qū)分度低的情況下（name與gender均屬于這種情況），會(huì)依次往右找到一個(gè)區(qū)分度高的索引字段，加速查詢

?

?

??在左邊條件成立但是索引字段的區(qū)分度低的情況下（name與gender均屬于這種情況），會(huì)依次往右找到一個(gè)區(qū)分度高的索引字段，加速查詢

?經(jīng)過(guò)分析，在條件為name='egon' and gender='male' and id>333 and email='xxx'的情況下，我們完全沒(méi)必要為前三個(gè)條件的字段加索引，因?yàn)橹荒苡蒙蟚mail字段的索引，前三個(gè)字段的索引反而會(huì)降低我們的查詢效率。

?5、最左前綴匹配原則，非常重要的原則，對(duì)于組合索引mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配(指的是范圍大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

6、其他情況

- 使用函數(shù)select * from tb1 where reverse(email) = 'egon';- 類(lèi)型不一致如果列是字符串類(lèi)型，傳入條件是必須用引號(hào)引起來(lái)，不然...select * from tb1 where email = 999;#排序條件為索引，則select字段必須也是索引字段，否則無(wú)法命中 - order byselect name from s1 order by email desc;當(dāng)根據(jù)索引排序時(shí)候，select查詢的字段如果不是索引，則速度仍然很慢select email from s1 order by email desc;特別的：如果對(duì)主鍵排序，則還是速度很快：select * from tb1 order by nid desc;- 組合索引最左前綴如果組合索引為：(name,email)name and email -- 命中索引name -- 命中索引email -- 未命中索引- count(1)或count(列)代替count(*)在mysql中沒(méi)有差別了- create index xxxx on tb(title(19)) #text類(lèi)型，必須制定長(zhǎng)度 - 避免使用select * - count(1)或count(列) 代替 count(*) - 創(chuàng)建表時(shí)盡量時(shí) char 代替 varchar - 表的字段順序固定長(zhǎng)度的字段優(yōu)先 - 組合索引代替多個(gè)單列索引（經(jīng)常使用多個(gè)條件查詢時(shí)） - 盡量使用短索引 - 使用連接（JOIN）來(lái)代替子查詢(Sub-Queries) - 連表時(shí)注意條件類(lèi)型需一致 - 索引散列值（重復(fù)少）不適合建索引，例：性別不適合

六、索引合并與覆蓋

1、索引合并

#覆蓋索引：- 所有字段（條件的，查詢結(jié)果的等）都是索引字段http://blog.itpub.net/22664653/viewspace-774667/#分析 select age from s1 where id=123 and name = 'egon'; #id字段有索引，但是name字段沒(méi)有索引 該sql命中了索引，但未覆蓋全部。 利用id=123到索引的數(shù)據(jù)結(jié)構(gòu)中定位到了id字段，但是仍要判斷name字段，但是name字段沒(méi)有索引，而且查詢結(jié)果的字段age也沒(méi)有索引 最牛逼的情況是，索引字段覆蓋了所有，那全程通過(guò)索引來(lái)加速查詢以及獲取結(jié)果就ok了

七、查詢優(yōu)化神器explain

　　關(guān)于explain命令相信大家并不陌生，具體用法和字段含義可以參考官網(wǎng)explain-output，這里需要強(qiáng)調(diào)rows是核心指標(biāo)，絕大部分rows小的語(yǔ)句執(zhí)行一定很快（有例外，下面會(huì)講到）。所以優(yōu)化語(yǔ)句基本上都是在優(yōu)化rows。

轉(zhuǎn)載于:https://www.cnblogs.com/xiaoluoboer/p/8075911.html

與50位技術(shù)專(zhuān)家面對(duì)面20年技術(shù)見(jiàn)證，附贈(zèng)技術(shù)全景圖

總結(jié)

以上是生活随笔為你收集整理的关于索引的相关 day45的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。