目錄

一、什么是索引？

索引的實現(xiàn)原理

什么時候考慮添加索引？

索引的類型

二、為什么要有索引？

三、怎么用索引？

索引的創(chuàng)建和刪除

怎么查看一條sql語句中使用了索引？

索引失效的情況以及對應(yīng)解決方案

---

一、什么是索引？

索引是數(shù)據(jù)庫表中的字段的索引，所謂的索引就是在表的字段上添加的，每個字段都可以添加索引來提高查找效率，也可以多個字段聯(lián)合添加一個索引。

參考字典的實現(xiàn)，索引相當(dāng)于字典的目錄，通過目錄縮小查找范圍。

索引的實現(xiàn)原理

假設(shè)有一張用戶表：t_user
?
? ? id(PK) ? ? ? ? ? ? ? ? ?name ? ? ? ? ? ? ? ? ? ? ? ?每一行記錄在硬盤上都有物理存儲編號
? ? ----------------------------------------------------------------------------------
? ? 100 ? ? ? ? ? ? ? ? ? ? zhangsan ? ? ? ? ? ? ? ? ? ?0x1111
? ? 120 ? ? ? ? ? ? ? ? ? ? lisi ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0x2222
? ? 99 ? ? ? ? ? ? ? ? ? ? ? wangwu? ? ? ? ? ? ? ? ? ? ?0x8888
? ? 88 ? ? ? ? ? ? ? ? ? ? ? zhaoliu? ? ? ? ? ? ? ? ? ? ? ?0x9999
? ? 101 ? ? ? ? ? ? ? ? ? ? jack ? ? ? ? ? ? ? ? ? ? ? ??? ?0x6666
? ? 55 ? ? ? ? ? ? ? ? ? ? ? lucy? ? ? ? ? ? ? ? ? ? ? ? ? ? 0x5555
? ? 130 ? ? ? ? ? ? ? ? ? ? tom ? ? ? ? ? ? ? ? ? ? ?? ? ? 0x7777

提醒1：

在任何數(shù)據(jù)庫當(dāng)中主鍵上都會自動添加索引對象，id字段上自動有索引，因為id是主鍵。另外在mysql當(dāng)中，一個字段上如果有unique約束的話，也會自動創(chuàng)建索引對象。
?
提醒2：

在任何數(shù)據(jù)庫當(dāng)中，任何一張表的任何一條記錄在實際硬盤存儲上都有一個硬盤的物理存儲編號。

提醒3：

不管索引存儲在哪里，索引在mysql當(dāng)中都是一個樹的形式存在。（自平衡二叉樹：B-Tree）

假如sql語句是：select * from t_user where id = 101;

條件是id = 101，mysql發(fā)現(xiàn)id字段上有索引對象，所以會通過索引對象進行查找：

101比100大，看右邊

101比120小，看左邊

定位到101，通過定位到的101得出對應(yīng)的物理編號0x6666，就會轉(zhuǎn)換sql語句成：

select * from t_user where 物理編號 = 0x6666;

直接通過物理編號0x6666定位到存儲的記錄

101 ? ? ? ? ? ? ? ? ? ? jack ? ? ? ? ? ? ? ? ? ? ? ??? ?0x6666，

返回給用戶。

假如id字段沒有索引的話，他會到id字段上，進行從上到下查找，直至找到101，效率很低。

什么時候考慮添加索引？

什么條件下，我們會考慮給字段添加索引呢？

• 條件1：數(shù)據(jù)量龐大（到底有多么龐大算龐大，這個需要測試，因為每一個硬件環(huán)境不同）
• 條件2：該字段經(jīng)常出現(xiàn)在where的后面，以條件的形式存在，也就是說這個字段總是被掃描。
• ?? ?條件3：該字段很少的DML(insert delete update)操作。（因為DML之后，索引需要重新排序。）

建議不要隨意添加索引，因為索引也是需要維護的，太多的話反而會降低系統(tǒng)的性能。
建議通過主鍵查詢，建議通過unique約束的字段進行查詢，效率是比較高的。

