5.2 慢查詢?nèi)罩?/h3>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過指定參數(shù)（long_query_time，單位：秒，默認10秒）的所有SQL語句的日志。

MySQL的慢查詢?nèi)罩灸J沒有開啟，我們可以查看一下系統(tǒng)變量 slow_query_log

 show variables like 'slow_query_log';

如果要開啟慢查詢?nèi)罩?，需要在MySQL的配置文件（如果是linux則是/etc/my.cnf，如果是windows在mysql安裝目錄下的my.ini）中配置如下信息：注意配置一定要加載配置文件最后面，不然可能會出現(xiàn)問題！

這里我用的是mysql5.5版本，然后是在windows下配置的以下參數(shù)，并沒有出現(xiàn)問題，如果您出現(xiàn)了問題，那么可能mysql版本之間變量名稱有變化！

ft_min_word_len=1
#慢查詢?nèi)罩敬娣怕窂降刂?
log-slow-queries = D:\slow.log    
#慢查詢記錄的時間
long_query_time = 2

測試： 配置完畢之后，重新啟動MySQL服務(wù)器進行測試，查看慢日志文件中記錄的信息。

mysql快速生成千萬數(shù)據(jù)：http://www.dbjr.com.cn/article/257848.htm

（1）執(zhí)行如下SQL語句 ：

 -- 這條SQL執(zhí)行效率比較高, 執(zhí)行耗時 0.00sec 
select * from tb_user;
-- 由于t_user表中, 預(yù)先存入了700w的記錄, count一次,耗時6s
select count(*) from t_user; 

（2）檢查慢查詢?nèi)罩?/strong>

最終我們發(fā)現(xiàn)，在慢查詢?nèi)罩局?，只會記錄?zhí)行時間超多我們預(yù)設(shè)時間（2s）的SQL，執(zhí)行較快的SQL是不會記錄的。

那這樣，通過慢查詢?nèi)罩?，就可以定位出?zhí)行效率比較低的SQL，從而有針對性的進行優(yōu)化。

5.3 profile詳情

show profiles 能夠在做SQL優(yōu)化時幫助我們了解時間都耗費到哪里去了。通過have_profiling參數(shù)，能夠看到當前MySQL是否支持profile操作：SELECT @@have_profiling ;

profile查詢出來的數(shù)據(jù)每個客戶端是相互隔離的。

查看prifile是否開啟了：select @@profiling;

可以看到，當前MySQL是支持 profile操作的，但是開關(guān)是關(guān)閉的?？梢酝ㄟ^set語句在session/global級別開啟profiling：

SET profiling = 1;

開關(guān)已經(jīng)打開了，接下來，我們所執(zhí)行的SQL語句，都會被MySQL記錄，并記錄執(zhí)行時間消耗到哪兒去了。 我們直接執(zhí)行如下的SQL語句：

select * from tb_user; 
select * from tb_user where id = 1; 
select * from tb_user where name = '白起'; 
select count(*) from t_user;

執(zhí)行一系列的業(yè)務(wù)SQL的操作，然后通過如下指令查看指令的執(zhí)行耗時：

• 查看每一條SQL的耗時基本情況：show profiles;
• 查看指定query_id的SQL語句各個階段的耗時情況：show profile for query query_id;
• 查看指定query_id的SQL語句CPU的使用情況：show profile cpu for query query_id;

查看每一條SQL的耗時情況:

查看指定SQL各個階段的耗時情況 :

5.4 explain

EXPLAIN 或者 DESC命令獲取 MySQL 如何執(zhí)行 SELECT 語句的信息，包括在 SELECT 語句執(zhí)行過程中表如何連接和連接的順序。

語法:

-- 直接在select語句之前加上關(guān)鍵字 
explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 條件 ;

Explain 執(zhí)行計劃中各個字段的含義:

6. 索引使用

6.1 驗證索引效率

在講解索引的使用原則之前，先通過一個簡單的案例，來驗證一下索引，看看是否能夠通過索引來提升數(shù)據(jù)查詢性能。在演示的時候，我們還是使用之前準備的一張表 t_user, 在這張表中準備了700w的記錄。

這張表中id為主鍵，有主鍵索引，而其他字段是沒有建立索引的。 我們先來查詢其中的一條記錄，看看里面的字段情況，執(zhí)行如下SQL：

