1、MySQL索引是什么？

在關系數據庫中，索引是一種單獨的、物理的對數據庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標志這些值的數據頁的邏輯指針清單。索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

索引提供指向存儲在表的指定列中的數據值的指針，然后根據您指定的排序順序對這些指針排序。數據庫使用索引以找到特定值，然后順指針找到包含該值的行。這樣可以使對應于表的SQL語句執行得更快，可快速訪問數據庫表中的特定信息。

索引是為了加速對表中數據行的檢索而創建的一種分散的存儲結構。索引是針對表而建立的，它是由數據頁面以外的索引頁面組成的，每個索引頁面中的行都會含有邏輯指針，以便加速檢索物理數據。

2、索引的優勢

1. 提高查詢速度：索引可以加速數據的檢索過程，通過索引，數據庫引擎可以快速定位到存儲在特定值或范圍內的數據，從而大幅減少了查詢所需的時間。
2. 減少磁盤IO操作：索引可以減少磁盤IO操作的次數，因為索引可以幫助數據庫引擎快速定位到存儲在特定位置的數據，而無需逐個掃描整個數據表。
3. 加速排序和分組操作：索引可以加速排序和分組操作，因為索引已經按特定的順序存儲了數據，數據庫引擎可以直接利用索引來完成排序和分組操作，而無需再對原始數據進行排序和分組。
4. 提高數據的完整性和唯一性：通過在索引上創建唯一索引或主鍵索引，可以保證數據的完整性和唯一性，避免數據重復和冗余。

3、索引的劣勢

1、索引需要占物理空間：創建索引就像建立圖書館的索引卡片系統，需要額外的空間和資源。在數據庫中，這意味著需要更多的存儲空間和時間來維護索引。

2、降低數據寫入（DML）性能：當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，降低了數據的維護速度。

3、增加維護：數據庫需要額外的開銷來維護索引的有序性和一致性。

4、優化器負擔：過多的索引會增加查詢優化器選擇執行路徑的難度和成本。

4、索引為什么會快？

索引的核心原理是：通過額外的數據結構（空間換時間）和精巧的設計，將隨機的全表掃描變為有序的、局部的快速查找。

1. 高效的數據結構：基于B+樹的優化設計

索引通常采用如B+樹（B+ Tree）這類高度優化的數據結構。B+樹是一種多路平衡搜索樹，其設計充分考慮了磁盤的讀寫特性。它保持樹的平衡，確保從根節點到任何葉子節點的查找路徑長度都完全相同且最短，這使得查找、插入和刪除操作的時間復雜度穩定在O(log n)，極其高效。

B+樹的所有數據都存儲在葉子節點，并形成有序雙向鏈表，而內部節點（非葉子節點）僅存儲鍵值和指向子節點的指針。這種設計使得單個節點可以存儲大量鍵值，從而極大地降低了樹的高度。更矮的樹意味著在查找任何一條記錄時，需要加載的磁盤塊（I/O次數）更少，這是速度快的根本原因之一。

2. 減少數據訪問量：從全表掃描到范圍定位

沒有索引時，數據庫必須執行全表掃描（Full Table Scan），即逐行讀取表中的每一塊數據來判斷是否滿足條件。對于數百萬甚至上億行記錄的大表，這無疑是災難性的，因為它需要加載整個表的數據，其中絕大部分都是無用的。

索引通過存儲排序的鍵值及其對應數據行的指針，像一本書的目錄一樣工作。數據庫引擎首先在索引結構中進行快速查找（在B+樹中只需幾次I/O），迅速定位到目標數據行的起始位置。它不再需要檢查每一行，而是直接“跳轉”到相關的數據區塊，極大地減少了需要訪問和檢查的數據量，從而實現了性能的飛躍。

3. 優化磁盤I/O：減少次數與數據量

數據庫性能的主要瓶頸往往是磁盤I/O（輸入/輸出），而索引正是優化I/O的利器。首先，索引文件本身比完整的數據文件小得多，因為它只包含被索引的列和指向數據的指針。更小的文件意味著從磁盤讀取它所需的I/O次數更少、速度更快。

其次，由于索引結構的有序性（特別是B+樹的葉子節點鏈表），它可以將原本需要隨機訪問磁盤的操作（因為數據行可能散落在磁盤各處）轉變為順序I/O。順序讀取磁盤的速度遠快于隨機讀取。此外，小的索引文件也更容易被完整地緩存到內存中，后續的查詢甚至可能完全無需訪問磁盤，直接在內存中完成，速度極快。

