快速查找匹配 WHERE 子句的行。To find the rows matching a WHERE clause quickly.

将行从考虑中排除。如果在多个索引之间有选择，MySQL 通常使用能找到最少行数的索引（最选择性强的索引）。To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index).

如果表具有多列索引，优化器可以使用索引的任何最左前缀来查找行。例如，如果您在 (col1, col2, col3) 上有三列索引，则可以在 (col1)、(col1, col2) 和 (col1, col2, col3) 上进行索引搜索。有关更多信息，请参见 第 10.3.6 节，“多列索引”。 If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3). For more information, see Section 10.3.6, “Multiple-Column Indexes”.

在执行连接查询时从其他表中检索行。如果列声明为相同的类型和大小，MySQL 可以更高效地使用索引。在这种情况下，如果 VARCHAR 和 CHAR 声明为相同大小，则它们被认为是相同的。例如，VARCHAR(10) 和 CHAR(10) 是相同大小，但 VARCHAR(10) 和 CHAR(15) 则不相同。To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. For example, VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.

对于非二进制字符串列之间的比较，两个列应使用相同的字符集。例如，将 utf8mb4 列与 latin1 列进行比较会导致无法使用索引。For comparisons between nonbinary string columns, both columns should use the same character set. For example, comparing a utf8mb4 column with a latin1 column precludes use of an index.

不同列的比较（例如将字符串列与时间列或数值列比较）可能会阻止使用索引，如果值在不进行转换的情况下无法直接比较。对于数值列中的某个值，例如 1，它可能与字符串列中的多个值相等，例如 '1'、' 1'、'00001' 或 '01.e1'。这排除了对字符串列使用任何索引的可能。Comparison of dissimilar columns (comparing a string column to a temporal or numeric column, for example) may prevent use of indexes if values cannot be compared directly without conversion. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1'. This rules out use of any indexes for the string column.

要查找特定索引列MIN()或MAX()的值key_col。这由一个预处理器优化，该预处理器会检查你是否在索引中key_col之前的所有键部分上使用了WHERE key_part_N = constant。在这种情况下，MySQL 对每个MIN()或MAX()表达式执行单次键查找，并将其替换为常量。如果所有表达式都被替换为常量，查询会立即返回。例如：To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example:

SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;

如果对表进行排序或分组，并且排序或分组是在可用索引的最左前缀上进行的（例如，ORDER BY key_part1, key_part2）。如果所有关键部分后面都跟有DESC，则按相反顺序读取键。（或者，如果索引是降序索引，则按正向顺序读取键。）参见 第 10.2.1.16 节，“ORDER BY 优化”，第 10.2.1.17 节，“GROUP BY 优化”，以及 第 10.3.13 节，“降序索引”。To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable index (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order. (Or, if the index is a descending index, the key is read in forward order.) See Section 10.2.1.16, “ORDER BY Optimization”, Section 10.2.1.17, “GROUP BY Optimization”, and Section 10.3.13, “Descending Indexes”.

在某些情况下，查询可以被优化以在不访问数据行的情况下检索值。（提供查询所需所有结果的索引称为覆盖索引。）如果查询只使用表中包含在某个索引中的列，那么选定的值可以从索引树中检索，从而提高速度：In some cases, a query can be optimized to retrieve values without consulting the data rows. (An index that provides all the necessary results for a query is called a covering index.) If a query uses from a table only columns that are included in some index, the selected values can be retrieved from the index tree for greater speed:

SELECT key_part3 FROM tbl_name
  WHERE key_part1=1