索引概念和作用

索引是一種使記錄有序化的技術崭别，它可以指定按某列/某幾列預先排序，從而大大提高查詢速度（類似于漢語詞典中按照拼音或者筆畫查找）肮之。

索引的主要作用是加快數(shù)據(jù)查找速度趾访，提高數(shù)據(jù)庫的性能七嫌。

MySQL 索引類型

從物理存儲角度上，索引可以分為聚集索引和非聚集索引店煞。

1.?聚集索引（Clustered Index）

聚集索引決定數(shù)據(jù)在磁盤上的物理排序蟹演，一個表只能有一個聚集索引。

2. 非聚集索引（Non-clustered Index）

非聚集索引并不決定數(shù)據(jù)在磁盤上的物理排序顷蟀，索引上只包含被建立索引的數(shù)據(jù)酒请，以及一個行定位符 row-locator，這個行定位符鸣个，可以理解為一個聚集索引物理排序的指針羞反，通過這個指針，可以找到行數(shù)據(jù)囤萤。

從邏輯角度昼窗，索引可以分為以下幾種。

普通索引：最基本的索引阁将，它沒有任何限制膏秫。

唯一索引：與普通索引類似，不同的就是索引列的值必須唯一做盅，但允許有空值缤削。如果是組合索引，則列值的組合必須唯一吹榴。

主鍵索引：它是一種特殊的唯一索引亭敢，用于唯一標識數(shù)據(jù)表中的某一條記錄，不允許有空值图筹，一般用 ? ? primary key 來約束帅刀。主鍵和聚集索引的關系詳見“問題詳解”中的第4題让腹。

聯(lián)合索引（又叫復合索引）：多個字段上建立的索引，能夠加速復合查詢條件的檢索扣溺。

全文索引：老版本 ? ? MySQL 自帶的全文索引只能用于數(shù)據(jù)庫引擎為 MyISAM 的數(shù)據(jù)表骇窍，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默認 MySQL 不支持中文全文檢索锥余，可以通過擴展 MySQL腹纳，添加中文全文檢索或為中文內(nèi)容表提供一個對應的英文索引表的方式來支持中文。

MySQL索引優(yōu)化規(guī)則

可以通過以下規(guī)則對 MySQL 索引進行優(yōu)化驱犹。

1.前導模糊查詢不能使用索引嘲恍。

例如下面 SQL 語句不能使用索引。

select?*?fromdoc?where?title?like?'%XX'

而非前導模糊查詢則可以使用索引雄驹，如下面的 SQL 語句佃牛。

select?*?fromdoc?where?title?like?'XX%'

頁面搜索嚴禁左模糊或者全模糊，如果需要可以用搜索引擎來解決医舆。

2.union俘侠、in、or 都能夠命中索引彬向，建議使用 in兼贡。

union：能夠命中索引。

示例代碼如下：

select?*?fromdoc?where status=1

unionall

select?*?fromdoc?where status=2

直接告訴 MySQL 怎么做娃胆，MySQL 耗費的 CPU 最少遍希，但是一般不這么寫 SQL。

in：能夠命中索引里烦。

示例代碼如下：

select?*?fromdoc?where status in?(1,?2)

查詢優(yōu)化耗費的 CPU 比 union all 多凿蒜，但可以忽略不計，一般情況下建議使用 in

or：新版的 MySQL 能夠命中索引胁黑。

示例代碼如下：

select?*?fromdoc?where status?=?1?or status?=?2

查詢優(yōu)化耗費的 CPU 比 in 多废封，不建議頻繁用 or。

3.負向條件查詢不能使用索引丧蘸，可以優(yōu)化為 in 查詢漂洋。

負向條件有：!=、<>力喷、not in刽漂、not exists、not like 等弟孟。

例如下面代碼：

select?*?fromdoc?where status?!=?1?and status?!=?2

可以優(yōu)化為 in 查詢：

select?*?fromdoc?where status in?(0,3,4)

4.聯(lián)合索引最左前綴原則（又叫最左側查詢）

如果在(a,b,c)三個字段上建立聯(lián)合索引贝咙，那么它能夠加快 a | (a,b) | (a,b,c) 三組查詢速度。

例如登錄業(yè)務需求拂募，代碼如下庭猩。

selectuid, login_time?from user where?login_name=??andpasswd=?

可以建立(login_name, passwd)的聯(lián)合索引窟她。

因為業(yè)務上幾乎沒有 passwd 的單條件查詢需求，而有很多?login_name?的單條件查詢需求蔼水，所以可以建立(login_name, passwd)的聯(lián)合索引震糖，而不是(passwd,?login_name)。

建聯(lián)合索引的時候徙缴，區(qū)分度最高的字段在最左邊试伙。

如果建立了(a,b)聯(lián)合索引，就不必再單獨建立 a 索引于样。同理，如果建立了(a,b,c)聯(lián)合索引潘靖，就不必再單獨建立 a穿剖、(a,b) 索引。

存在非等號和等號混合判斷條件時卦溢，在建索引時糊余，請把等號條件的列前置。如 ? ? where a>? and b=?单寂，那么即使 a 的區(qū)分度更高贬芥，也必須把 b 放在索引的最前列。

最左側查詢需求宣决，并不是指 SQL 語句的 where 順序要和聯(lián)合索引一致蘸劈。

下面的 SQL 語句也可以命中 (login_name, passwd) 這個聯(lián)合索引。

selectuid, login_time?from user where?passwd=??andlogin_name=?

但還是建議 where 后的順序和聯(lián)合索引一致尊沸，養(yǎng)成好習慣威沫。

5.范圍列可以用到索引（聯(lián)合索引必須是最左前綴）。

范圍條件有：<洼专、<=棒掠、>、>=屁商、between等烟很。

范圍列可以用到索引（聯(lián)合索引必須是最左前綴），但是范圍列后面的列無法用到索引蜡镶，索引最多用于一個范圍列雾袱，如果查詢條件中有兩個范圍列則無法全用到索引。

假如有聯(lián)合索引 (empno帽哑、title谜酒、fromdate)，那么下面的 SQL 中?emp_no?可以用到索引妻枕，而 title 和 from_date 則使用不到索引僻族。

select?*?fromemployees.titles?where?emp_no <?10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

6.把計算放到業(yè)務層而不是數(shù)據(jù)庫層粘驰。

在字段上進行計算不能命中索引。

例如下面的 SQL 語句述么。

select?*?fromdoc?where?YEAR(create_time) <=?'2016'

即使 date 上建立了索引蝌数，也會全表掃描，可優(yōu)化為值計算度秘，如下：

select?*?fromdoc?where?create_time <=?'2016-01-01'

把計算放到業(yè)務層顶伞。

這樣做不僅可以節(jié)省數(shù)據(jù)庫的 CPU，還可以起到查詢緩存優(yōu)化效果剑梳。

比如下面的 SQL 語句：

select?*?fromorder where?date?< =?CURDATE()

可以優(yōu)化為：

select?*?fromorder where?date?< =?'2018-01-2412:00:00'

優(yōu)化后的 SQL 釋放了數(shù)據(jù)庫的 CPU 多次調用唆貌，傳入的 SQL 相同，才可以利用查詢緩存垢乙。

7.強制類型轉換會全表掃描

如果 phone 字段是 varchar 類型锨咙，則下面的 SQL 不能命中索引。

select?*?fromuser where?phone=13800001234

可以優(yōu)化為：

select?*?fromuser where?phone='13800001234'

8.更新十分頻繁追逮、數(shù)據(jù)區(qū)分度不高的字段上不宜建立索引酪刀。

更新會變更 B+ 樹，更新頻繁的字段建立索引會大大降低數(shù)據(jù)庫性能钮孵。

“性別”這種區(qū)分度不大的屬性骂倘，建立索引是沒有什么意義的，不能有效過濾數(shù)據(jù)巴席，性能與全表掃描類似历涝。

一般區(qū)分度在80%以上的時候就可以建立索引，區(qū)分度可以使用 count(distinct(列名))/count(*) 來計算情妖。

