MySQL添加索引特點及優化問題

Posted on by

一、索引的特點

當MySQL單表記錄數過大時，增刪改查性能都會急劇下降。MySQL索引的建立對於MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。除非單表數據未來會一直不斷上漲，否則不要一開始就考慮拆分，拆分會帶來邏輯、部署、運維的各種復雜度。一般以整型值為主的表在千萬級以下，字符串為主的表在五百萬以下是沒有太大問題的，而事實上很多時候MySQL單表的性能依然有不少優化空間，甚至能正常支撐千萬級以上的數據量。

索引優勢和劣勢：

  • 優勢： 大大減少瞭服務器需要掃描的數據量，可以幫助服務器避免排序和臨時表，實現快速檢索，將隨機I/O變成順序I/O，減少I/O次數，加快檢索速度；根據索引分組和排序，可以加快分組和排序；
  • 劣勢： 索引本身也是表，因此會占用存儲空間，一般來說，索引表占用的空間的數據表的1.5倍；索引表的維護和創建需要時間成本，這個成本隨著數據量增大而增大；構建索引會降低數據表的修改操作（刪除，添加，修改）的效率，因為在修改數據表的同時還需要修改索引表；創建索引時需要對表加鎖，因此實際操作中需要在業務空閑期間進行。

二、索引類型

Mysql目前主要有以下幾種索引類型：FULLTEXT，HASH，BTREE，RTREE。

1.FULLTEXT

即為全文索引，目前隻有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前隻有 CHAR、VARCHAR ，TEXT 列上可以創建全文索引。

全文索引並不是和MyISAM一起誕生的，它的出現是為瞭解決WHERE name LIKE “%word%"這類針對文本的模糊查詢效率較低的問題。

FULLTEXT（全文）索引，僅可用於MyISAM和InnoDB

  • 對於較大的數據集，把數據添加到一個沒有FULLTEXT索引的表，然後添加FULLTEXT索引的速度比把數據添加到一個已經有FULLTEXT索引的表快。
  • 5.6版本前的MySQL自帶的全文索引隻能用於MyISAM存儲引擎，如果是其它數據引擎，那麼全文索引不會生效。5.6版本之後InnoDB存儲引擎開始支持全文索引。
  • 在MySQL中，全文索引支隊英文有用，目前對中文還不支持。5.7版本之後通過使用ngram插件開始支持中文。
  • 在MySQL中，如果檢索的字符串太短則無法檢索得到預期的結果，檢索的字符串長度至少為4字節，此外，如果檢索的字符包括停止詞，那麼停止詞會被忽略。

2.HASH

哈希索引用索引列的值計算該值的hashCode，然後在hashCode相應的位置存執該值所在行數據的物理位置，因為使用散列算法，因此訪問速度非常快，但是一個值隻能對應一個hashCode，而且是散列的分佈方式。由於HASH的唯一（幾乎100%的唯一）及類似鍵值對的形式，很適合作為索引。

HASH索引可以一次定位，不需要像樹形索引那樣逐層查找,因此具有極高的效率。但是，這種高效是有條件的，即隻在“=”和“in”條件下高效，對於范圍查詢、排序及組合索引仍然效率不高。

3.BTREE

BTREE(B+TREE)索引就是一種將索引值按一定的算法，存入一個樹形的數據結構中（二叉樹），每次查詢都是從樹的入口root開始，依次遍歷node，獲取leaf。由於BTREE非葉子節點不存儲數據（data），因此所有的數據都要查詢至葉子節點，而葉子節點的高度都是相同的，因此所有數據的查詢速度都是一樣的。這是MySQL裡默認和最常用的索引類型。

4.RTREE

RTREE在MySQL很少使用，僅支持geometry數據類型，支持該類型的存儲引擎隻有MyISAM、BDb、InnoDb、NDb、Archive幾種。

相對於BTREE，RTREE的優勢在於范圍查找。

