MySQL中的索引是什么

這篇文章將為大家詳細講解有關MySQL中的索引是什么，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

對于高級開發，我們經常要編寫一些復雜的sql，那么防止寫出低效sql，我們有必要了解一些索引的基礎知識。通過這些基礎知識我們可以寫出更高效的sql。

01 索引的優點

• 大大減少服務器需要掃描的數據量，也就是IO量

• 幫助服務器避免排序和臨時表（盡量避免文件排序，而是使用索引排序）

• 將隨機IO變成順序IO

02 索引的用處

• 快速查找匹配where子句中的行

• 如果可以在多個索引中選擇，mysql通常會使用找到最少行的索引

• 如果表具有多列索引，則優化器可以使用索引的任何最左前綴來查找行

• 當有表連接的時候，從其他表檢索行數據

• 查找特定索引列的min和max的值

• 如果排序或者分組時可用索引的最左前綴完成的，則對表進行排序和分組

• 在某些情況下，可以優化查詢以檢索數據值而無需查找數據行

03 索引的分類

數據庫默認建立的索引是給唯一鍵建立的

• 主鍵索引(唯一且非空)

• 唯一索引(唯一可為空)

• 普通索引(普通字段的索引)

• 全文索引(一般是varchar,char,text類型建立的，但很少用)

• 組合索引(多個字的建立的索引)

04 索引的技術名詞

1. 回表

name字段是普通索引，從name列的B+樹找到主鍵，再從主鍵的B+樹找到最終的數據，這就是回表。(主鍵索引的葉子節點保存的是列的所有數據，但是普通所有的葉子結點保存的是對應的主鍵ID）

如圖：一個use表中name建立的索引結構sql是select * from use where name='sun'首先會通過name這個非主鍵索引找到sun對應的主鍵Id=2,然后通過id=2在主鍵索引中找到整個行數據，并返回，這個就是回表。

2. 覆蓋索引

在非主鍵索引上可以查詢到所需要的字段，不需要回表再次查詢就叫覆蓋索引。

如上圖name索引，sql是 select id,name from user where name ="1" ，id的值在第一步非主鍵索引就已經有了，就不需要根據ID到主鍵索引中查詢行數據了。

3. 最左匹配

組合索引中 先匹配左邊，再繼續向后匹配；比如user表中有name+age組成的聯合索引，select * from user where name="紀先生" and age = 18 就符合最左匹配，可以用的索引。而select * from user where age = 18就不符合，用不到這個索引。

擴展；

如果是下面兩個sql怎么建立索引

select * from user where name="紀先生" and age = 18;
select * from user where age = 18;

由于最左匹配原則：只需要建立一個組合索引age+name即可

如果是下面三個sql呢

select * from user where name="紀先生" and age = 18;
select * from user where name= "紀先生";

建立name+age和age索引，或者建立age+name和name索引,看著兩個都可以。

其實name+age和age更好，因為索引也是需要持久化存儲的，占用磁盤空間，讀取的時候也是占用內存的，name+age和age+name這兩個占用是一樣的，但是name和age單獨比較，肯定age占用空間更少，name更長（索引越大，IO次數可能更多）

注意！注意！注意！：

在看很多文章的時候，經常看到一些對于最左匹配錯誤的舉例：

如果索引是name+age的組合索引，sql是select * from user where age = 18 and name="紀先生"很多人認為這種是不能走索引，實際上可以的。mysql的優化器會優化調整順序的，調整成 name="紀先生" and age = 18

4. 索引下推

組合索引中盡量利用索引信息，來盡可能的減少回表的次數

案例：還是 name+age的組合索引如果沒有索引下推的查詢是 在組合索引中通過name查詢所有匹配的數據，然后回表根據ID查詢對于的數據行，之后在篩選出符合age條件的數據。索引下推就是組合索引中通過name查詢匹配再根據age找到符合的數據ID，然后回表根據ID查詢對應行數據，明顯會減少數據的條數

05 索引匹配方式

mysql官網準備了一些學習測試的數據庫，可以直接下載通過source導入到我們自己的數據庫

官網地址：dev.mysql.com/doc/index-o…

如上圖下載zip, 其中包含了sakila-schema.sql和sakila-data.sql,分別是sakila的庫，表和數據的創建腳本。

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通過explain來查看索引的執行情況，執行計劃以前有文章詳細講過，具體參考執行計劃explain

1. 全值匹配

指和某個索引中的所有列進行匹配，例如使用數據庫sakila中的staff表

新建一個三個字段的聯合索引：

mysql> alter table staff add index index_n1(first_name,last_name,username);

執行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'復制代碼

其中的ref是三個const， 用到三個字段，能全匹配一條數據

2. 最左前綴匹配

只匹配組合索引中前面幾個字段

執行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

ref只出現2個const,比上面全值匹配少一個，就只匹配了前面兩個字段

3. 匹配列前綴

可以匹配某一列的的開頭部分，像like屬性

執行sql:

mysql> explain select * from staff where first_name like 'Mi%';

type=range ,是個范圍查詢，可以匹配一個字段的一部分，而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面，否則有索引也不能使用，如下

4. 匹配一個范圍值

可以查找某一個范圍的數據

mysql> explain select * from staff where first_name > 'Mike';

5. 精確匹配某一列并范圍匹配另一列

可以查詢第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

6. 只訪問索引的查詢

查詢的時候只需要訪問索引，不需要訪問數據行，其實就是索引覆蓋

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';

extra=Using index 說明是使用了索引覆蓋，不需要再次回表查詢。

其實一張表中有索引并不總是最好的。總的來說，只有當索引幫助存儲引擎快速提高查找到記錄帶來的好處大于其帶來的額外工作時，索引才是有效的。對應很小的表，大部分情況下沒有索引，全表掃描更高效；對應中大型表，索引時非常有效的；但是對于超大的表，索引的建立和使用代價也就非常高，一般需要單獨處理特大型的表，例如分區，分庫，分表等。

關于“MySQL中的索引是什么”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。