B树和哈希索引的比较
了解B树和哈希数据结构可以帮助预测不同查询如何对在索引中使用这些数据结构的不同存储引擎执行不同的查询,特别是对于MEMORY
允许您选择B树或哈希索引的存储引擎。
- B树索引特征
- 哈希指数特征
B树索引特征
A B树索引可以在使用表达式中使用的对列的比较=
,>
,>=
,<
,<=
,或BETWEEN
运营商。LIKE
如果to的参数LIKE
是不以通配符开头的常量字符串,则索引也可以用于比较。例如,以下SELECT
语句使用索引:
SELECT *FROM tbl_nameWHERE key_col LIKE 'Patrick%';SELECT *FROM tbl_nameWHERE key_col LIKE 'Pat%_ck%';
在第一条语句中,仅考虑带有的行。在第二条语句中,仅考虑带有的行。'Patrick'<=key_col<'Patricl'
'Pat'<=key_col<'Pau'
以下SELECT
语句不使用索引:
SELECT *FROM tbl_nameWHERE key_col LIKE '%Patrick%';SELECT *FROM tbl_nameWHERE key_col LIKE other_col;
在第一个语句中,该LIKE
值以通配符开头。在第二个语句中,该LIKE
值不是常数。
如果使用且长度超过三个字符,则MySQL使用Turbo Boyer-Moore算法初始化字符串的模式,然后使用该模式更快地执行搜索。... LIKE '%string%'
string
如果使用col_name IS NULL
索引,则使用的搜索会使用col_name
索引。
没有覆盖子句中所有AND
级别的任何索引都WHERE
不会用于优化查询。换句话说,为了能够使用索引,必须在每个AND
组中使用索引的前缀。
以下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
有时,即使索引可用,MySQL也不使用索引。发生这种情况的一种情况是,优化器估计使用索引将需要MySQL访问表中很大比例的行。(在这种情况下,表扫描可能会更快,因为它需要更少的查找。)但是,如果这样的查询LIMIT
仅用于检索某些行,则MySQL仍会使用索引,因为它可以更快地找到索引。几行返回结果。
哈希指数特征
哈希索引与刚刚讨论的索引具有一些不同的特征:
- 它们仅用于使用
=
or<=>
运算符的相等比较(但非常快)。它们不用于比较运算符,例如<
用于查找值范围的运算符。依赖于这种单值查找类型的系统称为“键值存储”;要将MySQL用于此类应用程序,请尽可能使用哈希索引。 - 优化器无法使用哈希索引来加快
ORDER BY
操作速度。(此类型的索引不能用于按顺序搜索下一个条目。) - MySQL无法确定两个值之间大约有多少行(范围优化器使用它来决定使用哪个索引)。如果将
MyISAM
或InnoDB
表更改为哈希索引MEMORY
表,这可能会影响某些查询。 - 仅整个键可用于搜索行。(对于B树索引,键的任何最左边的前缀都可用于查找行。)