目錄

一、索引概述

二、索引結(jié)構(gòu)

三、索引分類

四、索引語法

?五、SQL性能分析

1.? 查看執(zhí)行頻次

2.? 慢查詢?nèi)罩?/p>

3.? show profiles指令

?4.? explain執(zhí)行計劃

六、索引使用規(guī)則

1.? 驗證索引效率

2.? 最左前綴法則

?3.? 范圍查詢

4.? 索引失效情況

5.? SQL提示

?6.? 覆蓋索引

7.? 前綴索引

?8.? 單列索引與聯(lián)合索引的選擇

?七、索引設計原則

一、索引概述

索引 ( index）是幫助MysqL?高效獲取數(shù)據(jù)?的?數(shù)據(jù)結(jié)構(gòu)?(有序)。在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。

• 演示：

select * from user where age = 45;

?注意：上述二叉樹索引結(jié)構(gòu)的只是一個示意圖，并不是真實的索引結(jié)構(gòu)。

• ?優(yōu)缺點：

二、索引結(jié)構(gòu)

MySQL的索引是在存儲引擎層實現(xiàn)的，不同的存儲引擎有不同的結(jié)構(gòu)，主要包含以下幾種：

• B+Tree索引

MySQL索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典的B+Tree進行了優(yōu)化。在原B+Tree的基礎上，增加一個指向相鄰葉子節(jié)點的鏈表指針，就形成了帶有順序指針的B+Tree，提高區(qū)間訪問的性能。

• ?Hash索引

哈希索引就是采用一定的hash算法，將鍵值換算成新的hash值，映射到對應的槽位上，然后存儲在hash表中。
如果兩個(或多個)鍵值，映射到一個相同的槽位上，他們就產(chǎn)生了hash沖突（也稱為hash碰撞)，可以通過鏈表來解決。

① Hash索引特點：

1. Hash索引只能用于對等比較(=，in)，不支持范圍查詢(between，>，<，....)
2. 無法利用索引完成排序操作
3. 查詢效率高，通常只需要一次檢索就可以了，效率通常要高于B+tree索引

② 存儲引擎支持：在MsaL中，支持hash索引的是Memory引擎，而innoD8中具有自適應hash功能，hash索引是存儲引擎根據(jù)B+Tree索引在指定條件下自動構(gòu)建的。

思考：為什么InnoDB存儲引擎選擇使用B+Tree索引結(jié)構(gòu)？（面試題）

• 相對于二叉樹，層級更少，搜索效率越高。
• 對于B-tree，無論是葉子節(jié)點還是非葉子節(jié)點，都會保存數(shù)據(jù)，這樣導致一頁中存儲的鍵值減少，指針跟著減少，要同樣保存大量數(shù)據(jù)，只能增加樹的高度，導致性能降低。
• 相對Hash索引，B+Tree支持范圍匹配以及排序操作。

三、索引分類

在InnoDB存儲引擎中，根據(jù)索引的存儲形式，又可以分為以下兩種：

聚集索引選取規(guī)則：

• 如果存在主鍵，主鍵索引就是聚集索引。
• 如果不存在主鍵，將使用第一個唯一(UNIQUE）索引作為聚集索引。
• 如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引。

回表查詢：先根據(jù)二級索引拿到主鍵值，再根據(jù)主鍵值走聚集索引拿到這一行的數(shù)據(jù)。

思考：以下SQL語句中，哪個執(zhí)行效率高？為什么？

?解答：第一個執(zhí)行效率高。根據(jù) id 直接找聚集(主鍵)索引構(gòu)造的B+Tree，直接找到行數(shù)據(jù)返回；根據(jù) name字段 需要先到二級索引的B+Tree上查找name對應的 primary key 的值，然后再回表查詢?nèi)ゾ奂饕腂+Tree上查找對應的行數(shù)據(jù)。

四、索引語法

