概念:

在數據庫中除了數據之外，還維護著滿足特定查找算法的數據結構。這些數據結構以某種方式引用(指向)數據，這樣就可以在這些數據結構上實現高級查找算法，這種數據結構就是索引。通常使用B樹以及變種B+樹來實現。簡單來說索引就是一種B樹或者B+樹的數據結構。

一棵m階的B樹滿足下列條件：

⑴ 樹中每個結點至多有m個孩子；

⑵ 除根結點和葉子結點外，其它每個結點至少有m/2個孩子；

⑶ 若根結點不是葉子結點，則至少有2個孩子；

⑷ 所有葉子結點都出現在同一層，葉子結點不包含任何關鍵字信息；

⑸ 有k個孩子的非終端結點恰好包含有k-1個關鍵字。

用java的思想來說：

類似于student s=new student() 這里的s相當于這些數據結構，這個student()對象相當于實實在在的數據。

存放位置:

一般來說索引本身很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲在磁盤中。

MyISAM：如果mysql使用的MyISAM這個引擎的話，索引文件應該存在.myi文件中。

InnoDB：如果mysql的存儲引擎是InnoDB的話，索引文件應該存在.ibdata%這些文件中。

優缺點:

優點：提高數據檢索的效率，降低數據的io成本，通過索引對數據進行排序，降低數據排序的成本，降低了cpu的消耗。索引主要功能就是：查找和排序。

索引就是通過事先排好序，從而在查找時候可以應用二分查找等高效的算法，一般的順序查找復雜度為o(n),而二分查找的復雜度為o(log2n)，當n很大時，二者的效率差距很懸殊。

e：一百萬條數據，如果順序查找的話，平均需要查詢50萬條數據，而用二分法之多不超過20(2的20次方是104萬)次就能找到。二者的效率查了2.5萬倍。

缺點：實際上索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄，索引列也是要占用空間的；雖然索引大大提高了查詢速度，同時卻降低了更新表(update，insert，delete)的速度，因為更新表時，mysql不僅要保存數據，還要保存一下索引文件，每次更新添加索引列的字段，都會調整因為更新所帶來的減值變化后的索引信息。

分類:

我們平常所說的索引，如果沒有特別指明，都是指B樹(多樹搜索樹，并不一定是二叉的)結構組織的索引，其中聚集索引，覆蓋索引，復合索引，前綴索引，唯一索引默認都是使用B樹索引，統稱索引。除了B樹這種類型的索引之外，還有哈希索引(hash index)、全文索引(fulltext)、R數索引。

單值索引：即一個索引只包含單個列，一個表可以有多個單值索引。

create ?index ?索引名字 ?on ?表名 (字段名(長度))

唯一索引：索引列的值必須唯一，但允許有空值。

create unique ?index ?索引名字 ?on ?表名 (字段名(長度))

復合索引：一個索引包含多個列(最多貌似是16個)。一般都是建復合索引優于建單值索引，因為所需的開銷更小,可以代替多個單一索引，還有就是mysql查詢每次只能使用一個索引，復合索引就變成了變相的使用多個索引了。一般最常用作限制條件的列放在最左邊，依次遞減。

create ?index ?索引名字 ?on ?表名 (字段1(長度)，字段2(長度)，字段3(長度)...)

索引失效:

1、drop表：?drop將會刪除表的結構被依賴的約束(constrain)、觸發器(trigger)、索引(index)；從而導致索引失效。

2、like關鍵字：如果查詢的條件中使用了like關鍵字，并且%放在了第一個位置，則索引將會失效。

select id from cs_human where username like '%明';---失效的索引

select id from cs_human where username like '明%';---生效的索引

3、復合索引：在查詢條件中使用了第一個字段(組成復合索引的那些字段)時候，復合索引才會生效，不然就會失效。

例子：如果cs_human 這張表有一個復合索引(userid_age)

select * ?from ?cs_human where age=20 and orgid='111'---失效的索引

