核心目標： 理解 mysql 索引的工作原理、類型、優缺點，并掌握創建、管理和優化索引的方法，以顯著提升數據庫查詢性能。

什么是索引？
索引是一種特殊的數據庫結構，它包含表中一列或多列的值以及指向這些值所在物理行的指針（或對于聚集索引，直接包含數據）。其主要目的是加快數據檢索（select 查詢）的速度。你可以把它想象成一本書的目錄或索引，讓你能夠快速定位到需要查找的內容，而不是逐頁翻閱。

索引如何工作（簡化理解）？
mysql 最常用的索引類型是 b-tree 索引（或其變種如 b+tree）。b-tree 是一種自平衡的樹狀數據結構，它保持數據有序，并允許高效地進行查找、插入、刪除和順序訪問。當你在索引列上執行查詢時（例如 where indexed_col = value 或 order by indexed_col），數據庫可以利用 b-tree 結構快速定位到匹配的行，避免了全表掃描（逐行檢查）。

使用索引的優點

1. 大幅提高查詢速度： 這是索引最主要的好處，尤其是在 where 子句、join 操作的 on 子句中使用的列。
2. 加速排序： 如果 order by 子句中的列有索引，mysql 可以直接利用索引的有序性返回結果，避免額外的排序操作。
3. 加速分組： group by 操作通常也需要排序，索引可以幫助加速。
4. 保證數據唯一性： unique 索引和 primary key 約束可以確保列值的唯一性。

使用索引的缺點 (cons)

1. 占用存儲空間： 索引本身也需要存儲在磁盤上（或內存中），會增加數據庫的總體積。
2. 降低寫入性能： 當對表進行 insert, update, delete 操作時，不僅要修改數據行，還需要同步更新相關的索引結構，這會增加寫操作的開銷。索引越多，寫操作越慢。
3. 索引維護成本： 索引需要維護，例如在數據大量變動后可能需要重建或優化（雖然 innodb 在這方面自動化程度較高）。

索引的類型

• 按功能/邏輯分類:

1. 主鍵索引

• 一種特殊的唯一索引，用于唯一標識表中的每一行。
• 列值必須唯一 (unique) 且不能為空 (not null)。
• 一個表只能有一個主鍵。
• 通常在創建表時定義。innodb 表是圍繞主鍵組織的（聚集索引）。

-- 建表時定義
create table users (
user_id int primary key,
username varchar(50) not null
);
-- 或表級定義 (用于單列或復合主鍵)
create table user_roles (
user_id int,
role_id int,
primary key (user_id, role_id)
);

2. 唯一索引

• 確保索引列（或列組合）中的所有值都是唯一的。
• 與主鍵不同，它允許一個 null 值。
• 主要目的是保證數據完整性，同時也能加速查詢。

-- 建表時定義 (列級)
create table employees (
emp_id int primary key,
email varchar(100) unique
);
-- 建表時定義 (表級)
create table products (
product_id int primary key,
sku varchar(50),
constraint uq_sku unique (sku)
);
-- 后續添加
alter table employees add constraint uq_emp_ssn unique (social_security_number);
-- 或使用 create unique index
create unique index idx_uq_phone on customers (phone_number);

3. 普通索引 / 常規索引

• 最基本的索引類型，沒有唯一性限制。
• 其唯一目的就是加速數據檢索。
• key 是 index 的同義詞。

-- 建表時定義
create table logs (
log_id int primary key,
log_time datetime,
user_id int,
index idx_log_time (log_time), -- 創建普通索引
key idx_user_id (user_id) -- key 與 index 等效
);
-- 后續添加
alter table logs add index idx_message_prefix (log_message(50)); -- 前綴索引
-- 或使用 create index
create index idx_order_date on orders (order_date);

4. 復合索引 / 組合索引 / 多列索引

• 在表的多個列上創建的索引。
• 順序非常重要！ 遵循最左前綴原則 (leftmost prefix principle)。

-- 建表時定義
create table orders (
order_id int primary key,
customer_id int,
order_date date,
index idx_cust_date (customer_id, order_date) -- 復合索引
);
-- 后續添加
alter table products add index idx_category_price (category_id, price);

5. 全文索引

• 專門用于在文本列 (char, varchar, text) 中進行關鍵字搜索。
• 使用 match(column) against('keywords') 語法進行查詢。
• innodb (mysql 5.6+) 和 myisam 引擎支持。

-- 建表時定義
create table articles (
article_id int primary key,
title varchar(200),
body text,
fulltext index idx_ft_title_body (title, body)
) engine=innodb; -- 確保引擎支持
-- 后續添加
alter table articles add fulltext index idx_ft_body (body);
-- 查詢
select * from articles where match(title, body) against('database performance');

6. (了解) 空間索引

• 用于地理空間數據類型。優化地理位置查詢。

-- create table spatial_table (
-- g geometry not null,
-- spatial index(g)
-- );

• 按物理存儲方式/結構分類 (主要是 innodb vs myisam):

  1. 聚集索引 (clustered index)

     • innodb 表強制要求有且只有一個。
     • 表的物理存儲順序與索引順序一致，通常是按主鍵組織。
     • 優點：主鍵查找和范圍查詢快。缺點：插入慢，二級索引查找需兩次。

  2. 非聚集索引 (non-clustered index) / 二級索引 (secondary index)

     • myisam 表的所有索引都是非聚集的。innodb 表的非主鍵索引是二級索引。
     • 索引邏輯順序與數據物理存儲順序無關。
     • 索引項包含索引值和指向數據行的指針(myisam)或主鍵值(innodb)。
     • 優點：插入快。缺點：查找可能需要額外步驟獲取數據。

