軟體測試 |BTREE索引與HASH索引

MEMORY 存儲引擎的表可以選擇使用 BTREE 索引或者 HASH 索引，兩種不同類型的索引各有其不同的适用範圍。HASH 索引有一些重要的特征需要在使用的時候特别注意，如下所示。

隻用于使用=或<=>操作符的等式比較。

優化器不能使用HASH索引來加速ORDER BY操作。

MySQL不能确定在兩個值之間大約有多少行。如果将一個MyISAM表改為HASH索引的MEMORY表，會影響一些查找的執行效率。

隻能使用整個關鍵字來搜尋一行。

而對于BTREE索引，當使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE'pattern'(其中‘pattern’不可以通配符開始)操作符時，都可以使用相關列上的索引。

下列範圍查詢适用于BTREE索引和HASH索引：

SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);           

下列範圍查詢隻适用于 BTREE 索引：

SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10; 
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'lisa' AND 'simon';           

例如，建立一個和 city 表完全相同的 MEMORY 存儲引擎的表 city_memory：

mysql> CREATE TABLE city_memory ( 
 -> city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
 -> city VARCHAR(50) NOT NULL, 
 -> country_id SMALLINT UNSIGNED NOT NULL, 
 -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP, 
 -> PRIMARY KEY (city_id), 
 -> KEY idx_fk_country_id (country_id) 
 -> )ENGINE=Memory DEFAULT CHARSET=utf8; 
Query OK, 0 rows affected (0.03 sec) 
mysql> insert into city_memory select * from city; 
Query OK, 600 rows affected (0.00 sec) 
Records: 600 Duplicates: 0 Warnings: 0           

當對索引字段進行範圍查詢的時候，隻有 BTREE 索引可以通過索引通路：

mysql> explain SELECT * FROM city WHERE country_id > 1 and country_id < 10 \G 
*************************** 1. row *************************** 
 id: 1 
 select_type: SIMPLE 
 table: city 
 type: range 
possible_keys: idx_fk_country_id 
 key: idx_fk_country_id
key_len: 2 
 ref: NULL 
 rows: 24 
 Extra: Using where 
1 row in set (0.00 sec)           

而 HASH 索引實際上是全表掃描的：

mysql> explain SELECT * FROM city_memory WHERE country_id > 1 and country_id < 10 \G 
*************************** 1. row *************************** 
 id: 1 
 select_type: SIMPLE 
 table: city_memory 
 type: ALL 
possible_keys: idx_fk_country_id 
 key: NULL 
 key_len: NULL 
 ref: NULL 
 rows: 600 
 Extra: Using where 
1 row in set (0.00 sec)           

了解了 BTREE 索引和 HASH 索引不同後，當使用 MEMORY 表的時候，如果是預設建立的 HASH索引，就要注意 SQL 語句的編寫，確定可以使用上索引，如果一定要使用範圍查詢，那麼在建立索引的時候，就應該選擇建立成 BTREE 索引。

搜尋微信公衆号：TestingStudio霍格沃茲的幹貨都很硬核