mysql索引

pine1203發表於2024-05-11
MySql索引

1. 概述

  索引 (index) 是幫助 MySQL 高效獲取資料的資料結構 (有序)。在資料之外，資料庫系統還

維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料， 這樣就可

以在這些資料結構上實現高階查詢演算法，這種資料結構就是索引。

2. 索引結構

  MySQL的索引是在儲存引擎層實現的，不同的儲存引擎有不同的索引結構，主要包含以下幾種：

 


  上述是MySQL中所支援的所有的索引結構，接下來，我們再來看看不同的儲存引擎對於索引結構的支援情況。

 

注意： 我們平常所說的索引，如果沒有特別指明，都是指B+樹結構組織的索引。


3. 索引分類

  在 MySQL 資料庫，將索引的具體型別主要分為以下幾類：主鍵索引、唯一索引、常規索引、全文索引。

 


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

 


聚集索引選取規則:

  • 如果存在主鍵，主鍵索引就是聚集索引。

  • 如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚集索引。

  • 如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引。

回表查詢： 這種先到二級索引中查詢資料，找到主鍵值，然後再到聚集索引中根據主鍵值，獲取資料的方式，就稱之為回表查詢。

思考題：

以下兩條SQL語句，那個執行效率高? 為什麼?

A. select * from user where id = 10 ;

B. select * from user where name = 'Arm' ;

備註: id為主鍵，name欄位建立的有索引；

解答：

A 語句的執行效能要高於B 語句。

因為A語句直接走聚集索引，直接返回資料。 而B語句需要先查詢name欄位的二級索引，然後再查詢聚集索引，也就是需要進行回表查詢。


4. 索引語法

  • 建立索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;
  • 檢視索引
SHOW INDEX FROM table_name ;
  • 刪除索引
DROP INDEX index_name ON table_name ;


案例:

  1. name 欄位為姓名欄位，該欄位的值可能會重複，為該欄位建立索引。
CREATE INDEX idx_user_name ON tb_user(name);
  1. phone手機號欄位的值，是非空，且唯一的，為該欄位建立唯一索引。
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
  1. 為 profession、age、status 建立聯合索引。
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
  1. 為 email 建立合適的索引來提升查詢效率。
CREATE INDEX idx_email ON tb_user(email);
  1. 完成上述的需求之後，我們再檢視tb_user表的所有的索引資料。
show index from tb_user;

5. SQL 效能分析

 5.1 SQL執行頻率

  MySQL 客戶端連線成功後，透過 show [session | global] status 命令可以提供伺服器狀態資訊。透過如下指令，可以檢視當前資料庫的INSERT、UPDATE、DELETE、SELECT的訪問頻次：

-- session 是檢視當前會話 ;

-- global 是查詢全域性資料 ;

SHOW GLOBAL STATUS LIKE 'Com_______';


            

  • Com_delete: 刪除次數

  • Com_insert: 插入次數

  • Com_select: 查詢次數

  • Com_update: 更新次數

透過上述指令，我們可以檢視到當前資料庫到底是以查詢為主，還是以增刪改為主，從而為資料庫最佳化提供參考依據。 如果是以增刪改為主，我們可以考慮不對其進行索引的最佳化。 如果是以查詢為主，那麼就要考慮對資料庫的索引進行最佳化了。


 5.2 慢查詢日誌

  慢查詢日誌記錄了所有執行時間超過指定引數（long_query_time，單位：秒，預設10秒）的所有SQL語句的日誌。

  MySQL的慢查詢日誌預設沒有開啟，我們可以檢視慢日誌是否開啟和日誌檔案位置

show variables like '%slow_query_log%';

  以及慢日誌閾值

show variables like '%long_query_time%';


            

  臨時開啟慢查詢日誌

# 臨時開啟開啟MySQL慢日誌查詢開關

set slow_query_log=1;

# 設定慢日誌的時間為2秒，SQL語句執行時間超過2秒，就會視為慢查詢，記錄慢查詢日誌

set long_query_time=2;


 5.3 profile詳情

  show profiles 能夠在做 SQL 最佳化時幫助我們瞭解時間都耗費到哪裡去了。透過

have_profiling 引數，能夠看到當前MySQL是否支援 profile 操作：

SELECT @@have_profiling ;

SELECT @@profiling ;


        

  可以看到，當前MySQL是支援 profile操作的，開關是開啟的的。如果沒開啟，可以透過set語句在 session/global 級別開啟profiling：

SET profiling = 1;

  執行 SQL 語句後，檢視檢視指令的執行耗時：

-- 檢視每一條SQL的耗時基本情況

show profiles;

-- 檢視指定query_id的SQL語句各個階段的耗時情況

show profile for query query_id;

-- 檢視指定query_id的SQL語句CPU的使用情況

show profile cpu for query query_id;


 5.4 explain

  EXPLAIN 或者 DESC 命令獲取 MySQL 如何執行 SELECT 語句的資訊，包括在 SELECT 語句執行過程中表如何連線和連線的順序。