關鍵索引概念

• 覆蓋索引 (covering index)
  當查詢所需的所有列都包含在使用的索引中時，mysql 直接從索引獲取數據，無需訪問數據行（回表），性能極高。

-- 對于 index idx_name_age (name, age)
-- 這個查詢可以使用覆蓋索引
select name, age from users where name = 'alice';

• 索引選擇性 (index selectivity)
  索引列中不同值的比例 (cardinality / total rows)。選擇性越高（越接近 1），索引效果越好。性別列選擇性低，身份證號列選擇性高。

• 前綴索引 (prefix indexing)
  對長字符串列只索引前綴部分，節省空間，提高速度。語法：index(column_name(prefix_length))。缺點：不能用于 order by/group by。

alter table user_profiles add index idx_bio_prefix (biography(100));

• 索引基數 (index cardinality)
  索引中唯一值的估計數量。show index 可查看。基數越高通常選擇性越好。

• (了解) 降序索引 (descending indexes)
  mysql 8.0+ 支持真正的 desc 索引，優化 order by ... desc。

-- mysql 8.0+
create index idx_created_desc on articles (created_at desc);

• (了解) 不可見索引 (invisible indexes)
  mysql 8.0+ 引入。優化器不使用，但索引仍維護。用于測試移除索引的影響。

alter table my_table alter index idx_name invisible; -- 設為不可見
alter table my_table alter index idx_name visible; -- 設為可見

索引管理語法

創建索引

• 建表時 (create table): (見上文類型定義)
• 使用 create index:

create index idx_name on table_name (column1, column2(10));
create unique index uq_email on users (email);
create fulltext index ft_content on documents (content);

使用 alter table:

alter table table_name add index idx_name (column_name);
alter table table_name add unique key uq_name (column_name);
alter table table_name add primary key (column_name); -- (如果尚無主鍵)
alter table table_name add fulltext index ft_name (column_name);

查看索引

• show index from table_name;: 最常用，顯示詳細信息。

show index from employees;

• show create table table_name;: 顯示建表語句，包含索引定義。

show create table orders;

• 查詢 information_schema:

select index_name, column_name, index_type
from information_schema.statistics
where table_schema = 'your_database_name' and table_name = 'your_table_name';

刪除索引

• drop index index_name on table_name;: 最常用。

drop index idx_order_date on orders;
drop index uq_sku on products;

• alter table table_name drop index index_name;: 功能同上。

alter table logs drop index idx_user_id;

• alter table table_name drop primary key;: 刪除主鍵。

alter table some_table drop primary key;

• alter table table_name drop foreign key fk_name;: 刪除外鍵約束。

選擇哪些列加索引？

1. where 子句頻繁使用的列。
2. join on 子句的連接列。
3. order by 子句的列。
4. group by 子句的列。
5. 選擇性高的列。
6. 考慮復合索引（注意最左前綴和列順序）。

索引失效（不被使用）的常見情況

1. 對索引列使用函數或表達式 (where year(col)=...)。
2. like 查詢以 % 開頭 (where name like '%son')。
3. or 條件兩邊未都建立合適索引。
4. 數據類型不匹配 / 隱式類型轉換 (where string_col = 123)。
5. 索引選擇性過低。
6. 表數據量過小。
7. mysql 優化器認為全表掃描更快。

索引優化與 explain

• explain 命令： 分析 select 執行計劃的關鍵工具。查看 type, key, rows 等字段判斷索引使用情況。

explain select * from users where username = 'test';

• 定期維護 (相對次要，尤其對 innodb):
  • analyze table table_name;: 更新統計信息。
  • optimize table table_name;: myisam 整理碎片；innodb 通常重建表。

總結與最佳實踐

• 索引提速查詢，但降低寫入性能、占空間。
• 理解 innodb (默認) 和 myisam 區別。
• 優先索引 where, join, order by, group by 的列。
• 善用復合索引（最左前綴）和覆蓋索引。
• 避免索引列上用函數、隱式轉換、like '%...'。
• 用 explain 分析和驗證索引效果。
• 不過度索引，定期審查。

---

練習題

假設有 orders 表: (order_id int pk, customer_id int, product_name varchar(100), quantity int, order_date date)

1. 為 orders 表的 customer_id 列添加一個普通索引，名為 idx_cust_id。
   答案:

alter table orders add index idx_cust_id (customer_id);
-- 或者
-- create index idx_cust_id on orders (customer_id);

2. 為 orders 表添加一個復合索引，包含 order_date 和 product_name (前 50 個字符)，索引名為 idx_date_product。
   答案:

alter table orders add index idx_date_product (order_date, product_name(50));

3. 假設需要確保每個客戶在同一天的同一個產品只能下一個訂單。請為 orders 表添加一個合適的唯一約束（假設可以基于 customer_id, order_date, product_name）。約束名為 uq_cust_date_prod。
   答案:

alter table orders add constraint uq_cust_date_prod unique (customer_id, order_date, product_name);

4. 查看 orders 表上存在的所有索引。
   答案:

show index from orders;

5. 刪除第 2 題創建的復合索引 idx_date_product。
   答案:

drop index idx_date_product on orders;
-- 或者
-- alter table orders drop index idx_date_product;

6. 分析以下查詢的執行計劃（假設 customer_id 列已有索引 idx_cust_id）：explain select order_id, product_name from orders where customer_id = 123 order by order_date; 思考 order by 是否能利用索引。
   答案:

explain select order_id, product_name from orders where customer_id = 123 order by order_date;