4. 提升緩存效率：增強數據訪問局部性

現代數據庫系統會使用大量內存作為緩存池（Buffer Pool）來存儲最近訪問過的數據和索引頁。索引顯著提升了緩存的空間局部性。當執行一次索引查找時，不僅目標索引頁會被加載到緩存，其相鄰的索引頁也可能被預讀進來。這意味著后續的范圍查詢或鄰近值查詢很可能直接在內存中命中，避免了物理磁盤讀取。

同樣，當通過索引定位到數據行時，由于同一數據頁上的多行記錄很可能被一次I/O加載到緩存中。如果這些相關記錄被后續查詢訪問，它們已經在內存里，從而極大地提升了整體系統的吞吐量和響應速度。

5. 預先排序以加速操作：避免昂貴排序

如果查詢包含ORDER BY（排序）、GROUP BY（分組）或DISTINCT（去重）等子句，而沒有索引，數據庫就必須先將所有符合條件的數據集加載到內存或臨時磁盤空間中進行排序，這是一個非常消耗CPU和內存的昂貴操作。

如果索引（特別是B+樹索引）的鍵順序正好與ORDER BY或GROUP BY的字段順序匹配，數據庫引擎就可以簡單地按索引的順序讀取數據。因為索引本身就是一個已經排好序的數據結構，數據庫可以直接返回有序的結果集，完全避免了執行費時費力的臨時排序操作，從而再次大幅提升查詢性能。

5、索引為什么使用B+樹？

B+樹作為MySQL InnoDB存儲引擎的默認索引結構，其設計充分考慮了磁盤I/O效率和數據訪問模式，在處理大規模數據時展現出顯著的性能優勢。

首先，B+樹采用了矮胖的多層平衡樹結構，非葉子節點僅存儲鍵值而不包含實際數據，使得單個節點能夠容納大量索引項。在InnoDB默認16KB的頁大小支持下，單個節點可存儲更多鍵值，顯著降低樹的高度。這種設計直接減少了查詢過程中需要的磁盤I/O次數，從而大幅提升點查詢和等值查詢的性能表現。

其次，B+樹將所有數據記錄集中存儲在葉子節點，并通過指針將葉子節點連接成有序雙向鏈表。這種結構使得范圍查詢異常高效，只需定位到起始節點即可沿鏈表順序掃描，避免了不必要的中間節點訪問。對于需要排序、分組或去重的查詢操作，這種有序鏈表結構也能提供最優的訪問路徑。

第三，由于所有數據訪問最終都會到達葉子節點，B+樹的磁盤I/O次數始終保持穩定，基本等于樹的高度。這種可預測的I/O特性使得查詢性能不會因數據分布或查詢模式的變化而產生劇烈波動，為數據庫性能優化提供了可靠保障。

此外，葉子節點形成的完整數據鏈表使全局掃描操作異常高效。進行全表掃描時，系統只需順序遍歷葉子節點鏈表即可獲取全部數據，避免了B樹那樣需要遍歷整個樹結構的開銷，極大提升了批量數據處理的效率。

最后，B+樹在數據插入方面也經過特殊優化。當采用自增整型主鍵時，新數據總是追加到鏈表末尾，最小化葉子節點分裂的概率。這種順序寫入特性顯著減少了索引維護開銷，使得高并發寫入場景下仍能保持穩定的性能表現。

6、什么是B+樹？

B+樹是一種專門為磁盤或其他直接存取的輔助存儲設備而設計的多路平衡搜索樹。它在數據庫和文件系統中至關重要，是MySQL InnoDB存儲引擎的默認索引結構。其核心目標是最小化磁盤I/O次數，從而高效地管理大量數據。

B+樹的核心特性

一棵經典的B+樹具備以下關鍵特征：

多路平衡樹：B+樹是“矮胖”的，而不是“高瘦”的。每個節點可以有多個子節點（通常是幾百個），這使得樹的高度非常低。一棵擁有數百萬條記錄的B+樹可能只有3-4層高，這意味著查找任何一條記錄最多只需要3-4次磁盤I/O，效率極高。

數據只存儲在葉子節點：這是B+樹與B樹的一個關鍵區別。非葉子節點（也稱為內部節點）只存儲鍵（索引鍵值），不存儲實際的數據記錄。內部節點的作用純粹是充當指引路徑的“路標”。

