MySQL 8.0 官方文档 第八章 优化（三十三）—— 优化与索引（一）

第八章 优化（三十三）—— 优化与索引

8.3 优化与索引

8.3.1 MySQL如何使用索引
8.3.2 主键优化
8.3.3 空间索引优化
8.3.4 外键优化
8.3.5 列索引
8.3.6 多列索引
8.3.7 验证索引用法
8.3.8 InnoDB 和 MyISAM 索引统计集合或收集
8.3.9 B树和哈希索引比较
8.3.10 索引扩展的使用
8.3.11 生成列索引的优化器使用
8.3.12 不可见索引
8.3.13 降序索引
8.3.14 从时间戳（TIMESTAMP）列中进行索引查找

提高SELECT操作性能的最佳方法是在查询中进行条件测试的一个或多个列上创建索引。索引项的作用类似于指向表中行的指针，它允许查询能快速确定哪些行与WHERE子句中的条件匹配，从而检索到这些行的其他列值。所有MySQL数据类型都可以创建索引。

尽管为查询中使用的每一个可能的列都创建索引很有诱惑力，但是不必要的索引浪费了空间，也浪费了MySQL在决定到底使用那些索引时花费的时间。另外，索引增加了插入、更新和删除的成本，因为以上操作必须对每个索引进行更新。您必须要找到正确的平衡，以达到使用索引最佳集的快速查询。

8.3.1 MySQL如何使用索引

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后扫描全表以找到相关行。表越大，花费的成本越高。如果表中有相关列的索引，MySQL就能快速定位数据文件中要寻找的中间位置，不必在所有数据中查找。这比顺序读取每一行要快得多。

大多数MySQL索引（PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT–> 主键、唯一、索引和全文）都存储在B树中。例外的是：空间数据类型的索引使用R树，内存表也支持哈希索引，InnoDB的全文索引使用逆序列表。

一般来说，索引的使用方法如下所述。第8.3.9节“B树和哈希索引的比较”中描述了哈希索引（在内存表中使用）的特定特征。

MySQL使用索引进行以下操作：

• 快速查找匹配WHERE子句的行。

• 考虑减少行数。如果要在多个索引中进行选择的话，MySQL通常使用能找到最小行数的那个索引（最具选择性的索引）。

• 如果表中有多列索引（译者：复合索引），那么优化器就能使用任何最左前缀来查找行。例如，在(col1, col2, col3)上有一个三列索引，那么，在(col1), (col1, col2)和 (col1, col2, col3)上都有索引搜索功能。有关更详细信息，请参见第8.3.6节“多列索引”。

• 在执行连接查询时，从其他表中检索行。如果这些列被定义为相同的类型和大小，则MySQL能更高效地使用这些列上的索引。在这种上下文中，如果VARCHAR和CHAR被定义为相同的大小，则认为它们是相同的。例如，VARCHAR(10)和CHAR(10)的大小相同，但是VARCHAR(10)和CHAR(15)则不同。
  对于非二进制字符串列之间的比较，两列必须使用相同的字符集。例如，utf8列和latin1列之间的比较不会使用索引。
  如果不同类型列值在没有转换情况下直接进行比较，则不同类型列（例如，字符串列与时间列或数字列之间的比较）之间的比较可能会阻止使用索引。对于给定的值，例如数字类型的列中的1，它可能与任何字符串列中的值（例如'1', ' 1', '00001' 或'01.el'）进行相等比较。这就排除了在字符串列中使用任何索引。

• 在特定索引列key_col上查找MIN()或MAX()的值。这是通过预处理器优化的，它会检查在索引中key_col前发生的所有键部分上是否使用了WHERE key_part_N = constant（译者：键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，则按照相反的顺序读取键。（或者，如果索引是降序索引，则按照正序读取键）。参见第8.2.1.16节“ORDER BY 优化”，第8.2.1.17节“GROUP BY 优化”，以及第8.3.13节“降序索引”。

• 在某些情况下，查询可以通过优化，在没有查询数据行的前提下检索到值。（为查询提供所有必要结果的索引称为覆盖索引）。如果一个查询使用到表中的列全部包含在某些索引中，那么从索引树中就可以更快速度地检索到选择的值：

  SELECT key_part3 
  		FROM tbl_name
  		WHERE key_part1 = 1;
  

在小表上查询，或者需要报告大表上的大多数或全部行时，索引不再那么重要。当查询需要访问大多数行时，顺序读比遍历索引更快。即使查询并不需要所有行，顺序读也可最小化减少磁盘搜索。详见第8.2.1.23节“避免全表扫描”。

8.3.2 主键优化

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

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

8.3.3 空间索引优化

MySQL允许在非空的几何值列上创建空间索引（参阅第11.4.10节“创建空间索引”）。优化器检查索引列的SRID属性，以确定要用于比较的空间引用系统（spatial reference system --> SRS），并使用适合SRS的计算（在MySQL8.0之前，优化器使用笛卡尔计算执行空间索引值的比较；如果该列包含非笛卡尔SRID的值，则此类操作的结果是未定义的。）

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

优化器只考虑SRID限制列的空间索引：

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

• 限制为地理SRID的列上的索引支持地理边界框计算。

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

• 它们会因表修改（插入、更新、删除等）而更新。即使该列可能包含笛卡尔值和地理值的混合值，也会像索引是笛卡尔值一样进行更新。

• 它们的存在只是为了向后兼容（例如，在MySQL 5.7中执行转储，在MySQL 8.0中执行恢复）。因为没有SRID限制的列上的空间索引对优化器没有任何用处，所以应该修改每一个这样的列：

  • 验证列中的所有值是否具有相同的SRID。要确定几何体列col_name中包含的SRID，请使用以下查询：

    SELECT DISTINCT ST_SRID(col_name) 
    		FROM tbl_name;
    

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

  • 重新定义列以具有显式SRID属性。

  • 重新创建空间索引。

上一集 MySQL 8.0 官方文档 第八章 优化（三十二）—— 优化数据库权限和其他优化提示