Mysql系列第二十三講 如何正確的使用索引？

qwer1030274531發表於2020-10-14
MySql索引

先來回顧一些知識

本篇文章我們以innodb儲存引擎為例來做說明。

mysql採用b+樹的方式儲存索引資訊。

b+樹結構如下：
在這裡插入圖片描述
說一下b+樹的幾個特點：

  1. 葉子節點（最下面的一層）儲存關鍵字（索引欄位的值）資訊及對應的data，葉子節點儲存了所有記錄的關鍵字資訊

  2. 其他非葉子節點只儲存關鍵字的資訊及子節點的指標

  3. 每個葉子節點相當於mysql中的一頁，同層級的葉子節點以雙向連結串列的形式相連

  4. 每個節點（頁）中儲存了多條記錄，記錄之間用單連結串列的形式連線組成了一條有序的連結串列，順序是按照索引欄位排序的

  5. b+樹中檢索資料時：每次檢索都是從根節點開始，一直需要搜尋到葉子節點

InnoDB 的資料是按資料頁為單位來讀寫的。也就是說，當需要讀取一條記錄的時候，並不是將這個記錄本身從磁碟讀取出來，而是以頁為單位，將整個也載入到記憶體中，一個頁中可能有很多記錄，然後在記憶體中對頁進行檢索。在innodb中，每個頁的大小預設是16kb。

Mysql中索引分為

聚集索引（主鍵索引）

每個表一定會有一個聚集索引，整個表的資料儲存以b+樹的方式存在檔案中，b+樹葉子節點中的key為主鍵值，data為完整記錄的資訊；非葉子節點儲存主鍵的值。

透過聚集索引檢索資料只需要按照b+樹的搜尋過程，即可以檢索到對應的記錄。

非聚集索引

每個表可以有多個非聚集索引，b+樹結構，葉子節點的key為索引欄位欄位的值，data為主鍵的值；非葉子節點只儲存索引欄位的值。

透過非聚集索引檢索記錄的時候，需要2次操作，先在非聚集索引中檢索出主鍵，然後再到聚集索引中檢索出主鍵對應的記錄，該過程比聚集索引多了一次操作。

索引怎麼走，為什麼有些查詢不走索引？為什麼使用函式了資料就不走索引了？

這些問題可以先放一下，我們先看一下b+樹檢索資料的過程，這個屬於原理的部分，理解了b+樹各種資料檢索過程，上面的問題就都可以理解了。

通常說的這個查詢走索引了是什麼意思？

當我們對某個欄位的值進行某種檢索的時候，如果這個檢索過程中，我們能夠快速定位到目標資料所在的頁，有效的降低頁的io操作，而不需要去掃描所有的資料頁的時候，我們認為這種情況能夠有效的利用索引，也稱這個檢索可以走索引，如果這個過程中不能夠確定資料在那些頁中，我們認為這種情況下索引對這個查詢是無效的，此查詢不走索引。

b+樹中資料檢索過程

唯一記錄檢索

在這裡插入圖片描述
如上圖，所有的資料都是唯一的，查詢105的記錄，過程如下：

  1. 將P1頁載入到記憶體

  2. 在記憶體中採用二分法查詢，可以確定105位於[100,150)中間，所以我們需要去載入100關聯P4頁

  3. 將P4載入到記憶體中，採用二分法找到105的記錄後退出

查詢某個值的所有記錄

在這裡插入圖片描述
如上圖，查詢105的所有記錄，過程如下：

  1. 將P1頁載入到記憶體

  2. 在記憶體中採用二分法查詢，可以確定105位於[100,150)中間，100關聯P4頁

  3. 將P4載入到記憶體中，採用二分法找到最有一個小於105的記錄，即100，然後透過連結串列從100開始向後訪問，找到所有的105記錄，直到遇到第一個大於100的值為止

範圍查詢

在這裡插入圖片描述
資料如上圖，查詢[55,150]所有記錄，由於頁和頁之間是雙向連結串列升序結構，頁內部的資料是單項升序連結串列結構，所以只用找到範圍的起始值所在的位置，然後透過依靠連結串列訪問兩個位置之間所有的資料即可，過程如下：

  1. 將P1頁載入到記憶體

  2. 記憶體中採用二分法找到55位於50關聯的P3頁中，150位於P5頁中

  3. 將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能夠有效的確定索引頁的範圍。

類似這種的還有>、<、between and，多欄位索引的情況下，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配。

上面說的各種情況，大家都多看一下圖中資料，認真分析一下查詢的過程，基本上都可以理解了。

上面這種查詢叫做最左匹配原則。

索引區分度

我們看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();123456789101112131415161718192021222324252627282930313233

上面插入的400萬資料，除了sex列，其他列的值都是沒有重複的。

無索引檢索效果

按照id查詢記錄

mysql> select * from test1 where id = 1;+----+-----------+-----+-------------------+| id | name      | sex | email             |+----+-----------+-----+-------------------+|  1 | javacode1 |   1 | javacode1@163.com |+----+-----------+-----+-------------------+1 row in set (1.91 sec)1234567

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: 0mysql> 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)1234567891011

