目錄
一、索引（Index）
二、事務（Transaction）
三、總結
一、索引（Index）
索引的本質：一種數據結構（如 B+Tree、Hash），用于快速定位數據，避免全表掃描。
核心作用：提升查詢效率，但會犧牲一定的寫性能（增刪改需維護索引）。
1. 索引類型
- B+Tree 索引（默認）
- 特點：支持范圍查詢、排序、最左前綴匹配。
- 適用場景：=,?>,?<,?BETWEEN,?ORDER BY,?GROUP BY?等操作。
- InnoDB 聚簇索引：數據直接存儲在 B+Tree 葉子節點，主鍵即聚簇索引。
- 非聚簇索引（二級索引）：葉子節點存儲主鍵值，需回表查詢數據。
- 哈希索引
- 特點：O(1) 時間復雜度，僅支持精確匹配（=），不支持范圍查詢。
- 適用場景：內存表（如 MEMORY 引擎）、等值查詢頻繁的場景。
- 限制：哈希沖突、無法排序。
- 全文索引（FULLTEXT）
- 特點：基于分詞技術，支持自然語言搜索。
- 適用場景：MATCH() ... AGAINST?全文檢索（如文章內容搜索）。
- 引擎支持：MyISAM 和 InnoDB（5.6+）。
- 空間索引（R-Tree）
- 適用場景：地理空間數據（如經緯度查詢）。
- 引擎支持：MyISAM。
2. 索引創建與使用
- 創建語法：
CREATE INDEX idx_name ON table(column);????????? -- 普通索引
CREATE UNIQUE INDEX idx_name ON table(column);?? -- 唯一索引
ALTER TABLE table ADD PRIMARY KEY(column);?????? -- 主鍵索引
- 聯合索引（復合索引）
- 最左前綴原則：索引?(a, b, c)?可生效于?a,?a+b,?a+b+c，但無法單獨使用?b?或?c。
- 覆蓋索引：查詢字段全部在索引中時，無需回表（性能最優）。
- 索引失效場景
- 對索引列進行運算或函數操作（如?WHERE YEAR(date) = 2023）。
- 使用?LIKE?以通配符開頭（如?LIKE '%abc'）。
- 數據類型隱式轉換（如字符串列用數字查詢）。
- OR 連接非索引列（除非所有列均有索引）。
- 優化器認為全表掃描更快（小表或低區分度數據）。
3. 索引優化建議
- 選擇高區分度的列：區分度越高（如唯一鍵），過濾效果越好。
- 避免冗余索引：聯合索引可替代多個單列索引。
- 控制索引長度：使用前綴索引（如?INDEX(column(10))）減少存儲。
- 監控索引使用率：通過?SHOW INDEX FROM table?或?INFORMATION_SCHEMA.STATISTICS?分析。
- 執行計劃分析：用?EXPLAIN?查看?type（訪問類型）、key（使用索引）、Extra（是否覆蓋索引）。
二、事務（Transaction）
事務的本質：一組原子性操作的集合，保證數據一致性。
ACID 特性：
- Atomicity（原子性）：事務全部成功或全部回滾。
- Consistency（一致性）：事務前后數據滿足業務約束。
- Isolation（隔離性）：并發事務相互隔離。
- Durability（持久性）：事務提交后數據永久存儲。
1. 事務隔離級別
| 隔離級別 | 臟讀 | 不可重復讀 | 幻讀 | 實現機制 |
| READ UNCOMMITTED | ?? | ?? | ?? | 無鎖，直接讀最新數據 |
| READ COMMITTED (RC) | ?? | ?? | ?? | 快照讀（MVCC） |
| REPEATABLE READ (RR) | ?? | ?? | △ | 快照讀 + 間隙鎖（InnoDB） |
| SERIALIZABLE | ?? | ?? | ?? | 所有操作加鎖 |
- InnoDB 默認隔離級別：REPEATABLE READ（通過 MVCC + 間隙鎖解決幻讀）。
- 幻讀：RR 級別下，通過?SELECT ... FOR UPDATE?可能觸發間隙鎖，阻止其他事務插入。
2. 事務實現機制
- Redo Log（重做日志）
- 作用：保證持久性，記錄物理修改（如頁的修改）。
- 寫入流程：事務提交時先寫 redo log（順序寫，高性能），再異步刷盤。
- Undo Log（回滾日志）
- 作用：保證原子性，記錄數據修改前的版本，用于回滾或 MVCC。
- 存儲位置：InnoDB 的 undo tablespace。
- MVCC（多版本并發控制）
- 核心思想：每個事務看到的數據快照版本不同。
- 實現細節：
- 隱藏字段：DB_TRX_ID（事務ID）、DB_ROLL_PTR（回滾指針）。
- ReadView：事務啟動時生成活躍事務ID列表，決定可見性。
- RC vs RR：RC 每次讀生成新 ReadView；RR 使用事務啟動時的 ReadView。
- 鎖機制
- 行鎖：鎖住單行數據（如?SELECT ... FOR UPDATE）。
- 間隙鎖（Gap Lock）：鎖住索引范圍間隙，防止插入（解決幻讀）。
- 臨鍵鎖（Next-Key Lock）：行鎖 + 間隙鎖，鎖住左開右閉區間。
3. 事務最佳實踐
- 控制事務長度：避免長事務占用鎖資源，導致死鎖或性能下降。
- 明確事務邊界：業務邏輯中盡早提交或回滾。
- 合理選擇隔離級別：根據業務需求權衡一致性與性能。
- 死鎖處理：
- 設置?innodb_lock_wait_timeout?控制鎖等待超時。
- 使用?SHOW ENGINE INNODB STATUS?分析死鎖日志。
- 避免隱式提交：如 DDL 語句（ALTER TABLE）會自動提交當前事務。
三、總結
- 索引優化：根據查詢模式設計索引，權衡讀寫性能，避免過度索引。
- 事務設計：選擇合適隔離級別，利用 MVCC 和鎖機制平衡并發與一致性。
- 監控工具：善用?EXPLAIN、SHOW PROFILE、INFORMATION_SCHEMA?等分析性能瓶頸。
