了解 B 树和散列数据结构有助于预测不同的查询如何在索引中使用这些数据结构的不同存储引擎上执行,特别是对于MEMORY
允许您选择 B 树或散列索引的存储引擎。
B 树索引可用于在使用
=
、
>
、
>=
、
<
、
<=
或BETWEEN
运算符的表达式中进行列比较。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
值不是常量。
如果使用and
超过三个字符,MySQL 使用Turbo Boyer-Moore 算法初始化字符串的模式,然后使用该模式更快地执行搜索。
... LIKE
'%
string
%'string
如果已编入索引,则使用使用索引
的搜索col_name
IS
NULLcol_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 树索引,键的任何最左边的前缀都可用于查找行。)