mysql 翻译系列 二十七

10.3.1 MySQL如何使用索引

版本8.0
索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后逐行读取整个表以找到相关行。表越大，这种方式的成本就越高。如果表中针对相关列建有索引，MySQL可以快速确定在数据文件中查找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。

大多数MySQL索引（PRIMARY KEY、UNIQUE、INDEX和FULLTEXT）都存储在B树中。但也有例外：空间数据类型的索引使用R树；MEMORY表还支持哈希索引；InnoDB对FULLTEXT索引使用倒排链表。

一般来说，索引的使用方式如下所述。有关哈希索引（如在MEMORY表中使用的）的特定特性，请参见10.3.9节 “B树索引和哈希索引的比较”。

MySQL在以下操作中使用索引：

• 快速查找符合WHERE子句的行：通过索引可以快速定位到满足WHERE子句条件的行。
• 排除无需考虑的行：如果有多个索引可供选择，MySQL通常会使用能找到最少行数的索引（即选择性最强的索引）。
• 利用多列索引的最左前缀：如果表有一个多列索引，优化器可以使用该索引的任何最左前缀来查找行。例如，如果在(col1, col2, col3)上有一个三列索引，那么就可以在(col1)、(col1, col2)和(col1, col2, col3)上进行索引搜索。更多信息，请参见10.3.6节 “多列索引”。
• 在连接操作中检索其他表的行：在执行连接时，MySQL可以更高效地使用列上的索引，前提是这些列声明为相同的类型和大小。在这种情况下，如果VARCHAR和CHAR声明为相同的大小，则被视为相同类型。例如，VARCHAR(10)和CHAR(10)大小相同，但VARCHAR(10)和CHAR(15)大小不同。对于非二进制字符串列之间的比较，两列应使用相同的字符集。例如，比较utf8mb4列和latin1列会导致无法使用索引。如果不同类型的列（如字符串列与时间或数字列）的值在不进行转换的情况下无法直接比较，那么这种比较可能会阻止使用索引。例如，对于数字列中的值1，它可能与字符串列中的多个值（如'1'、' 1'、'00001'或'01.e1' ）相等。这就排除了对字符串列使用任何索引的可能性。
• 查找特定索引列的MIN()或MAX()值：对于查找特定索引列key_col的MIN()或MAX()值，有一个预处理器会进行优化。它会检查在索引中key_col之前的所有键部分是否使用了WHERE key_part_N = constant。在这种情况下，MySQL会对每个MIN()或MAX()表达式进行一次键查找，并将其替换为常量。如果所有表达式都被替换为常量，查询会立即返回。例如：

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节 “降序索引”。
• 无需访问数据行即可检索值：在某些情况下，查询可以通过优化，在不访问数据行的情况下检索值。（能够为查询提供所有必要结果的索引称为覆盖索引。）如果查询仅使用表中包含在某个索引中的列，那么可以从索引树中检索选定的值，从而提高速度。例如：

SELECT key_part3 FROM tbl_name
  WHERE key_part1=1

对于小表的查询，或者对于处理大部分或所有行的大表的报表查询，索引的重要性较低。当查询需要访问大部分行时，按顺序读取比通过索引读取更快。即使查询不需要所有行，顺序读取也可以最小化磁盘查找。详细信息，请参见10.2.1.23节 “避免全表扫描”。