索引是一種特殊的資料庫結構，由資料表中的一列或多列組合而成，可以用來快速查詢資料表中有某一特定值的記錄。
索引可以很大程度上提高資料庫的查詢速度。
在沒有索引的時，我們根據id查詢一條資料，資料庫系統將進行全表掃描，逐行遍歷，讀取每條記錄的所有資訊進行匹配，直到找到符合的目標資料。這種情況下資料庫的資料量和查詢時間無疑是正向增加的，當資料庫中資料量十分龐大時時，查詢效率會十分低下。
而索引訪問呢，則對錶的關鍵資料列建立一個索引，查詢資料時不用讀完記錄的所有資訊，直接根據該列上的索引找到對應記錄行的位置，就像我們翻一本書的目錄一樣。
優點
索引大大加快資料的查詢速度
索引大大減小了伺服器需要掃描的資料量
索引可以幫助伺服器避免排序和臨時表
索引可以將隨機IO變成順序IO
索引對於InnoDB（對索引支援行級鎖）非常重要，因為它可以讓查詢鎖更少的元組。在MySQL5.1和更新的版本中，InnoDB可以在伺服器端過濾掉行後就釋放鎖，但在早期的MySQL版本中，InnoDB直到事務提交時才會解鎖。對不需要的元組的加鎖，會增加鎖的開銷，降低併發性。 InnoDB僅對需要訪問的元組加鎖，而索引能夠減少InnoDB訪問的元組數。但是隻有在儲存引擎層過濾掉那些不需要的資料才能達到這種目的。一旦索引不允許InnoDB那樣做（即索引達不到過濾的目的），MySQL伺服器只能對InnoDB返回的資料進行WHERE操作，此時，已經無法避免對那些元組加鎖了。如果查詢不能使用索引，MySQL會進行全表掃描，並鎖住每一個元組，不管是否真正需要。
關於InnoDB、索引和鎖：InnoDB在二級索引上使用共享鎖（讀鎖），但訪問主鍵索引需要排他鎖（寫鎖）
缺點
- 建立和維護索引要耗費時間，並且隨著資料量的增加所耗費的時間也會增加。
- 索引需要佔磁碟空間，除了資料表佔資料空間以外，每一個索引還要佔一定的物理空間。
不要盲目的建立索引，只為查詢操作頻繁的列建立索引，建立索引會使查詢操作變得更加快速，但是會降低增加、刪除、更新操作的速度，因為執行這些操作的同時會對索引檔案進行重新排序或更新。
大量資料匯入時，可以先刪除索引，再批次插入資料，最後再新增索引。
儲存方式區分
根據儲存方式的不同，MySQL 中常用的索引在物理上分為 B-樹索引和 HASH 索引兩類，兩種不同型別的索引各有其不同的適用範圍。
1) B-樹索引
B-樹索引又稱為 BTREE 索引 ，目前大部分的索引都是採用 B-樹索引來儲存的。
- 葉子節點：包含的條目直接指向表裡的資料行。葉子節點之間彼此相連，一個葉子節點有一個指向下一個葉子節點的指標。
- 分支節點：包含的條目指向索引裡其他的分支節點或者葉子節點。
- 根節點：一個 B-樹索引只有一個根節點，實際上就是位於樹的最頂端的分支節點。
基於這種樹形資料結構，表中的每一行都會在索引上有一個對應值。因此，在表中進行資料查詢時，可以根據索引值一步一步定位到資料所在的行。
- 查詢必須從索引的最左邊的列開始。
- 查詢不能跳過某一索引列，必須按照從左到右的順序進行匹配。
- 儲存引擎不能使用索引中範圍條件右邊的列。
2) 雜湊索引
雜湊（Hash）一般翻譯為“雜湊”，也有直接音譯成“雜湊”的，就是把任意長度的輸入（又叫作預對映，pre-image）透過雜湊演算法變換成固定長度的輸出，該輸出就是雜湊值。
雜湊索引也稱為雜湊索引或 HASH 索引。MySQL 目前僅有 MEMORY 儲存引擎和 HEAP 儲存引擎支援這類索引。其中，MEMORY 儲存引擎可以支援 B-樹索引和 HASH 索引，且將 HASH 當成預設索引。
HASH 索引不是基於樹形的資料結構查詢資料，而是根據索引列對應的雜湊值的方法獲取表的記錄行。雜湊索引的最大特點是訪問速度快，但也存在下面的一些缺點：
- MySQL 需要讀取表中索引列的值來參與雜湊計算，雜湊計算是一個比較耗時的操作。也就是說，相對於 B-樹索引來說，建立雜湊索引會耗費更多的時間。
- 不能使用 HASH 索引排序。
- HASH 索引只支援等值比較，如“=”“IN()”或“<=>”。
- HASH 索引不支援鍵的部分匹配，因為在計算 HASH 值的時候是透過整個索引值來計算的。
邏輯區分
根據索引的具體用途，MySQL 中的索引在邏輯上分為以下 5 類：
1) 普通索引
普通索引是 MySQL 中最基本的索引型別，它沒有任何限制，唯一任務就是加快系統對資料的訪問速度。
普通索引允許在定義索引的列中插入重複值和空值。
建立普通索引時，通常使用的關鍵字是 INDEX 或 KEY。
例 1
下面在 tb_student 表中的 id 欄位上建立名為 index_id 的索引。
CREATE INDEX index_id ON tb_student(id);2) 唯一索引
唯一索引與普通索引類似，不同的是建立唯一性索引的目的不是為了提高訪問速度，而是為了避免資料出現重複。
建立唯一索引通常使用 UNIQUE 關鍵字。
例 2
tb_student 表中的 id 欄位上建立名為 index_id 的索引，SQL 語句如下：
CREATE UNIQUE INDEX index_id ON tb_student(id);其中，id 欄位可以有唯一性約束，也可以沒有。
3) 主鍵索引
顧名思義，主鍵索引就是專門為主鍵欄位建立的索引，也屬於索引的一種。
主鍵索引是一種特殊的唯一索引，不允許值重複或者值為空。
建立主鍵索引通常使用 PRIMARY KEY 關鍵字。不能使用 CREATE INDEX 語句建立主鍵索引。
4) 空間索引
空間索引是對空間資料型別的欄位建立的索引，使用 SPATIAL 關鍵字進行擴充套件。
建立空間索引的列必須將其宣告為 NOT NULL，空間索引只能在儲存引擎為 MyISAM 的表中建立。
空間索引主要用於地理空間資料型別GEOMETRY。對於初學者來說，這類索引很少會用到。
例 3
下面在 tb_student 表中的 line 欄位上建立名為 index_line 的索引，SQL 語句如下：
CREATE SPATIAL INDEX index_line ON tb_student(line);其中，tb_student 表的儲存引擎必須是 MyISAM，line 欄位必須為空間資料型別，而且是非空的。
5) 全文索引
全文索引主要用來查詢文字中的關鍵字，只能在 CHAR、VARCHAR 或 TEXT 型別的列上建立。在 MySQL 中只有 MyISAM 儲存引擎支援全文索引。
全文索引允許在索引列中插入重複值和空值。
不過對於大容量的資料表，生成全文索引非常消耗時間和硬碟空間。
建立全文索引使用 FULLTEXT 關鍵字。
例 4
在 tb_student 表中的 info 欄位上建立名為 index_info 的全文索引，SQL 語句如下：
CREATE FULLTEXT INDEX index_info ON tb_student(info);其中，index_info 的儲存引擎必須是 MyISAM，info 欄位必須是 CHAR、VARCHAR 和 TEXT 等型別。
實際使用區分
索引在邏輯上分為以上 5 類，但在實際使用中，索引通常被建立成單列索引和組合索引。
1）單列索引
單列索引就是索引只包含原表的一個列。在表中的單個欄位上建立索引，單列索引只根據該欄位進行索引。
單列索引可以是普通索引，也可以是唯一性索引，還可以是全文索引。只要保證該索引只對應一個欄位即可。
例 5
下面在 tb_student 表中的 address 欄位上建立名為 index_addr 的單列索引，address 欄位的資料型別為 VARCHAR(20)，索引的資料型別為 CHAR(4)。SQL 語句如下：
CREATE INDEX index_addr ON tb_student(address(4));這樣，查詢時可以只查詢 address 欄位的前 4 個字元，而不需要全部查詢。
2）多列索引
組合索引也稱為複合索引或多列索引。相對於單列索引來說，組合索引是將原表的多個列共同組成一個索引。多列索引是在表的多個欄位上建立一個索引。該索引指向建立時對應的多個欄位，可以透過這幾個欄位進行查詢。但是，只有查詢條件中使用了這些欄位中第一個欄位時，索引才會被使用。
例如，在表中的 id、name 和 sex 欄位上建立一個多列索引，那麼，只有查詢條件使用了 id 欄位時，該索引才會被使用。
例 6
下面在 tb_student 表中的 name 和 address 欄位上建立名為 index_na 的索引，SQL 語句如下：
CREATE INDEX index_na ON tb_student(name,address);該索引建立好了以後，查詢條件中必須有 name 欄位才能使用索引。
提示：一個表可以有多個單列索引，但這些索引不是組合索引。一個組合索引實質上為表的查詢提供了多個索引，以此來加快查詢速度。比如，在一個表中建立了一個組合索引(c1，c2，c3)，在實際查詢中，系統用來實際加速的索引有三個：單個索引(c1)、雙列索引(c1，c2)和多列索引(c1，c2，c3)。
本節轉自MySQL索引型別詳解
1）建立表時建立索引
CREATE TABLE 表名 (
欄位名1 資料型別 [完整性約束條件…],
欄位名2 資料型別 [完整性約束條件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY 索引名 (欄位名[(長度)] [ASC |DESC]));2）在已存在的表上建立索引 CREATE
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (欄位名[(長度)] [ASC |DESC]) ;3）在已存在的表上建立索引 ALTER
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (欄位名[(長度)] [ASC |DESC]) ;4）刪除索引
DROP INDEX 索引名 ON 表名字;示例
1.建立索引
-在建立表時就建立（需要注意的幾點）
create table s1(
id int ,#可以在這加primary key
#id int index #不可以這樣加索引，因為index只是索引，沒有約束一說，
#不能像主鍵，還有唯一約束一樣，在定義欄位的時候加索引
name char(20),
age int,
email varchar(30)
#primary key(id) #也可以在這加
index(id) #可以這樣加
);
-在建立表後在建立
create index name on s1(name); #新增普通索引
create unique age on s1(age);新增唯一索引
alter table s1 add primary key(id); #新增住建索引，也就是給id欄位增加一個主鍵約束
create index name on s1(id,name); #新增普通聯合索引
2.刪除索引
drop index id on s1;
drop index name on s1; #刪除普通索引
drop index age on s1; #刪除唯一索引，就和普通索引一樣，不用在index前加unique來刪，直接就可以刪了
alter table s1 drop primary key; #刪除主鍵(因為它新增的時候是按照alter來增加的，那麼我們也用alter來刪)- 儘量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，
表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、
性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，
這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄 - 對 where,on,group by,order by 中出現的列使用索引。
- 對較小的資料列使用索引，這樣會使索引檔案更小，同時記憶體中也可以裝載更多的索引鍵。
- 為較長的字串使用字首索引。
- 不要過多建立索引，除了增加額外的磁碟空間外，對於DML操作的速度影響很大，因為其每增刪改一次就得從新建立索引。
- 使用組合索引，可以減少檔案索引大小，在使用時速度要優於多個單列索引。
- 最左字首匹配原則。mysql會一直向右匹配直到遇到範圍查詢就停滯匹配。比如：a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。索引的建立需要考慮常用查詢的欄位順序。
- 索引列不能參與計算，保持列“乾淨”，比如from_unixtime(create_time) = ’2014-05-29’
就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的欄位值，
但進行檢索時，需要把所有元素都應用函式才能比較，顯然成本太大。
所以語句應該寫成create_time = unix_timestamp(’2014-05-29’); - 儘可能去擴充套件索引，而不是新建索引。當需要透過增加索引來提高效能時，考慮結合現有索引判斷是否可以在已有索引上做擴充套件。
- =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢最佳化器
會幫你最佳化成索引可以識別的形式
SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會使用索引，因為所有索引列參與了計算
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引，因為使用了函式運算，原理與上面相同
SELECT * FROM `houdunwang` WHERE `uname` LIKE'後盾%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%後盾%" -- 不走索引
-- 正規表示式不使用索引，這應該很好理解，所以為什麼在SQL中很難看到regexp關鍵字的原因
-- 字串與數字比較不使用索引;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引
select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果條件中有or，即使其中有條件帶索引也不會使用。換言之，就是要求使用的所有欄位，都必須建立索引，我們建議大家儘量避免使用or 關鍵字
-- 如果mysql估計使用全表掃描要比使用索引快，則不使用索引
-- 組合索引最左字首
如果組合索引為：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
-- 排序條件為索引，則select欄位必須也是索引欄位，否則無法命中
- order by
select name from s1 order by email desc;
當根據索引排序時候，select查詢的欄位如果不是索引，則不走索引
select email from s1 order by email desc;
特別的：如果對主鍵排序，則還是走索引：
select * from tb1 order by nid desc;- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 建立表時儘量時 char 代替 varchar
- 表的欄位順序固定長度的欄位優先
- 組合索引代替多個單列索引（經常使用多個條件查詢時）
- 儘量使用短索引
- 使用連線（JOIN）來代替子查詢(Sub-Queries)
- 連表時注意條件型別需一致
- 索引雜湊值（重複少）不適合建索引，例：性別不適合
- 先執行看看是否真的很慢，注意設定SQL_NO_CACHE
- where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個欄位分別查詢，看哪個欄位的區分度最高
- explain檢視執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）
- order by limit 形式的sql語句讓排序的表優先查
- 瞭解業務方使用場景
- 加索引時參照建索引的幾大原則
- 觀察結果，不符合預期繼續從1分析
本作品採用《CC 協議》，轉載必須註明作者和本文連結