-- 直接在select語句之前加上關鍵字 explain / desc

EXPLAIN SELECT 欄位列表 FROM 表名 WHERE 條件 ;


Explain 執行計劃中各個欄位的含義:

 


6. 索引使用

 6.1 最左字首法則

  如果索引了多列（聯合索引），要遵守最左字首法則。最左字首法則指的是查詢從索引的最左列開始，並且不跳過索引中的列。如果跳躍某一列，索引將會部分失效(後面的欄位索引失效)。

  以 t_user 表為例，我們先來檢視一下之前 t_user 表所建立的索引。

 

  在 t_user 表中，有一個聯合索引，這個聯合索引涉及到三個欄位，順序分別為：name, age, email 。

  對於最左字首法則指的是，查詢時，最左變的列，也就是 name須存在，否則索引全部失效。
  而且中間不能跳過某一列，否則該列後面的欄位索引將失效。


 6.2 範圍查詢

  聯合索引中，出現範圍查詢(>,<)，範圍查詢右側的列索引失效。在業務允許的情況下，儘可能的使用類似於 >= 或 <= 這類的範圍查詢，而避免使用 > 或 < 。


 6.3 索引失效情況

  • 索引列運算

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

  • 字串不加引號

  字串型別欄位使用時，不加引號，索引將失效。

  • 模糊查詢

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

  • or 連線條件

  用or分割開的條件， 如果 or 前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。當or連線的條件，左右兩側欄位都有索引時，索引才會生效。

  • 資料分佈影響

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


 6.4 SQL提示

  SQL提示，是最佳化資料庫的一個重要手段，簡單來說，就是在SQL語句中加入一些人為的提示來達到最佳化操作的目的。

  • use index ： 建議 MySQL 使用哪一個索引完成此次查詢（僅僅是建議，mysql 內部還會再次進行評估）。
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 *。 那麼什麼是覆蓋索引呢？ 覆蓋索引是指查詢使用了索引，並且需要返回的列，在該索引中已經全部能夠找到 。
 


  因為，在 t_user 表中有一個聯合索引 idx_user_name_age_email，該索引關聯了三個欄位 name、age、email，而這個索引也是一個二級索引，所以葉子節點下面掛的是這一行的主鍵id。 所以當我們查詢返回的資料在 id、name、age、email 之中，則直接走二級索引直接返回資料了。 如果超出這個範圍，就需要拿到主鍵id，再去掃描聚集索引，再獲取額外的資料了，這個過程就是回表。 而我們如果一直使用select * 查詢返回所有欄位值，很容易就會造成回表查詢（除非是根據主鍵查詢，此時只會掃描聚集索引）。

思考題：

  一張表, 有四個欄位(id, username, password, status), 由於資料量大, 需要對
以下SQL語句進行最佳化, 該如何進行才是最優方案:

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

答案: 針對於 username, password建立聯合索引, sql為:

create index idx_user_name_pass on tb_user(username,password);

這樣可以避免上述的SQL語句，在查詢的過程中，出現回表查詢


 6.6 字首索引

  當欄位型別為字串（varchar，text，longtext等）時，有時候需要索引很長的字串，這會讓索引變得很大，查詢時，浪費大量的磁碟 IO， 影響查詢效率。此時可以只將字串的一部分字首，建立索引，這樣可以大大節約索引空間，從而提高索引效率。

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


示例：

  為 t_user 表的 email 欄位，建立長度為5的字首索引。

create index idx_email_5 on tb_user(email(5));
  • 字首長度

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

select count(distinct email) / count(*) from t_user ;

select count(distinct substring(email,1,5)) / count(*) from t_user ;
  • 字首索引的查詢流程


 6.7 單列索引與聯合索引

  • 單列索引：即一個索引只包含單個列。

  • 聯合索引：即一個索引包含了多個列。

在業務場景中，如果存在多個查詢條件，考慮針對於查詢欄位建立索引時，建議建立聯合索引，
而非單列索引。

如果查詢使用的是聯合索引，具體的結構示意圖如下：
 


7. 索引設計原則

  • 針對於資料量較大，且查詢比較頻繁的表建立索引。

  • 針對於常作為查詢條件（where）、排序（order by）、分組（group by）操作的欄位建立索
    引。

  • 儘量選擇區分度高的列作為索引，儘量建立唯一索引，區分度越高，使用索引的效率越高。

  • 如果是字串型別的欄位，欄位的長度較長，可以針對於欄位的特點，建立字首索引。

  • 儘量使用聯合索引，減少單列索引，查詢時，聯合索引很多時候可以覆蓋索引，節省儲存空間，避免回表，提高查詢效率。

  • 要控制索引的數量，索引並不是多多益善，索引越多，維護索引結構的代價也就越大，會影響增刪改的效率。

  • 如果索引列不能儲存 NULL 值，請在建立表時使用 NOT NULL 約束它。當最佳化器知道每列是否包含 NULL 值時，它可以更好地確定哪個索引最有效地用於查詢。

相關文章