B 树索引可以用于使用 =、>、>=、<、<= 或 BETWEEN 运算符的列比较。 如果 LIKE 的参数是一个不以通配符字符开头的常量字符串，索引也可以用于 LIKE 比较。 例如，以下 SELECT 语句使用索引：A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes:

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' 的行。In the first statement, only rows with 'Patrick' <= key_col < 'Patricl' are considered. In the second statement, only rows with 'Pat' <= key_col < 'Pau' are considered.

以下 SELECT 语句不使用索引：The following SELECT statements do not use indexes:

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

在第一个语句中，LIKE 值以通配符字符开头。在第二个语句中，LIKE 值不是常量。In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.

如果你使用 ... LIKE '%string%' 并且 string 长度超过三个字符，MySQL 会使用 Turbo Boyer-Moore 算法 初始化字符串的模式，然后使用该模式更快地执行搜索。If you use ... LIKE '%string%' and string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then uses this pattern to perform the search more quickly.

如果 col_name 有索引，使用 col_name IS NULL 进行的搜索将会使用索引。A search using col_name IS NULL employs indexes if col_name is indexed.

任何未跨越AND子句中所有级别的索引都不能用于优化查询。换句话说，要能够使用索引，索引的前缀必须在每个WHERE组中使用。AND对于Any index that does not span all AND levels in the WHERE clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every AND group.

以下 WHERE 子句会使用索引：The following WHERE clauses use indexes:

... 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 子句 不会 使用索引：These WHERE clauses do not use indexes:

    /* 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 仍会使用索引，因为它可以更快地找到要在结果中返回的少量行。Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.

哈希索引与刚才讨论的索引有些不同的特性：Hash indexes have somewhat different characteristics from those just discussed: