MYSQL性能故障優化利器之索引優化

                       MYSQL性能故障優化利器之索引優化

從性能角度看80%的性能問題是設計出來的，從性能測試問題統計概率來分析，80%的性能問題來源與數據打交道引發的問題，其中SQL語法問題占比比價高，而這類問題大部分是因為檢索數據的方式問題引起的，例如全表掃描、多表關聯設計、大表使用等導致查詢數據慢，而這類問題中優化方式有：語法拆解、優化表連接方式、表物理分區、邏輯分區、合適利用數據庫參數、合理使用不同類型的索引、優化硬件資源、優化業務需求等來提高檢索數據速率。

     而對于測試人員來說主要是通過設計合理的測試場景，通過合理的監控利器，這樣能快速發現問題，把將來生產可能出現的故障，解決在搖籃之中，而有些索引的使用合理性，也只能在測試過程中通過各種調試嘗試，才能知道怎么優化最合理，而這時我們身為測試人員，也可以學習怎么建立合適的索引才能起到最優效果，這也是我們作為測試人員能親自體驗親自優化的，當然我們自己要了解數據庫索引使用原理，才能知道怎么優化，畢竟發現SQL問題容易，解決問題比較難，難得原因在于不了解它們的工作原理。

     那我們作為測試人員既然要做到如何測試才能測試出問題，然后去診斷診斷分析問題，優化問題，從測試角度看我們要了解測試方案的設計、測試模型的分析、測試策略的定制、監控模型設計、測試案例設計、測試結果分析、測試報告編寫等這些簡單易，我們更要學習架構原理、操作系統原理、數據庫原理、JVM原理等目的是了解他們的底層原理才能更有效的發現問題，提供優化解決方案、發現問題是技術行為、解決優化問題是藝術行為。

   具體性能測試實施流程可以參考：https://blog.51cto.com/372550/2068876

索引設計要求：

       1. 適合索引的列是出現在where子句中的列，或者連接子句中指定的列；

       2、表基數較小，索引效果較差，沒有必要在此列建立索引；

       3、 表類型單一的不建議使用索引，例如性別表，男和女

       4、不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。

        5、. 過濾的數據量比較少，一般來說<20%,一般會走索引。20%-40% 可能走索引也可能不走索引，看數據庫成本分析。> 40% 基本不走索引(會全表掃描)

        6、 對索引的字段進行計算時，必須在運算符右側進行計算。也就是 to_char(oc.create_date, 'yyyyMMdd')是沒用的--可以使用函數索引oracle等

那我們本文主要講解的是MYSQL的索引使用原理與效能。

一、什么是索引？為什么要建立索引？

　　　　索引用于快速找出在某個列中有一特定值的行，不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大，查詢數據所花費的時間就越多，如果表中查詢的列有一個索引，MySQL能夠快速到達一個位置去搜索數據文件，而不必查看所有數據，那么將會節省很大一部分時間。

       就像我們中華字典，如果不知道偏旁、拼音，找一個字怎么讀，怎么解釋，需要一頁一頁的、一行一行的查找，直到找到想要的字。這速度多慢可想而知。

  例如，這一張用戶user表，有一千萬條數據，需要查找張三豐、家住武當山、、電話號碼為15900xxxxxx1的個人信息，如果沒有使用索引，那邊需要從表中第一筆記錄開始一筆一筆的遍歷查詢，直找到該條信息為主，如果剛好第一筆是那可能快，如果在表最后一筆呢？如果這時有了索引，那那張子杰根據姓名、電話號碼進行索引檢索，就能夠快的查找出對應的數據，而不用挨筆的遍歷。

 既然知道通過索引就能提高查詢效率，那我們需要了解索引的存儲類型有幾種：BTREE、HASH。

 查看索引類型方法：

 可以使用SHOW INDEX FROM table_name;查看索引詳情：

Table:創建索引的表

Non_unique：表示索引非唯一，1代表 非唯一索引， 0代表 唯一索引，意思就是該索引是不是唯一索引

Key_name：索引名稱　Seq_in_index 表示該字段在索引中的位置，單列索引的話該值為1，組合索引為每個字段在索引定義中的順序(這個只需要知道單列索引該值就為1，組合索引為別的)

Column_name：表示定義索引的列字段，Sub_part：表示索引的長度；　　　　　　　　　　　　　　

Null：表示該字段是否能為空值

Index_type：表示索引類型

主鍵索引 PRIMARY KEY

它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創建主鍵索引。注意：一個表只能有一個主鍵。

唯一索引 UNIQUE

唯一索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一

-創建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

–修改表結構

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

普通索引 INDEX

這是最基本的索引，它沒有任何限制。

–--直接創建索引

CREATE INDEX index_name ON table(column(length))

–--修改表結構的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

–--刪除索引

DROP INDEX index_name ON table

全文索引（FULLTEXT）

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT類型字段上使用全文索引

–添加全文索引

CREATE FULLTEXT INDEX index_content ON article(content)

–修改表結構添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

組合索引

在表中的多個字段組合上創建的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用，使用組合索引時遵循最左前綴集合。

案例說明

具體索引使用情況，案例描述如：https://blog.51cto.com/372550/2089965，MYSQL數據庫服務CPU高問題分析與優化