三、索引種類

  • 普通索引：僅加速查詢。
  • 唯一索引：加速查詢 + 列值唯一（可以有null）。
  • 主鍵索引：加速查詢 + 列值唯一（不可以有null）+ 表中隻有一個。
  • 組合索引：多列值組成一個索引，專門用於組合搜索，其效率大於索引合並，遵循“最左前綴”原則，把最常用作為檢索或排序的列放在最左，依次遞減，組合索引相當於建立瞭col1,col1col2,col1col2col3三個索引，而col2或者col3是不能使用索引的。
  • 全文索引：對文本的內容進行分詞，進行搜索。

四、索引的使用策略

1.什麼時候要使用索引？

主鍵自動建立唯一索引；經常作為查詢條件在WHERE或者ORDER BY；語句中出現的列要建立索引；作為排序的列要建立索引；查詢中與其他表關聯的字段，外鍵關系建立索引高並發條件下傾向組合索引；用於聚合函數的列可以建立索引，例如使用瞭max(column_1)或者count(column_1)時的column_1就需要建立索引。

2.什麼時候不要使用索引？

經常增刪改的列不要建立索引；有大量重復的列不建立索引；表記錄太少不要建立索引。隻有當數據庫裡已經有瞭足夠多的測試數據時，它的性能測試結果才有實際參考價值。如果在測試數據庫裡隻有幾百條數據記錄，它們往往在執行完第一條查詢命令之後就被全部加載到內存裡，這將使後續的查詢命令都執行得非常快–不管有沒有使用索引。隻有當數據庫裡的記錄超過瞭1000條、數據總量也超過瞭MySQL服務器上的內存總量時，數據庫的性能測試結果才有意義。

3.索引失效的情況?

在組合索引中不能有列的值為NULL，如果有，那麼這一列對組合索引就是無效的；在一個SELECT語句中，索引隻能使用一次，如果在WHERE中使用瞭，那麼在ORDER BY中就不要用瞭；LIKE操作中，’%aaa%'不會使用索引，也就是索引會失效，但是’aaa%'可以使用索引；在索引的列上使用表達式或者函數會使索引失效，例如：

select * from table where ceate_time > unix_timestamp(curdate());

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成當前時間由程序作為參數傳入：

select * from table where ceate_time > 1524561911;
  • 其它通配符同樣，也就是說，在查詢條件中使用正則表達式時，隻有在搜索模板的第一個字符不是通配符的情況下才能使用索引；
  • 在查詢條件中使用不等於，包括<符號、>符號和！=會導致索引失效。特別的是：如果對主鍵索引使用！=則不會使索引失效，如果對主鍵索引或者整數類型的索引使用<符號或者>符號也不會使索引失效。（不等於，包括<符號、>符號和！，如果占總記錄的比例很小的話，也不會失效）；
  • 在查詢條件中使用IS NULL或者IS NOT NULL會導致索引失效；
  • 字符串不加單引號會導致索引失效。更準確的說是類型不一致會導致失效，比如字段mobile是字符串類型的，使用WHERE mobile=99999 則會導致失敗，應該改為WHERE mobile=‘99999’；
  • 在查詢條件中使用OR連接多個條件會導致索引失效，除非OR鏈接的每個條件都加上索引，這時應該改為兩次查詢，然後用UNION ALL連接起來；
  • 如果排序的字段使用瞭索引，那麼select的字段也要是索引字段，否則索引失效。特別的是：如果排序的是主鍵索引則select * 也不會導致索引失效；
  • 盡量不要包括多列排序，如果一定要，最好為這隊列構建組合索引。

4.mysql查詢優化?

字段:

  • 盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數類型而非INT，如果非負則加上UNSIGNED；
  • VARCHAR的長度隻分配真正需要的空間；
  • 使用枚舉或整數代替字符串類型；
  • 盡量使用TIMESTAMP而非DATETIME；
  • 單表不要有太多字段，建議在20以內；
  • 避免使用NULL字段，很難查詢優化且占用額外索引空間；
  • 用整型來存IP。

索引：

  • 索引並不是越多越好，要根據查詢有針對性的創建，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據EXPLAIN來查看是否用瞭索引還是全表掃描；
  • 應盡量避免在WHERE子句中對字段進行NULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描；
  • 值分佈很稀少的字段不適合建索引，例如“性別”這種隻有兩三個值的字段；
  • 字符字段隻建前綴索引；
  • 字符字段最好不要做主鍵；
  • 不用外鍵，由程序保證約束；盡量不用UNIQUE，由程序保證約束；
  • 使用多列索引時主意順序和查詢條件保持一致，同時刪除不必要的單列索引。