id被置為主鍵之後，會在id上建立聚集索引，隨便檢索一條我們看一下效果：

mysql> select * from test1 where id = 1000000;+---------+-----------------+-----+-------------------------+| id      | name            | sex | email                   |+---------+-----------------+-----+-------------------------+| 1000000 | javacode1000000 |   2 | javacode1000000@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)1234567

這個速度很快，這個走的是上面介紹的 唯一記錄檢索。

between and範圍檢索 

mysql> select count(*) from test1 where id between 100 and 110;+----------+| count(*) |+----------+|       11 |+----------+1 row in set (0.00 sec)1234567

速度也很快，id上有主鍵索引，這個採用的上面介紹的範圍查詢可以快速定位目標資料。

但是如果範圍太大，跨度的page也太多，速度也會比較慢，如下：

mysql> select count(*) from test1 where id between 1 and 2000000;+----------+| count(*) |+----------+|  2000000 |+----------+1 row in set (1.17 sec)1234567

上面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 | javacode100000@163.com || 100001 | javacode100001 |   1 | javacode100001@163.com || 100002 | javacode100002 |   2 | javacode100002@163.com |.......| 100099 | javacode100099 |   1 | javacode100099@163.com |+--------+----------------+-----+------------------------+100 rows in set (0.00 sec)1234567891011

耗時不到1毫秒，還是相當快的。

這個相當於多個分解為多個唯一記錄檢索，然後將記錄合併。

多個索引時查詢如何走？

我們在name、sex兩個欄位上分別建個索引 

mysql> create index idx1 on test1(name);Query OK, 0 rows affected (13.50 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> create index idx2 on test1(sex);Query OK, 0 rows affected (6.77 sec)Records: 0  Duplicates: 0  Warnings: 01234567

看一下查詢：

mysql> select * from test1 where name='javacode3500000' and sex=2;+---------+-----------------+-----+-------------------------+| id      | name            | sex | email                   |+---------+-----------------+-----+-------------------------+| 3500000 | javacode3500000 |   2 | javacode3500000@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)1234567

上面查詢速度很快，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 | javacode3500000@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)1234567

走name索引，然後再過濾，確實可以，速度也很快，果真和where後欄位順序有關麼？我們把name和sex的順序對調一下，如下：

mysql> select * from test1 where sex=2 and name='javacode3500000';+---------+-----------------+-----+-------------------------+| id      | name            | sex | email                   |+---------+-----------------+-----+-------------------------+| 3500000 | javacode3500000 |   2 | javacode3500000@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)1234567

速度還是很快，這次是不是先走sex索引檢索出資料，然後再過濾name呢？我們先來看一下sex=2查詢速度：

mysql> select count(id) from test1 where sex=2;+-----------+| count(id) |+-----------+|   2000000 |+-----------+1 row in set (0.36 sec)1234567

看上面，查詢耗時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)1234567

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)123456789101112131415

上面第一個查詢可以利用到name欄位上面的索引，下面的查詢是無法確定需要查詢的值所在的範圍的，只能全表掃描，無法利用索引，所以速度比較慢，這個過程上面有說過。

回表

當需要查詢的資料在索引樹中不存在的時候，需要再次到聚集索引中去獲取，這個過程叫做回表，如查詢：

mysql> select * from test1 where name='javacode3500000';+---------+-----------------+-----+-------------------------+| id      | name            | sex | email                   |+---------+-----------------+-----+-------------------------+| 3500000 | javacode3500000 |   2 | javacode3500000@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)1234567

上面查詢是*，由於name列所在的索引中只有name、id兩個列的值，不包含sex、email，所以上面過程如下：

走name索引檢索javacode3500000對應的記錄，取出id為3500000

在主鍵索引中檢索出id=3500000的記錄，獲取所有欄位的值

索引覆蓋

查詢中採用的索引樹中包含了查詢所需要的所有欄位的值，不需要再去聚集索引檢索資料，這種叫索引覆蓋。

我們來看一個查詢：

select id,name from test1 where name='javacode3500000';1

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)1234567

過程：

  1. 走name索引檢索出以javacode35的第一條記錄，得到記錄的id

  2. 利用id去主鍵索引中查詢出這條記錄R1

  3. 判斷R1中的sex是否為1，然後重複上面的操作，直到找到所有記錄為止。

上面的過程中需要走name索引以及需要回表操作。

如果採用ICP的方式，我們可以這麼做，建立一個(name,sex)的組合索引，查詢過程如下：

  1. 走(name,sex)索引檢索出以javacode35的第一條記錄，可以得到(name,sex,id)，記做R1

  2. 判斷R1.sex是否為1，然後重複上面的操作，知道找到所有記錄為止

這個過程中不需要回表操作了，透過索引的資料就可以完成整個條件的過濾，速度比上面的更快一些。

數字使字串類索引失效 http://dxb.myzx.cn/cure/