• 創(chuàng)建索引：CREATE [?UNIQUE|FULLTEXT?] INDEX index_name ON table_name ( index_col_name, ... );
• 查看索引：SHOW INDEX FROM table_name;
• 刪除索引：DROP INDEX index_name ON table_name;

示例練習：根據(jù)下列的需求，完成索引的創(chuàng)建

1. name 字段為姓名字段，該字段的值可能會重復，為該字段創(chuàng)建索引。
2. phone 手機號字段的值，是非空，且唯一的，為該字段創(chuàng)建唯一索引。
3. 為 profession、age、status 創(chuàng)建聯(lián)合索引。
4. 為 email 建立合適的索引來提升查詢效率。

show index from tb_user;

-- 1. name 字段為姓名字段，該字段的值可能會重復，為該字段創(chuàng)建索引。
create index idx_user_name on tb_user (name);

-- 2. phone 手機號字段的值，是非空，且唯一的，為該字段創(chuàng)建唯一索引。
create unique index idx_user_phone on tb_user(phone);

-- 3. 為 profession、age、status 創(chuàng)建聯(lián)合索引。
create index idx_user_pro_age_stu on tb_user(profession,age,status);

-- 4. 為 email 建立合適的索引來提升查詢效率。
create index idx_user_email on tb_user(email);

?五、SQL性能分析

1.? 查看執(zhí)行頻次

MySQL客戶端連接成功后，通過show [ session|global?] status 命令可以提供服務器狀態(tài)信息。通過如下指令，可以查看當前數(shù)據(jù)庫的INSERT、UPDATE、DELETE、SELECT的訪問頻次：??SHOW GLOBAL STATUS LIKE? ' Com_ _ _ _ _ '；（一個下劃線一個字符）

?通過這種方式查看SQL執(zhí)行頻率，為SQL優(yōu)化提供支撐。

2.? 慢查詢?nèi)罩?/h4>

• 查看慢查詢?nèi)罩鹃_啟情況

慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過指定參數(shù)（long_query_time,單位：秒，默認100秒）的所有SQL語句的日志。MySQL的慢查詢?nèi)罩灸J沒有開啟，需要在MySQL的配置文件（/etc/my.cnf）中配置信息：

配置完畢之后，通過以下指令重新啟動MySQL服務器進行測試，查看慢日志文件中記錄的信息 /var/lib/mysqL/localhost-slow.log。

3.? show profiles指令

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

• 默認profiling是關(guān)閉的，可以通過set語句在 session / global 級別開啟profiling：SET profiling= 1；

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

?4.? explain執(zhí)行計劃

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

?EXPLAIN執(zhí)行計劃各字段含義：

• id：select查詢的序列號，表示查詢中執(zhí)行select子句或者是操作表的順序（id相同，執(zhí)行順序從上到下；id不同，值越大，越先執(zhí)行）。

>? 多對多的多表關(guān)聯(lián)：id相同，執(zhí)行順序從上到下

>? 子查詢（ 查詢選修了"MySQL"課程的學生 ）：id不同，值越大，越先執(zhí)行

