MySQL索引優(yōu)缺點(diǎn)、何時(shí)需要/不需要?jiǎng)?chuàng)建索引、索引及sql語(yǔ)句的優(yōu)化

一、什么是索引？

索引是對(duì)數(shù)據(jù)庫(kù)表中的一列或多列值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可以快速訪問(wèn)數(shù)據(jù)庫(kù)表中的特定信息。

二、索引的作用？

索引相當(dāng)于圖書(shū)上的目錄，可以根據(jù)目錄上的頁(yè)碼快速找到所需的內(nèi)容，提高性能（查詢速度）

三、優(yōu)點(diǎn)：

• 通過(guò)創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)表中的每一行數(shù)據(jù)的唯一性。

• 可以加快數(shù)據(jù)的檢索速度

• 可以加速表與表之間的連接

• 在使用分組和排序進(jìn)行檢索的時(shí)候，可以減少查詢中分組和排序的時(shí)間

四、缺點(diǎn)

• 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。

• 索引需要占用物理空間，數(shù)據(jù)量越大，占用空間越大

• 會(huì)降低表的增刪改的效率，因?yàn)槊看卧鰟h改索引，都需要進(jìn)行動(dòng)態(tài)維護(hù)

五、什么時(shí)候需要?jiǎng)?chuàng)建索引

• 主鍵自動(dòng)建立唯一索引

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

• 查詢中排序的字段創(chuàng)建索引將大大提高排序的速度（索引就是排序加快速查找

• 查詢中統(tǒng)計(jì)或者分組的字段；

六、什么時(shí)候不需要?jiǎng)?chuàng)建索引

• 頻繁更新的字段不適合創(chuàng)建索引，因?yàn)槊看胃虏粏螁问歉掠涗洠€會(huì)更新索引，保存索引文件

• where條件里用不到的字段，不創(chuàng)建索引；

• 表記錄太少，不需要?jiǎng)?chuàng)建索引；

• 經(jīng)常增刪改的表；

• 數(shù)據(jù)重復(fù)且分布平均的字段，因此為經(jīng)常查詢的和經(jīng)常排序的字段建立索引。注意某些數(shù)據(jù)包含大量重復(fù)數(shù)據(jù)，因此他建立索引就沒(méi)有太大的效果，例如性別字段，只有男女，不適合建立索引。更多面試題，歡迎關(guān)注公眾號(hào) Java面試題精選

七、索引的分類(lèi)：

• 普通索引：最基本的索引，它沒(méi)有任何限制

• 唯一索引：索引列的值必須唯一，且不能為空，如果是組合索引，則列值的組合必須唯一。

• 主鍵索引：特殊的索引，唯一的標(biāo)識(shí)一條記錄，不能為空，一般用primary key來(lái)約束。

• 聯(lián)合索引：在多個(gè)字段上建立索引，能夠加速查詢到速度

八、索引和sql語(yǔ)句的優(yōu)化

1、前導(dǎo)模糊查詢不能使用索引，如name like ‘%靜’

2、Union、in、or可以命中索引，建議使用in

3、負(fù)條件查詢不能使用索引，可以優(yōu)化為in查詢，其中負(fù)條件有！=、<>、not in、not exists、not like等

4、聯(lián)合索引最左前綴原則，又叫最左側(cè)查詢，如果在（a，b，c）三個(gè)字段上建立聯(lián)合索引，那么它能夠加快a|（a，b）|（a，b，c）三組的查詢速度。

5、建立聯(lián)合查詢時(shí)，區(qū)分度最高的字段在最左邊

6、如果建立了（a，b）聯(lián)合索引，就不必再單獨(dú)建立a索引。同理，如果建立了（a，b，c）索引就不必再建立a，（a，b）索引

7、存在非等號(hào)和等號(hào)混合判斷條件時(shí)，在建索引時(shí)，要把等號(hào)條件的列前置

8、范圍列可以用到索引，但是范圍列后面的列無(wú)法用到索引。索引最多用于一個(gè)范圍列，如果查詢條件中有兩個(gè)范圍列則無(wú)法全用到索引。范圍條件有：<、<=、>、>=、between等。往期：100期面試題匯總

9、把計(jì)算放到業(yè)務(wù)層而不是數(shù)據(jù)庫(kù)層。在字段上計(jì)算不能命中索引。

10、強(qiáng)制類(lèi)型轉(zhuǎn)換會(huì)全表掃描，如果phone字段是varcher類(lèi)型，則下面的SQL不能命中索引。Select * fromuser where phone=13800001234

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

更新會(huì)變更B+樹(shù)，更新頻繁的字段建立索引會(huì)大大降低數(shù)據(jù)庫(kù)性能。“性別”這種區(qū)分度不太大的屬性，建立索引是沒(méi)有什么意義的，不能有效過(guò)濾數(shù)據(jù)，性能與全表掃描類(lèi)似。一般區(qū)分度在80%以上就可以建立索引。區(qū)分度可以使用count（distinct（列名））/count（*）來(lái)計(jì)算。

12、利用覆蓋索引來(lái)進(jìn)行查詢操作，避免回表。被查詢的列，數(shù)據(jù)能從索引中取得，而不是通過(guò)定位符row-locator再到row上獲取，即“被查詢列要被所建的索引覆蓋”，這能夠加速度查詢。

13、建立索引的列不能為null，使用not null約束及默認(rèn)值

14、利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁(yè)場(chǎng)景，MySQL并不是跳過(guò)offset行，而是取offset+N行，然后放棄前offset行，返回N行，那當(dāng)offset特別大的時(shí)候，效率非常低下，要么控制返回的總數(shù)，要么對(duì)超過(guò)特定閾值的頁(yè)進(jìn)行SQL改寫(xiě)。

15、業(yè)務(wù)上唯一特性的字段，即使是多個(gè)字段的組合，也必須建成唯一索引。

16、超過(guò)三個(gè)表最好不要用join，需要join的字段，數(shù)據(jù)類(lèi)型必須一致，多表關(guān)聯(lián)查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引。

17、如果明確知道查詢結(jié)果只要一條，limit 1能夠提高效率，比如驗(yàn)證登錄的時(shí)候。

18、Select語(yǔ)句務(wù)必指明字段名稱

19、如果排序字段沒(méi)有用到索引，就盡量少排序

20、盡量用union all 代替 union。Union需要將集合合并后在進(jìn)行唯一性過(guò)濾操作，這會(huì)涉及到排序，大量的cpu運(yùn)算，加大資源消耗及延遲，當(dāng)然，使用union all的前提條件是兩個(gè)結(jié)果集沒(méi)有重復(fù)數(shù)據(jù)。

21、使用合理的分頁(yè)提高效率。select id,name from product limit 866613, 20

使用上述SQL語(yǔ)句做分頁(yè)的時(shí)候，可能有人會(huì)發(fā)現(xiàn)，隨著表數(shù)據(jù)量的增加，直接使用limit分頁(yè)查詢會(huì)越來(lái)越慢。

優(yōu)化的方法如下：

可以取前一頁(yè)的最大行數(shù)的id，然后根據(jù)這個(gè)最大的id來(lái)限制下一頁(yè)的起點(diǎn)。

比如此列中，上一頁(yè)最大的id是866612。

SQL可以采用如下的寫(xiě)法：select id,name from product where id> 866612 limit 20。

出處：cnblogs.com/l199616j/p/11232392.html