可以看到即使有1000w的數(shù)據(jù),根據(jù)id進行數(shù)據(jù)查詢,性能依然很快，因為主鍵id是有索引的。 那么接下來，我們再來根據(jù) c_name字段進行查詢，執(zhí)行如下SQL：

我們可以看到根據(jù)c_name字段進行查詢，查詢返回了一條數(shù)據(jù)，結(jié)果耗時 3.52 sec，就是因為c_name沒有索引，而造成查詢效率很低。

那么我們可以針對于c_name字段，建立一個索引，建立了索引之后，我們再次根據(jù)c_name進行查詢，再來看一下查詢耗時情況。
創(chuàng)建索引： create index idx_user_cname on t_user(c_name); 然后再次執(zhí)行相同的SQL語句，再次查看SQL的耗時。

我們明顯會看到，c_name字段建立了索引之后，查詢性能大大提升。建立索引前后，查詢耗時都不是一個數(shù)量級的。

6.2 最左前綴法則

如果索引了多列（聯(lián)合索引），要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列。如果跳躍某一列，索引將會部分失效(后面的字段索引失效)。

以 tb_user 表為例，我們先來查看一下之前 tb_user 表所創(chuàng)建的索引。

在 tb_user 表中，有一個聯(lián)合索引，這個聯(lián)合索引涉及到三個字段，順序分別為：profession，age，status。

對于最左前綴法則指的是，查詢時，最左變的列，也就是profession必須存在，否則索引全部失效。而且中間不能跳過某一列，否則該列后面的字段索引將失效。

（1）第一步我們先演示索引成功的案例，看一下具體的執(zhí)行計劃：

explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

explain select * from tb_user where profession = '軟件工程' and age = 31;

explain select * from tb_user where profession = '軟件工程';

以上的這三組測試中，我們發(fā)現(xiàn)只要聯(lián)合索引最左邊的字段 profession存在，索引就會生效，只不過索引的長度不同。 而且由以上三組測試，我們也可以推測出profession字段索引長度為36、age字段索引長度為2、status字段索引長度為4。我們下面主要根據(jù)這個長度來判斷組合索引當中哪個索引沒有生效！

注意：如果看不懂explain執(zhí)行計劃的，一定要看explamin介紹的參數(shù)解釋！不同的數(shù)據(jù)庫編碼以及數(shù)據(jù)庫版本可能key_len長度也會不一樣，這里我用的mysql是5.5版本，表用的是utf-8編碼

（2）下面我們進行演示最左邊的profession列沒用到的時候，索引失效的情況：

explain select * from tb_user where age = 31 and status = '0';

explain select * from tb_user where status = '0';

而通過上面的這兩組測試，我們也可以看到索引并未生效，原因是因為不滿足最左前綴法則，聯(lián)合索引最左邊的列profession不存在。

（3）下面我們進行演示跳過中間的列，索引失效的情況：

explain select * from tb_user where profession = '軟件工程' and status = '0';
上述的SQL查詢時，存在profession字段，最左邊的列是存在的，索引滿足最左前綴法則的基本條件。但是查詢時，跳過了age這個列，所以后面的列索引是不會使用的，也就是索引部分生效，所以索引的長度就是36。

思考題：當執(zhí)行SQL語句:
explain select * from tb_user where age = 31 and status = '0' and profession = '軟件工程'; 時，是否滿足最左前綴法則，走不走上述的聯(lián)合索引，索引長度？

可以看到，是完全滿足最左前綴法則的，索引長度42，聯(lián)合索引是生效的。

注意 ： 最左前綴法則中指的最左邊的列，是指在查詢時，聯(lián)合索引的最左邊的字段(即是第一個字段)必須存在，與我們編寫SQL時，條件編寫的先后順序無關(guān)。

6.3 索引失效情況

6.3.1 ><范圍查詢

聯(lián)合索引中，出現(xiàn)范圍查詢(>,<)，范圍查詢右側(cè)的列索引失效。

explain select * from tb_user where profession = '軟件工程' and age > 30 and status = '0';
當范圍查詢使用> 或 < 時，走聯(lián)合索引了，但是索引的長度為38，就說明范圍查詢右邊的status字段是沒有走索引的。

explain select * from tb_user where profession = '軟件工程' and age >= 30 and status = '0';

當范圍查詢使用>= 或 <= 時，走聯(lián)合索引了，但是索引的長度為42，就說明所有的字段都是走索引
的。

所以，在業(yè)務(wù)允許的情況下，盡可能的使用類似于 >= 或 <= 這類的范圍查詢，而避免使用 > 或 < 。

