MySQL中如何使用索引

這篇文章給大家介紹MySQL中如何使用索引，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

mysql采用b+樹的方式存儲索引信息。

b+樹結(jié)構(gòu)如下：

說一下b+樹的幾個特點：

葉子節(jié)點（最下面的一層）存儲關(guān)鍵字（索引字段的值）信息及對應(yīng)的data，葉子節(jié)點存儲了所有記錄的關(guān)鍵字信息

其他非葉子節(jié)點只存儲關(guān)鍵字的信息及子節(jié)點的指針

每個葉子節(jié)點相當(dāng)于mysql中的一頁，同層級的葉子節(jié)點以雙向鏈表的形式相連

每個節(jié)點（頁）中存儲了多條記錄，記錄之間用單鏈表的形式連接組成了一條有序的鏈表，順序是按照索引字段排序的

b+樹中檢索數(shù)據(jù)時：每次檢索都是從根節(jié)點開始，一直需要搜索到葉子節(jié)點

InnoDB 的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的。也就是說，當(dāng)需要讀取一條記錄的時候，并不是將這個記錄本身從磁盤讀取出來，而是以頁為單位，將整個也加載到內(nèi)存中，一個頁中可能有很多記錄，然后在內(nèi)存中對頁進(jìn)行檢索。在innodb中，每個頁的大小默認(rèn)是16kb。

Mysql中索引分為

聚集索引（主鍵索引）

每個表一定會有一個聚集索引，整個表的數(shù)據(jù)存儲以b+樹的方式存在文件中，b+樹葉子節(jié)點中的key為主鍵值，data為完整記錄的信息；非葉子節(jié)點存儲主鍵的值。

通過聚集索引檢索數(shù)據(jù)只需要按照b+樹的搜索過程，即可以檢索到對應(yīng)的記錄。

非聚集索引

每個表可以有多個非聚集索引，b+樹結(jié)構(gòu)，葉子節(jié)點的key為索引字段字段的值，data為主鍵的值；非葉子節(jié)點只存儲索引字段的值。

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

索引怎么走，為什么有些查詢不走索引？為什么使用函數(shù)了數(shù)據(jù)就不走索引了？

這些問題可以先放一下，我們先看一下b+樹檢索數(shù)據(jù)的過程，這個屬于原理的部分，理解了b+樹各種數(shù)據(jù)檢索過程，上面的問題就都可以理解了。

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

當(dāng)我們對某個字段的值進(jìn)行某種檢索的時候，如果這個檢索過程中，我們能夠快速定位到目標(biāo)數(shù)據(jù)所在的頁，有效的降低頁的io操作，而不需要去掃描所有的數(shù)據(jù)頁的時候，我們認(rèn)為這種情況能夠有效的利用索引，也稱這個檢索可以走索引，如果這個過程中不能夠確定數(shù)據(jù)在那些頁中，我們認(rèn)為這種情況下索引對這個查詢是無效的，此查詢不走索引。

b+樹中數(shù)據(jù)檢索過程

唯一記錄檢索

如上圖，所有的數(shù)據(jù)都是唯一的，查詢105的記錄，過程如下：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2.  將P1頁加載到內(nèi)存

3.  在內(nèi)存中采用二分法查找，可以確定105位于[100,150)中間，所以我們需要去加載100關(guān)聯(lián)P4頁

4.  將P4加載到內(nèi)存中，采用二分法找到105的記錄后退出

查詢某個值的所有記錄

如上圖，查詢105的所有記錄，過程如下：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2.  將P1頁加載到內(nèi)存

3.  在內(nèi)存中采用二分法查找，可以確定105位于[100,150)中間，100關(guān)聯(lián)P4頁

4.  將P4加載到內(nèi)存中，采用二分法找到最有一個小于105的記錄，即100，然后通過鏈表從100開始向后訪問，找到所有的105記錄，直到遇到第一個大于100的值為止

范圍查找