葉子節點通過指針串聯：所有葉子節點都包含了所有的鍵值及其對應的完整數據記錄（或指向數據記錄的指針）。此外，葉子節點之間使用雙向指針鏈表連接起來，形成了一個有序的鏈表。

7、索引失效場景？

1. 使用OR條件
當查詢條件中包含OR時，即使OR兩側的列都單獨建有索引，MySQL也通常只能使用其中一個索引，而不是合并索引。它會分別根據兩個索引查找出結果集，然后進行合并與去重，這個過程可能比直接進行全表掃描更耗時。因此，優化器在某些情況下可能會選擇放棄使用索引而進行全表掃描。
舉例： SELECT * FROM users WHERE age = 25 OR name = 'John'; 如果age和name上都有單列索引，MySQL可能不會使用這兩個索引，而是選擇全表掃描來避免復雜的索引合并操作。

2. 字符串字段未用引號括起來
如果查詢條件中的字符串類型字段沒有被引號括起來，MySQL會隱式地進行類型轉換，試圖將字符串值轉換為字段對應的數據類型（通常是數字）。這個轉換過程會使索引失效，因為MySQL無法直接使用索引樹中的字符串值來匹配一個經過函數轉換后的表達式。
舉例： SELECT * FROM users WHERE phone = 1234567890; （phone字段是VARCHAR類型）。這里MySQL會將索引中的字符串phone值轉換為數字，再與1234567890比較，導致索引失效。應寫為 phone = '1234567890'。

3. 使用LIKE通配符（以%開頭）
最左前綴匹配原則是索引有效的基礎。當LIKE語句的通配符%出現在字符串的開頭時，索引就失去了其有序性的優勢。MySQL無法知道什么字符串會出現在%之后，因此只能從索引樹的第一個條目開始遍歷所有值，檢查其是否符合條件，這實質上等同于全索引掃描或全表掃描。
舉例： SELECT * FROM articles WHERE content LIKE '%database%'; 這個查詢無法利用content字段上的索引。如果必須使用前綴模糊查詢，可考慮使用全文索引(FullText Index)。

4. 聯合索引的條件列順序問題
聯合索引（復合索引）的鍵值存儲順序是按照索引定義的列順序排列的。查詢必須使用聯合索引的“最左前綴”（即從第一列開始且連續不跳過中間列），索引才能被有效使用。如果查詢條件沒有從聯合索引的第一個列開始，或者跳過了中間的列，那么后續列的索引部分將無法被使用。
舉例： 假設有一個聯合索引 INDEX (last_name, first_name)。查詢 WHERE first_name = 'John' 無法使用該索引，因為它缺少最左邊的last_name條件。而查詢 WHERE last_name = 'Smith' AND first_name = 'John' 則可以高效地使用整個聯合索引。

5. 在索引列上使用內置函數
對索引列使用函數（如DATE()， UPPER()， CONCAT()等）會在查詢時對每一行數據的該列值進行計算，生成一個新的結果。索引中存儲的是原始數據，而不是函數計算后的結果，因此MySQL無法將函數條件與索引內容進行直接匹配，導致索引失效。
舉例： SELECT * FROM orders WHERE DATE(order_date) = '2023-10-24'; 雖然order_date字段有索引，但DATE()函數使其失效。應改為范圍查詢： WHERE order_date >= '2023-10-24 00:00:00' AND order_date < '2023-10-25 00:00:00'。

6. 索引列上的運算
與使用函數類似，在索引列上進行算術運算（如 +, -, *, /）或邏輯運算，都會導致MySQL無法直接使用索引中存儲的原始值，因為它需要先對每一行的列值進行計算，然后再與條件進行比較。這破壞了索引的直接可比性。
舉例： SELECT * FROM products WHERE price * 1.1 > 100; 即使price字段有索引，這個查詢也會失效。應重構查詢，將運算移到運算符的另一側： WHERE price > 100 / 1.1。

7. 使用不等于或范圍查詢
不等于操作符（!= 或 <>）和某些范圍查詢（NOT IN， NOT LIKE）本質上需要排除掉一部分數據。由于索引是排序的，對于“等于”條件可以快速定位，但對于“不等于”條件，它需要檢查索引中的幾乎所有條目（因為要返回所有非特定值的記錄），其效率有時并不比全表掃描高，因此優化器可能選擇不使用索引。
舉例： SELECT * FROM customers WHERE status != 'active'; 如果status字段只有少數幾種值（如'active', 'inactive'），且'inactive'數據量很大，優化器可能會判斷全表掃描更快，從而導致索引失效。