6.3.2 索引列運算

不要在索引列上進行運算操作， 索引將失效。

在tb_user表中，除了前面介紹的聯(lián)合索引之外，還有一個索引，是phone字段的單列索引。

當根據(jù)phone字段進行等值匹配查詢時, 索引生效。

explain select * from tb_user where phone = '17799990015';

當根據(jù)phone字段進行函數(shù)運算操作之后，索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

6.3.3 字符串不加引號

字符串類型字段使用時，不加引號，索引將失效。

接下來，我們通過兩組示例，來看看對于字符串類型的字段，加單引號與不加單引號的區(qū)別.

第一組：

explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

explain select * from tb_user where profession = '軟件工程' and age = 31 and status = 0;

第二組：

explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;

6.3.4 模糊查詢

如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

接下來，我們來看一下這三條SQL語句的執(zhí)行效果，查看一下其執(zhí)行計劃：

由于下面查詢語句中，都是根據(jù)profession字段查詢，符合最左前綴法則，聯(lián)合索引是可以生效的，我們主要看一下，模糊查詢時，%加在關(guān)鍵字之前，和加在關(guān)鍵字之后的影響。

explain select * from tb_user where profession like ‘軟件%';
explain select * from tb_user where profession like ‘%工程';
explain select * from tb_user where profession like ‘%工%';

經(jīng)過上述的測試，我們發(fā)現(xiàn)，在like模糊查詢中，在關(guān)鍵字后面加%，索引可以生效。而如果在關(guān)鍵字前面加了%，索引將會失效。

6.3.5 or連接條件

用or分割開的條件， 如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會被用到。

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = ‘17799990017' or age = 23;

由于age沒有索引，所以即使id、phone有索引，索引也會失效。所以需要針對于age也要建立索引。

然后，我們可以對age字段建立索引：create index idx_user_age on tb_user(age);

建立了索引之后，我們再次執(zhí)行上述的SQL語句，看看前后執(zhí)行計劃的變化。

這里我發(fā)現(xiàn)一個問題，我用的mysql5.5版本or不管兩邊是否都有索引，直接都不會走索引，但是又用了mysql 8測試了一下,.當age沒有索引的時候不走索引，當兩邊都有索引的時候確實會走索引。

6.3.6 數(shù)據(jù)分布影響

如果MySQL評估使用索引比全表更慢，則不使用索引。

explain select * from tb_user where phone >= '17799990005';
explain select * from tb_user where phone >= '17799990015';

mysql5.5版本執(zhí)行如下：

mysql8.0版本執(zhí)行如下：

經(jīng)過測試我們發(fā)現(xiàn)，在mysql8版本當中，相同的SQL語句，只是傳入的字段值不同，最終的執(zhí)行計劃完全不一樣，這是為什么呢？

就是因為MySQL 8 版本 在查詢時，會評估使用索引的效率與走全表掃描的效率，如果走全表掃描更快，則放棄索引，走全表掃描。 因為索引是用來索引少量數(shù)據(jù)的，如果通過索引查詢返回大批量的數(shù)據(jù)，則還不如走全表掃描來的快，此時索引就會失效。

接下來，我們再來看看 is null 與 is not null 操作是否走索引。

explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;

接下來，我們做一個操作將profession字段值全部更新為null：update tb_user set profession = null;

然后，再次執(zhí)行上述的兩條SQL，查看SQL語句的執(zhí)行計劃。

最終我們看到，一模一樣的SQL語句，先后執(zhí)行了兩次，結(jié)果查詢計劃是不一樣的，為什么會出現(xiàn)這種現(xiàn)象，這是和數(shù)據(jù)庫的數(shù)據(jù)分布有關(guān)系。查詢時MySQL會評估，走索引快，還是全表掃描快，如果全表掃描更快，則放棄索引走全表掃描。 因此，is null 、is not null是否走索引，得具體情況具體分析，并不是固定的。

6.4 SQL提示

目前tb_user表的索引情況如下:

（1）執(zhí)行SQL : explain select * from tb_user where profession = '軟件工程';

查詢走了聯(lián)合索引。

（2）執(zhí)行SQL，創(chuàng)建profession的單列索引：create index idx_user_pro on tb_user(profession);
創(chuàng)建單列索引后，再次執(zhí)行A中的SQL語句，查看執(zhí)行計劃，看看到底走哪個索引。