數(shù)據(jù)如上圖，查詢[55,150]所有記錄，由于頁和頁之間是雙向鏈表升序結(jié)構(gòu)，頁內(nèi)部的數(shù)據(jù)是單項升序鏈表結(jié)構(gòu)，所以只用找到范圍的起始值所在的位置，然后通過依靠鏈表訪問兩個位置之間所有的數(shù)據(jù)即可，過程如下：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2.  將P1頁加載到內(nèi)存

3.  內(nèi)存中采用二分法找到55位于50關(guān)聯(lián)的P3頁中，150位于P5頁中

4.  將P3加載到內(nèi)存中，采用二分法找到第一個55的記錄，然后通過鏈表結(jié)構(gòu)繼續(xù)向后訪問P3中的60、67，當(dāng)P3訪問完畢之后，通過P3的nextpage指針訪問下一頁P4中所有記錄，繼續(xù)遍歷P4中的所有記錄，直到訪問到P5中所有的150為止。

模糊匹配

數(shù)據(jù)如上圖。

查詢以`f`開頭的所有記錄

過程如下：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2.     將P1數(shù)據(jù)加載到內(nèi)存中

3.     在P1頁的記錄中采用二分法找到最后一個小于等于f的值，這個值是f，以及第一個大于f的，這個值是z，f指向葉節(jié)點P3，z指向葉節(jié)點P6，此時可以斷定以f開頭的記錄可能存在于[P3,P6)這個范圍的頁內(nèi)，即P3、P4、P5這三個頁中

    3.     加載P3這個頁，在內(nèi)部以二分法找到第一條f開頭的記錄，然后以鏈表方式繼續(xù)向后訪問P4、P5中的記錄，即可以找到所有已f開頭的數(shù)據(jù)

查詢包含`f`的記錄

包含的查詢在sql中的寫法是%f%，通過索引我們還可以快速定位所在的頁么？

可以看一下上面的數(shù)據(jù)，f在每個頁中都存在，我們通過P1頁中的記錄是無法判斷包含f的記錄在那些頁的，只能通過io的方式加載所有葉子節(jié)點，并且遍歷所有記錄進(jìn)行過濾，才可以找到包含f的記錄。

所以如果使用了%值%這種方式，索引對查詢是無效的。

最左匹配原則

當(dāng)b+樹的數(shù)據(jù)項是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時候，b+樹是按照從左到右的順序來建立搜索樹的，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來檢索的時候，b+樹會優(yōu)先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒有name的數(shù)據(jù)來的時候，b+樹就不知道下一步該查哪個節(jié)點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來檢索時，b+樹可以用name來指定搜索方向，但下一個字段age的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了， 這個是非常重要的性質(zhì)，即索引的最左匹配特性。

來一些示例我們體驗一下。

下圖中是3個字段(a,b,c)的聯(lián)合索引，索引中數(shù)據(jù)的順序是以a asc,b asc,c asc這種排序方式存儲在節(jié)點中的，索引先以a字段升序，如果a相同的時候，以b字段升序，b相同的時候，以c字段升序，節(jié)點中每個數(shù)據(jù)認(rèn)真看一下。

查詢a=1的記錄

由于頁中的記錄是以a asc,b asc,c asc這種排序方式存儲的，所以a字段是有序的，可以通過二分法快速檢索到，過程如下：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2.  將P1加載到內(nèi)存中

3.  在內(nèi)存中對P1中的記錄采用二分法找，可以確定a=1的記錄位于{1,1,1}和{1,5,1}關(guān)聯(lián)的范圍內(nèi)，這兩個值子節(jié)點分別是P2、P4

4.  加載葉子節(jié)點P2，在P2中采用二分法快速找到第一條a=1的記錄，然后通過鏈表向下一條及下一頁開始檢索，直到在P4中找到第一個不滿足a=1的記錄為止

查詢a=1 and b=5的記錄

方法和上面的一樣，可以確定a=1 and b=5的記錄位于{1,1,1}和{1,5,1}關(guān)聯(lián)的范圍內(nèi)，查找過程和a=1查找步驟類似。