8. 索引字段上的NULL檢查
雖然MySQL允許在包含NULL值的列上建立索引，并且索引會記錄NULL值，但使用IS NULL或IS NOT NULL查詢時，索引的使用效率取決于數據的分布。如果NULL值或非NULL值的數量非常大，優化器可能會認為掃描整個索引（或全表）比通過索引樹定位特定部分更高效，從而選擇不使用索引。
舉例： SELECT * FROM contacts WHERE email IS NULL; 如果表中絕大多數記錄的email都不為NULL，只有幾條是NULL，這個查詢很可能會使用索引。反之，如果一半記錄都是NULL，優化器就可能選擇全表掃描。

9. 連接查詢中的字段編碼不一致
當進行表連接（如LEFT JOIN）時，如果關聯字段的字符集（Charset）或排序規則（Collation）不一致，MySQL需要先對其中一個字段進行轉換才能進行比較。這個隱式的轉換操作相當于在字段上使用了函數，會導致關聯字段上的索引失效。
舉例： TableA.utf8mb4_string 與 TableB.latin1_string 進行JOIN。MySQL必須轉換其中一個編碼以便比較，這使得TableB.latin1_string上的索引無法使用。解決方案是確保連接字段使用相同的字符集和排序規則。

10. MySQL優化器的選擇
MySQL優化器是一個基于成本的優化器（Cost-Based Optimizer, CBO）。它會根據統計信息（如表的行數、索引的選擇性、數據分布等）來估算不同執行路徑（使用索引或全表掃描）的成本。如果它估算出需要訪問的數據行數超過表總行數的一個較高比例（通常認為是20%-30%），或者索引的選擇性非常低（如性別字段），它就會認為使用索引（需要回表查詢）的成本高于直接掃描全表的成本，從而選擇全表掃描。
舉例： 在一個有10萬條記錄的用戶表中，執行 SELECT * FROM users WHERE gender = 'F'; （假設性別分布均勻）。即使gender字段上有索引，優化器也會因為需要返回約5萬條記錄（50%的數據）而選擇全表掃描，因為順序I/O讀取整個表比隨機I/O通過索引回表5萬次更快。

8、索引類型

1. 主鍵索引 (Primary Key Index)

解釋：
主鍵索引是關系數據庫中最核心的索引類型。它基于主鍵約束建立，其核心特性是唯一且非空。每個表只能有一個主鍵索引。它不僅是用于加速查詢的索引，更定義了表中數據的邏輯順序和唯一性約束。InnoDB存儲引擎使用主鍵索引作為“聚簇索引”，這意味著表數據本身就直接存儲在主鍵索引的葉子節點上。因此，通過主鍵進行查找是速度最快的操作。

舉例：
在一個 users（用戶）表中，user_id 列是唯一標識每個用戶的字段，不允許重復也不允許為NULL。將它設為主鍵是最合適的選擇。任何通過 user_id 來查詢特定用戶信息的操作都會非常高效。

創建與刪除代碼：

-- 創建表時直接指定（推薦）
CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY, -- 在此指定username VARCHAR(50) NOT NULL
);-- 為已存在的表添加主鍵
ALTER TABLE users ADD PRIMARY KEY (user_id);-- 刪除主鍵索引
ALTER TABLE users DROP PRIMARY KEY;

注意：刪除主鍵索引會同時移除主鍵約束。如果字段是自增的，需要先修改字段屬性再刪除主鍵。

2. 唯一索引 (Unique Index)

解釋：
唯一索引的主要作用是保證數據唯一性，而不是 primarily 為了提升性能（雖然它也能加速查詢）。它與主鍵索引的關鍵區別在于：唯一索引允許有NULL值（除非同時有NOT NULL約束），并且一個表可以創建多個唯一索引。當插入或更新數據時，如果會導致唯一索引列出現重復值（多個NULL值在MySQL中不被視為重復），數據庫會阻止該操作并拋出錯誤。它確保了業務層面上數據的唯一性，如防止郵箱、手機號重復注冊。

舉例：
在 users 表中，email（郵箱）和 phone_number（手機號）列都要求不能有重復值（NULL除外）。可以為這兩列分別創建唯一索引，以確保數據庫不會存入兩個相同郵箱或手機號的用戶賬號。

