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