查詢b=1的記錄

這種情況通過P1頁中的記錄，是無法判斷b=1的記錄在那些頁中的，只能加鎖索引樹所有葉子節(jié)點，對所有記錄進(jìn)行遍歷，然后進(jìn)行過濾，此時索引是無效的。

按照c的值查詢

這種情況和查詢b=1也一樣，也只能掃描所有葉子節(jié)點，此時索引也無效了。

按照b和c一起查

這種也是無法利用索引的，也只能對所有數(shù)據(jù)進(jìn)行掃描，一條條判斷了，此時索引無效。

按照[a,c]兩個字段查詢

這種只能利用到索引中的a字段了，通過a確定索引范圍，然后加載a關(guān)聯(lián)的所有記錄，再對c的值進(jìn)行過濾。

查詢a=1 and b>=0 and c=1的記錄

這種情況只能先確定a=1 and b>=0所在頁的范圍，然后對這個范圍的所有頁進(jìn)行遍歷，c字段在這個查詢的過程中，是無法確定c的數(shù)據(jù)在哪些頁的，此時我們稱c是不走索引的，只有a、b能夠有效的確定索引頁的范圍。

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

上面說的各種情況，大家都多看一下圖中數(shù)據(jù)，認(rèn)真分析一下查詢的過程，基本上都可以理解了。

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

索引區(qū)分度

我們看2個有序數(shù)組

[1,2,3,4,5,6,7,8,8,9,10]

[1,1,1,1,1,8,8,8,8,8]

上面2個數(shù)組是有序的，都是10條記錄，如果我需要檢索值為8的所有記錄，那個更快一些？

咱們使用二分法查找包含8的所有記錄過程如下：先使用二分法找到最后一個小于8的記錄，然后沿著這條記錄向后獲取下一個記錄，和8對比，知道遇到第一個大于8的數(shù)字結(jié)束，或者到達(dá)數(shù)組末尾結(jié)束。

采用上面這種方法找到8的記錄，第一個數(shù)組中更快的一些。因為第二個數(shù)組中含有8的比例更多的，需要訪問以及匹配的次數(shù)更多一些。

這里就涉及到數(shù)據(jù)的區(qū)分度問題：

索引區(qū)分度 = count(distint 記錄) / count(記錄)。

當(dāng)索引區(qū)分度高的時候，檢索數(shù)據(jù)更快一些，索引區(qū)分度太低，說明重復(fù)的數(shù)據(jù)比較多，檢索的時候需要訪問更多的記錄才能夠找到所有目標(biāo)數(shù)據(jù)。

當(dāng)索引區(qū)分度非常小的時候，基本上接近于全索引數(shù)據(jù)的掃描了，此時查詢速度是比較慢的。

第一個數(shù)組索引區(qū)分度為1，第二個區(qū)分度為0.2，所以第一個檢索更快的一些。

所以我們創(chuàng)建索引的時候，盡量選擇區(qū)分度高的列作為索引。

正確使用索引

準(zhǔn)備400萬測試數(shù)據(jù)

/*建庫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));/*準(zhǔn)備數(shù)據(jù)*/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 ii = i + 1;   if i%10000=0 THEN   COMMIT;   START TRANSACTION;   END IF;   END WHILE;   COMMIT;   END $  DELIMITER ;  CALL proc1();

上面插入的400萬數(shù)據(jù)，除了sex列，其他列的值都是沒有重復(fù)的。

無索引檢索效果

400萬數(shù)據(jù)，我們隨便查詢幾個記錄看一下效果。

按照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)

id=1的數(shù)據(jù)，表中只有一行，耗時近2秒，由于id列無索引，只能對400萬數(shù)據(jù)進(jìn)行全表掃描。

主鍵檢索

test1表中沒有明確的指定主鍵，我們將id設(shè)置為主鍵：

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 | javacode1000000@163.com |  +---------+-----------------+-----+-------------------------+  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上有主鍵索引，這個采用的上面介紹的范圍查找可以快速定位目標(biāo)數(shù)據(jù)。