創建與刪除代碼：

-- 創建表時指定
CREATE TABLE users (user_id INT PRIMARY KEY,email VARCHAR(100) UNIQUE, -- 在此指定phone_number VARCHAR(20) UNIQUE
);-- 為已存在的表添加唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 或使用ALTER TABLE語法
ALTER TABLE users ADD UNIQUE KEY idx_phone (phone_number);-- 刪除唯一索引
DROP INDEX idx_email ON users;

---

3. 普通索引 (Normal Index / Index)

解釋：
普通索引（也稱為非唯一索引）是最基本、最常用的索引類型，它沒有任何唯一性限制的唯一目的就是提高查詢速度。它允許被索引的列包含重復值和NULL值。當你的查詢條件（WHERE）、排序（ORDER BY）或分組（GROUP BY）涉及到某一列或某些列時，為其創建普通索引可以避免全表掃描，大幅減少需要檢查的數據行數。

舉例：
在 orders（訂單）表中，經常需要根據 customer_id（客戶ID）來查詢某個客戶的所有訂單。由于一個客戶可以有多個訂單，customer_id 必然重復，此時為其創建一個普通索引是最佳選擇，可以極大加速這類查詢。

創建與刪除代碼：

-- 創建表時指定
CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT,INDEX idx_customer_id (customer_id) -- 在此指定
);-- 為已存在的表添加普通索引
CREATE INDEX idx_customer_id ON orders(customer_id);
-- 或使用ALTER TABLE語法
ALTER TABLE orders ADD INDEX idx_customer (customer_id);-- 刪除索引
DROP INDEX idx_customer_id ON orders;

---

4. 全文索引 (Full-Text Index)

解釋：
全文索引是一種專門用于對大量文本內容進行高效詞語匹配和搜索的特殊索引。它與前三種基于精確匹配的索引原理完全不同，其核心是“分詞”。MySQL會將文本字符串按詞進行拆分，并建立倒排索引，從而能夠快速查找包含特定詞語或短語組合的行。它適用于實現像文章搜索、商品關鍵詞搜索這樣的功能，使用 MATCH(...) AGAINST(...) 語法進行查詢。

舉例：
在一個 articles（文章）表中，用戶需要在 title（標題）和 content（正文）中搜索包含“人工智能”關鍵詞的文章。為這兩個列創建全文索引后，就可以進行高效的關鍵詞檢索，而不是低效的 LIKE '%人工智能%' 操作。

創建與刪除代碼：

-- 創建表時指定（僅限InnoDB和MyISAM引擎）
CREATE TABLE articles (id INT PRIMARY KEY,title TEXT,content TEXT,FULLTEXT KEY ft_index (title, content) -- 在此指定
);-- 為已存在的表添加全文索引
ALTER TABLE articles ADD FULLTEXT ft_index (title, content);
-- 或
CREATE FULLTEXT INDEX ft_index ON articles(title, content);-- 刪除全文索引
DROP INDEX ft_index ON articles;

如何使用全文索引

全文索引的使用核心在于 MATCH(...) AGAINST(...) 語法。MATCH 子句指定要搜索的列（這些列必須包含在同一個全文索引中），AGAINST 子句指定要搜索的關鍵詞（也稱為搜索字符串或查詢字符串），并可以指定搜索模式。

1. 自然語言搜索 (NATURAL LANGUAGE MODE) - 默認模式
這是默認模式，用于查找包含至少一個搜索詞的行。結果集會自動按相關性（Relevance）進行降序排序，相關性是一個非負浮點數，表示匹配程度。

示例查詢：

