今天探討關於HASH格式的索引與Memory引擎
Memory引擎若未指定就是使用HASH格式索引.

先把預計使用的Memory加大.

SET SESSION max_heap_table_size = 1024 * 1024 * 200;

建立使用Memory引擎的Table

CREATE TABLE memory_words(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
word VARCHAR(50) NOT NULL,
UNIQUE INDEX (word)
)ENGINE=Memory;

從第15天建立的million_words輸入資料.

INSERT INTO memory_words(id, word)
SELECT id
     , word
  FROM million_words
 ORDER BY id;

Query OK, 1000000 rows affected (3.34 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

接著啟動profiling

SET PROFILING = 1;

查一下 "橘"

SELECT * FROM memory_words WHERE word = 'orange';

查一下 "檸檬"

SELECT * FROM memory_words WHERE word = 'lemon';

查一下apple開頭的字吧

SELECT * FROM memory_words WHERE word LIKE 'apple%';
找到80筆資料,不列出.

觀察一下上面三道Query的執行時間.

SHOW PROFILES;

+----------+------------+-----------------------------------------------------+
| Query_ID | Duration   | Query                                               |
+----------+------------+-----------------------------------------------------+
|        1 | 0.00027375 | SELECT * FROM memory_words WHERE word = 'orange'    |
|        2 | 0.00025650 | SELECT * FROM memory_words WHERE word = 'lemon'     |
|        3 | 0.08938075 | SELECT * FROM memory_words WHERE word LIKE 'apple%' |
+----------+------------+-----------------------------------------------------+

查橘與檸檬時速度較快,因為是直接根據HASH值去找,而且是單一的.
但是若是用LIKE 搭配 % 方式去找,多花了300多倍的時間.
至於為何用橘,檸檬當例子來查詢呢?可以試試看google 橘檸檬 ,就知道了.