索引的類型

分單列索引、組合索引和全文索引

單列索引：

主鍵索引，數(shù)據(jù)庫表的主鍵字段會自動創(chuàng)建索引。

唯一索引，當(dāng)某個列添加了Unique約束，也會自動創(chuàng)建唯一索引，要求值必須是唯一的。

普通索引，給普通字段添加索引就是普通索引。

組合索引：

多個字段合起來創(chuàng)建一個索引，只有當(dāng)查詢條件中使用了組合索引的第一個字段，索引才會被使用，使用組合索引是遵循最左前綴原則。

CREATE INDEX index_id_name ON mytable(id,name)；

二、為什么要有索引？

為了提高查詢的效率。

三、怎么用索引？

索引的創(chuàng)建和刪除

創(chuàng)建索引：

create index 要創(chuàng)建的索引名 on 要創(chuàng)建索引的表名(要創(chuàng)建索引的字段);

刪除索引：

drop index 要刪除的索引名 on 要刪除的索引所在的表名;

怎么查看一條sql語句中使用了索引？

dept表：

解釋這條sql語句：

EXPLAIN select * from dept where loc = '北京';

?rows = 5，表示遍歷了5條，說明是全部都遍歷查找比對，找loc = '北京'，說明loc字段沒有索引。

給loc字段添加索引：

create index locIndex on dept(loc);

再執(zhí)行sql語句：

EXPLAIN select * from dept where loc = '北京';

?rows = 2，就是遍歷了2條，說明loc字段的索引起作用了。

索引失效的情況以及對應(yīng)解決方案

失效的第1種情況：
?? ??? ?select * from emp where ename like '%T';

?? ??? ?ename上即使添加了索引，也不會走索引，為什么？
?? ??? ??? ?原因是因為模糊匹配當(dāng)中以“%”開頭了，mysql找不到。
? ? ? ? ? ? 解決方案：盡量避免模糊查詢的時候以“%”開始。這是一種優(yōu)化的手段/策略。

失效的第2種情況：
?? ??? ?使用or的時候會失效，如果使用or那么要求or兩邊的條件字段都要有索引，才會走索引，如果or其中一邊有一個字段沒有索引，那么另一個字段上的索引也會失效。

? ? ? ? 解決方案：

????????????????不建議使用or，所以這就是為什么不建議使用or的原因。

? ? ? ? ? ? ? ? 或使用union聯(lián)合查詢。

舉例：

dept表，loc字段有索引，deptname字段沒有索引

select * from dept where loc = '福建' or deptname = '信息部';

查詢位于福建的或者信息部的部門信息

以上sql語句，由于deptname沒有索引，會導(dǎo)致loc的索引失效，可以使用union聯(lián)合查詢：

select * from dept where loc =?'福建'

union

select * from dept where deptname = '信息部';

這樣loc字段的索引還是生效的。
?

失效的第3種情況：
?? ??? ?使用復(fù)合索引的時候，沒有用到左側(cè)的字段作為查找條件，索引失效
?? ??? ?什么是復(fù)合索引？
?? ??? ??? ?兩個字段，或者更多的字段聯(lián)合起來添加一個索引，叫做復(fù)合索引。

????????create index emp_job_sal_index on emp(job,sal);

索引正常：explain select * from emp where job = 'MANAGER';

索引失效：explain select * from emp where sal = 800;

失效的第4種情況：
?? ??? ?在where當(dāng)中索引列參加了運算，索引失效。
? ? ? ? create index emp_sal_index on emp(sal);

索引正常：explain select * from emp where sal = 800;

索引失效：explain select * from emp where sal+1 = 800;

失效的第5種情況：
?? ??? ?在where當(dāng)中索引列使用了函數(shù)

? ? ? ? ename字段有索引
?? ??? ?explain select * from emp where lower(ename) = 'smith';

總結(jié)

以上是生活随笔為你收集整理的数据库中的索引的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。