ALTER TABLE user ADD INDEX idx_name(name);

例子：聯(lián)合索引

ALTER TABLE user ADD INDEX idx_name_phone(name, phone);

查詢：

SELECT * FROM user WHERE name='Tom' AND phone='138...';

?? 更容易走 (name, phone) 的聯(lián)合索引。

4.全文索引（FULLTEXT）

定義

• InnoDB 從 MySQL 5.6 開(kāi)始支持 FULLTEXT（歷史上 MyISAM 更早支持）。

• 適合：分詞 + 相關(guān)性排序

• 用來(lái)做“文本檢索”，支持對(duì)長(zhǎng)文本按詞（或按分詞）搜索，例如 MATCH(col) AGAINST(...)。

實(shí)現(xiàn)與特點(diǎn)

• InnoDB 的 FULLTEXT 是專門的倒排索引體系（不是 B+Tree）。

• 更適合：文章、評(píng)論、商品描述等“文本搜索”。

• 注意：中文檢索通常需要分詞支持（MySQL 原生能力有限，很多場(chǎng)景會(huì)用 Elasticsearch 等專用搜索引擎）。

例子

CREATE TABLE article (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  FULLTEXT KEY ft_content(content)
) ENGINE=InnoDB;

查詢：

SELECT * FROM article
WHERE MATCH(content) AGAINST('mysql 索引' IN NATURAL LANGUAGE MODE);

四、按“存儲(chǔ)形式/數(shù)據(jù)組織方式”分類

1.聚簇索引（聚集索引）

聚簇索引：在 InnoDB 存儲(chǔ)引擎中，聚簇索引通常就是主鍵索引 。聚簇索引的特點(diǎn)是數(shù)據(jù)與索引一體化存儲(chǔ)，即數(shù)據(jù)行（整行，不是指針）直接存儲(chǔ)在索引的葉子節(jié)點(diǎn)中，并且數(shù)據(jù)按照主鍵的順序進(jìn)行物理存儲(chǔ) 。這使得聚簇索引在查詢時(shí)具有極高的效率，尤其是對(duì)于主鍵查詢和范圍查詢。因?yàn)閿?shù)據(jù)是按照主鍵順序存儲(chǔ)的，所以在進(jìn)行范圍查詢（如查詢 ID 在某個(gè)范圍內(nèi)的用戶）時(shí)，可以利用索引的有序性，快速定位到滿足條件的數(shù)據(jù)。此外，聚簇索引還能利用順序檢測(cè)預(yù)取機(jī)制，提高數(shù)據(jù)讀取的效率。例如，在一個(gè)用戶表中，以用戶 ID 為主鍵創(chuàng)建聚簇索引，當(dāng)查詢用戶 ID 為 100 的用戶信息時(shí)，數(shù)據(jù)庫(kù)可以直接通過(guò)聚簇索引找到對(duì)應(yīng)的葉子節(jié)點(diǎn)，獲取用戶信息，無(wú)需進(jìn)行額外的查找操作。需要注意的是，一張表只能有一個(gè)聚簇索引，因?yàn)閿?shù)據(jù)的物理存儲(chǔ)順序只能有一種。

概括:用主鍵查 = 直接定位到葉子節(jié)點(diǎn) = 一次 B+Tree 查找拿到整行。

為什么叫“聚集”

• 因?yàn)閿?shù)據(jù)行與索引鍵“聚在一起”，數(shù)據(jù)就是索引的一部分。

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

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

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

• 如果表沒(méi)有主鍵，或沒(méi)有合適的唯一索引，則InnoDB會(huì)自動(dòng)生成一個(gè)rowid作為隱藏的聚集索引。

聚簇索引的優(yōu)缺點(diǎn)：

優(yōu)點(diǎn)：

• 查詢速度非常快：聚簇索引的查詢速度非常的快，因?yàn)檎麄€(gè) B+ 樹本身就是一顆多叉平衡樹，葉子節(jié)點(diǎn)也都是有序的，定位到索引的節(jié)點(diǎn)，就相當(dāng)于定位到了數(shù)據(jù)。相比于非聚簇索引， 聚簇索引少了一次讀取數(shù)據(jù)的 IO 操作。