• select_type：表示SELECT的類型，常見的取值有SIMPLE（簡單表，即不使用表連接或者子查詢)、PRIMARY（主查詢，即外層的查詢)、UNION (UNION中的第二個或者后面的查詢語句)、SUBQUERY (SELECT/WHERE之后包含了子查詢）等。
• type：表示連接類型，性能由好到差的連接類型為NULL、system、const、eq_ref、ref、range、index、all 。
• possible_key：顯示可能應用在這張表上的索引，一個或多個。
• key：實際使用的索引，如果為NULL，則沒有使用索引。
• Key_len：表示索引中使用的字節(jié)數(shù)，該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下，長度越短越好。
• rows：MySQL認為必須要執(zhí)行查詢的行數(shù)，在innodb引擎的表中，是一個估計值，可能并不總是準確的。
• filtered：表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比，filtered的值越大越好。

六、索引使用規(guī)則

1.? 驗證索引效率

• 在未創(chuàng)立索引之前，執(zhí)行如下SQL語句，查看SQL的耗時：SELECT *FROM tb_sku WHERE sn = '100000003145001' ;

我們發(fā)現(xiàn)執(zhí)行一條數(shù)據(jù)的查詢用時20.78秒，效率極低，原因在于：因為表中 id 為主鍵，默認主鍵索引，而 sn字段沒有索引，所以效率低。

• ?針對字段創(chuàng)建索引：create index idx_sku_sn on tb_sku(sn) ;（構(gòu)建B+Tree索引結(jié)構(gòu)）

• 然后再次執(zhí)行相同的SQL語句，再次查看SQL的耗時：SELECT *FROM tb_sku WHERE sn = '100000003145001' ;

?PS：以上證明了索引對于查詢效率的提升。

2.? 最左前綴法則

• 如果索引了多列（聯(lián)合索引)，要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列。
• 如果跳躍某一列，索引將部分失效(后面的字段索引失效)。
• 與存放位置無關(guān)，只要存在即可。

示例：聯(lián)合索引idx_user_pro_age_sta

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

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

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

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

?3.? 范圍查詢

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

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

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

4.? 索引失效情況

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

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

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

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

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

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

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

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

explain select * from tb_user where id = 10 or age = 23;

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

5.? SQL提示

通過小例子了解SQL提示：

已知profession查詢會用到復合索引，那么如果我們再創(chuàng)建單列索引。

create index idx_user _pro on tb_user (profession);

那么當我們再次查詢時，那么將會選擇用復合索引還是單列索引呢？

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

• use index：建議使用某個索引

• ignore index：忽略使用某個索引

• force index：強制使用某個索引

?6.? 覆蓋索引

之前我們在基礎篇中提到，盡量不要使用select * ，一方面不直觀，可讀性差，另一方面效率低下。所以盡量使用覆蓋索引（查詢使用了索引，并且需要返回的列，在該索引中已經(jīng)全部能夠找到）?。

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

解釋：id,pofession,age之間存在聯(lián)合索引，屬于二級索引，可以拿到我們想要找到的數(shù)據(jù)直接返回，不需要再查找聚集索引。

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

?解釋：id,profession,age,status通過二級索引都可以查詢到，但是name字段不可以，需要通過id再到聚集索引中進行查找name字段即回表查詢。

知識小貼紙：

• using index condition ;查找使用了索引，但是需要回表查詢數(shù)據(jù)。
• using where; using index：查找使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需要回表查詢數(shù)據(jù)。

7.? 前綴索引

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

• 語法：create index idx xooxx on table_name(column(n)) ;
• 前綴長度：可以根據(jù)索引的選擇性來決定，而選擇性是指不重復的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值，索引選擇性越高則查詢效率越高，唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。
• 公式：

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

-- 創(chuàng)建前綴索引 長度為5
create index idx_email_5 on tb_user (email(5));

explain select * from tb_user where email = 'daqiao666@sina.com' ;

?8.? 單列索引與聯(lián)合索引的選擇

• 單列索引：即一個索引只包含單個列。
• 聯(lián)合索引：即一個索引包含了多個列。
• 在業(yè)務場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯(lián)合索引，而非但列索引。

單列索引情況：

explain select id, phone, name from tb_user 
where phone = '123456789' and name = '張三';

多條件聯(lián)合查詢時，MySQL優(yōu)化器會評估哪個字段的索引效率更高，會選擇該索引完成本次查詢。

而我們想要使用我們創(chuàng)建聯(lián)合索引，不使用單列索引：

create unqiue index idx_user_phone_name on tb_user (phone, name) ;
explain select id,phone, name from tb_user use index(idx_user_phone_name)
where phone = '123456789' and name = '張三';

文章來源地址http://www.zghlxwxcb.cn/news/detail-821230.html

?七、索引設計原則

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

到了這里，關(guān)于數(shù)據(jù)結(jié)構(gòu)MySQL —— 索引的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！