Mysql（8）如何使用索引

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

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

MySQL 对这些操作使用索引：

• 快速找到匹配 WHERE 子句的行。
• 如果在多个索引之间进行选择，MySQL 通常使用找到最少行数的索引（最具选择性的索引）。
• 如果表有一个多列索引，优化器可以使用索引的任何最左边的前缀来查找行。 例如，如果我们在 (col1, col2, col3) 上有一个三列索引，则我们在 (col1)、(col1, col2) 和 (col1, col2, col3) 上有索引搜索功能。 
• 在执行连接时从其他表中检索行。 如果将列声明为相同的类型和大小，MySQL 可以更有效地使用列上的索引。 在此上下文中，如果将 VARCHAR 和 CHAR 声明为相同大小，则它们被视为相同。 例如，VARCHAR(10) 和 CHAR(10) 的大小相同，但 VARCHAR(10) 和 CHAR(15) 不同。                                                                                                                                      对于非二进制字符串列之间的比较，两列应使用相同的字符集。 例如，将 utf8 列与 latin1 列进行比较会排除使用索引。                                                                                                      如果值不能在没有转换的情况下直接比较，则比较不同的列（例如，将字符串列与时间或数字列进行比较）可能会阻止使用索引。 对于数字列中的给定值（例如 1），它可能与字符串列中的任意数量的值（例如“1”、“1”、“00001”或“01.e1”）进行比较。 这排除了对字符串列使用任何索引。
• 查找特定索引列 key_col 的 MIN() 或 MAX() 值。 这是由预处理器优化的，该预处理器检查我们是否在索引中 key_col 之前出现的所有key part上使用 WHERE key_part_N = constant。 在这种情况下，MySQL 为每个 MIN() 或 MAX() 表达式执行单个键查找，并将其替换为常量。 如果所有表达式都替换为常量，则查询立即返回。 例如：

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

• 如果排序sort或分组group是在可用索引的最左前缀上完成的（例如，ORDER BY key_part1、key_part2），则对表进行排序或分组。 如果所有key part后跟 DESC，则以相反的顺序读取key。 
• 在某些情况下，可以优化查询以在不查阅数据行的情况下检索值。 （为查询提供所有必要结果的索引称为覆盖索引。）如果查询仅使用表中包含在某个索引中的列，则可以从索引树中检索所选值以获得更快的速度：

  SELECT key_part3 FROM tbl_name
    WHERE key_part1=1

  对于小表或报表查询处理大部分或所有行的大表的查询，索引不太重要。 当查询需要访问大部分行时，顺序读取比通过索引更快。 即使查询不需要所有行，顺序读取也可以最大限度地减少磁盘寻道。

Primary Key（主键） 优化

表的主键表示我们在最重要的查询中使用的列或列集。 它有一个关联的索引，用于快速查询性能。 查询性能受益于 NOT NULL 优化，因为它不能包含任何 NULL 值。 使用 InnoDB 存储引擎，表数据在物理上进行组织，以根据主键列或列进行超快速查找和排序。

如果我们的表很大且很重要，但没有明显的列或列集用作主键，我们可以创建一个具有自动递增值的单独列以用作主键。 当我们使用外键连接表时，这些唯一 ID 可以用作指向其他表中相应行的指针。

Foreign Key（外键） 优化

如果一个表有很多列，并且我们查询了许多不同的列组合，那么将不常用的数据拆分为每个包含几列的单独表，并通过复制主表中的数字 ID 列将它们关联回主表。这样，每个小表都可以有一个主键来快速查找其数据，并且我们可以使用连接操作仅查询所需的列集。 根据数据的分布方式，查询可能会执行较少的 I/O 并占用较少的高速缓存，因为相关的列在磁盘上打包在一起。 （为了最大限度地提高性能，查询尝试从磁盘读取尽可能少的数据块；只有几列的表可以在每个数据块中容纳更多的行。）

Column Indexes（列索引）