• 對(duì)排序查找和范圍查找優(yōu)化：聚簇索引對(duì)于主鍵的排序查找和范圍查找速度非?？?。

缺點(diǎn)：

• 依賴于有序的數(shù)據(jù)：因?yàn)?B+ 樹是多路平衡樹，如果索引的數(shù)據(jù)不是有序的，那么就需要在插入時(shí)排序，如果數(shù)據(jù)是整型還好，否則類似于字符串或 UUID 這種又長(zhǎng)又難比較的數(shù)據(jù)，插入或查找的速度肯定比較慢。

• 更新代價(jià)大：如果對(duì)索引列的數(shù)據(jù)被修改時(shí)，那么對(duì)應(yīng)的索引也將會(huì)被修改，而且聚簇索引的葉子節(jié)點(diǎn)還存放著數(shù)據(jù)，修改代價(jià)肯定是較大的，所以對(duì)于主鍵索引來(lái)說(shuō)，主鍵一般都是不可被修改的。

2.非聚簇索引

也稱為二級(jí)索引，其索引存儲(chǔ)的是主鍵值，而不是實(shí)際的數(shù)據(jù)行 。當(dāng)使用非聚簇索引進(jìn)行查詢時(shí)，首先會(huì)根據(jù)索引找到對(duì)應(yīng)的主鍵值，然后再通過(guò)主鍵值在聚簇索引中查找實(shí)際的數(shù)據(jù)行（整行），這個(gè)過(guò)程稱為回表查詢 。非聚簇索引支持多列組合索引，適用于多個(gè)字段聯(lián)合查詢的場(chǎng)景。例如，在一個(gè)訂單表中，經(jīng)常需要根據(jù)客戶 ID 和訂單日期進(jìn)行查詢，可以為客戶 ID 和訂單日期創(chuàng)建組合非聚簇索引。雖然非聚簇索引需要回表查詢，查詢效率相對(duì)聚簇索引略低，但在某些情況下，它可以提供更靈活的查詢方式。比如，在查詢訂單表中某個(gè)客戶在特定日期之后的訂單時(shí)，通過(guò)組合非聚簇索引可以快速定位到滿足條件的主鍵值，然后再通過(guò)回表查詢獲取完整的訂單信息。

概括：二級(jí)索引的葉子節(jié)點(diǎn)存的不是整行，而是“索引列 + 主鍵值”。

覆蓋索引（避免回表）

• 如果查詢需要的列 都在二級(jí)索引里（或索引里包含它們），那就不需要回表，這叫 覆蓋索引。

• 例如：select name from user where age=20;

  • 若建了 (age, name) 聯(lián)合索引，就可能直接從索引葉子拿到 name，無(wú)需回表。

非聚簇索引的優(yōu)缺點(diǎn)：

優(yōu)點(diǎn)：

更新代價(jià)比聚簇索引要小。非聚簇索引的更新代價(jià)就沒(méi)有聚簇索引那么大了，非聚簇索引的葉子節(jié)點(diǎn)是不存放數(shù)據(jù)的。

缺點(diǎn)：

• 依賴于有序的數(shù)據(jù)：跟聚簇索引一樣，非聚簇索引也依賴于有序的數(shù)據(jù)。

• 可能會(huì)二次查詢（回表）：這應(yīng)該是非聚簇索引最大的缺點(diǎn)了。當(dāng)查到索引對(duì)應(yīng)的指針或主鍵后，可能還需要根據(jù)指針或主鍵再到數(shù)據(jù)文件或表中查詢。

五、圖解

1.示例表結(jié)構(gòu)

CREATE TABLE account_example (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '賬戶ID',
  name VARCHAR(20) COMMENT '姓名',
  money DOUBLE(10,2) COMMENT '余額',
  INDEX idx_name (name),
  INDEX idx_name_money (name, money)
) ENGINE=InnoDB;
?
-- 插入數(shù)據(jù)
insert into account_example(id, name , money)
values
(1, '張三', 100.00),
(3, '李四', 200.00),
(7, '王五', 300.00);

