MySQL中如何利用索引，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

作為一家“創(chuàng)意+整合+營(yíng)銷(xiāo)”的成都網(wǎng)站建設(shè)機(jī)構(gòu)，我們?cè)跇I(yè)內(nèi)良好的客戶(hù)口碑。成都創(chuàng)新互聯(lián)提供從前期的網(wǎng)站品牌分析策劃、網(wǎng)站設(shè)計(jì)、網(wǎng)站設(shè)計(jì)制作、做網(wǎng)站、創(chuàng)意表現(xiàn)、網(wǎng)頁(yè)制作、系統(tǒng)開(kāi)發(fā)以及后續(xù)網(wǎng)站營(yíng)銷(xiāo)運(yùn)營(yíng)等一系列服務(wù)，幫助企業(yè)打造創(chuàng)新的互聯(lián)網(wǎng)品牌經(jīng)營(yíng)模式與有效的網(wǎng)絡(luò)營(yíng)銷(xiāo)方法,創(chuàng)造更大的價(jià)值。

一、前言

在MySQL中進(jìn)行SQL優(yōu)化的時(shí)候，經(jīng)常會(huì)在一些情況下，對(duì)MySQL能否利用索引有一些迷惑。

譬如：

1、MySQL 在遇到范圍查詢(xún)條件的時(shí)候就停止匹配了，那么到底是哪些范圍條件？

2、MySQL 在LIKE進(jìn)行模糊匹配的時(shí)候又是如何利用索引的呢？

3、MySQL 到底在怎么樣的情況下能夠利用索引進(jìn)行排序？

今天，我將會(huì)用一個(gè)模型，把這些問(wèn)題都一一解答，讓你對(duì)MySQL索引的使用不再畏懼。

二、知識(shí)補(bǔ)充

key_len

EXPLAIN執(zhí)行計(jì)劃中有一列 key_len 用于表示本次查詢(xún)中，所選擇的索引長(zhǎng)度有多少字節(jié)，通常我們可借此判斷聯(lián)合索引有多少列被選擇了。

在這里 key_len 大小的計(jì)算規(guī)則是：

• 一般地，key_len 等于索引列類(lèi)型字節(jié)長(zhǎng)度，例如int類(lèi)型為4 bytes，bigint為8 bytes；

• 如果是字符串類(lèi)型，還需要同時(shí)考慮字符集因素，例如：CHAR(30) UTF8則key_len至少是90 bytes；

• 若該列類(lèi)型定義時(shí)允許NULL，其key_len還需要再加 1 bytes；

• 若該列類(lèi)型為變長(zhǎng)類(lèi)型，例如 VARCHAR（TEXT\BLOB不允許整列創(chuàng)建索引，如果創(chuàng)建部分索引也被視為動(dòng)態(tài)列類(lèi)型），其key_len還需要再加 2 bytes;

三、哪些條件能用到索引

首先非常感謝登博，給了我一個(gè)很好的啟發(fā)，我通過(guò)他的文章，然后結(jié)合自己的理解，制作出了這幅圖

乍一看，是不是很暈，不急，我們慢慢來(lái)看

圖中一共分了三個(gè)部分：

1、Index Key ：MySQL是用來(lái)確定掃描的數(shù)據(jù)范圍，實(shí)際就是可以利用到的MySQL索引部分，體現(xiàn)在Key Length。

2、Index Filter：MySQL用來(lái)確定哪些數(shù)據(jù)是可以用索引去過(guò)濾，在啟用ICP后，可以用上索引的部分。

3、Table Filter：MySQL無(wú)法用索引過(guò)濾，回表取回行數(shù)據(jù)后，到server層進(jìn)行數(shù)據(jù)過(guò)濾。

我們細(xì)細(xì)展開(kāi)。

Index Key

Index Key是用來(lái)確定MySQL的一個(gè)掃描范圍，分為上邊界和下邊界。

MySQL利用=、>=、> 來(lái)確定下邊界（first key），利用最左原則，首先判斷***個(gè)索引鍵值在where條件中是否存在，如果存在，則判斷比較符號(hào)，如果為(=,>=)中的一種，加入下邊界的界定，然后繼續(xù)判斷下一個(gè)索引鍵，如果存在且是(>)，則將該鍵值加入到下邊界的界定，停止匹配下一個(gè)索引鍵；如果不存在，直接停止下邊界匹配。

exp: idx_c1_c2_c3(c1,c2,c3) where c1>=1 and c2>2 and c3=1 -->  first key (c1,c2) --> c1為 '>=' ，加入下邊界界定，繼續(xù)匹配下一個(gè) -->c2 為 '>'，加入下邊界界定，停止匹配

上邊界（last key）和下邊界（first key）類(lèi)似，首先判斷是否是否是(=,<=)中的一種，如果是，加入界定，繼續(xù)下一個(gè)索引鍵值匹配，如果是(<)，加入界定，停止匹配

exp:  idx_c1_c2_c3(c1,c2,c3)  where c1<=1 and c2=2 and c3<3  --> first key (c1,c2,c3)  --> c1為 '<='，加入上邊界界定，繼續(xù)匹配下一個(gè)  --> c2為 '='加入上邊界界定，繼續(xù)匹配下一個(gè)  --> c3 為 '<'，加入上邊界界定，停止匹配

注：這里簡(jiǎn)單的記憶是，如果比較符號(hào)中包含'='號(hào)，'>='也是包含'='，那么該索引鍵是可以被利用的，可以繼續(xù)匹配后面的索引鍵值；如果不存在'='，也就是'>','<'，這兩個(gè)，后面的索引鍵值就無(wú)法匹配了。同時(shí)，上下邊界是不可以混用的，哪個(gè)邊界能利用索引的的鍵值多，就是最終能夠利用索引鍵值的個(gè)數(shù)。

