深入了解MySQL中的索引（用處、分類、配對方式）

這篇文章帶大家深入了解MySQL中的索引，介紹一下索引的優點、用途、分類、技術名詞以及配對方式，希望對大家有幫助！

對於進階開發，我們經常要寫一些複雜的sql，那麼防止寫出低效sql，我們有必要了解一些索引的基礎知識。透過這些基礎知識我們可以寫出更有效率的sql。 【相關推薦：mysql影片教學】

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中的索引（用處、分類、配對方式）的詳細內容。更多資訊請關注PHP中文網其他相關文章！