學習索引，主要是寫出更快的sql，當我們寫sql的時候，需要明確的知道sql為什麼會走索引？為什麼有些sql不走索引？sql會走那些索引，為什麼會這麼走？我們需要了解其原理，瞭解內部具體過程，這樣使用起來才能更順手，才可以寫出更高效的sql。本篇我們就是搞懂這些問題。
讀本篇文章之前，需要先了解一些知識：
- 什麼是索引？
- mysql索引原理詳解
- mysql索引管理詳解
上面3篇文章沒有讀過的最好去讀一下，不然後面的內容會難以理解。
先來回顧一些知識
本篇文章我們以innodb存儲引擎為例來做說明。
mysql採用b+樹的方式存儲索引信息。
b+樹結構如下：
說一下b+樹的幾個特點：
葉子節點（最下面的一層）存儲關鍵字（索引字段的值）信息及對應的data，葉子節點存儲了所有記錄的關鍵字信息
其他非葉子節點只存儲關鍵字的信息及子節點的指針
每個葉子節點相當於mysql中的一頁，同層級的葉子節點以雙向鏈表的形式相連
每個節點（頁）中存儲了多條記錄，記錄之間用單鏈表的形式連接組成了一條有序的鏈表，順序是按照索引字段排序的
b+樹中檢索數據時：每次檢索都是從根節點開始，一直需要搜索到葉子節點
InnoDB 的數據是按數據頁為單位來讀寫的。也就是說，當需要讀取一條記錄的時候，並不是將這個記錄本身從磁盤讀取出來，而是以頁為單位，將整個也加載到內存中，一個頁中可能有很多記錄，然後在內存中對頁進行檢索。在innodb中，每個頁的大小默認是16kb。
Mysql中索引分為
聚集索引（主鍵索引）
每個表一定會有一個聚集索引，整個表的數據存儲以b+樹的方式存在文件中，b+樹葉子節點中的key為主鍵值，data為完整記錄的信息；非葉子節點存儲主鍵的值。
通過聚集索引檢索數據只需要按照b+樹的搜索過程，即可以檢索到對應的記錄。
非聚集索引
每個表可以有多個非聚集索引，b+樹結構，葉子節點的key為索引字段字段的值，data為主鍵的值；非葉子節點只存儲索引字段的值。
通過非聚集索引檢索記錄的時候，需要2次操作，先在非聚集索引中檢索出主鍵，然後再到聚集索引中檢索出主鍵對應的記錄，該過程比聚集索引多了一次操作。
索引怎麼走，為什麼有些查詢不走索引？為什麼使用函數了數據就不走索引了？
這些問題可以先放一下，我們先看一下b+樹檢索數據的過程，這個屬於原理的部分，理解了b+樹各種數據檢索過程，上面的問題就都可以理解了。
通常說的這個查詢走索引了是什麼意思？
當我們對某個字段的值進行某種檢索的時候，如果這個檢索過程中，我們能夠快速定位到目標數據所在的頁，有效的降低頁的io操作，而不需要去掃描所有的數據頁的時候，我們認為這種情況能夠有效的利用索引，也稱這個檢索可以走索引，如果這個過程中不能夠確定數據在那些頁中，我們認為這種情況下索引對這個查詢是無效的，此查詢不走索引。
b+樹中數據檢索過程
唯一記錄檢索
如上圖，所有的數據都是唯一的，查詢105的記錄，過程如下：
- 將P1頁加載到內存
- 在內存中採用二分法查找，可以確定105位於[100,150)中間，所以我們需要去加載100關聯P4頁
- 將P4加載到內存中，採用二分法找到105的記錄後退出
查詢某個值的所有記錄
如上圖，查詢105的所有記錄，過程如下：
- 將P1頁加載到內存
- 在內存中採用二分法查找，可以確定105位於[100,150)中間，100關聯P4頁
- 將P4加載到內存中，採用二分法找到最有一個小於105的記錄，即100，然後通過鏈表從100開始向後訪問，找到所有的105記錄，直到遇到第一個大於100的值為止
範圍查找
數據如上圖，查詢[55,150]所有記錄，由於頁和頁之間是雙向鏈表升序結構，頁內部的數據是單項升序鏈表結構，所以只用找到範圍的起始值所在的位置，然後通過依靠鏈表訪問兩個位置之間所有的數據即可，過程如下：
- 將P1頁加載到內存
- 內存中採用二分法找到55位於50關聯的P3頁中，150位於P5頁中
- 將P3加載到內存中，採用二分法找到第一個55的記錄，然後通過鏈表結構繼續向後訪問P3中的60、67，當P3訪問完畢之後，通過P3的nextpage指針訪問下一頁P4中所有記錄，繼續遍歷P4中的所有記錄，直到訪問到P5中所有的150為止。
模糊匹配
數據如上圖。
查詢以`f`開頭的所有記錄
過程如下：
- 將P1數據加載到內存中
- 在P1頁的記錄中採用二分法找到最後一個小於等於f的值，這個值是f，以及第一個大於f的，這個值是z，f指向葉節點P3，z指向葉節點P6，此時可以斷定以f開頭的記錄可能存在於[P3,P6)這個範圍的頁內，即P3、P4、P5這三個頁中
- 加載P3這個頁，在內部以二分法找到第一條f開頭的記錄，然後以鏈表方式繼續向後訪問P4、P5中的記錄，即可以找到所有已f開頭的數據
查詢包含`f`的記錄
包含的查詢在sql中的寫法是%f%，通過索引我們還可以快速定位所在的頁麼？
可以看一下上面的數據，f在每個頁中都存在，我們通過P1頁中的記錄是無法判斷包含f的記錄在那些頁的，只能通過io的方式加載所有葉子節點，並且遍歷所有記錄進行過濾，才可以找到包含f的記錄。
所以如果使用了%值%這種方式，索引對查詢是無效的。
最左匹配原則
當b+樹的數據項是複合的數據結構，比如(name,age,sex)的時候，b+樹是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數據來檢索的時候，b+樹會優先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最後得到檢索的數據；但當(20,F)這樣的沒有name的數據來的時候，b+樹就不知道下一步該查哪個節點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據name來搜索才能知道下一步去哪裡查詢。比如當(張三,F)這樣的數據來檢索時，b+樹可以用name來指定搜索方向，但下一個字段age的缺失，所以只能把名字等於張三的數據都找到，然後再匹配性別是F的數據了， 這個是非常重要的性質，即索引的最左匹配特性。
來一些示例我們體驗一下。
下圖中是3個字段(a,b,c)的聯合索引，索引中數據的順序是以a asc,b asc,c asc這種排序方式存儲在節點中的，索引先以a字段升序，如果a相同的時候，以b字段升序，b相同的時候，以c字段升序，節點中每個數據認真看一下。
查詢a=1的記錄
由於頁中的記錄是以a asc,b asc,c asc這種排序方式存儲的，所以a字段是有序的，可以通過二分法快速檢索到，過程如下：
- 將P1加載到內存中
- 在內存中對P1中的記錄採用二分法找，可以確定a=1的記錄位於{1,1,1}和{1,5,1}關聯的範圍內，這兩個值子節點分別是P2、P4
- 加載葉子節點P2，在P2中採用二分法快速找到第一條a=1的記錄，然後通過鏈表向下一條及下一頁開始檢索，直到在P4中找到第一個不滿足a=1的記錄為止
查詢a=1 and b=5的記錄
方法和上面的一樣，可以確定a=1 and b=5的記錄位於{1,1,1}和{1,5,1}關聯的範圍內，查找過程和a=1查找步驟類似。
查詢b=1的記錄
這種情況通過P1頁中的記錄，是無法判斷b=1的記錄在那些頁中的，只能加鎖索引樹所有葉子節點，對所有記錄進行遍歷，然後進行過濾，此時索引是無效的。
按照c的值查詢
這種情況和查詢b=1也一樣，也只能掃描所有葉子節點，此時索引也無效了。
按照b和c一起查
這種也是無法利用索引的，也只能對所有數據進行掃描，一條條判斷了，此時索引無效。
按照[a,c]兩個字段查詢
這種只能利用到索引中的a字段了，通過a確定索引範圍，然後加載a關聯的所有記錄，再對c的值進行過濾。
查詢a=1 and b>=0 and c=1的記錄
這種情況只能先確定a=1 and b>=0所在頁的範圍，然後對這個範圍的所有頁進行遍歷，c字段在這個查詢的過程中，是無法確定c的數據在哪些頁的，此時我們稱c是不走索引的，只有a、b能夠有效的確定索引頁的範圍。
類似這種的還有>、、
上面說的各種情況，大家都多看一下圖中數據，認真分析一下查詢的過程，基本上都可以理解了。
上面這種查詢叫做最左匹配原則。
索引區分度
我們看2個有序數組
[1,2,3,4,5,6,7,8,8,9,10]
[1,1,1,1,1,8,8,8,8,8]
上面2個數組是有序的，都是10條記錄，如果我需要檢索值為8的所有記錄，那個更快一些？
咱們使用二分法查找包含8的所有記錄過程如下：先使用二分法找到最後一個小於8的記錄，然後沿著這條記錄向後獲取下一個記錄，和8對比，知道遇到第一個大於8的數字結束，或者到達數組末尾結束。
採用上面這種方法找到8的記錄，第一個數組中更快的一些。因為第二個數組中含有8的比例更多的，需要訪問以及匹配的次數更多一些。
這裡就涉及到數據的區分度問題：
索引區分度 = count(distint 記錄) / count(記錄)。
當索引區分度高的時候，檢索數據更快一些，索引區分度太低，說明重複的數據比較多，檢索的時候需要訪問更多的記錄才能夠找到所有目標數據。
當索引區分度非常小的時候，基本上接近於全索引數據的掃描了，此時查詢速度是比較慢的。
第一個數組索引區分度為1，第二個區分度為0.2，所以第一個檢索更快的一些。
所以我們創建索引的時候，儘量選擇區分度高的列作為索引。
正確使用索引
準備400萬測試數據
/*建庫javacode2018*/DROP DATABASE IF EXISTS javacode2018;CREATE DATABASE javacode2018;USE javacode2018;/*建表test1*/DROP TABLE IF EXISTS test1;CREATE TABLE test1 ( id INT NOT NULL COMMENT '編號', name VARCHAR(20) NOT NULL COMMENT '姓名', sex TINYINT NOT NULL COMMENT '性別,1：男，2：女', email VARCHAR(50));/*準備數據*/DROP PROCEDURE IF EXISTS proc1;DELIMITER $CREATE PROCEDURE proc1() BEGIN DECLARE i INT DEFAULT 1; START TRANSACTION; WHILE i <= 4000000 DO
INSERT INTO test1 (id, name, sex, email) VALUES (i,concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com'));
SET i = i + 1;
if i%10000=0 THEN
COMMIT;
START TRANSACTION;
END IF;
END WHILE;
COMMIT;
END $
DELIMITER ;
CALL proc1();
上面插入的400萬數據，除了sex列，其他列的值都是沒有重複的。
無索引檢索效果
400萬數據，我們隨便查詢幾個記錄看一下效果。
按照id查詢記錄
mysql> select * from test1 where id = 1;
+----+-----------+-----+-------------------+
| id | name | sex | email |
+----+-----------+-----+-------------------+
| 1 | javacode1 | 1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (1.91 sec)
id=1的數據，表中只有一行，耗時近2秒，由於id列無索引，只能對400萬數據進行全表掃描。
主鍵檢索
test1表中沒有明確的指定主鍵，我們將id設置為主鍵：
mysql> alter table test1 modify id int not null primary key;
Query OK, 0 rows affected (10.93 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test1 | 0 | PRIMARY | 1 | id | A | 3980477 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
id被置為主鍵之後，會在id上建立聚集索引，隨便檢索一條我們看一下效果：
mysql> select * from test1 where id = 1000000;
+---------+-----------------+-----+-------------------------+
| id | name | sex | email |
+---------+-----------------+-----+-------------------------+
| 1000000 | javacode1000000 | 2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)
這個速度很快，這個走的是上面介紹的`唯一記錄檢索`。
between and範圍檢索
mysql> select count(*) from test1 where id between 100 and 110;
+----------+
| count(*) |
+----------+
| 11 |
+----------+
1 row in set (0.00 sec)
速度也很快，id上有主鍵索引，這個採用的上面介紹的範圍查找可以快速定位目標數據。
但是如果範圍太大，跨度的page也太多，速度也會比較慢，如下：
mysql> select count(*) from test1 where id between 1 and 2000000;
+----------+
| count(*) |
+----------+
| 2000000 |
+----------+
1 row in set (1.17 sec)
上面id的值跨度太大，1所在的頁和200萬所在頁中間有很多頁需要讀取，所以比較慢。
所以使用between and的時候，區間跨度不要太大。
in的檢索
in方式檢索數據，我們還是經常用的。
平時我們做項目的時候，建議少用表連接，比如電商中需要查詢訂單的信息和訂單中商品的名稱，可以先查詢查詢訂單表，然後訂單表中取出商品的id列表，採用in的方式到商品表檢索商品信息，由於商品id是商品表的主鍵，所以檢索速度還是比較快的。
通過id在400萬數據中檢索100條數據，看看效果：
mysql> select * from test1 a where a.id in (100000, 100001, 100002, 100003, 100004, 100005, 100006, 100007, 100008, 100009, 100010, 100011, 100012, 100013, 100014, 100015, 100016, 100017, 100018, 100019, 100020, 100021, 100022, 100023, 100024, 100025, 100026, 100027, 100028, 100029, 100030, 100031, 100032, 100033, 100034, 100035, 100036, 100037, 100038, 100039, 100040, 100041, 100042, 100043, 100044, 100045, 100046, 100047, 100048, 100049, 100050, 100051, 100052, 100053, 100054, 100055, 100056, 100057, 100058, 100059, 100060, 100061, 100062, 100063, 100064, 100065, 100066, 100067, 100068, 100069, 100070, 100071, 100072, 100073, 100074, 100075, 100076, 100077, 100078, 100079, 100080, 100081, 100082, 100083, 100084, 100085, 100086, 100087, 100088, 100089, 100090, 100091, 100092, 100093, 100094, 100095, 100096, 100097, 100098, 100099);
+--------+----------------+-----+------------------------+
| id | name | sex | email |
+--------+----------------+-----+------------------------+
| 100000 | javacode100000 | 2 | [email protected] |
| 100001 | javacode100001 | 1 | [email protected] |
| 100002 | javacode100002 | 2 | [email protected] |
.......
| 100099 | javacode100099 | 1 | [email protected] |
+--------+----------------+-----+------------------------+
100 rows in set (0.00 sec)
耗時不到1毫秒，還是相當快的。
這個相當於多個分解為多個唯一記錄檢索，然後將記錄合併。
多個索引時查詢如何走？
我們在name、sex兩個字段上分別建個索引
mysql> create index idx1 on test1(name);
Query OK, 0 rows affected (13.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index idx2 on test1(sex);
Query OK, 0 rows affected (6.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
看一下查詢：
mysql> select * from test1 where name='javacode3500000' and sex=2;
+---------+-----------------+-----+-------------------------+
| id | name | sex | email |
+---------+-----------------+-----+-------------------------+
| 3500000 | javacode3500000 | 2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)
上面查詢速度很快，name和sex上各有一個索引，覺得上面走哪個索引？
有人說name位於where第一個，所以走的是name字段所在的索引，過程可以解釋為這樣：
走name所在的索引找到javacode3500000對應的所有記錄
遍歷記錄過濾出sex=2的值
我們看一下name='javacode3500000'檢索速度，確實很快，如下：
mysql> select * from test1 where name='javacode3500000';
+---------+-----------------+-----+-------------------------+
| id | name | sex | email |
+---------+-----------------+-----+-------------------------+
| 3500000 | javacode3500000 | 2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)
走name索引，然後再過濾，確實可以，速度也很快，果真和where後字段順序有關麼？我們把name和sex的順序對調一下，如下：
mysql> select * from test1 where sex=2 and name='javacode3500000';
+---------+-----------------+-----+-------------------------+
| id | name | sex | email |
+---------+-----------------+-----+-------------------------+
| 3500000 | javacode3500000 | 2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)
速度還是很快，這次是不是先走sex索引檢索出數據，然後再過濾name呢？我們先來看一下sex=2查詢速度：
mysql> select count(id) from test1 where sex=2;
+-----------+
| count(id) |
+-----------+
| 2000000 |
+-----------+
1 row in set (0.36 sec)
看上面，查詢耗時360毫秒，200萬數據，如果走sex肯定是不行的。
我們使用explain來看一下：
mysql> explain select * from test1 where sex=2 and name='javacode3500000';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test1 | NULL | ref | idx1,idx2 | idx1 | 62 | const | 1 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
possible_keys：列出了這個查詢可能會走兩個索引（idx1、idx2）
實際上走的卻是idx1（key列：實際走的索引）。
當多個條件中有索引的時候，並且關係是and的時候，會走索引區分度高的，顯然name字段重複度很低，走name查詢會更快一些。
模糊查詢
看兩個查詢
mysql> select count(*) from test1 a where a.name like 'javacode1000%';
+----------+
| count(*) |
+----------+
| 1111 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from test1 a where a.name like '%javacode1000%';
+----------+
| count(*) |
+----------+
| 1111 |
+----------+
1 row in set (1.78 sec)
上面第一個查詢可以利用到name字段上面的索引，下面的查詢是無法確定需要查找的值所在的範圍的，只能全表掃描，無法利用索引，所以速度比較慢，這個過程上面有說過。
回表
當需要查詢的數據在索引樹中不存在的時候，需要再次到聚集索引中去獲取，這個過程叫做回表，如查詢：
mysql> select * from test1 where name='javacode3500000';
+---------+-----------------+-----+-------------------------+
| id | name | sex | email |
+---------+-----------------+-----+-------------------------+
| 3500000 | javacode3500000 | 2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)
上面查詢是*，由於name列所在的索引中只有name、id兩個列的值，不包含sex、email，所以上面過程如下：
走name索引檢索javacode3500000對應的記錄，取出id為3500000
在主鍵索引中檢索出id=3500000的記錄，獲取所有字段的值
索引覆蓋
查詢中採用的索引樹中包含了查詢所需要的所有字段的值，不需要再去聚集索引檢索數據，這種叫索引覆蓋。
我們來看一個查詢：
select id,name from test1 where name='javacode3500000';
name對應idx1索引，id為主鍵，所以idx1索引樹葉子節點中包含了name、id的值，這個查詢只用走idx1這一個索引就可以了，如果select後面使用*，還需要一次回表獲取sex、email的值。
所以寫sql的時候，儘量避免使用*，*可能會多一次回表操作，需要看一下是否可以使用索引覆蓋來實現，效率更高一些。
索引下推
簡稱ICP，Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一種在存儲引擎層使用索引過濾數據的一種優化方式，ICP可以減少存儲引擎訪問基表的次數以及MySQL服務器訪問存儲引擎的次數。
舉個例子來說一下：
我們需要查詢name以javacode35開頭的，性別為1的記錄數，sql如下：
mysql> select count(id) from test1 a where name like 'javacode35%' and sex = 1;
+-----------+
| count(id) |
+-----------+
| 55556 |
+-----------+
1 row in set (0.19 sec)
過程：
走name索引檢索出以javacode35的第一條記錄，得到記錄的id
利用id去主鍵索引中查詢出這條記錄R1
判斷R1中的sex是否為1，然後重複上面的操作，直到找到所有記錄為止。
上面的過程中需要走name索引以及需要回表操作。
如果採用ICP的方式，我們可以這麼做，創建一個(name,sex)的組合索引，查詢過程如下：
走(name,sex)索引檢索出以javacode35的第一條記錄，可以得到(name,sex,id)，記做R1
判斷R1.sex是否為1，然後重複上面的操作，知道找到所有記錄為止
這個過程中不需要回表操作了，通過索引的數據就可以完成整個條件的過濾，速度比上面的更快一些。
數字使字符串類索引失效
mysql> insert into test1 (id,name,sex,email) values (4000001,'1',1,'[email protected]');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1 where name = '1';
+---------+------+-----+----------------------+
| id | name | sex | email |
+---------+------+-----+----------------------+
| 4000001 | 1 | 1 | [email protected] |
+---------+------+-----+----------------------+
1 row in set (0.00 sec)
mysql> select * from test1 where name = 1;
+---------+------+-----+----------------------+
| id | name | sex | email |
+---------+------+-----+----------------------+
| 4000001 | 1 | 1 | [email protected] |
+---------+------+-----+----------------------+
1 row in set, 65535 warnings (3.30 sec)
上面3條sql，我們插入了一條記錄。
第二條查詢很快，第三條用name和1比較，name上有索引，name是字符串類型，字符串和數字比較的時候，會將字符串強制轉換為數字，然後進行比較，所以第二個查詢變成了全表掃描，只能取出每條數據，將name轉換為數字和1進行比較。
數字字段和字符串比較什麼效果呢？如下：
mysql> select * from test1 where id = '4000000';
+---------+-----------------+-----+-------------------------+
| id | name | sex | email |
+---------+-----------------+-----+-------------------------+
| 4000000 | javacode4000000 | 2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)
mysql> select * from test1 where id = 4000000;
+---------+-----------------+-----+-------------------------+
| id | name | sex | email |
+---------+-----------------+-----+-------------------------+
| 4000000 | javacode4000000 | 2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)
id上面有主鍵索引，id是int類型的，可以看到，上面兩個查詢都非常快，都可以正常利用索引快速檢索，所以如果字段是數組類型的，查詢的值是字符串還是數組都會走索引。
函數使索引無效
mysql> select a.name+1 from test1 a where a.name = 'javacode1';
+----------+
| a.name+1 |
+----------+
| 1 |
+----------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from test1 a where concat(a.name,'1') = 'javacode11';
+----+-----------+-----+-------------------+
| id | name | sex | email |
+----+-----------+-----+-------------------+
| 1 | javacode1 | 1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (2.88 sec)
name上有索引，上面查詢，第一個走索引，第二個不走索引，第二個使用了函數之後，name所在的索引樹是無法快速定位需要查找的數據所在的頁的，只能將所有頁的記錄加載到內存中，然後對每條數據使用函數進行計算之後再進行條件判斷，此時索引無效了，變成了全表數據掃描。
結論：索引字段使用函數查詢使索引無效。
運算符使索引無效
mysql> select * from test1 a where id = 2 - 1;
+----+-----------+-----+-------------------+
| id | name | sex | email |
+----+-----------+-----+-------------------+
| 1 | javacode1 | 1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (0.00 sec)
mysql> select * from test1 a where id+1 = 2;
+----+-----------+-----+-------------------+
| id | name | sex | email |
+----+-----------+-----+-------------------+
| 1 | javacode1 | 1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (2.41 sec)
id上有主鍵索引，上面查詢，第一個走索引，第二個不走索引，第二個使用運算符，id所在的索引樹是無法快速定位需要查找的數據所在的頁的，只能將所有頁的記錄加載到內存中，然後對每條數據的id進行計算之後再判斷是否等於1，此時索引無效了，變成了全表數據掃描。
結論：索引字段使用了函數將使索引無效。
使用索引優化排序
我們有個訂單表t_order(id,user_id,addtime,price)，經常會查詢某個用戶的訂單，並且按照addtime升序排序，應該怎麼創建索引呢？我們來分析一下。
在user_id上創建索引，我們分析一下這種情況，數據檢索的過程：
- 走user_id索引，找到記錄的的id
- 通過id在主鍵索引中回表檢索出整條數據
- 重複上面的操作，獲取所有目標記錄
- 在內存中對目標記錄按照addtime進行排序
我們要知道當數據量非常大的時候，排序還是比較慢的，可能會用到磁盤中的文件，有沒有一種方式，查詢出來的數據剛好是排好序的。
我們再回顧一下mysql中b+樹數據的結構，記錄是按照索引的值排序組成的鏈表，如果將user_id和addtime放在一起組成聯合索引(user_id,addtime)，這樣通過user_id檢索出來的數據自然就是按照addtime排好序的，這樣直接少了一步排序操作，效率更好，如果需addtime降序，只需要將結果翻轉一下就可以了。
總結一下使用索引的一些建議
- 在區分度高的字段上面建立索引可以有效的使用索引，區分度太低，無法有效的利用索引，可能需要掃描所有數據頁，此時和不使用索引差不多
- 聯合索引注意最左匹配原則：必須按照從左到右的順序匹配，mysql會一直向右匹配直到遇到範圍查詢(>、 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整
- 查詢記錄的時候，少使用*，儘量去利用索引覆蓋，可以減少回表操作，提升效率
- 有些查詢可以採用聯合索引，進而使用到索引下推（IPC），也可以減少回表操作，提升效率
- 禁止對索引字段使用函數、運算符操作，會使索引失效
- 字符串字段和數字比較的時候會使索引無效
- 模糊查詢'%值%'會使索引無效，變為全表掃描，但是'值%'這種可以有效利用索引
- 排序中儘量使用到索引字段，這樣可以減少排序，提升查詢效率
閱讀更多 真致信息技術 的文章
