MySQL性能故障優(yōu)化利器之索引優(yōu)化

公司主營業(yè)務(wù)：網(wǎng)站設(shè)計制作、成都做網(wǎng)站、移動網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。創(chuàng)新互聯(lián)建站是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊有機(jī)會用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)建站推出鳳泉免費做網(wǎng)站回饋大家。

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

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

     那我們作為測試人員既然要做到如何測試才能測試出問題，然后去診斷診斷分析問題，優(yōu)化問題，從測試角度看我們要了解測試方案的設(shè)計、測試模型的分析、測試策略的定制、監(jiān)控模型設(shè)計、測試案例設(shè)計、測試結(jié)果分析、測試報告編寫等這些簡單易，我們更要學(xué)習(xí)架構(gòu)原理、操作系統(tǒng)原理、數(shù)據(jù)庫原理、JVM原理等目的是了解他們的底層原理才能更有效的發(fā)現(xiàn)問題，提供優(yōu)化解決方案、發(fā)現(xiàn)問題是技術(shù)行為、解決優(yōu)化問題是藝術(shù)行為。

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

索引設(shè)計要求：

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

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

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

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

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

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

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

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

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

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

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

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

 查看索引類型方法：

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

Table:創(chuàng)建索引的表

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

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

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

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

Index_type：表示索引類型

主鍵索引 PRIMARY KEY

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

唯一索引 UNIQUE

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

-創(chuàng)建唯一索引

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

–修改表結(jié)構(gòu)

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

普通索引 INDEX

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

–--直接創(chuàng)建索引

CREATE INDEX index_name ON table(column(length))

–--修改表結(jié)構(gòu)的方式添加索引

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)

–修改表結(jié)構(gòu)添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

組合索引

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

案例說明

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