一、概述

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

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

通常，索引的使用如下所述。

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

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

• 如果对可用索引的最左侧前缀（例如，ORDER BY key_part1，key_part2）进行排序或分组，则对表进行排序或编组。如果所有关键部分后面跟着DESC，则按相反顺序读取密钥。（或者，如果索引是降序索引，则按正向顺序读取键。）
• 在某些情况下，可以优化查询以检索值，而无需查询数据行。（为查询提供所有必要结果的索引称为覆盖索引。）如果查询仅使用表中包含在某些索引中的列，则可以从索引树中检索所选值以提高速度：

SELECT key_part3 FROM tbl_name
  WHERE key_part1=1

索引对于小表或大表的查询不太重要 报表查询处理大部分或全部行的表。 当查询需要访问大部分行时，读取 按顺序比处理索引更快

二、主键优化

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

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

三、空间索引优化

MySQL允许在NOT NULL几何值列上创建SPATIAL索引（参见第11.4.10节“创建空间索引”）。优化器检查索引列的SRID属性，以确定用于比较的空间参考系统（SRS），并使用适合于SRS的计算。（在MySQL 8.0之前，优化器使用笛卡尔计算对SPATIAL索引值进行比较；如果列包含具有非笛卡尔SRID的值，则此类操作的结果未定义。）

为了使比较正常工作，SPATIAL索引中的每一列都必须受SRID限制。也就是说，列定义必须包含显式SRID属性，并且所有列值必须具有相同的SRID。

优化器仅考虑SRID受限列的SPATIAL索引：

• 限制为笛卡尔SRID的列上的索引启用笛卡尔边界框计算。
• 限制为地理SRID的列上的索引启用地理边界框计算。

优化器忽略没有SRID属性（因此不受SRID限制）的列上的SPATIAL索引。MySQL仍然保持这样的索引，如下所示：

• 它们会针对表修改（INSERT、UPDATE、DELETE等）进行更新。即使列可能包含笛卡尔和地理值的混合，更新也会发生，就好像索引是笛卡尔的。
• 它们的存在只是为了向后兼容（例如，在MySQL 5.7中执行转储和在MySQL 8.0中进行恢复的能力）。由于不受SRID限制的列上的SPATIAL索引对优化器没有任何用处，因此应修改每个这样的列：
• 验证列中的所有值是否具有相同的SRID。要确定几何体列col_name中包含的SRID，请使用以下查询：

SELECT DISTINCT ST_SRID(col_name) FROM tbl_name;

如果查询返回多行，则该列包含SRID的混合。在这种情况下，修改其内容，使所有值都具有相同的SRID。

• 重新定义列以具有显式SRID属性。
• 重新创建SPATIAL索引。

四、外键优化

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

五、列索引

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

每个表的最大索引数和最大索引长度由每个存储引擎定义。请参阅第15章，InnoDB存储引擎和第16章，替代存储引擎。所有存储引擎都支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎具有更高的限制。

1、索引前缀

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

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

对于使用REDUNDANT或COMPACT行格式的InnoDB表，前缀可以长达767字节。对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，前缀长度限制为3072字节。对于MyISAM表，前缀长度限制为1000字节。

注意

前缀限制以字节为单位，而前缀 创建表、更改表和创建索引语句中的长度为 解释为非二进制字符串的字符数 的类型（字符、变量、文本）和字节数 二进制字符串类型（BINARY、VARBINARY、BLOB）。考虑到这一点 为非二进制字符串指定前缀长度时 使用多字节字符集的列。

如果搜索词超过索引前缀长度，则索引为 用于排除不匹配的行，其余行为 检查可能的匹配项。

2、全文索引

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

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

• 仅返回文档ID或文档ID和搜索排名的FULLTEXT查询。
• FULLTEXT查询按照分数降序对匹配行进行排序，并应用LIMIT子句获取前N个匹配行。要应用此优化，必须没有WHERE子句，只能有一个ORDERBY子句（降序）。
• FULLTEXT查询，只检索与搜索项匹配的行的COUNT（*）值，而不附加WHERE子句。将WHERE子句编码为WHERE MATCH（text）AND（'other_text'），不使用任何>0比较运算符。

对于包含全文表达式的查询，MySQL会在查询执行的优化阶段评估这些表达式。优化器不只是查看全文表达式并作出估计，而是在开发执行计划的过程中对其进行评估。

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

全文查询的EXPLAIN可能会在Extra列中显示Select table optimited away，因为在优化过程中发生了匹配；在这种情况下，在以后的执行过程中不需要进行表访问。

3、空间索引

可以在空间数据类型上创建索引。MyISAM和InnoDB支持空间类型上的R-树索引。其他存储引擎使用B树对空间类型进行索引（ARCHIVE除外，它不支持空间类型索引）。

4、MEMORY存储引擎中的索引

默认情况下，MEMORY存储引擎使用HASH索引，但也支持BTREE索引。

六、多列索引

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

MySQL可以使用多列索引进行查询，以测试所有 索引中的列，或仅测试第一个列的查询 列、前两列、前三列等 上。如果在索引中以正确的顺序指定列 定义，单个复合索引可以加速几种 同一表上的查询。

多列索引可以被视为排序数组，即 行包含通过连接创建的值 索引列的值。

ySQL可以使用多列索引进行查询，以测试所有 索引中的列，或仅测试第一个列的查询 列、前两列、前三列等 上。如果在索引中以正确的顺序指定列 定义，单个复合索引可以加速几种 同一表上的查询。

多列索引可以被视为排序数组，即 行包含通过连接创建的值 索引列的值。

SELECT * FROM tbl_name
  WHERE hash_col=MD5(CONCAT(val1,val2))
  AND col1=val1 AND col2=val2;

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

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';

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

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上存在单独的单列索引，优化器会尝试使用索引合并优化（请参阅第8.2.1.3节“索引合并优化”），或者尝试通过决定哪个索引排除更多行并使用该索引获取行来查找最具限制性的索引。

如果表具有多列索引，优化器可以使用索引的任何最左侧前缀来查找行。例如，如果您在（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）的最左边前缀。