9.利用覆蓋索引來進行查詢操作睬关，避免回表。

被查詢的列毡证，數(shù)據(jù)能從索引中取得电爹，而不用通過行定位符 row-locator 再到 row 上獲取，即“被查詢列要被所建的索引覆蓋”料睛，這能夠加速查詢速度丐箩。

例如登錄業(yè)務需求，代碼如下恤煞。

selectuid, login_time?from user where?login_name=??andpasswd=?

可以建立(login_name, passwd,?login_time)的聯(lián)合索引屎勘，由于?login_time?已經(jīng)建立在索引中了，被查詢的 uid 和?login_time?就不用去 row 上獲取數(shù)據(jù)了居扒，從而加速查詢概漱。

10.如果有 order by、group by 的場景喜喂，請注意利用索引的有序性瓤摧。

order ? ? by 最后的字段是組合索引的一部分竿裂，并且放在索引組合順序的最后，避免出現(xiàn) ? ? file_sort 的情況照弥，影響查詢性能腻异。

例如對于語句 where a=? and b=? order by ? ? c，可以建立聯(lián)合索引(a,b,c)这揣。

如果索引中有范圍查找悔常，那么索引有序性無法利用，如?WHERE ? ? a>10 ORDER BY b;给赞，索引(a,b)無法排序机打。

11.使用短索引（又叫前綴索引）來優(yōu)化索引。

前綴索引塞俱，就是用列的前綴代替整個列作為索引 key姐帚，當前綴長度合適時，可以做到既使得前綴索引的區(qū)分度接近全列索引障涯，同時因為索引 key 變短而減少了索引文件的大小和維護開銷，可以使用 count(distinct left(列名, 索引長度))/count(*) 來計算前綴索引的區(qū)分度膳汪。

前綴索引兼顧索引大小和查詢速度唯蝶，但是其缺點是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆蓋索引（Covering Index遗嗽，即當索引本身包含查詢所需全部數(shù)據(jù)時粘我，不再訪問數(shù)據(jù)文件本身），很多時候沒必要對全字段建立索引痹换，根據(jù)實際文本區(qū)分度決定索引長度即可征字。

例如對于下面的 SQL 語句：

SELEC?*FROM employees.employees WHERE first_name='Eric'AND last_name='Anido';

我們可以建立索引：(firstname, lastname(4))。

12.建立索引的列娇豫，不允許為 null匙姜。

單列索引不存 null 值，復合索引不存全為 null 的值冯痢，如果列允許為 null氮昧，可能會得到“不符合預期”的結果集，所以浦楣，請使用 not null 約束以及默認值袖肥。

13.利用延遲關聯(lián)或者子查詢優(yōu)化超多分頁場景。

MySQL 并不是跳過 offset 行振劳，而是取 offset+N 行椎组，然后返回放棄前 offset 行，返回 N 行历恐，那當 offset 特別大的時候寸癌，效率就非常的低下专筷，要么控制返回的總頁數(shù)，要么對超過特定閾值的頁數(shù)進行 SQL 改寫灵份。

示例如下仁堪，先快速定位需要獲取的 id 段，然后再關聯(lián):

selecta.*?from?表1?a,(select id from?表1?where?條件?limit100000,20?) b?where?a.id=b.id

14.業(yè)務上具有唯一特性的字段填渠，即使是多個字段的組合弦聂，也必須建成唯一索引。

不要以為唯一索引影響了 insert 速度氛什，這個速度損耗可以忽略莺葫，但提高查找速度是明顯的。另外枪眉，即使在應用層做了非常完善的校驗控制捺檬，只要沒有唯一索引，根據(jù)墨菲定律贸铜，必然有臟數(shù)據(jù)產(chǎn)生堡纬。

15.超過三個表最好不要 join。

需要 join 的字段蒿秦，數(shù)據(jù)類型必須一致烤镐，多表關聯(lián)查詢時，保證被關聯(lián)的字段需要有索引棍鳖。

16.如果明確知道只有一條結果返回炮叶，limit 1 能夠提高效率。

比如如下 SQL 語句：

select?*?fromuser where?login_name=?