測試結(jié)果，我們可以看到，possible_keys中 idx_user_pro_age_sta,idx_user_pro 這兩個索引都可能用到，最終MySQL選擇了idx_user_pro_age_sta索引。這是MySQL自動選擇的結(jié)果。

那么，我們能不能在查詢的時候，自己來指定使用哪個索引呢？ 答案是肯定的，此時就可以借助于MySQL的SQL提示來完成。 接下來，介紹一下SQL提示。

SQL提示，是優(yōu)化數(shù)據(jù)庫的一個重要手段，簡單來說，就是在SQL語句中加入一些人為的提示來達到優(yōu)化操作的目的。

use index ： 建議MySQL使用哪一個索引完成此次查詢（僅僅是建議，mysql內(nèi)部還會再次進行評估）。
explain select * from tb_user use index(idx_user_pro) where profession = '軟件工 程';
示例演示：

ignore index ： 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '軟件工 程';
示例演示：

force index ： 強制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '軟件工 程';
示例演示：

6.5 覆蓋索引

盡量使用覆蓋索引，減少select *。 那么什么是覆蓋索引呢？ 覆蓋索引是指 查詢使用了索引，并且需要返回的列，在該索引中已經(jīng)全部能夠找到 。說白了就是避免回表查詢

接下來，我們來看一組SQL的執(zhí)行計劃，看看執(zhí)行計劃的差別，然后再來具體做一個解析。

explain select id, profession from tb_user where profession = ‘軟件工程' and age = 31 and status = ‘0' ;
explain select id,profession,age, status from tb_user where profession = ‘軟件工程' and age = 31 and status = ‘0' ;
explain select id,profession,age, status, name from tb_user where profession = ‘軟 件工程' and age = 31 and status = ‘0' ;
explain select * from tb_user where profession = ‘軟件工程' and age = 31 and status = ‘0';

mysql5.5執(zhí)行結(jié)果：

mysql8.0執(zhí)行結(jié)果：

從上述的執(zhí)行計劃我們可以看到，這四條SQL語句的執(zhí)行計劃前面所有的指標都是一樣的，看不出來差異。但是此時，我們主要關(guān)注的是后面的Extra。

在5.5版本當中，前面兩條SQL的結(jié)果為 Using where; Using Index ; 而后面兩條SQL的結(jié)果為: Using where 。

在8.0版本當中，前面兩條SQL的結(jié)果為 Using Index ; 而后面兩條SQL的結(jié)果為: NULL 。

因為，在tb_user表中有一個聯(lián)合索引 idx_user_pro_age_sta，該索引關(guān)聯(lián)了三個字段profession、age、status，而這個索引也是一個二級索引，所以葉子節(jié)點下面掛的是這一行的主鍵id。 所以當我們查詢返回的數(shù)據(jù)在 id、profession、age、status 之中，則直接走二級索引直接返回數(shù)據(jù)了。 如果超出這個范圍，就需要拿到主鍵id，再去掃描聚集索引，再獲取額外的數(shù)據(jù)了，這個過程就是回表。 而我們?nèi)绻恢笔褂胹elect * 查詢返回所有字段值，很容易就會造成回表查詢（除非是根據(jù)主鍵查詢，此時只會掃描聚集索引）。

表結(jié)構(gòu)及索引示意圖:

id是主鍵，是一個聚集索引。 name字段建立了普通索引，是一個二級索引（輔助索引）。

執(zhí)行SQL : select * from tb_user where id = 2;

根據(jù)id查詢，直接走聚集索引查詢，一次索引掃描，直接返回數(shù)據(jù)，性能高。

執(zhí)行SQL：selet id,name from tb_user where name = 'Arm';

執(zhí)行SQL：selet id,name,gender from tb_user where name = 'Arm';

由于在name的二級索引中，不包含gender，所以，需要兩次索引掃描，也就是需要回表查詢，性能相對較差一點。

思考題： 一張表, 有四個字段(id, username, password, status), 由于數(shù)據(jù)量大, 需要對以下SQL語句進行優(yōu)化, 該如何進行才是最優(yōu)方案:

select id,username,password from tb_user where username ='zhangsan';

答案: 針對于 username, password建立聯(lián)合索引, sql為:
create index idx_user_name_pass on tb_user(username,password);
這樣可以避免上述的SQL語句，在查詢的過程中，出現(xiàn)回表查詢。

6.6 前綴索引

當字段類型為字符串（varchar，text，longtext等）時，有時候需要索引很長的字符串，這會讓索引變得很大，查詢時，浪費大量的磁盤IO， 影響查詢效率。此時可以只將字符串的一部分前綴，建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。