-- 查找包含“人工智能”的文章
SELECT id, title,-- 可以使用MATCH(...) AGAINST(...)來獲取相關性得分MATCH(title, content) AGAINST('人工智能' IN NATURAL LANGUAGE MODE) AS score
FROM articles 
WHERE MATCH(title, content) AGAINST('人工智能' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

2. 布爾搜索 (BOOLEAN MODE)
這種模式允許使用特殊的操作符來構造更復雜、更強大的查詢。你可以要求某些詞必須出現(+)，某些詞必須排除(-)，或者使用通配符(*)。

常用操作符：

• +：必須包含。+apple 表示結果必須包含 "apple"。
• -：必須排除。-banana 表示結果不能包含 "banana"。
• *：通配符。data* 可以匹配 "data", "database", "dataset" 等。
• ""：短語搜索。"machine learning" 表示必須精確匹配整個短語 "machine learning"。
• > <：提高或降低詞的貢獻值。

示例查詢：

-- 查找必須包含“區塊鏈”但不能包含“金融”的文章
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+區塊鏈 -金融' IN BOOLEAN MODE);-- 查找包含以“data”開頭的詞（如database, data）的文章
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('data*' IN BOOLEAN MODE);-- 查找精確包含短語“深度學習框架”的文章
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('"深度學習框架"' IN BOOLEAN MODE);

3. 查詢擴展搜索 (WITH QUERY EXPANSION)
這種模式會進行兩次搜索：第一次用給定的關鍵詞進行搜索；然后，它會從第一次找到的最相關行中提取出一些重要的詞，將這些詞加入到原搜索詞中進行第二次搜索。這有助于擴大搜索范圍，找到更多相關結果，但也可能引入一些不相關的噪聲。適用于用戶搜索詞過于簡短或模糊的情況。

示例查詢：

-- 使用查詢擴展來搜索“數據庫”
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('數據庫' WITH QUERY EXPANSION);

5. 組合索引 (Composite Index / Compound Index)

解釋：
組合索引是指由多個列共同組成的一個索引。它并不是一個獨立的索引類型，而是一種創建索引的策略（可以是普通組合索引，也可以是唯一組合索引等）。它的核心優勢是最左前綴原則。查詢時，如果條件中包含了索引定義中最左邊的列，那么這個組合索引就可以被使用。一個設計良好的組合索引，其效能往往遠高于多個單列索引，因為它可以優化多列查詢、排序和避免回表操作。

舉例：
在 users 表中，有一個非常常見的查詢是：“查找來自‘北京’(city)且姓‘張’(last_name)的所有用戶”。如果為 (city, last_name) 創建一個組合索引，數據庫可以高效地利用這個索引直接定位到所需數據，效率遠高于分別對 city 和 last_name 建兩個獨立索引。

創建與刪除代碼：

-- 創建表時指定
CREATE TABLE users (user_id INT PRIMARY KEY,country VARCHAR(50),city VARCHAR(50),last_name VARCHAR(50),INDEX idx_location_name (country, city, last_name) -- 在此指定
);-- 為已存在的表添加組合索引
CREATE INDEX idx_location_name ON users(country, city, last_name);
-- 或
ALTER TABLE users ADD INDEX idx_location_name (country, city, last_name);-- 刪除組合索引（與刪除普通索引語法相同）
DROP INDEX idx_location_name ON users;

6. 覆蓋索引 (Covering Index)

解釋：
覆蓋索引不是一種物理上特殊的索引，而是一種高性能的查詢優化場景。當一個索引包含了查詢所需要的所有字段時（即SELECT的列、WHERE的條件、JOIN的字段等都在同一個索引的列中），數據庫引擎就無需再“回表”去讀取數據行本身，只需掃描索引本身即可得到全部結果。這極大地減少了I/O操作，是數據庫優化的重要技術之一。通常需要通過創建恰當的組合索引來實現覆蓋索引的效果。

舉例：
有一個高頻查詢：SELECT user_id, age, score FROM user_profiles WHERE age > 25;。如果在 user_profiles 表上只有一個在 age 列的單列索引，那么查詢需要根據索引找到所有age>25的行ID，再根據這些ID回表去取 user_id 和 score 的值。但如果創建一個 (age, user_id, score) 的組合索引，這個索引本身就包含了查詢所需的所有數據，引擎只需掃描這個索引就能完成整個查詢，速度極快。

創建與刪除代碼：
覆蓋索引本身不是一種索引類型，而是通過創建特定的組合索引來實現的。其創建和刪除語法與創建組合索引完全相同。

-- 為了實現上述舉例中的覆蓋索引，我們創建組合索引
CREATE INDEX idx_age_cover ON user_profiles(age, user_id, score);-- 刪除索引
DROP INDEX idx_age_cover ON user_profiles;

9、聚簇索引和非聚簇索引
?

聚簇索引 (Clustered Index)

聚簇索引并不是一個單獨的索引類型，而是一種數據存儲方式。在InnoDB中，表數據本身（即數據行）就存儲在聚簇索引的B+樹的葉子節點上。因此，一個表有且只有一個聚簇索引，因為它決定了表中數據的物理存儲順序。

關鍵特性：

1. 表即索引：數據行和主鍵索引是綁定在一起的。
2. 物理有序：數據行按照聚簇索引鍵值的順序在磁盤上存儲。相鄰的鍵值，其數據行在物理上也盡可能相鄰。
3. 快速主鍵查詢：通過主鍵查找數據非常快，因為只需一次B+樹查找就能直接定位到數據行。

如何創建？

1. 首選主鍵：如果你為表定義了主鍵（PRIMARY KEY），InnoDB會自動使用它來構建聚簇索引。
2. 唯一非空索引：如果沒有主鍵，InnoDB會選擇第一個所有列都定義為NOT NULL的UNIQUE索引作為聚簇索引。
3. 隱藏行ID：如果以上兩者都沒有，InnoDB會在內部生成一個名為GEN_CLUST_INDEX的隱藏聚簇索引，包含一個6字節的ROWID值。這個行ID會在插入新行時自增。但強烈不建議依賴于此，最好顯式定義主鍵。

舉例說明：

假設我們有一個users表，結構如下：

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, -- 主鍵，將成為聚簇索引username VARCHAR(50) NOT NULL,email VARCHAR(100),created_at DATETIME
);