可以優(yōu)化為：

select?*?fromuser where?login_name=??limit?1

自己明確知道只有一條結果渡处，但數(shù)據(jù)庫并不知道镜悉，明確告訴它，讓它主動停止游標移動医瘫。

17.SQL 性能優(yōu)化 explain 中的 type：至少要達到 range 級別侣肄，要求是 ref 級別，如果可以是 consts 最好登下。

consts：單表中最多只有一個匹配行（主鍵或者唯一索引）茫孔，在優(yōu)化階段即可讀取到數(shù)據(jù)。

ref：使用普通的索引（Normal Index）被芳。

range：對索引進行范圍檢索缰贝。

當 type=index 時，索引物理文件全掃畔濒，速度非常慢剩晴。

18.單表索引建議控制在5個以內(nèi)。

19.單索引字段數(shù)不允許超過5個。

字段超過5個時赞弥，實際已經(jīng)起不到有效過濾數(shù)據(jù)的作用了毅整。

20.創(chuàng)建索引時避免以下錯誤觀念

索引越多越好，認為一個查詢就需要建一個索引绽左。

寧缺勿濫悼嫉，認為索引會消耗空間、嚴重拖慢更新和新增速度拼窥。

抵制惟一索引戏蔑，認為業(yè)務的惟一性一律需要在應用層通過“先查后插”方式解決。

過早優(yōu)化鲁纠，在不了解系統(tǒng)的情況下就開始優(yōu)化总棵。

問題詳解

這部分，我將列出平時會遇到的一些問題改含，并給予解答情龄。

1. 請問如下三條 SQL 該如何建立索引？

where?a=1and b=1

where?b=1

where?b=1order?by?time desc

MySQL 的查詢優(yōu)化器會自動調整 where 子句的條件順序以使用適合的索引嗎捍壤？

回答：?????????????

第一問：建議建立兩個索引骤视，即 idxab(a,b) 和 idxbtime(b,time)。

第二問：MySQL 的查詢優(yōu)化器會自動調整 where 子句的條件順序以使用適合的索引鹃觉，對于上面的第一條 SQL尚胞，如果建立索引為 idxba(b,a) 也是可以用到索引的，不過建議 where 后的字段順序和聯(lián)合索引保持一致帜慢，養(yǎng)成好習慣。

2.假如有聯(lián)合索引(empno唯卖、title粱玲、fromdate)，下面的 SQL 是否可以用到索引拜轨，如果可以的話抽减，會使用幾個列？

select?*?fromemployees.titles?where?emp_no?between?'10001'?and'10010'?and?title='Senior Engineer'?and?from_datebetween?'1986-01-01'and?'1986-12-31'

回答：可以使用索引橄碾，可以用到索引全部三個列卵沉，這個 SQL 看起來是用了兩個范圍查詢，但作用于 empno 上的“between”實際上相當于“in”法牲，也就是說 empno 實際是多值精確匹配史汗，在 MySQL 中要謹慎地區(qū)分多值匹配和范圍匹配，否則會對 MySQL 的行為產(chǎn)生困惑拒垃。

3.既然索引可以加快查詢速度停撞，那么是不是只要是查詢語句需要，就建上索引？

回答：不是戈毒，因為索引雖然加快了查詢速度艰猬，但索引也是有代價的。索引文件本身要消耗存儲空間埋市，同時索引會加重插入冠桃、刪除和修改記錄時的負擔。另外道宅，MySQL 在運行時也要消耗資源維護索引食听，因此索引并不是越多越好。一般兩種情況下不建議建索引培己。第一種情況是表記錄比較少碳蛋，例如一兩千條甚至只有幾百條記錄的表，沒必要建索引省咨，另一種是數(shù)據(jù)的區(qū)分度比較低肃弟，可以使用 count(distinct(列名))/count(*) 來計算區(qū)分度。

4.主鍵和聚集索引的關系零蓉？