Index Filter

字面理解就是可以用索引去過(guò)濾。也就是字段在索引鍵值中，但是無(wú)法用去確定Index Key的部分。

exp:  idex_c1_c2_c3  where c1>=1 and c2<=2 and c3 =1  index key --> c1  index filter--> c2 c3

這里為什么index key 只是c1呢？因?yàn)閏2 是用來(lái)確定上邊界的，但是上邊界的c1沒(méi)有出現(xiàn)(<=,=)，而下邊界中，c1是>=,c2沒(méi)有出現(xiàn)，因此index key 只有c1字段。c2,c3 都出現(xiàn)在索引中，被當(dāng)做index filter.

Table Filter

無(wú)法利用索引完成過(guò)濾，就只能用table filter。此時(shí)引擎層會(huì)將行數(shù)據(jù)返回到server層，然后server層進(jìn)行table filter。

四、Between 和Like 的處理

那么如果查詢(xún)中存在between 和like，MySQL是如何進(jìn)行處理的呢？

Between

where c1 between  'a' and 'b' 等價(jià)于 where c1>='a' and c1 <='b'，所以進(jìn)行相應(yīng)的替換，然后帶入上層模型，確定上下邊界即可

Like

首先需要確認(rèn)的是%不能是最在最左側(cè)，where c1 like '%a' 這樣的查詢(xún)是無(wú)法利用索引的，因?yàn)樗饕钠ヅ湫枰献钭笄熬Y原則

where c1 like 'a%'  其實(shí)等價(jià)于 where c1>='a' and c1<'b' 大家可以仔細(xì)思考下。

五、索引的排序

在數(shù)據(jù)庫(kù)中，如果無(wú)法利用索引完成排序，隨著過(guò)濾數(shù)據(jù)的數(shù)據(jù)量的上升，排序的成本會(huì)越來(lái)越大，即使是采用了limit，但是數(shù)據(jù)庫(kù)是會(huì)選擇將結(jié)果集進(jìn)行全部排序，再取排序后的limit 記錄，而且MySQL 針對(duì)可以用索引完成排序的limit 有優(yōu)化，更能減少成本。

Make sure it uses index It is very important to have ORDER BY with LIMIT executed without scanning and sorting full result set, so it is important for it to use index &ndash; in this case index range scan will be started and query execution stopped as soon as soon as required amount of rows generated.

CREATE TABLE `t1` (      `id` int(11) NOT NULL AUTO_INCREMENT,      `c1` int(11) NOT NULL DEFAULT '0',      `c2` int(11) NOT NULL DEFAULT '0',      `c3` int(11) NOT NULL DEFAULT '0',      `c4` int(11) NOT NULL DEFAULT '0',      `c5` int(11) NOT NULL DEFAULT '0',      PRIMARY KEY (`id`),      KEY `idx_c1_c2_c3` (`c1`,`c2`,`c3`)    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4      select * from t1;    +----+----+----+----+----+----+    | id | c1 | c2 | c3 | c4 | c5 |    +----+----+----+----+----+----+    |  1 |  3 |  3 |  2 |  0 |  0 |    |  2 |  2 |  4 |  5 |  0 |  0 |    |  3 |  3 |  2 |  4 |  0 |  0 |    |  4 |  1 |  3 |  2 |  0 |  0 |    |  5 |  1 |  3 |  3 |  0 |  0 |    |  6 |  2 |  3 |  5 |  0 |  0 |    |  7 |  3 |  2 |  6 |  0 |  0 |    +----+----+----+----+----+----+    7 rows in set (0.00 sec)      select c1,c2,c3 from t1;    +----+----+----+    | c1 | c2 | c3 |    +----+----+----+    |  1 |  3 |  2 |    |  1 |  3 |  3 |    |  2 |  3 |  5 |    |  2 |  4 |  5 |    |  3 |  2 |  4 |    |  3 |  2 |  6 |    |  3 |  3 |  2 |    +----+----+----+    7 rows in set (0.00 sec)

存在一張表，c1,c2,c3上面有索引，select c1,c2,c3 from t1; 查詢(xún)走的是索引全掃描，因此呈現(xiàn)的數(shù)據(jù)相當(dāng)于在沒(méi)有索引的情況下select c1,c2,c3 from t1 order by c1,c2,c3; 的結(jié)果

因此，索引的有序性規(guī)則是怎么樣的呢？

c1=3 &mdash;> c2 有序，c3 無(wú)序

c1=3,c2=2 &mdash; > c3 有序

c1 in(1,2) &mdash;> c2 無(wú)序 ，c3 無(wú)序

有個(gè)小規(guī)律，idx_c1_c2_c3，那么如何確定某個(gè)字段是有序的呢？c1 在索引的最前面，肯定是有序的，c2在第二個(gè)位置，只有在c1 ***確定一個(gè)值的時(shí)候，c2才是有序的，如果c1有多個(gè)值，那么c2 將不一定有序，同理，c3也是類(lèi)似

關(guān)于MySQL中如何利用索引問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識(shí)。

分享名稱(chēng)：MySQL中如何利用索引
URL地址：http://aaarwkj.com/article2/gdesic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、自適應(yīng)網(wǎng)站、外貿(mào)建站、關(guān)鍵詞優(yōu)化、網(wǎng)站排名、網(wǎng)站內(nèi)鏈

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)