select * ?from ?cs_human where age=20 and userid='111'---生效的索引

4、or關鍵字：在查詢條件中使用了or關鍵字，只有or前后兩個字段都是索引列，索引才能生效，否則索引將不會生效。

例子：如果cs_human 這張表中，userid和age是索引列，而username不是索引列

select * ?from ?cs_human where age=20 or username='小明'---失效的索引

select * ?from ?cs_human where age=20 or userid='111'---生效的索引

5、在索引列上進行計算：索引失效的原因是索引是針對原值建的B樹的，將列值計算后，原來的B樹就用不上了；為了解決索引列上計算引起的索引失效問題，將計算放到索引列外的表達式上。

6、null值：只要列中包含有NULL值的行都將不會被包含在索引中(即索引中只有非null的行)，復合索引中只要有一列含有NULL值，那么這一列對于此復合索引就是無效的。(個人猜想：因為null值對于mysql來說就是未知不確定，沒法排序，但是C字段為null的這些行A字段又不為null，所以對于AC復合索引來說這些行必須要包含進來，只是C字段對復合索引來說是失效的)

7、不使用NOT IN和<>操作：?NOT IN和<>操作都不會使用索引，將進行全表掃描。NOT IN可以NOT EXISTS代替，id<>3則可使用id>3 or id<3來代替。

8、短索引：?如果有一個CHAR(255)的列，如果在前10個或20個字符內，多數值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

這里需要對索引的長度做一個聲明：

索引的長度的計算是根據表字段設定的長度來,對于復合索引就是將涉及到的字段長度加起來。

在MyISAM的引擎中：索引的長度不得超過333字節。超過了也只取最前面的333字節

在InnoDB的引擎中：索引的長度不得超過255字節。超過了也只取最前面的255字節

9、類型不一致：?比如username這個列定義的是varchar類型，但是查詢的時候條件寫了其他類型，這樣也會使索引失效(這個問題在mysql5.5之后就不存在了)

select * from cs_user where username=1;

是否需要建立索引:

需要創建索引的情況：

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

2.頻繁作為查詢條件的字段應該創建索引

3.查詢中與其他表關聯的字段，外鍵關系建立索引

4.查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度

解釋：建索引的時候需要考慮是否排序也是這個順序

比如，建了一個復合索引：名字，性別，地址 那么order的時候也是第一個排名字然后性別然后地址排序，這樣是能大大提高排序速度的

5.查詢中統計或分組的字段建立索引—group by是分組，但是分組的前提是排序，也就是說group by和索引也是息息相關的，也要滿足復合要求

不能創建索引的情況：

1.表單的記錄太少的時候 ，2萬條數據不需要建索引，據說mysql在500萬以內還是比較牛逼的

2.經常增刪改查的表 因為：雖然提高了查詢速度，同時會降低更新表的速度，如果對表進行增刪改的話。更新表時mysql不僅要保存數據，還要保存一下索引文件

3.數據重復且分布平均的表字段，因此應該只為最經常查詢和最經常排序的數據列建立索引

注意：如果某個數據列包含許多重復的內容，為他建立索引就沒有太大的實際效果，也就是說數據的差異性不高的字段建索引沒有意義

比如是性別字段，國籍這種字段，再怎么都沒有多大的差異性

索引效率問題：

假如一個表有10萬行記錄，有一個字段A只有T和F兩種值，且每個值的分布概率大約為50%，那么對這種表A字段建索引一般不會提高數據庫的查詢速度，索引的選擇性是指索引列中不同值的數目與表中記錄數的比，如果一個表中有2000條數據，表索引類由1980個不同的值，那么這個索引的選擇性就是1980/2000=0.99

一個索引的選擇性越接近1，這個索引的效率就越高

4.where條件中用不到的字段不要創建索引

新人創作打卡挑戰賽發博客就能抽獎！定制產品紅包拿不停！

總結

以上是生活随笔為你收集整理的mysql优化之索引_mysql优化之索引的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。