語法：create index idx_xxxx on table_name(column(n)) ;

示例: 為tb_user表的email字段，建立長度為5的前綴索引。

create index idx_email_5 on tb_user(email(5));

可以根據(jù)索引的選擇性來決定，而選擇性是指不重復(fù)的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值，索引選擇性越高則查詢效率越高， 唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

select count(distinct email) / count(*) from tb_user ; 
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

前綴索引的查詢流程

6.7 單列索引與聯(lián)合索引

• 單列索引：即一個索引只包含單個列。
• 聯(lián)合索引：即一個索引包含了多個列。

我們先來看看 tb_user 表中目前的索引情況：在查詢出來的索引中，既有單列索引，又有聯(lián)合索引。

接下來，我們來執(zhí)行一條SQL語句，看看其執(zhí)行計劃：

通過上述執(zhí)行計劃我們可以看出來，在and連接的兩個字段 phone、name上都是有單列索引的，但是最終mysql只會選擇一個索引，也就是說，只能走一個字段的索引，此時是會回表查詢的。

緊接著，我們再來創(chuàng)建一個phone和name字段的聯(lián)合索引來查詢一下執(zhí)行計劃。

此時，查詢時，就走了聯(lián)合索引，而在聯(lián)合索引中包含 phone、name的信息，在葉子節(jié)點下掛的是對應(yīng)的主鍵id，所以查詢是無需回表查詢的。

在業(yè)務(wù)場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯(lián)合索引，而非單列索引。

如果查詢使用的是聯(lián)合索引，具體的結(jié)構(gòu)示意圖如下：

7. 索引設(shè)計原則

1. 針對于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引。
2. 針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引。
3. 盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高。
4. 如果是字符串類型的字段，字段的長度較長，可以針對于字段的特點，建立前綴索引。
5. 盡量使用聯(lián)合索引，減少單列索引，查詢時，聯(lián)合索引很多時候可以覆蓋索引，節(jié)省存儲空間，避免回表，提高查詢效率。
6. 要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護索引結(jié)構(gòu)的代價也就越大，會影響增刪改的效率。create unique index idx_user_phone_name on tb_user(phone,name);
7. 如果索引列不能存儲NULL值，請在創(chuàng)建表時使用NOT NULL約束它。當優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢。

本篇要求掌握：

1. 什么是索引?
2. 索引是個什么樣的數(shù)據(jù)結(jié)構(gòu)呢?
3. 為什么使用索引？
4. Hash 索引和 B+ 樹索引有什么區(qū)別或者說優(yōu)劣呢?
5. 什么是聚簇索引（一個）
6. 說一說索引的底層實現(xiàn)？（一般就是指的B+Tree）
7. 索引有哪些優(yōu)缺點？
8. 聚簇索引和非聚簇索引的區(qū)別（非聚簇索引就是二級索引）
9. MySQL中有幾種索引類型，可以簡單說說嗎？（主鍵，唯一，常規(guī)（常規(guī)又分為了多列和單列，多列的一般稱之為組合索引），全文）
10. 覆蓋索引是什么？ （覆蓋索引指的就是查詢的列盡量是索引所覆蓋的列，這樣可以避免回表查）
11. 非聚簇索引一定會回表查詢嗎?（假如恰好select的列是條件當中用到的索引列，是不用回表的）
12. 聯(lián)合索引是什么?為什么需要注意聯(lián)合索引中的順序?（最左前綴法則）
13. 創(chuàng)建的索引有沒有被使用到?或者說怎么才可以知道這條語句運行很慢的原因?（使用explain）
14. 索引什么情況下會失效?
15. 為什么Mysql用B+樹做索引而不用B-樹或紅黑樹、二叉樹？
16. 索引在什么情況下遵循最左前綴的規(guī)則？（組合索引）

總結(jié)

到此這篇關(guān)于Mysql索引(index)的文章就介紹到這了,更多相關(guān)Mysql索引詳解內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

• Mysql?InnoDB中B+樹索引使用注意事項
• MySQL索引總結(jié)(Index?Type)
• mysql?in索引慢查詢優(yōu)化實現(xiàn)步驟解析
• MySQL使用explain命令查看與分析索引的使用情況
• Mysql的Explain使用方式及索引總結(jié)
• mysql如何修改index索引名稱
• 淺談MySQL中使用IN會走索引嗎