mysql> insert into test1 (id,name,sex,email) values (4000001,'1',1,'javacode2018@163.com');Query OK, 1 row affected (0.00 sec)mysql> select * from test1 where name = '1';+---------+------+-----+----------------------+| id      | name | sex | email                |+---------+------+-----+----------------------+| 4000001 | 1    |   1 | javacode2018@163.com |+---------+------+-----+----------------------+1 row in set (0.00 sec)mysql> select * from test1 where name = 1;+---------+------+-----+----------------------+| id      | name | sex | email                |+---------+------+-----+----------------------+| 4000001 | 1    |   1 | javacode2018@163.com |+---------+------+-----+----------------------+1 row in set, 65535 warnings (3.30 sec)123456789101112131415161718

上面3條sql，我們插入了一條記錄。

第二條查詢很快，第三條用name和1比較，name上有索引，name是字串型別，字串和數字比較的時候，會將字串強制轉換為數字，然後進行比較，所以第二個查詢變成了全表掃描，只能取出每條資料，將name轉換為數字和1進行比較。

數字欄位和字串比較什麼效果呢？如下：

mysql> select * from test1 where id = '4000000';+---------+-----------------+-----+-------------------------+| id      | name            | sex | email                   |+---------+-----------------+-----+-------------------------+| 4000000 | javacode4000000 |   2 | javacode4000000@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)mysql> select * from test1 where id = 4000000;+---------+-----------------+-----+-------------------------+| id      | name            | sex | email                   |+---------+-----------------+-----+-------------------------+| 4000000 | javacode4000000 |   2 | javacode4000000@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)123456789101112131415

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 | javacode1@163.com |+----+-----------+-----+-------------------+1 row in set (2.88 sec)123456789101112131415

name上有索引，上面查詢，第一個走索引，第二個不走索引，第二個使用了函式之後，name所在的索引樹是無法快速定位需要查詢的資料所在的頁的，只能將所有頁的記錄載入到記憶體中，然後對每條資料使用函式進行計算之後再進行條件判斷，此時索引無效了，變成了全表資料掃描。

結論：索引欄位使用函式查詢使索引無效。

運算子使索引無效

mysql> select * from test1 a where id = 2 - 1;+----+-----------+-----+-------------------+| id | name      | sex | email             |+----+-----------+-----+-------------------+|  1 | javacode1 |   1 | javacode1@163.com |+----+-----------+-----+-------------------+1 row in set (0.00 sec)mysql> select * from test1 a where id+1 = 2;+----+-----------+-----+-------------------+| id | name      | sex | email             |+----+-----------+-----+-------------------+|  1 | javacode1 |   1 | javacode1@163.com |+----+-----------+-----+-------------------+1 row in set (2.41 sec)123456789101112131415

id上有主鍵索引，上面查詢，第一個走索引，第二個不走索引，第二個使用運算子，id所在的索引樹是無法快速定位需要查詢的資料所在的頁的，只能將所有頁的記錄載入到記憶體中，然後對每條資料的id進行計算之後再判斷是否等於1，此時索引無效了，變成了全表資料掃描。

結論：索引欄位使用了函式將使索引無效。

使用索引最佳化排序

我們有個訂單表t_order(id,user_id,addtime,price)，經常會查詢某個使用者的訂單，並且按照addtime升序排序，應該怎麼建立索引呢？我們來分析一下。

在user_id上建立索引，我們分析一下這種情況，資料檢索的過程：

  1. 走user_id索引，找到記錄的的id

  2. 透過id在主鍵索引中回表檢索出整條資料

  3. 重複上面的操作，獲取所有目標記錄

  4. 在記憶體中對目標記錄按照addtime進行排序

我們要知道當資料量非常大的時候，排序還是比較慢的，可能會用到磁碟中的檔案，有沒有一種方式，查詢出來的資料剛好是排好序的。

我們再回顧一下mysql中b+樹資料的結構，記錄是按照索引的值排序組成的連結串列，如果將user_id和addtime放在一起組成聯合索引(user_id,addtime)，這樣透過user_id檢索出來的資料自然就是按照addtime排好序的，這樣直接少了一步排序操作，效率更好，如果需addtime降序，只需要將結果翻轉一下就可以了。

總結一下使用索引的一些建議

  1. 在區分度高的欄位上面建立索引可以有效的使用索引，區分度太低，無法有效的利用索引，可能需要掃描所有資料頁，此時和不使用索引差不多

  2. 聯合索引注意最左匹配原則：必須按照從左到右的順序匹配，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整

  3. 查詢記錄的時候，少使用*，儘量去利用索引覆蓋，可以減少回表操作，提升效率

  4. 有些查詢可以採用聯合索引，進而使用到索引下推（IPC），也可以減少回表操作，提升效率

  5. 禁止對索引欄位使用函式、運算子操作，會使索引失效

  6. 字串欄位和數字比較的時候會使索引無效

  7. 模糊查詢’%值%'會使索引無效，變為全表掃描，但是’值%'這種可以有效利用索引

  8. 排序中儘量使用到索引欄位，這樣可以減少排序，提升查詢效率


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30239065/viewspace-2726756/，如需轉載，請註明出處，否則將追究法律責任。

相關文章