查詢sql：

  • 可通過開啟慢查詢日志來找出較慢的SQL；
  • 不做列運算：SELECT id WHERE age + 1 = 10，任何對列的操作都將導致表掃描，它包括數據庫教程函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊；
  • sql語句盡可能簡單：一條sql隻能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫；
  • 不用SELECT *；
  • OR改寫成IN：OR的效率是n級別，IN的效率是log(n)級別，IN的個數建議控制在200以內；
  • 不用函數和觸發器，在應用程序實現；
  • 避免%xxx式查詢，’%xxx%'不會使用索引,可以使用全文索引，然後：
  • SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
  • 少用JOIN；
  • 使用同類型進行比較，比如用’123’和’123’比，123和123比；
  • 組合索引要遵循最做前綴原則，排序分組頻率最高的列放在最左邊，以此類推；
  • 盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描；
  • 對於連續數值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5；
  • 列表數據不要拿全表，要使用LIMIT來分頁，每頁數量也不要太大；
  • 使用短索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字符內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

5.索引的常見問題

1.索引是幹什麼的？

索引用於快速找出在某個列中有一特定值的行。不使用索引，mysql必須從第一條記錄開始讀完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的列有一個索引，mysql能快速到達一個位置搜尋到數據文件的中間，沒有必要查看所有數據。

大多數mysql的索引(primary key、index、unique、fulltext)在B樹中存儲，隻是空間列類型的索引使用R樹，並且memory表還支持hash索引。

2.索引好復雜，我該怎麼理解索引，有沒有一個更形象的例子？

索引就像是一本書的目錄。

3.索引越多越好?

大多數情況下，索引能大幅提高查詢效率。但是:數據變更(增刪改)都需要維護索引，因此更多索引意味著更多維護成本；也意味著需要更多控件空間(一本書100頁，卻有50頁目錄？)；過小的表，建索引可能會更慢。

4.索引的字段類型問題

text類型，也可建索引(需要指定長度)；MyISAM存儲引擎長度綜合不能超過1000字節；用來篩選的值盡量保持和索引列同樣的數據類型。

5.like能用到索引？

盡量減少like查詢，但是也不是絕對不可用，'xxx%'是可以用到索引的。除瞭like，以下操作符也可以用到索引：

<，<=，=，>，>=，between，in

這些用不到索引：

<>，not in，!=

6.什麼樣的字段不適合建索引？

列的值唯一性太小(比如性別，類型)，不適合建索引。(什麼叫大小？一般來說，同值的數據超過表的15%，那就沒有必要建索引瞭)更新非常頻繁的數據不適合建索引。

7.一次查詢能用多個索引？

不能

8.多列查詢該如何建索引？

一次查詢隻能用到一個索引， a列建索引還是b列建索引？誰的區分度（同值的少）更高，建誰！當然，聯合索引也是個不錯的方案。

9.聯合索引的問題

-- 命中col1、col2聯合索引
select col1,col2 from test where col1 = 'xxx';
-- 不能命中col1、col2聯合索引
select col1,col2 from test where col2 = 'xxx';

所以大多數情況下，有col1、col2索引瞭，就不用再去建col1索引瞭

10.哪些常見的情況不能用到索引？

like '%xxx'
not in
!=

對列進行函數運算，如：

where md5(password) = "xxx"

存瞭數值的字符串類型字段(如手機號)，查詢是記得不要丟掉值的引號，否則無法命中索引：

select * from test where mobile = 13800002222;

如果mobile字段是char或者varchar類型，則上面查詢無法命中索引,應為：

select * from test where mobile = '13800002222';

11.NULL的問題

Null會導致索引形同虛設，所以在設計表結構應避免NULL的存在。
可用其他方式來表達，比如-1。

到此這篇關於MySQL添加索引特點及優化問題的文章就介紹到這瞭,更多相關MySQL索引優化內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：