（一）什麼是索引？？
索引（Index）是在資料庫的字段上添加的，是為了提高查詢的效率存在的一種機制。一張表的一個字段可以添加一個索引，當然，多個字段聯合起來也可以添加索引。索引相當于是一本書的目錄，主要是為縮小掃描範圍而存在的一種機制。
（二）查詢的分類
① 全表掃描（缺點：效率比較低，查詢速度比較慢）
② 索引定位（優點：查詢速度比較快，效率比較高）
說明：對于通過索引的這一種查詢方式，索引也是需要排序的，并且索引的排序和TreeSet資料結構相同。TreeSet(TreeMap)底層是一個自平衡二叉樹！在mysql中索引是一個B-Tree的資料結構。
（三）索引的實作原理
假設存在一張使用者表 t_user
id(PK) name 每一行記錄在硬碟上都有實體存儲編号
------------------------------------------------------------------------------------
100 張三 0x1111
120 李四 0x2222
99 王五 0x8888
88 趙六 0x9999
101 小胡 0x6666
55 小明 0x5555
130 小紅 0x7777
說明：
I.在任何資料庫當中主鍵上會自動添加索引對象，id字段上有自動索引。一個字段上如果有unique限制的話，也會自動建立索引對象。
II.在任何資料庫當中，任何一張表的任何一條記錄都在硬碟上存儲都有一個硬碟上的實體編号。
III.在mysql中，是以是一個單獨的對象，不同的存儲引擎以不同的形式存在，在MYSQL存儲引擎中，索引存儲在一個.MYI檔案中。在InnoDB存儲引擎中，索引存儲在一個邏輯名稱叫做tablespace當中。在MEMORY存儲引擎中索引被存儲在記憶體當中。不管索引存儲在哪裡，索引在mysql中都是一個樹的形式存在（自平衡二叉樹：B-Tree）
建構與查找圖示如下：
（四）應用場景
① 資料量龐大
② 該字段經常出現在where的後面
③ 該字段進行很少的DML（insert 、delete、update）操作。
（頻繁的DML操作會導緻索引重新排序，降低效率）
注意：索引是不可以進行随意添加的，索引是需要維護的，索引太多反而會影響系統的性能，通過主鍵查詢或通過unique限制的字段進行查詢，效率很高。
（五）如何使用
I.索引的建立
mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0 II.索引的删除
mysql> drop index emp_ename_index on emp;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 III.檢視索引方式
mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | emp_ename_index | emp_ename_index | 33 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec) 說明：MYSQL索引底層是B-Tree
IV.索引失效
情況一：explain select * from emp where ename like '%T';
mysql> explain select * from emp where ename like '%T';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) 失效原因：模糊比對當中條件使用了“%”開頭（應該盡量避免）
情況二：explain select * from emp where ename = 'KING' or job='MANAGER';
mysql> explain select * from emp where ename = 'KING' or job='MANAGER';
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | emp_ename_index | NULL | NULL | NULL | 14 | 19.00 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) 失效原因：在使用or時候，要求其兩邊的條件字段都需要有索引，才會通過索引進行檢索，反之不可以。
#通過使用union來實作
mysql> explain select * from emp where ename = 'KING' union select * from emp where job='MANAGER';
+----+--------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | emp | NULL | ref | emp_ename_index | emp_ename_index | 33 | const | 1 | 100.00 | NULL |
| 2 | UNION | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec) 情況三：create index emp_job_sal_index on emp(job,sal);
#建立索引
mysql> create index emp_job_sal_index on emp(job,sal);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
#檢視索引次數
mysql> explain select * from emp where job='MANAGER';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | emp_job_sal_index | emp_job_sal_index | 30 | const | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#檢視索引次數
mysql> explain select * from emp where sal = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) 失效原因：在複合索引（兩個及以上字段聯合起來添加索引）中。沒有使用左側的列查找，索引失效。
情況四：explain select * from emp where sal+1=800;
mysql> create index emp_sal_index on emp(sal);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from emp where sal=800;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where sal+1=800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) 失效原因：在where中索引參與了運算。
情況五：explain select * from emp where lower(ename)='smith';
mysql> explain select * from emp where lower(ename)='smith';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) 失效原因： 在where中索引使用了函數
V.索引分類
① 單一索引：一個字段上添加索引。
②複合索引： 兩個及以上字段上添加索引。