MySQL索引背後的之使用原則及最佳化(1)

MySQL的最佳化主要分為結構最佳化(Scheme optimization)和查詢最佳化(Query optimization)。本章討論的高效能索引策略主要屬於結構最佳化範疇。本章的內容完全基於上文的理論基礎，實際上一旦理解了索引背後的機制，那麼選擇高效能的策略就變成了純粹的推理，並且可以理解這些策略背後的邏輯。

樣本資料庫

為了討論索引策略，需要一個資料量不算小的資料庫作為樣本。本文選用MySQL官方文檔中提供的樣本資料庫之一：employees。這個資料庫關係複雜度適中，且資料量較大。是這個資料庫的E-R關係圖(引用自MySQL官方手冊)：

圖12

MySQL官方文檔中關於此資料庫的頁面為http://dev.mysql.com/doc/employee/en/employee.html。裡面詳細介紹了此資料庫，並提供了和匯入方法，如果有興趣匯入此資料庫到自己的MySQL可以參考文中內容。

最左首碼原理與相關最佳化

高效使用索引的首要條件是知道什麼樣的查詢會使用到索引，這個問題和B+Tree中的“最左首碼原理”有關，下面通過例子說明最左首碼原理。

這裡先說一下聯合索引的概念。在上文中，我們都是假設索引只引用了單個的列，實際上，MySQL中的索引可以以一定順序引用多個列，這種索引叫做聯合索引，一般的，一個聯合索引是一個有序元組，其中各個元素均為資料表的一列，實際上要嚴格定義索引需要用到關係代數，但是這裡我不想討論太多關係代數的話題，因為那樣會顯得很枯燥，所以這裡就不再做嚴格定義。另外，單列索引可以看成聯合索引元素數為1的特例。

以employees.titles表為例，下面先查看其上都有哪些索引：

 

  
SHOW INDEX FROM employees.titles; 
  
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+ 
  
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type | 
  
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+ 
  
| titles |          0 | PRIMARY  |            1 | emp_no      | A         |        NULL |      | BTREE      | 
  
| titles |          0 | PRIMARY  |            2 | title       | A         |        NULL |      | BTREE      | 
  
| titles |          0 | PRIMARY  |            3 | from_date   | A         |      443308 |      | BTREE      | 
  
| titles |          1 | emp_no   |            1 | emp_no      | A         |      443308 |      | BTREE      | 
  
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+ 
 

從結果中可以到titles表的主索引為<emp_no, title, from_date>，還有一個輔助索引<emp_no>。為了避免多個索引使事情變複雜MySQL的SQL最佳化器在多索引時行為比較複雜），這裡我們將輔助索引drop掉：

 

  
ALTER TABLE employees.titles DROP INDEX emp_no; 
 

這樣就可以專心分析索引PRIMARY的行為了。