[TOC] ***** # 13.1 索引 當向一個表中插入一行時，數據庫服務器不會試圖將數據放到表里任何特定的地方。例如，要向 department表增加一行，那么服務器不會依據 dept\_id列的數字順序或者 name 列的字母順序存放該行。相反，服務器只是簡單地將數據存放在文件中下一個可存放 的位置（服務器為每個表預留了一系列空間）。因此，當查詢 department 表時，服務器需要通過檢查表中的每一行來完成查詢。 **表掃描** 為了尋找所有名字以 A 開頭的部門，服務器必須訪問 department 表中的每一行并檢查name 列的內容。如果部門名以 A 開頭，就將該行加入結果集。這種類型的訪問稱為表掃描。  **索引** 類似英語單詞書的索引。 索引是一種以特定順序保存的表。索引不包含實體中的所有數據，包含用于定位表中行的列和這些行的物理位置信息索引的作用就是便捷化檢索表中行和列的子集，不需要檢查表中的每行。 ## 13.1.1 創建索引 再回到 department 表，為 name 列添加索引可以加速任何指定全部或者部分部門名字的 查詢以及 update 或 delete 操作。  有了索引后，如果索引有利于改善查詢，查詢優化器（第 3 章中討論過）就可以選擇索引（假如 department 表中只有 3 行，那么優化器選擇忽略索引而直接讀取整個表可能會更合理）。如果表中的索引不止一個，那么優化器就必須判斷對于特定的 SQL 語句使用哪個索引最有利。  **查看索引** 所有數據庫服務器都允許讀者查看可用索引。 MySQL使用 show 命令查看指定表中的所有索引，例如： ``` SHOW INDEX FROM department ``` 結果顯示 department 表共有兩個索引：一個是 dept\_id 列的索引 PRIMARY，另一個是 name 的索引 dept\_name\_idx  創建表時， MySQL 自動為主鍵列(dept\_id)生成索引，生成索引名PRIMARY。  ***** **刪除索引** mysql:   ***** **唯一索引** department 表中不能存在 兩個相同名字的部門。讀者可以通過 [department.name](http://department.name) 創建唯一索引限制出現重復部門名字。這里的唯一索引起了多重作用，除了提供常規索引的所有好處，還作為一種機制限制索引列出現重復值。無論是插入一行還是修改索引列，數據庫服務器都會檢查唯一索引以判斷該值是否已存在于本表的某一行 **創建唯一索引**  ***** **多列索引** 除了上面涉及的單列索引，讀者還可以創建跨越多列的索引。例如，使用姓氏和名字查找雇員，讀者就可以為這兩列一起創建索引  這個索引在兩種查詢中是有用的：先指定了姓氏，再指定了名字。 * 但它不適合用于只指定客戶名字的查詢中。查找某個人的 電話號碼。如果知道此人的姓名就可以用電話簿快速查到號碼，因為電話簿是先依據姓氏順序，再依據名字順序組織的；如果只知道此人名字就必須瀏覽電話簿中每個條目來查找。 * 因此，在創建多列索引時，讀者必須仔細考慮哪一列作為第一列，哪一列作為第二列等，這樣索引才會盡可能的有用。如果需要保證充分的響應時間，也可以基于不同順序為同一列集創建多列索引。 ## 13.1.2 索引類型 **B 樹索引** MySQL、 Oracle 數據庫和 SQL Server 默認都是 B 樹索引。 B 樹索引以樹結構組織，它有一個或多個分支節點，分支節點又指向單級的葉節點。分支節點用于遍歷樹，葉節點則保存真正的值和位置信息。 **employee.lname 列的 B 樹索引**  如果讀者發起一個查詢，檢索所有姓氏以 G 開頭的雇員，那么服務器將首先查找頂分支節點（稱為根節點），接著順著指針前進到姓氏以 A 到 M 開始的分支節點，然后服務器會在此分支節點內依次查看直至找到姓氏以 G 到 I 開始的葉節點，最后服務器開始讀葉子中的數據直至遇到一個不以 G 開頭的值（此時，這個值是 Hawthorne）。 當向 employee 表中插入、更新和刪除行時，服務器會盡力保持樹的平衡，這樣就不會出現根節點的某一側擁有比另一側多得多的分支節點/葉節點。服務器通過增加或刪除分支節點重新將值分配得更加均勻。通過保持樹的均勻，不需要遍歷多層分支節點，服務器就能夠快速地到達葉節點查找到需要的值。 **位圖索引(如書)** 為了能夠快速檢索某一特定類型（比如支票賬戶、儲蓄賬戶）的所有賬戶，讀者可能希望為 account.product\_cd 列創建索引，但是，由于總共只有 8 種不同的產品，并且有些產品比其他產品受歡迎的多，所以客戶數目的不斷增長會使 B 樹索引很難繼續維持平衡。對于那些包含少量值卻占據了大量行（所謂低基數）的列，應該采用不同的索引策略。 為了更有效地處理這個問題， Oracle 數據庫引入了位圖索引，它為存儲在某列中的每個值生成一個位圖。 account.product\_cd 中數據的位圖索引大致如圖 13-2 所示  未完 **文本索引(如書)** # 13.1.3 如何使用索引 服務器通常首先利用索引快速定位特定表中的行，之后再訪問相關表提取用戶請求的為指定的客戶聚合賬戶余額:  用explain 語句請求MySQL 查詢優化器顯示查詢的執行計劃   * 使用索引 fk\_a\_cust\_id 查找 account 表中滿足 where 子句的行； * 讀取索引后，服務器預計會讀取 account 表的所有 24 行以聚合可用余額數據，這是因為它不知道除了 ID 號為 1、 5、 9 和 11 的客戶外，還可能有其他客戶。 索引 fk\_a\_cust\_id 是由服務器自動生成的又一個索引，但是這次自動生成的原因是外鍵約束而不是主鍵約束（本章稍后作更多介紹）。 fk\_a\_cust\_id 是 account.cust\_id 列的索引， 因此服務器先使用索引定位 account 表中的 ID 為 1、 5、 9 和 11 的客戶，然后訪問這些行，再實現檢索和聚合可用余額數據  * 服務器可以使用索引定位關聯表中的行。 * 或者只要索引包含查詢需要的所有列，服務器可以把索引當做表一樣使用。 ## 13.1.4 索引的不足 每個索引事實上都是一個表。因此，每次在對表添加或者刪除行時，表中的所有索引必須被修改；當更新行時，受到影響的列的任何索引也必須被修改。因此，索引越多，服務器就需要做越多的工作來保持所有模式對象最新，當然，這將會拖慢服務器處理任務的速度。 僅當出現清晰需求時才添加索引。如果有特殊目的需要索引，比如每月例行維護程序，那么讀者可以添加索引，運行程序，然后刪除索引，下次需要時再如此重復一遍。對于數據倉庫來說，用戶在營業期間生成報表和特定查詢時，索引至關重要，但是當數據被徹夜裝載到數據倉庫時，就會出現問題，所以一種常見的做法是：裝載數據前刪除索引，然后在倉庫開放營業前重建它們 **索引策略:** * 確保所有主鍵列被索引（大部分服務器會在創建主鍵約束時自動生成唯一索引）。針對多列主鍵，考慮為主鍵列的子集構建附加索引，或者以與主鍵約束定義不同的順序為所有主鍵列另外生成索引。 * 為所有被外鍵約束引用的列創建索引。 服務器在準備刪除父行時會查找以確保沒有子行存在，為此它發出一個查詢在索引中搜索列中的特定值，如果該列沒有索引， 那么服務器必須掃描整個表。 * 索引那些被頻繁檢索的列。除了短字符串（3～50 個字符）列，大多數日期列也是不錯的候選。