MYSQL——B-Tree索引与hash索引的比较

B-Tree索引与hash索引的比较

对B-Tree索引和hash索引的数据结构的理解可以帮助我们预测，在它们索引上使用到这些数据结构的存储引擎上不同的查询是如何进行的。特别是使用MEMORY存储引擎的时候，你需要去选择使用B-Tree索引或者书hash索引。

---

B-Tree索引的特点

1、B-Tree索引可使用=, >, >=, <, <=, or BETWEEN 运算符进行列值的比较。B-Tree索引也可以用于LIKE匹配，但LIKE关键字后的字符串常量必须不是以模糊字符开头的字符串常量。例如以下的SELECT语句可以走索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第一条语句中，只要符合'Patrick' <= key_col < 'Patricl'的记录都将被检索到。在第二条语句中，符合'Pat' <= key_col < 'Pau'的记录将被检索。

下面这些SELECT语句不会走索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

在第一条语句里，LIKE的值是一个以模糊字符开始的字符串。在第二条语句里，LIKE的值不是一个常量。

2、如果使用... LIKE '%string%'并且string长于三个字符，MySQL使用Turbo Boyer-Moore算法初始化该字符串模式，然后使用这个初始化好的字符串模式去进行更快的搜索。

3、当一个可空值的列建立过索引后，使用IS NULL关键字是可以走索引的。

4、如果想要使用索引，那么索引的前缀必须在ANDgroup中出现。

下面的WHERE条件会使用到索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面的WHERE条件不会使用到索引：

    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10

5、有时候MySQL在可以使用索引的情况下也不去使用索引。一种是在执行优化器估算到在使用索引的情况下MySQL会需要查询表中很大比例的数据的情况下，全表扫描需要的查询更少的情况下。不过，这样的查询加上LIMIT语句后，MySQL也会使用到索引，因为使用索引可以更快的检索到这几条数据并返回。

---

hash索引的特点

hash索引有一些不同于刚才讨论的特征:

• hash索引仅仅被用于=或<=>等值比较符的比较，速度极快，不能被用于<之类的查找范围值的运算符。这一特性类似与key-value 存储一类的单值查找存储方式，如果应用需要类似的存储，可以使用hash索引。

• 使用hash索引，执行优化器无法对ORDER BY操作进行优化加速，因为hash索引是无序的。

• 使用hash索引，MySQL无法确切的知道两个值之间有多少条数据，而range优化器是需要根据这一点去判断使用哪个索引的。因此，当你的MyISAM或InnoDB表被转化为hash索引的MEMORY表时，查询会收到影响。

• 使用hash索引，只有完整的索引键在语句中使用时，才能被使用。使用B-Tree索引时，遵循最左前缀匹配的原则，最接近的索引被使用。