回答：在 MySQL 中笤受，InnoDB 引擎表是（聚集）索引組織表（Clustered IndexOrganize Table)，它會先按照主鍵進行聚集敌蜂，如果沒有定義主鍵箩兽，InnoDB 會試著使用唯一的非空索引來代替，如果沒有這種索引章喉，InnoDB 就會定義隱藏的主鍵然后在上面進行聚集汗贫。由此可見，在 InnoDB 表中秸脱，主鍵必然是聚集索引落包，而聚集索引則未必是主鍵。MyISAM 引擎表是堆組織表（Heap Organize Table)摊唇，它沒有聚集索引的概念咐蝇。

5.一個6億的表 a，一個3億的表 b巷查，通過外鍵 tid 關聯(lián)有序，如何最快的查詢出滿足條件的第50000到第50200中的這200條數(shù)據(jù)記錄？

回答：方法一：如果?a?表?tid?是自增長岛请，并且是連續(xù)的旭寿，b表的id為索引。SQL語句如下髓需。

select?*?froma,b?where?a.tid = b.id?and?a.tid>500000?limit200;

方法二：如果 a 表的 tid 不是連續(xù)的许师，那么就需要使用覆蓋索引，tid 要么是主鍵，要么是輔助索引微渠，b 表 id 也需要有索引搭幻。SQL語句如下。

select?*?fromb, (select?tid?from?a?limit?50000,200) awhere?b.id = a.tid;

6.假如建立聯(lián)合索引(a,b,c)逞盆，下列語句是否可以使用索引檀蹋，如果可以，使用了那幾列云芦？（考察聯(lián)合索引最左前綴原則）

where?a= 3

答：是俯逾，使用了 a 列。

where?a= 3 and b = 5

答：是舅逸，使用了 a桌肴，b 列。

where a =?3?and?c =?4?and?b =?5

答：是琉历，使用了 a坠七，b，c 列旗笔。

where?b= 3

答：否彪置。

where?a= 3 and c = 4

答：是，使用了 a 列蝇恶。

where a =?3?and?b >?10?andc =?7

答：是拳魁，使用了 a，b 列撮弧。

where a =?3?and?b like?'xx%'?andc =?7

答：是潘懊，使用了 a，b 列贿衍。

7.文章表的表結構如下：

CREATE?TABLEIF NOT EXISTS?`article`?(`id`int(10)?unsigned NOT?NULLAUTO_INCREMENT,

`author_id`int(10)?unsignedNOT?NULL,

`category_id`int(10)?unsigned NOT?NULL,

`views`int(10)?unsignedNOT?NULL,

`comments`int(10)?unsignedNOT?NULL,

`title`varbinary(255)?NOT?NULL,

`content`text?NOTNULL,

PRIMARY?KEY?(`id`)

);

下面語句應該如何建立索引卦尊？

selectauthor_id, title,?content from?`article`

wherecategory_id =?1?and?comments >?1

order?byviews?desc limit?1;

回答：

沒有聯(lián)合索引時，explain顯示舌厨，如下圖所示：

創(chuàng)建 idxcategoryidcommentsviews(category_id,comments, views) 聯(lián)合索引時，explain顯示忿薇，如下圖所示：

創(chuàng)建 idxcategoryidviews(categoryid,views) 聯(lián)合索引裙椭，explain 顯示，如下圖所示：

由此可見署浩，可以創(chuàng)建 idxcategoryidviews(categoryid,views) 聯(lián)合索引揉燃。

結語

如果你想學好JAVA這門技術，也想在IT行業(yè)拿高薪筋栋，可以參加我們的訓練營課程炊汤，選擇最適合自己的課程學習，技術大牛親授，8個月后抢腐，進入名企拿高薪姑曙。我們的課程內(nèi)容有：Java工程化、高性能及分布式迈倍、高性能伤靠、深入淺出。高架構啼染。性能調優(yōu)宴合、Spring，MyBatis迹鹅，Netty源碼分析和大數(shù)據(jù)等多個知識點卦洽。如果你想拿高薪的，想學習的斜棚，想就業(yè)前景好的阀蒂，想跟別人競爭能取得優(yōu)勢的，想進阿里面試但擔心面試不過的打肝，你都可以來脂新，q群號為：180705916 進群免費領取學習資料。