索引的類型 B-Tree索引 B-Tree 索引 通常意味著所有的值都是按順序存儲的，並且每一個葉子頁到根的距離相同。 B-Tree 索引 能夠加快訪問數據的速度，存儲引擎不再需要進行全表掃描來獲取需要的數據，取而代之的是從索引的根節點開始搜索。 B-Tree 索引 適用於全鍵值、鍵值範圍或鍵首碼查 ...
- 索引的類型
- B-Tree索引
- B-Tree 索引 通常意味著所有的值都是按順序存儲的，並且每一個葉子頁到根的距離相同。
- B-Tree 索引 能夠加快訪問數據的速度，存儲引擎不再需要進行全表掃描來獲取需要的數據，取而代之的是從索引的根節點開始搜索。
- B-Tree 索引 適用於全鍵值、鍵值範圍或鍵首碼查找（最左首碼原則）。
- 哈希索引
- 哈希索引 基於哈希表實現，只有精確匹配索引所有列的查詢才有效。
- 哈希索引 是Memory引擎表的預設索引類型，但Memory同時也支持B-Tree索引。
- 哈希索引 自身只需存儲對應的哈希值和行指針，而不存儲欄位值，所以索引的結構十分緊湊，這也讓哈希索引查找的速度非常快。
- 哈希索引 數據並不是按照索引值順序存儲的，所以無法用於排序。
- 哈希索引 不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的全部內容來計算哈希值的。例如數據列（A，B）上建立索引，如果查詢只有數據列A，則無法使用該索引。
- 哈希索引 不支持任何範圍查詢，如WHERE score > 60。
- 哈希索引 只支持等值比較查詢，包括=、IN()、<=>（註意<>和<=>是不同的操作）。
- 介紹一個使用場景：如需要存儲大量的URL，並需要根據URL進行搜索查找。如果使用B-Tree來存儲URL，存儲的內容就會非常大，因為URL本身很長。
- 創建表
1 mysql> CREATE TABLE TB3 ( 2 -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, 3 -> url VARCHAR(255) NOT NULL, 4 -> url_crc INT UNSIGNED NOT NULL DEFAULT 0, 5 -> PRIMARY KEY(id),
6 -> KEY IDX(url_crc) 7 -> ); - 創建觸發器
mysql> DELIMITER // mysql> CREATE TRIGGER TB3_CRC_INS BEFORE INSERT ON TB3 FOR EACH ROW BEGIN SET NEW.url_crc=CRC32(NEW.url);
-> END;
-> // mysql> CREATE TRIGGER TB3_CRC_UPD BEFORE UPDATE ON TB3 FOR EACH ROW BEGIN SET NEW.url_crc=CRC32(NEW.url);
-> END;
-> // mysql> DELIMITER ; - 插入或更新數據
-
1 mysql> INSERT INTO TB3(url) VALUES('http://www.mysql.com'); 2 mysql> SELECT * FROM TB3; 3 +----+----------------------+------------+ 4 | id | url | url_crc | 5 +----+----------------------+------------+ 6 | 1 | http://www.mysql.com | 1560514994 | 7 +----+----------------------+------------+ 8 9 mysql> UPDATE TB3 SET url="https://www.mysql.com" WHERE id=1; 10 mysql> SELECT * FROM TB3; 11 +----+-----------------------+------------+ 12 | id | url | url_crc | 13 +----+-----------------------+------------+ 14 | 1 | https://www.mysql.com | 1053537447 | 15 +----+-----------------------+------------+
查詢（可以看出ref: const，已經是最好的級別了），有同學問為什麼在WHERE條件中不直接使用一個url_crc作為篩選條件，因為一旦出現哈希衝突，另一個字元串的哈希值也恰好一樣的時候，只是用url_crc來來查詢是無法工作的，所以要避免衝突問題，必須在WHERE中帶入哈希值和對應的列值。1 mysql> EXPLAIN SELECT * FROM TB3 WHERE url_crc=CRC32('https://www.mysql.com') AND url="https://www.mysql.com"\G 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB3 6 partitions: NULL 7 type: ref 8 possible_keys: IDX 9 key: IDX 10 key_len: 4 11 ref: const 12 rows: 1 13 filtered: 100.00 14 Extra: Using where
PS：如果採用這種方式，不要使用SHA1()和MD5()作為哈希函數，因為這兩個函數計算出來的哈希值是非常長的字元串，會浪費大量空間，比較時也會更慢。 但如果數據表非常大，CRC32會出現大量的哈希衝突，可以自己實現一個簡單的64位哈希函數，如SELECT CONV(RIGHT(MD5("https://www.mysql.com"), 16), 16, 10) AS HASH64;
- 空間數據索引
- MyISAM表支持空間索引，可以用作地理數據存儲。
- MySQL的GIS支持並不完善，所以大部分人都不會使用該特性。
- 空間索引會從所有維度來索引數據，和B-Tree不同，這類索引無須首碼查詢。
- 必須使用MySQL的GIS相關函數如MBRCONTAINS()等來維護數據。
- 全文索引
- 全文索引是一種特殊類型的索引，它查找的是文本中的關鍵詞，而不是直接比較索引中的值。
- 全文索引使用與MATCH AGAINST操作，而不是普通的WHERE條件操作。
- 其他索引
- TokuDB 使用分形樹索引，既有B-Tree的很多優點，又避免了B-Tree的一些缺點。
- ScaleDB 使用Patricia tries。
- InfiniDB 和 Infobright 使用了一些特殊的數據結構來優化某些特殊的查詢。
- B-Tree索引
