當前位置: 首頁> 學習園地

mysql索引優化技巧分享--full-text、btree、hash、rtree

2019-10-10 09:36:23更新

概述

目前大部分數據庫系統及文件系統都采用B-Tree(B樹)或其變種B+Tree(B+樹)作為索引結構。B+Tree是數據庫系統實現索引的首選數據結構。在MySQL中,索引屬于存儲引擎級別的概念,不同存儲引擎對索引的實現方式是不同的。今天主要簡單講下mysql的幾個索引類型,下面一起看看吧~

 

01

MySQL索引類型

mysql里目前只支持4種索引,分別是:full-text,b-tree,hash,r-tree

b-tree索引應該是mysql里最廣泛的索引了,除了archive基本所有的存儲引擎都支持它.

1、full-text索引

full-text在mysql里僅有myisam支持它,而且支持full-text的字段只有char、varchar、text數據類型。

full-text主要是用來代替like "%***%"效率低下的問題。

強烈注意:MySql自帶的全文索引只能用于數據庫引擎為MYISAM的數據表,如果是其他數據引擎,則全文索引不會生效。此外,MySql自帶的全文索引只能對英文進行全文檢索,目前無法對中文進行全文檢索。如果需要對包含中文在內的文本數據進行全文檢索,我們需要采用Sphinx(斯芬克斯)/Coreseek技術來處理中文。

ps:目前,使用MySql自帶的全文索引時,如果查詢字符串的長度過短將無法得到期望的搜索結果。MySql全文索引所能找到的詞默認最小長度為4個字符。另外,如果查詢的字符串包含停止詞,那么該停止詞將會被忽略。

如果可能,請盡量先創建表并插入所有數據后再創建全文索引,而不要在創建表時就直接創建全文索引,因為前者比后者的全文索引效率要高。

2、b-tree索引

b-tree在myisam里的形式和innodb稍有不同

innodb里,有兩種形態:一是primary key形態,其leaf node里存放的是數據,而且不僅存放了索引鍵的數據,還存放了其他字段的數據。二是secondary index,其leaf node和普通的b-tree差不多,只是還存放了指向主鍵的信息.

采用InnoDB引擎的數據存儲文件有兩個,一個定義文件,一個數據文件。若建索引的字段不是主鍵ID,則對該字段建索引,然后在葉子節點中存儲的是該記錄的主鍵,然后通過主鍵索引來找到對應的記錄

而在myisam里,主鍵和其他的并沒有太大區別。不過和innodb不太一樣的地方是在myisam里,leaf node里存放的不是主鍵的信息,而是指向數據文件里的對應數據行的信息。MyIsam中索引和數據分別存放在不同的文件,所以在索引樹中的葉子節點中存放的數據是該索引對應的數據記錄的地址,由于數據與索引不在一起,所以MyIsam是非聚簇索引。

3、hash索引

目前應該只有memory和ndb cluster支持這種索引。hash索引由于其結構,所以在每次查詢的時候直接一次到位,不像b-tree那樣一點點的前進。所以hash索引的效率高于b-tree,簡單地說,哈希索引就是采用一定的哈希算法,把鍵值換算成新的哈希值,檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查找,只需一次哈希算法即可立刻定位到相應的位置,速度非常快。

hash也有缺點,主要如下:

(1)由于存放的是hash值,所以僅支持<=>以及in操作.

(2)hash索引無法通過操作索引來排序,這是因為存放的時候經過hash計算,但是計算的hash值和存放的不一定相等,所以無法排序.

(3)在組合所以里,無法對部分使用索引.

(4)不能避免全表掃描,只是由于在memory表里支持非唯一值hash索引,就是不同的索引鍵,可能存在相同的hash值.

(5)當存在大量相同hash值得時候,hash索引的效率會變低。

4、r-tree索引

myisam支持空間索引,可以用作地理數據存儲,R-tree無須前綴索引。空間索引會從所有維度來索引數據。查詢時,可以有效地使用任意維度來組合查詢。

不過r-tree在mysql很少使用,僅支持geometry數據類型,支持該類型的存儲引擎只有myisam、bdb、innodb、ndb、archive幾種。

相對于b-tree,r-tree的優勢在于范圍查找。

02

mysql里sql語句注意點

1. myisam里所有鍵的長度僅支持1000字節,innodb是767

2. blob和text字段僅支持前綴索引

3. 使用!=以及<>不等于的時候,mysql不使用索引

4. 當在字段時候函數的時候,mysql無法使用索引;在join時條件字段類型不一致的時候,mysql無法使用索引;在組合索引里使用非第一個索引時也不使用索引.

5. 在使用like的時候,以%開頭,即"%***"的時候無法使用索引;在使用or的時候,要求or前后字段都有索引

有時候mysql query optimizer會認為使用索引并不是最優計劃,所以不使用索引。可以在sql語句里可以用use,force index,當然有時候使用也不會比不用快,所以需要忽略掉index方法是ignore index.

03

幾個技巧

1. 強制連接順序: STRAIGHT_JOIN

由上面的SQL語句可知,通過STRAIGHT_JOIN強迫MySQL按TABLE1、TABLE2的順序連接表。如果你認為按自己的順序比MySQL推薦的順序進行連接的效率高的話,就可以通過STRAIGHT_JOIN來確定連接順序。

2. 強制使用臨時表: SQL_BUFFER_RESULT

當我們查詢的結果集中的數據比較多時,可以通過SQL_BUFFER_RESULT,選項強制將結果集放到臨時表中,這樣就可以很快地釋放MySQL的表鎖(這樣其它的SQL語句就可以對這些記錄進行查詢了),并且可以長時間地為客戶端提供大記錄集。

3. 分組使用臨時表 SQL_BIG_RESULT和SQL_SMALL_RESULT

一般用于分組或DISTINCT關鍵字,這個選項通知MySQL,如果有必要,就將查詢結果放到臨時表中,甚至在臨時表中進行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多,很少使用。

首頁 課程設置 師資力量 學習園地 就業園地 關于我們

地址:哈爾濱市南崗區哈西大街西城紅場寫字樓B塔15樓

咨詢熱線:0451-51062021

版權所有 ? 2008-2019 哈爾濱市南崗區愛尚職業技能培訓學校

黑ICP備17001119號-2