這篇文章給大家介紹MySQL中如何使用索引，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。
創(chuàng)新互聯(lián)公司致力于互聯(lián)網(wǎng)品牌建設(shè)與網(wǎng)絡(luò)營(yíng)銷(xiāo)，包括成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、SEO優(yōu)化、網(wǎng)絡(luò)推廣、整站優(yōu)化營(yíng)銷(xiāo)策劃推廣、電子商務(wù)、移動(dòng)互聯(lián)網(wǎng)營(yíng)銷(xiāo)等。創(chuàng)新互聯(lián)公司為不同類(lèi)型的客戶(hù)提供良好的互聯(lián)網(wǎng)應(yīng)用定制及解決方案，創(chuàng)新互聯(lián)公司核心團(tuán)隊(duì)十載專(zhuān)注互聯(lián)網(wǎng)開(kāi)發(fā)，積累了豐富的網(wǎng)站經(jīng)驗(yàn)，為廣大企業(yè)客戶(hù)提供一站式企業(yè)網(wǎng)站建設(shè)服務(wù)，在網(wǎng)站建設(shè)行業(yè)內(nèi)樹(shù)立了良好口碑。
mysql采用b+樹(shù)的方式存儲(chǔ)索引信息。
b+樹(shù)結(jié)構(gòu)如下：
說(shuō)一下b+樹(shù)的幾個(gè)特點(diǎn)：
葉子節(jié)點(diǎn)（最下面的一層）存儲(chǔ)關(guān)鍵字（索引字段的值）信息及對(duì)應(yīng)的data，葉子節(jié)點(diǎn)存儲(chǔ)了所有記錄的關(guān)鍵字信息
其他非葉子節(jié)點(diǎn)只存儲(chǔ)關(guān)鍵字的信息及子節(jié)點(diǎn)的指針
每個(gè)葉子節(jié)點(diǎn)相當(dāng)于mysql中的一頁(yè)，同層級(jí)的葉子節(jié)點(diǎn)以雙向鏈表的形式相連
每個(gè)節(jié)點(diǎn)（頁(yè)）中存儲(chǔ)了多條記錄，記錄之間用單鏈表的形式連接組成了一條有序的鏈表，順序是按照索引字段排序的
b+樹(shù)中檢索數(shù)據(jù)時(shí)：每次檢索都是從根節(jié)點(diǎn)開(kāi)始，一直需要搜索到葉子節(jié)點(diǎn)
InnoDB 的數(shù)據(jù)是按數(shù)據(jù)頁(yè)為單位來(lái)讀寫(xiě)的。也就是說(shuō)，當(dāng)需要讀取一條記錄的時(shí)候，并不是將這個(gè)記錄本身從磁盤(pán)讀取出來(lái)，而是以頁(yè)為單位，將整個(gè)也加載到內(nèi)存中，一個(gè)頁(yè)中可能有很多記錄，然后在內(nèi)存中對(duì)頁(yè)進(jìn)行檢索。在innodb中，每個(gè)頁(yè)的大小默認(rèn)是16kb。
Mysql中索引分為
聚集索引（主鍵索引）
每個(gè)表一定會(huì)有一個(gè)聚集索引，整個(gè)表的數(shù)據(jù)存儲(chǔ)以b+樹(shù)的方式存在文件中，b+樹(shù)葉子節(jié)點(diǎn)中的key為主鍵值，data為完整記錄的信息；非葉子節(jié)點(diǎn)存儲(chǔ)主鍵的值。
通過(guò)聚集索引檢索數(shù)據(jù)只需要按照b+樹(shù)的搜索過(guò)程，即可以檢索到對(duì)應(yīng)的記錄。
非聚集索引
每個(gè)表可以有多個(gè)非聚集索引，b+樹(shù)結(jié)構(gòu)，葉子節(jié)點(diǎn)的key為索引字段字段的值，data為主鍵的值；非葉子節(jié)點(diǎn)只存儲(chǔ)索引字段的值。
通過(guò)非聚集索引檢索記錄的時(shí)候，需要2次操作，先在非聚集索引中檢索出主鍵，然后再到聚集索引中檢索出主鍵對(duì)應(yīng)的記錄，該過(guò)程比聚集索引多了一次操作。
索引怎么走，為什么有些查詢(xún)不走索引？為什么使用函數(shù)了數(shù)據(jù)就不走索引了？
這些問(wèn)題可以先放一下，我們先看一下b+樹(shù)檢索數(shù)據(jù)的過(guò)程，這個(gè)屬于原理的部分，理解了b+樹(shù)各種數(shù)據(jù)檢索過(guò)程，上面的問(wèn)題就都可以理解了。
通常說(shuō)的這個(gè)查詢(xún)走索引了是什么意思？
當(dāng)我們對(duì)某個(gè)字段的值進(jìn)行某種檢索的時(shí)候，如果這個(gè)檢索過(guò)程中，我們能夠快速定位到目標(biāo)數(shù)據(jù)所在的頁(yè)，有效的降低頁(yè)的io操作，而不需要去掃描所有的數(shù)據(jù)頁(yè)的時(shí)候，我們認(rèn)為這種情況能夠有效的利用索引，也稱(chēng)這個(gè)檢索可以走索引，如果這個(gè)過(guò)程中不能夠確定數(shù)據(jù)在那些頁(yè)中，我們認(rèn)為這種情況下索引對(duì)這個(gè)查詢(xún)是無(wú)效的，此查詢(xún)不走索引。
b+樹(shù)中數(shù)據(jù)檢索過(guò)程
唯一記錄檢索
如上圖，所有的數(shù)據(jù)都是唯一的，查詢(xún)105的記錄，過(guò)程如下：
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)
將P1頁(yè)加載到內(nèi)存
在內(nèi)存中采用二分法查找，可以確定105位于[100,150)中間，所以我們需要去加載100關(guān)聯(lián)P4頁(yè)
將P4加載到內(nèi)存中，采用二分法找到105的記錄后退出
查詢(xún)某個(gè)值的所有記錄
如上圖，查詢(xún)105的所有記錄，過(guò)程如下：
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)
將P1頁(yè)加載到內(nèi)存
在內(nèi)存中采用二分法查找，可以確定105位于[100,150)中間，100關(guān)聯(lián)P4頁(yè)
將P4加載到內(nèi)存中，采用二分法找到最有一個(gè)小于105的記錄，即100，然后通過(guò)鏈表從100開(kāi)始向后訪(fǎng)問(wèn)，找到所有的105記錄，直到遇到第一個(gè)大于100的值為止
范圍查找
數(shù)據(jù)如上圖，查詢(xún)[55,150]所有記錄，由于頁(yè)和頁(yè)之間是雙向鏈表升序結(jié)構(gòu)，頁(yè)內(nèi)部的數(shù)據(jù)是單項(xiàng)升序鏈表結(jié)構(gòu)，所以只用找到范圍的起始值所在的位置，然后通過(guò)依靠鏈表訪(fǎng)問(wèn)兩個(gè)位置之間所有的數(shù)據(jù)即可，過(guò)程如下：
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)
將P1頁(yè)加載到內(nèi)存
內(nèi)存中采用二分法找到55位于50關(guān)聯(lián)的P3頁(yè)中，150位于P5頁(yè)中
將P3加載到內(nèi)存中，采用二分法找到第一個(gè)55的記錄，然后通過(guò)鏈表結(jié)構(gòu)繼續(xù)向后訪(fǎng)問(wèn)P3中的60、67，當(dāng)P3訪(fǎng)問(wèn)完畢之后，通過(guò)P3的nextpage指針訪(fǎng)問(wèn)下一頁(yè)P(yáng)4中所有記錄，繼續(xù)遍歷P4中的所有記錄，直到訪(fǎng)問(wèn)到P5中所有的150為止。
模糊匹配
數(shù)據(jù)如上圖。
查詢(xún)以`f`開(kāi)頭的所有記錄
過(guò)程如下：
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)
將P1數(shù)據(jù)加載到內(nèi)存中
在P1頁(yè)的記錄中采用二分法找到最后一個(gè)小于等于f的值，這個(gè)值是f，以及第一個(gè)大于f的，這個(gè)值是z，f指向葉節(jié)點(diǎn)P3，z指向葉節(jié)點(diǎn)P6，此時(shí)可以斷定以f開(kāi)頭的記錄可能存在于[P3,P6)這個(gè)范圍的頁(yè)內(nèi)，即P3、P4、P5這三個(gè)頁(yè)中
3. 加載P3這個(gè)頁(yè)，在內(nèi)部以二分法找到第一條f開(kāi)頭的記錄，然后以鏈表方式繼續(xù)向后訪(fǎng)問(wèn)P4、P5中的記錄，即可以找到所有已f開(kāi)頭的數(shù)據(jù)
查詢(xún)包含`f`的記錄
包含的查詢(xún)?cè)趕ql中的寫(xiě)法是%f%，通過(guò)索引我們還可以快速定位所在的頁(yè)么？
可以看一下上面的數(shù)據(jù)，f在每個(gè)頁(yè)中都存在，我們通過(guò)P1頁(yè)中的記錄是無(wú)法判斷包含f的記錄在那些頁(yè)的，只能通過(guò)io的方式加載所有葉子節(jié)點(diǎn)，并且遍歷所有記錄進(jìn)行過(guò)濾，才可以找到包含f的記錄。
所以如果使用了%值%這種方式，索引對(duì)查詢(xún)是無(wú)效的。
最左匹配原則
當(dāng)b+樹(shù)的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時(shí)候，b+樹(shù)是按照從左到右的順序來(lái)建立搜索樹(shù)的，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來(lái)檢索的時(shí)候，b+樹(shù)會(huì)優(yōu)先比較name來(lái)確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒(méi)有name的數(shù)據(jù)來(lái)的時(shí)候，b+樹(shù)就不知道下一步該查哪個(gè)節(jié)點(diǎn)，因?yàn)榻⑺阉鳂?shù)的時(shí)候name就是第一個(gè)比較因子，必須要先根據(jù)name來(lái)搜索才能知道下一步去哪里查詢(xún)。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來(lái)檢索時(shí)，b+樹(shù)可以用name來(lái)指定搜索方向，但下一個(gè)字段age的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了， 這個(gè)是非常重要的性質(zhì)，即索引的最左匹配特性。
來(lái)一些示例我們體驗(yàn)一下。
下圖中是3個(gè)字段(a,b,c)的聯(lián)合索引，索引中數(shù)據(jù)的順序是以a asc,b asc,c asc這種排序方式存儲(chǔ)在節(jié)點(diǎn)中的，索引先以a字段升序，如果a相同的時(shí)候，以b字段升序，b相同的時(shí)候，以c字段升序，節(jié)點(diǎn)中每個(gè)數(shù)據(jù)認(rèn)真看一下。
查詢(xún)a=1的記錄
由于頁(yè)中的記錄是以a asc,b asc,c asc這種排序方式存儲(chǔ)的，所以a字段是有序的，可以通過(guò)二分法快速檢索到，過(guò)程如下：
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)
將P1加載到內(nèi)存中
在內(nèi)存中對(duì)P1中的記錄采用二分法找，可以確定a=1的記錄位于{1,1,1}和{1,5,1}關(guān)聯(lián)的范圍內(nèi)，這兩個(gè)值子節(jié)點(diǎn)分別是P2、P4
加載葉子節(jié)點(diǎn)P2，在P2中采用二分法快速找到第一條a=1的記錄，然后通過(guò)鏈表向下一條及下一頁(yè)開(kāi)始檢索，直到在P4中找到第一個(gè)不滿(mǎn)足a=1的記錄為止
查詢(xún)a=1 and b=5的記錄
方法和上面的一樣，可以確定a=1 and b=5的記錄位于{1,1,1}和{1,5,1}關(guān)聯(lián)的范圍內(nèi)，查找過(guò)程和a=1查找步驟類(lèi)似。
查詢(xún)b=1的記錄
這種情況通過(guò)P1頁(yè)中的記錄，是無(wú)法判斷b=1的記錄在那些頁(yè)中的，只能加鎖索引樹(shù)所有葉子節(jié)點(diǎn)，對(duì)所有記錄進(jìn)行遍歷，然后進(jìn)行過(guò)濾，此時(shí)索引是無(wú)效的。
按照c的值查詢(xún)
這種情況和查詢(xún)b=1也一樣，也只能掃描所有葉子節(jié)點(diǎn)，此時(shí)索引也無(wú)效了。
按照b和c一起查
這種也是無(wú)法利用索引的，也只能對(duì)所有數(shù)據(jù)進(jìn)行掃描，一條條判斷了，此時(shí)索引無(wú)效。
按照[a,c]兩個(gè)字段查詢(xún)
這種只能利用到索引中的a字段了，通過(guò)a確定索引范圍，然后加載a關(guān)聯(lián)的所有記錄，再對(duì)c的值進(jìn)行過(guò)濾。
查詢(xún)a=1 and b>=0 and c=1的記錄
這種情況只能先確定a=1 and b>=0所在頁(yè)的范圍，然后對(duì)這個(gè)范圍的所有頁(yè)進(jìn)行遍歷，c字段在這個(gè)查詢(xún)的過(guò)程中，是無(wú)法確定c的數(shù)據(jù)在哪些頁(yè)的，此時(shí)我們稱(chēng)c是不走索引的，只有a、b能夠有效的確定索引頁(yè)的范圍。
類(lèi)似這種的還有>、<、between and，多字段索引的情況下，mysql會(huì)一直向右匹配直到遇到范圍查詢(xún)(>、<、between、like)就停止匹配。
上面說(shuō)的各種情況，大家都多看一下圖中數(shù)據(jù)，認(rèn)真分析一下查詢(xún)的過(guò)程，基本上都可以理解了。
上面這種查詢(xún)叫做最左匹配原則。
索引區(qū)分度
我們看2個(gè)有序數(shù)組
[1,2,3,4,5,6,7,8,8,9,10]
[1,1,1,1,1,8,8,8,8,8]
上面2個(gè)數(shù)組是有序的，都是10條記錄，如果我需要檢索值為8的所有記錄，那個(gè)更快一些？
咱們使用二分法查找包含8的所有記錄過(guò)程如下：先使用二分法找到最后一個(gè)小于8的記錄，然后沿著這條記錄向后獲取下一個(gè)記錄，和8對(duì)比，知道遇到第一個(gè)大于8的數(shù)字結(jié)束，或者到達(dá)數(shù)組末尾結(jié)束。
采用上面這種方法找到8的記錄，第一個(gè)數(shù)組中更快的一些。因?yàn)榈诙€(gè)數(shù)組中含有8的比例更多的，需要訪(fǎng)問(wèn)以及匹配的次數(shù)更多一些。
這里就涉及到數(shù)據(jù)的區(qū)分度問(wèn)題：
索引區(qū)分度 = count(distint 記錄) / count(記錄)。
當(dāng)索引區(qū)分度高的時(shí)候，檢索數(shù)據(jù)更快一些，索引區(qū)分度太低，說(shuō)明重復(fù)的數(shù)據(jù)比較多，檢索的時(shí)候需要訪(fǎng)問(wèn)更多的記錄才能夠找到所有目標(biāo)數(shù)據(jù)。
當(dāng)索引區(qū)分度非常小的時(shí)候，基本上接近于全索引數(shù)據(jù)的掃描了，此時(shí)查詢(xún)速度是比較慢的。
第一個(gè)數(shù)組索引區(qū)分度為1，第二個(gè)區(qū)分度為0.2，所以第一個(gè)檢索更快的一些。
所以我們創(chuàng)建索引的時(shí)候，盡量選擇區(qū)分度高的列作為索引。
正確使用索引
準(zhǔn)備400萬(wàn)測(cè)試數(shù)據(jù)
重慶分公司
重慶分公司
/*建庫(kù)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 '編號(hào)', 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萬(wàn)數(shù)據(jù)，除了sex列，其他列的值都是沒(méi)有重復(fù)的。
無(wú)索引檢索效果
400萬(wàn)數(shù)據(jù)，我們隨便查詢(xún)幾個(gè)記錄看一下效果。
按照id查詢(xún)記錄
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ù)，表中只有一行，耗時(shí)近2秒，由于id列無(wú)索引，只能對(duì)400萬(wàn)數(shù)據(jù)進(jìn)行全表掃描。
主鍵檢索
test1表中沒(méi)有明確的指定主鍵，我們將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被置為主鍵之后，會(huì)在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)
這個(gè)速度很快，這個(gè)走的是上面介紹的`唯一記錄檢索`。
between and范圍檢索
mysql> select count(*) from test1 where id between 100 and 110; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (0.00 sec)
速度也很快，id上有主鍵索引，這個(gè)采用的上面介紹的范圍查找可以快速定位目標(biāo)數(shù)據(jù)。
但是如果范圍太大，跨度的page也太多，速度也會(huì)比較慢，如下：
mysql> select count(*) from test1 where id between 1 and 2000000; +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (1.17 sec)
上面id的值跨度太大，1所在的頁(yè)和200萬(wàn)所在頁(yè)中間有很多頁(yè)需要讀取，所以比較慢。
所以使用between and的時(shí)候，區(qū)間跨度不要太大。
in的檢索
in方式檢索數(shù)據(jù)，我們還是經(jīng)常用的。
平時(shí)我們做項(xiàng)目的時(shí)候，建議少用表連接，比如電商中需要查詢(xún)訂單的信息和訂單中商品的名稱(chēng)，可以先查詢(xún)查詢(xún)訂單表，然后訂單表中取出商品的id列表，采用in的方式到商品表檢索商品信息，由于商品id是商品表的主鍵，所以檢索速度還是比較快的。
通過(guò)id在400萬(wàn)數(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)
耗時(shí)不到1毫秒，還是相當(dāng)快的。
這個(gè)相當(dāng)于多個(gè)分解為多個(gè)唯一記錄檢索，然后將記錄合并。
多個(gè)索引時(shí)查詢(xún)?nèi)绾巫撸?/strong>
我們?cè)趎ame、sex兩個(gè)字段上分別建個(gè)索引
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
看一下查詢(xún)：
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)
上面查詢(xún)速度很快，name和sex上各有一個(gè)索引，覺(jué)得上面走哪個(gè)索引？
有人說(shuō)name位于where第一個(gè)，所以走的是name字段所在的索引，過(guò)程可以解釋為這樣：
走name所在的索引找到j(luò)avacode3500000對(duì)應(yīng)的所有記錄
遍歷記錄過(guò)濾出sex=2的值
我們看一下name='javacode3500000'檢索速度，確實(shí)很快，如下：
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索引，然后再過(guò)濾，確實(shí)可以，速度也很快，果真和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ù)，然后再過(guò)濾name呢？我們先來(lái)看一下sex=2查詢(xún)速度：
mysql> select count(id) from test1 where sex=2; +-----------+ | count(id) | +-----------+ | 2000000 | +-----------+ 1 row in set (0.36 sec)
看上面，查詢(xún)耗時(shí)360毫秒，200萬(wàn)數(shù)據(jù)，如果走sex肯定是不行的。
我們使用explain來(lái)看一下：
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：列出了這個(gè)查詢(xún)可能會(huì)走兩個(gè)索引（idx1、idx2）
實(shí)際上走的卻是idx1（key列：實(shí)際走的索引）。
當(dāng)多個(gè)條件中有索引的時(shí)候，并且關(guān)系是and的時(shí)候，會(huì)走索引區(qū)分度高的，顯然name字段重復(fù)度很低，走name查詢(xún)會(huì)更快一些。
模糊查詢(xún)
看兩個(gè)查詢(xún)
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)
上面第一個(gè)查詢(xún)可以利用到name字段上面的索引，下面的查詢(xún)是無(wú)法確定需要查找的值所在的范圍的，只能全表掃描，無(wú)法利用索引，所以速度比較慢，這個(gè)過(guò)程上面有說(shuō)過(guò)。
回表
當(dāng)需要查詢(xún)的數(shù)據(jù)在索引樹(shù)中不存在的時(shí)候，需要再次到聚集索引中去獲取，這個(gè)過(guò)程叫做回表，如查詢(xún)：
mysql> select * from test1 where name='javacode3500000'; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 3500000 | javacode3500000 | 2 | javacode3500000@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)
上面查詢(xún)是*，由于name列所在的索引中只有name、id兩個(gè)列的值，不包含sex、email，所以上面過(guò)程如下：
走name索引檢索javacode3500000對(duì)應(yīng)的記錄，取出id為3500000
在主鍵索引中檢索出id=3500000的記錄，獲取所有字段的值
索引覆蓋
查詢(xún)中采用的索引樹(shù)中包含了查詢(xún)所需要的所有字段的值，不需要再去聚集索引檢索數(shù)據(jù)，這種叫索引覆蓋。
我們來(lái)看一個(gè)查詢(xún)：
select id,name from test1 where name='javacode3500000';
name對(duì)應(yīng)idx1索引，id為主鍵，所以idx1索引樹(shù)葉子節(jié)點(diǎn)中包含了name、id的值，這個(gè)查詢(xún)只用走idx1這一個(gè)索引就可以了，如果select后面使用*，還需要一次回表獲取sex、email的值。
所以寫(xiě)sql的時(shí)候，盡量避免使用*，*可能會(huì)多一次回表操作，需要看一下是否可以使用索引覆蓋來(lái)實(shí)現(xiàn)，效率更高一些。
索引下推
簡(jiǎn)稱(chēng)ICP，Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一種在存儲(chǔ)引擎層使用索引過(guò)濾數(shù)據(jù)的一種優(yōu)化方式，ICP可以減少存儲(chǔ)引擎訪(fǎng)問(wèn)基表的次數(shù)以及MySQL服務(wù)器訪(fǎng)問(wèn)存儲(chǔ)引擎的次數(shù)。
舉個(gè)例子來(lái)說(shuō)一下：
我們需要查詢(xún)name以javacode35開(kāi)頭的，性別為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)
過(guò)程：
走name索引檢索出以javacode35的第一條記錄，得到記錄的id
利用id去主鍵索引中查詢(xún)出這條記錄R1
判斷R1中的sex是否為1，然后重復(fù)上面的操作，直到找到所有記錄為止。
上面的過(guò)程中需要走name索引以及需要回表操作。
如果采用ICP的方式，我們可以這么做，創(chuàng)建一個(gè)(name,sex)的組合索引，查詢(xún)過(guò)程如下：
走(name,sex)索引檢索出以javacode35的第一條記錄，可以得到(name,sex,id)，記做R1
判斷R1.sex是否為1，然后重復(fù)上面的操作，知道找到所有記錄為止
這個(gè)過(guò)程中不需要回表操作了，通過(guò)索引的數(shù)據(jù)就可以完成整個(gè)條件的過(guò)濾，速度比上面的更快一些。
數(shù)字使字符串類(lèi)索引失效
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，我們插入了一條記錄。
第二條查詢(xún)很快，第三條用name和1比較，name上有索引，name是字符串類(lèi)型，字符串和數(shù)字比較的時(shí)候，會(huì)將字符串強(qiáng)制轉(zhuǎn)換為數(shù)字，然后進(jìn)行比較，所以第二個(gè)查詢(xú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類(lèi)型的，可以看到，上面兩個(gè)查詢(xún)都非常快，都可以正常利用索引快速檢索，所以如果字段是數(shù)組類(lèi)型的，查詢(xún)的值是字符串還是數(shù)組都會(huì)走索引。
函數(shù)使索引無(wú)效
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上有索引，上面查詢(xún)，第一個(gè)走索引，第二個(gè)不走索引，第二個(gè)使用了函數(shù)之后，name所在的索引樹(shù)是無(wú)法快速定位需要查找的數(shù)據(jù)所在的頁(yè)的，只能將所有頁(yè)的記錄加載到內(nèi)存中，然后對(duì)每條數(shù)據(jù)使用函數(shù)進(jìn)行計(jì)算之后再進(jìn)行條件判斷，此時(shí)索引無(wú)效了，變成了全表數(shù)據(jù)掃描。
結(jié)論：索引字段使用函數(shù)查詢(xún)使索引無(wú)效。
運(yùn)算符使索引無(wú)效
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上有主鍵索引，上面查詢(xún)，第一個(gè)走索引，第二個(gè)不走索引，第二個(gè)使用運(yùn)算符，id所在的索引樹(shù)是無(wú)法快速定位需要查找的數(shù)據(jù)所在的頁(yè)的，只能將所有頁(yè)的記錄加載到內(nèi)存中，然后對(duì)每條數(shù)據(jù)的id進(jìn)行計(jì)算之后再判斷是否等于1，此時(shí)索引無(wú)效了，變成了全表數(shù)據(jù)掃描。
結(jié)論：索引字段使用了函數(shù)將使索引無(wú)效。
使用索引優(yōu)化排序
我們有個(gè)訂單表t_order(id,user_id,addtime,price)，經(jīng)常會(huì)查詢(xún)某個(gè)用戶(hù)的訂單，并且按照addtime升序排序，應(yīng)該怎么創(chuàng)建索引呢？我們來(lái)分析一下。
在user_id上創(chuàng)建索引，我們分析一下這種情況，數(shù)據(jù)檢索的過(guò)程：
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)
走user_id索引，找到記錄的的id
通過(guò)id在主鍵索引中回表檢索出整條數(shù)據(jù)
重復(fù)上面的操作，獲取所有目標(biāo)記錄
在內(nèi)存中對(duì)目標(biāo)記錄按照addtime進(jìn)行排序
我們要知道當(dāng)數(shù)據(jù)量非常大的時(shí)候，排序還是比較慢的，可能會(huì)用到磁盤(pán)中的文件，有沒(méi)有一種方式，查詢(xún)出來(lái)的數(shù)據(jù)剛好是排好序的。
我們?cè)倩仡櫼幌耺ysql中b+樹(shù)數(shù)據(jù)的結(jié)構(gòu)，記錄是按照索引的值排序組成的鏈表，如果將user_id和addtime放在一起組成聯(lián)合索引(user_id,addtime)，這樣通過(guò)user_id檢索出來(lái)的數(shù)據(jù)自然就是按照addtime排好序的，這樣直接少了一步排序操作，效率更好，如果需addtime降序，只需要將結(jié)果翻轉(zhuǎn)一下就可以了。
總結(jié)一下使用索引的一些建議
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)
在區(qū)分度高的字段上面建立索引可以有效的使用索引，區(qū)分度太低，無(wú)法有效的利用索引，可能需要掃描所有數(shù)據(jù)頁(yè)，此時(shí)和不使用索引差不多
聯(lián)合索引注意最左匹配原則：必須按照從左到右的順序匹配，mysql會(huì)一直向右匹配直到遇到范圍查詢(xún)(>、<、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. 查詢(xún)記錄的時(shí)候，少使用*，盡量去利用索引覆蓋，可以減少回表操作，提升效率
4. 有些查詢(xún)可以采用聯(lián)合索引，進(jìn)而使用到索引下推（IPC），也可以減少回表操作，提升效率
5. 禁止對(duì)索引字段使用函數(shù)、運(yùn)算符操作，會(huì)使索引失效
6. 字符串字段和數(shù)字比較的時(shí)候會(huì)使索引無(wú)效
7. 模糊查詢(xún)'%值%'會(huì)使索引無(wú)效，變?yōu)槿頀呙瑁?#39;值%'這種可以有效利用索引
8. 排序中盡量使用到索引字段，這樣可以減少排序，提升查詢(xún)效率
關(guān)于MySQL中如何使用索引就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