最常见的索引类型涉及单个列，将来自该列的值的副本存储在数据结构中，从而允许快速查找具有相应列值的行。 B树数据结构让索引可以快速找到一个特定的值，一组值，或一个范围的值，对应于WHERE子句中的=、>、≤、BETWEEN、IN等运算符。

Index Prefixes（索引前缀）

使用字符串列的索引规范中的 col_name(N) 语法，我们可以创建仅使用该列的前 N 个字符的索引。 以这种方式仅索引列值的前缀可以使索引文件更小。 索引 BLOB 或 TEXT 列时，必须为索引指定前缀长度。 例如：

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

前缀最长可达 1000 字节（InnoDB 表为 767 字节，除非我们设置了 innodb_large_prefix）。

如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，并检查剩余的行是否可能匹配。

FULLTEXT Indexes（全文索引）

FULLTEXT 索引用于全文搜索。 只有 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引并且只支持 CHAR、VARCHAR 和 TEXT 列。 索引始终在整个列上进行，并且不支持列前缀索引。

优化适用于针对单个 InnoDB 表的某些类型的 FULLTEXT 查询。 具有这些特征的查询特别有效：

• 仅返回文档 ID 或文档 ID 和搜索排名的 FULLTEXT 查询。
• FULLTEXT 查询按分数降序对匹配行进行排序，并应用 LIMIT 子句获取前 N 个匹配行。 要应用此优化，必须没有 WHERE 子句，并且只有一个按降序排列的 ORDER BY 子句。
• FULLTEXT 查询仅检索与搜索词匹配的行的 COUNT(*) 值，没有额外的 WHERE 子句。 将 WHERE 子句编码为 WHERE MATCH(text) AGAINST ('other_text')，没有任何 > 0 比较运算符。

对于包含全文表达式的查询，MySQL 在查询执行的优化阶段评估这些表达式。 优化器不只是查看全文表达式并进行估计，它实际上是在制定执行计划的过程中评估它们。

这种行为的一个含义是，全文查询的 EXPLAIN 通常比在优化阶段不进行表达式评估的非全文查询慢。

Multiple-Column Indexes（多列索引）

MySQL 可以创建复合索引（即多列上的索引）。 一个索引最多可以包含 16 列。

MySQL 可以将多列索引用于test索引中所有列的查询，或者只test第一列、前两列、前三列等的查询。 如果在索引定义中以正确的顺序指定列，则单个复合索引可以加快对同一张表的多种查询。

多列索引可以被认为是一个排序数组，其中的行包含通过连接索引列的值创建的值。

假设一个表具有以下规范：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

名称索引是对 last_name 和 first_name 列的索引。 该索引可用于查询中的查找，这些查询为 last_name 和 first_name 值的组合指定已知范围内的值。 它还可用于仅指定 last_name 值的查询，因为该列是索引的最左侧前缀（如本节后面所述）。 因此，名称索引用于以下查询中的查找：

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

但是，名称索引不用于以下查询中的查找：

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

假设我们发出以下 SELECT 语句：

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

如果 col1 和 col2 上存在多列索引，则可以直接获取相应的行。 如果 col1 和 col2 上存在单独的单列索引，则优化器会尝试使用索引合并优化，或者通过确定哪个索引排除更多行来尝试找到最严格的索引 并使用该索引来获取行。

如果表有一个多列索引，优化器可以使用索引的任何最左边的前缀来查找行。 例如，如果我们在 (col1, col2, col3) 上有一个三列索引，则我们在 (col1)、(col1, col2) 和 (col1, col2, col3) 上有索引搜索功能。

如果列不构成索引的最左前缀，则 MySQL 无法使用索引执行查找。 假设我们有此处显示的 SELECT 语句：

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果 (col1, col2, col3) 上存在索引，则只有前两个查询使用该索引。 第三和第四个查询确实涉及索引列，但不使用索引来执行查找，因为 (col2) 和 (col2, col3) 不是 (col1, col2, col3) 的最左边的前缀。