但是如果范圍太大，跨度的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的時候，區(qū)間跨度不要太大。

in的檢索

in方式檢索數(shù)據(jù)，我們還是經(jīng)常用的。

平時我們做項目的時候，建議少用表連接，比如電商中需要查詢訂單的信息和訂單中商品的名稱，可以先查詢查詢訂單表，然后訂單表中取出商品的id列表，采用in的方式到商品表檢索商品信息，由于商品id是商品表的主鍵，所以檢索速度還是比較快的。

通過id在400萬數(shù)據(jù)中檢索100條數(shù)據(jù)，看看效果：

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)

耗時不到1毫秒，還是相當(dāng)快的。

這個相當(dāng)于多個分解為多個唯一記錄檢索，然后將記錄合并。

多個索引時查詢?nèi)绾巫撸?/strong>

我們在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 | javacode3500000@163.com |  +---------+-----------------+-----+-------------------------+  1 row in set (0.00 sec)

上面查詢速度很快，name和sex上各有一個索引，覺得上面走哪個索引？

有人說name位于where第一個，所以走的是name字段所在的索引，過程可以解釋為這樣：

走name所在的索引找到j(luò)avacode3500000對應(yīng)的所有記錄

遍歷記錄過濾出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)

走name索引，然后再過濾，確實可以，速度也很快，果真和where后字段順序有關(guān)么？我們把name和sex的順序?qū)φ{(diào)一下，如下：

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)

速度還是很快，這次是不是先走sex索引檢索出數(shù)據(jù)，然后再過濾name呢？我們先來看一下sex=2查詢速度：

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

看上面，查詢耗時360毫秒，200萬數(shù)據(jù)，如果走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列：實際走的索引）。

當(dāng)多個條件中有索引的時候，并且關(guān)系是and的時候，會走索引區(qū)分度高的，顯然name字段重復(fù)度很低，走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字段上面的索引，下面的查詢是無法確定需要查找的值所在的范圍的，只能全表掃描，無法利用索引，所以速度比較慢，這個過程上面有說過。

回表

當(dāng)需要查詢的數(shù)據(jù)在索引樹中不存在的時候，需要再次到聚集索引中去獲取，這個過程叫做回表，如查詢：

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

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

走name索引檢索javacode3500000對應(yīng)的記錄，取出id為3500000

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

索引覆蓋

查詢中采用的索引樹中包含了查詢所需要的所有字段的值，不需要再去聚集索引檢索數(shù)據(jù)，這種叫索引覆蓋。

我們來看一個查詢：

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

name對應(yīng)idx1索引，id為主鍵，所以idx1索引樹葉子節(jié)點中包含了name、id的值，這個查詢只用走idx1這一個索引就可以了，如果select后面使用*，還需要一次回表獲取sex、email的值。

所以寫sql的時候，盡量避免使用*，*可能會多一次回表操作，需要看一下是否可以使用索引覆蓋來實現(xiàn)，效率更高一些。

索引下推

簡稱ICP，Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一種在存儲引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式，ICP可以減少存儲引擎訪問基表的次數(shù)以及MySQL服務(wù)器訪問存儲引擎的次數(shù)。

舉個例子來說一下：

我們需要查詢name以javacode35開頭的，性別為1的記錄數(shù)，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，然后重復(fù)上面的操作，直到找到所有記錄為止。

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

如果采用ICP的方式，我們可以這么做，創(chuàng)建一個(name,sex)的組合索引，查詢過程如下：

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

判斷R1.sex是否為1，然后重復(fù)上面的操作，知道找到所有記錄為止

這個過程中不需要回表操作了，通過索引的數(shù)據(jù)就可以完成整個條件的過濾，速度比上面的更快一些。

數(shù)字使字符串類索引失效

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)

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

