MySQL資料庫最佳化（四）－MySQL索引優化

一、索引基礎
索引類型：
     1、一般索引： 建立任何資料型態中       
     2、唯一性指標：限制索引值必須唯一
     3、全文索引：只能建立到char、varchar、text類型欄位上，主要用於提高文字查詢速度。 MyISAM引擎支援。
     4、單一欄位索引：給表中單一欄位建立索引
     5、多列索引：建立一個欄位索引
     6、空間索引：使用spatial參數建立，提供系統取得資料的效率
索引的基本操作：

CREATE TABLE t_user1(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX (userName)  
             );
             
CREATE TABLE t_user2(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     UNIQUE INDEX index_userName(userName)
             );
           
CREATE TABLE t_user3(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX index_userName_password(userName,PASSWORD)//多列索引
             );
             
CREATE  INDEX index_userName ON t_user4(userName);--对已经创建的表指定索引


CREATE  UNIQUE INDEX index_userName ON t_user4(userName);


CREATE  INDEX index_userName_password ON t_user4(userName,PASSWORD);


ALTER TABLE t_user5 ADD INDEX index_userName(userName);--修改索引


ALTER TABLE t_user5 ADD UNIQUE INDEX index_userName(userName);


ALTER TABLE t_user5 ADD INDEX index_userName_password(userName,PASSWORD);


DROP INDEX index_userName ON t_user5;


DROP INDEX index_userName_password ON t_user5;

     新增索引可以加速查詢效率，避免全表資料查詢，取而代之的是透過查找索引再找到目的資料。 select t.name from user t where t.id=5 ，如果在actor_id列建立索引，則mysql使用該索引找到id=5的行，也就是說先在索引上按值進行查找，然後傳回所有包含該值的資料行。
     索引可以包含一個或多列，如果索引包含多個列，那列的順序也非常重要，因為mysql只能有效率的使用索引的最左前綴列。而索引的建立和維護也需要佔用系統資源，這涉及如何建立高效率的索引來提高查詢效率。
二、mysql的索引類型
     一般來說，資料索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。這樣的話，索引查找過程中就要產生磁碟I/O消耗，相對於記憶體訪問，所以索引的結構組織要盡量減少查找過程中磁碟I/O的訪問次數。這就要求一個優質的資料結構整理、儲存資料庫索引。
     一般索引類型皆是利用了資料結構演算法，例如B-Tree既是mysql中索引類型，也是動態查找樹：二元查找樹（Binary Search Tree），平衡二元查找樹（Balanced Binary Search Tree） ，紅黑樹(Red-Black Tree )，B-tree/B+-tree/ B*-tree (B~Tree)的二元樹結構。
     由於MySQL這些索引類型都是基於資料結構演算法的實現，因此一般開發開始在基礎之上應用。根據不同的資料結構所建立的索引，應用程式也有所不同。

三、索引使用常見誤解 
1、獨立列索引
     錯誤使用：select t.name from user t where t.age+1=5 ，對於這個條件age+1=5 ，mysql無法自動解析，即使對age列創建了索引，但mysql在查詢時也不會使用索引查詢，依舊全表掃描。
     錯誤使用：select t.name  from user t where TO_DAYS(`CURRENT_DATE`())-TO_DAYS(date_col)<=10;  同上
     正確原則：總是將索引欄位放到比較符的單獨一邊。
2、多列索引
     常見錯誤：沒有列建立獨立的索引，或依照錯誤的順序建立多列索引，或索性直接把where條件中的列都建立索引。
     正確使用：針對不同的索引類型選擇適當的索引順序
     例如select t.name from user t where t.staffId= 2 and custom_id=7;
應對上面的查詢，是應該建立一個（staffId，custom_id）還是顛倒一下這兩列順序。

以B-Tree為例，索引列的順序表示索引會先依照最左列進行排序，由左至右。一般將篩選條件頻率最高的欄位放在索引最前面是明智的。這樣設計的索引能夠最快的過濾出所需的行。

四、總結

     查看之前做的一個java雲端平台項目，使用的ORM框架，當時項目出現級聯查詢緩慢，故結合了JDBC+ORM組合形式編程，現在查看幾個數據庫的表設計，對於索引優化這塊還需要再應用，一般創建的索引都僅使用了單列索引，使用主鍵創建；這樣做沒什麼大問題，但如果出現了查詢耗時問題時，表結構優化、索引優化、查詢優化就需要齊頭並進，但依靠ORM框架重新選型或再優化，是無法解決問題的。

 以上就是MySQL資料庫最佳化（四）－MySQL索引優化 的內容，更多相關內容請關注PHP中文網（www.php.cn）！

#