數據是如何存儲的？
聚簇索引（基于id列）的B+樹結構大致如下：

• 根節點/中間節點：存儲主鍵id的范圍和指向子節點的指針。
• 葉子節點：直接存儲了整個users表的所有行數據（id, username, email, created_at）。

id (PK)	username	email	created_at
1	alice	alice@example.com	2023-01-01 10:00:00
2	bob	bob@example.com	2023-01-02 11:00:00
3	charlie	charlie@example.com	2023-01-03 12:00:00
...	...	...	...

因為這些數據按id排序存儲，所以查詢SELECT * FROM users WHERE id = 2;的速度極快，B+樹能直接導航到包含該id數據的葉子頁。

非聚簇索引 (Non-Clustered Index / Secondary Index)

非聚簇索引，也叫二級索引或輔助索引，是獨立于聚簇索引的傳統意義上的索引。它的葉子節點不包含完整的行數據，而是包含兩部分：

1. 索引列的鍵值：你創建索引時指定的列的值。
2. 主鍵值：指向對應數據行的主鍵（即聚簇索引的鍵）。

關鍵特性：

1. 邏輯指針：它通過主鍵值這個“指針”來指向實際的數據行。
2. 一個表可以有多個：你可以根據需要創建很多個二級索引（如INDEX, UNIQUE）。
3. 需要回表查詢：使用二級索引查找數據通常需要兩次B+樹查找：

• • 第一次：在二級索引的B+樹中查找，得到對應的主鍵值。
  • 第二次：拿著這個主鍵值，回到聚簇索引的B+樹中查找，最終拿到完整的行數據。這個過程稱為回表（Bookmark Lookup）。

舉例說明：

我們在users表的username列上創建一個非聚簇索引：

CREATE INDEX idx_username ON users(username);

這個索引是如何存儲的？
索引idx_username的B+樹結構：

• 根節點/中間節點：存儲username的范圍和指針。
• 葉子節點：存儲的是username的值和對應的主鍵id。

username (Indexed)	id (PK)
alice	1
bob	2
charlie	3
...	...

查詢過程分析：
執行查詢：SELECT * FROM users WHERE username = 'bob';

1. 步驟一（二級索引查找）：數據庫在idx_username的B+樹中快速找到username = 'bob'的條目，并取出其對應的主鍵值id = 2。
2. 步驟二（回表查詢）：數據庫拿著這個id = 2，回到聚簇索引的B+樹中，像最初那個例子一樣，查找id = 2的條目，最終從該葉子節點中取出所有列的數據（id=2, username='bob', email=...）并返回。

特性	聚簇索引	非聚簇索引（二級索引）
數量	每表唯一一個	每表可創建多個
內容	在葉子節點存儲完整的數據行	在葉子節點存儲索引列的值 + 主鍵值
速度	主鍵查詢極快，一次查找即可	查詢需要兩次查找（二級索引 -> 聚簇索引，即回表）
物理順序	數據行的物理順序與索引順序一致	索引順序與數據行的物理順序無關
依賴性	決定數據的物理存儲	依賴于聚簇索引（需要主鍵來“定位”數據行）