id → 主鍵索引（聚簇索引）

idx_name(name) → 普通二級(jí)索引

idx_name_money(name, money)→ 聯(lián)合索引

2.聚簇索引（id）B+Tree 示意圖：

聚簇索引的B+Tree 的葉子節(jié)點(diǎn)存放的是整行數(shù)據(jù)。

特點(diǎn)總結(jié)

• 一張表 只能有一個(gè)聚集索引

• 數(shù)據(jù)行 按主鍵順序物理存儲(chǔ)

• 使用主鍵查詢：

SELECT * FROM account WHERE id = 3;

?? 一次 B+Tree 查找即可拿到整行數(shù)據(jù)，不存在回表

3.非聚簇索引（idx_name）的 B+Tree 示意圖：

非聚簇索引的葉子節(jié)點(diǎn)中：不存整行數(shù)據(jù)，只存「索引列值 + 主鍵值」

二級(jí)索引的葉子節(jié)點(diǎn)下掛的是該字段值對(duì)應(yīng)的主鍵值。

4.為什么會(huì)發(fā)生回表

示例 SQL（會(huì)回表）

SELECT * FROM account WHERE name = '李四';

執(zhí)行過(guò)程拆解

Step 1：通過(guò)二級(jí)索引 idx_name 查找

name='李四'
   ↓
在 idx_name B+Tree 中定位
   ↓
得到主鍵 id = 3

Step 2：根據(jù)主鍵回到聚集索引

id=3
   ↓
在 PRIMARY KEY B+Tree 中查找
   ↓
獲取完整行數(shù)據(jù)

?? 這一步稱為：回表

本質(zhì)原因：二級(jí)索引不存完整數(shù)據(jù)

5.聯(lián)合索引 idx(name, money) 的存儲(chǔ)結(jié)構(gòu)

聯(lián)合索引葉子節(jié)點(diǎn)存：(name, money) + id

idx_name_money(name, money) B+Tree 示意圖：

6.覆蓋索引 vs 回表

? 示例 1：需要回表

SELECT * FROM account
WHERE name = '李四';

• 使用 idx_name

• money 不在索引中

• 必須回表

? 示例 2：覆蓋索引（不回表）

SELECT name, money
FROM account
WHERE name = '李四';

• 使用 idx_name_money

• 查詢字段 全部存在索引葉子節(jié)點(diǎn)

• 無(wú)需回表

?? 這就叫：覆蓋索引（Covering Index）

7.總結(jié)

六、關(guān)聯(lián)

七、問(wèn)題

主鍵為什么不要用隨機(jī)值（如隨機(jī) UUID）？

• 聚集索引決定了數(shù)據(jù)行的物理組織順序

• 隨機(jī)插入會(huì)導(dǎo)致頻繁頁(yè)分裂、碎片、寫放大，性能更差 （自增/有序主鍵插入更“順滑”）

二級(jí)索引為什么存主鍵值，而不是物理地址？

• 因?yàn)?InnoDB 的數(shù)據(jù)行會(huì)移動(dòng)/頁(yè)會(huì)分裂，存物理地址維護(hù)成本高

• 存主鍵值更穩(wěn)定，代價(jià)是可能需要回表

建索引的常見(jiàn)收益點(diǎn)

• 加速 WHERE、JOIN、ORDER BY、GROUP BY

• 利用覆蓋索引減少回表、減少 IO

八、總結(jié)

• InnoDB 中，主鍵索引就是聚集索引，數(shù)據(jù)行存放在主鍵 B+Tree 的葉子節(jié)點(diǎn)；

• 二級(jí)索引的葉子節(jié)點(diǎn)只保存索引列和主鍵值，因此在查詢非索引字段時(shí)需要回表；

• 當(dāng)查詢字段完全被索引覆蓋時(shí)，可以避免回表，從而顯著提升查詢性能。

到此這篇關(guān)于MySQL索引的原理與性能優(yōu)化教程的文章就介紹到這了,更多相關(guān)索引設(shè)計(jì)優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！