第二條查詢很快，第三條用name和1比較，name上有索引，name是字符串類型，字符串和數(shù)字比較的時候，會將字符串強制轉(zhuǎn)換為數(shù)字，然后進(jìn)行比較，所以第二個查詢變成了全表掃描，只能取出每條數(shù)據(jù)，將name轉(zhuǎn)換為數(shù)字和1進(jìn)行比較。

數(shù)字字段和字符串比較什么效果呢？如下：

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)

id上面有主鍵索引，id是int類型的，可以看到，上面兩個查詢都非?？欤伎梢哉＠盟饕焖贆z索，所以如果字段是數(shù)組類型的，查詢的值是字符串還是數(shù)組都會走索引。

函數(shù)使索引無效

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)

name上有索引，上面查詢，第一個走索引，第二個不走索引，第二個使用了函數(shù)之后，name所在的索引樹是無法快速定位需要查找的數(shù)據(jù)所在的頁的，只能將所有頁的記錄加載到內(nèi)存中，然后對每條數(shù)據(jù)使用函數(shù)進(jìn)行計算之后再進(jìn)行條件判斷，此時索引無效了，變成了全表數(shù)據(jù)掃描。

結(jié)論：索引字段使用函數(shù)查詢使索引無效。

運算符使索引無效

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)

id上有主鍵索引，上面查詢，第一個走索引，第二個不走索引，第二個使用運算符，id所在的索引樹是無法快速定位需要查找的數(shù)據(jù)所在的頁的，只能將所有頁的記錄加載到內(nèi)存中，然后對每條數(shù)據(jù)的id進(jìn)行計算之后再判斷是否等于1，此時索引無效了，變成了全表數(shù)據(jù)掃描。

結(jié)論：索引字段使用了函數(shù)將使索引無效。

使用索引優(yōu)化排序

我們有個訂單表t_order(id,user_id,addtime,price)，經(jīng)常會查詢某個用戶的訂單，并且按照addtime升序排序，應(yīng)該怎么創(chuàng)建索引呢？我們來分析一下。

在user_id上創(chuàng)建索引，我們分析一下這種情況，數(shù)據(jù)檢索的過程：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

3.  通過id在主鍵索引中回表檢索出整條數(shù)據(jù)

4.  重復(fù)上面的操作，獲取所有目標(biāo)記錄

5.  在內(nèi)存中對目標(biāo)記錄按照addtime進(jìn)行排序

我們要知道當(dāng)數(shù)據(jù)量非常大的時候，排序還是比較慢的，可能會用到磁盤中的文件，有沒有一種方式，查詢出來的數(shù)據(jù)剛好是排好序的。

我們再回顧一下mysql中b+樹數(shù)據(jù)的結(jié)構(gòu)，記錄是按照索引的值排序組成的鏈表，如果將user_id和addtime放在一起組成聯(lián)合索引(user_id,addtime)，這樣通過user_id檢索出來的數(shù)據(jù)自然就是按照addtime排好序的，這樣直接少了一步排序操作，效率更好，如果需addtime降序，只需要將結(jié)果翻轉(zhuǎn)一下就可以了。

總結(jié)一下使用索引的一些建議

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. 在區(qū)分度高的字段上面建立索引可以有效的使用索引，區(qū)分度太低，無法有效的利用索引，可能需要掃描所有數(shù)據(jù)頁，此時和不使用索引差不多

3. 聯(lián)合索引注意最左匹配原則：必須按照從左到右的順序匹配，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的順序可以任意調(diào)整

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

    4. 有些查詢可以采用聯(lián)合索引，進(jìn)而使用到索引下推（IPC），也可以減少回表操作，提升效率

    5. 禁止對索引字段使用函數(shù)、運算符操作，會使索引失效

    6. 字符串字段和數(shù)字比較的時候會使索引無效

    7. 模糊查詢'%值%'會使索引無效，變?yōu)槿頀呙?，但?#39;值%'這種可以有效利用索引

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

關(guān)于MySQL中如何使用索引就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。