MySQL8 查询性能调优教程（四）
十四、索引
向表中添加索引是提高查询性能的一种非常有效的方法。索引允许 MySQL 快速找到查询所需的数据。当向表中添加正确的索引时，查询性能可能会提高几个数量级。诀窍是知道要添加哪些索引。为什么不在所有列上添加索引呢？索引也有开销，所以您需要在添加随机索引之前分析您的需求。
本章首先讨论什么是索引，一些索引概念，以及添加索引会有什么缺点。然后介绍 MySQL 支持的各种索引类型和特性。本章的下一部分开始讨论 InnoDB 如何使用与索引组织的表特别相关的索引。最后，讨论了如何选择应该向表中添加哪些索引以及何时添加。
什么是索引？
为了能够使用索引来适当地提高性能，理解什么是索引是很重要的。这一节将不讨论不同的索引类型——这将在本章后面的“索引类型”一节中讨论——而是更高层次的索引概念。
索引的概念并不新鲜，早在计算机数据库出现之前就已经存在了。举个简单的例子，考虑这本书。在这本书的结尾，有一些单词和术语的索引，这些单词和术语被选为与本书中的文本最相关的搜索术语。图书索引的工作方式在概念上类似于数据库索引的工作方式。它组织数据库中的“术语”,因此您的查询可以比读取所有数据并检查它是否与搜索标准匹配更快地找到相关数据。这里引用了术语一词，因为索引不一定是由人类可读的词组成的。也可以索引二进制数据，如空间数据。
简而言之，索引组织数据的方式可以减少查询需要检查的行数。精心选择的索引带来的加速可能是巨大的——几个数量级。再次考虑这本书:如果你想阅读 B 树索引，你可以从第 1 页开始，继续阅读整本书，或者在书的索引中查找术语“B 树索引”,直接跳到相关的页面。当查询 MySQL 数据库时，改进是相似的，不同之处在于查询可能比在书中查找信息复杂得多，因此索引的重要性增加了。
显然，您只需要添加所有可能的索引，对吗？不。除了添加索引的管理复杂性之外，索引本身不仅在正确使用时提高了性能；它们也增加了开销。所以你需要小心选择你的索引。
另一件事是，即使可以使用索引，它也不总是比扫描整个表更有效。如果你想阅读这本书的重要部分，在索引中查找每一个感兴趣的术语，找出主题在哪里讨论，然后阅读它，最终会比从头到尾阅读整本书慢。同样，如果您的查询无论如何都需要访问表中的大部分数据，那么从一端到另一端读取整个表会变得更快。扫描整个表变得更便宜的确切阈值取决于几个因素。这些因素包括磁盘类型、顺序 I/O 与随机 I/O 相比的性能、数据是否适合内存等等。
在深入研究索引的细节之前，有必要快速浏览一下一些关键的索引概念。
索引概念
考虑到主题索引有多大，有几个术语用来描述索引就不足为奇了。当然还有索引类型的名称，比如 B 树、全文、空间等等，但是还有一些更通用的术语需要注意。索引类型将在本章后面介绍，因此这里将讨论更一般的术语。
键与索引
您可能已经注意到，有时会使用“索引”一词，有时会使用“键”一词。有什么区别？索引是键的列表。然而，在 MySQL 语句中，这两个术语经常可以互换。
一个很重要的例子是“主键”——在这种情况下，必须使用“key”。另一方面，当你添加一个索引时，你可以随心所欲地写ALTER TABLE table_name ADD INDEX ...或ALTER TABLE table_name ADD KEY ...。在这种情况下，手册使用“索引”,因此为了保持一致，建议坚持使用索引。
有几个术语可以描述你正在使用的索引类型。首先要讨论的是一个独特的索引。
唯一索引
唯一索引是指对于索引中的每个值只允许一行的索引。考虑一个包含人的数据的表。您可以包括该人的社会保险号或类似的标识符。任何两个人都不应该共享社会保险号，所以在存储社会保险号的列上定义一个惟一的索引是有意义的。
从这个意义上说，“唯一”更多地是指一种约束，而不是索引特征。然而，索引部分对于 MySQL 能够快速确定新值是否已经存在是至关重要的。
在 MySQL 中使用惟一索引时，一个重要的考虑因素是如何处理NULL值。比较两个NULL值是未定义的(或者换句话说NULL不等于NULL，所以允许NULL值的列上的唯一索引不会限制该列可以有多少行NULL。如果您想要将您的唯一约束限制为只允许一个NULL值，那么使用触发器来检查是否已经有一个NULL值，并使用SIGNAL语句引发一个错误。在清单 14-1 中可以看到一个触发器的例子。
CREATE TABLE my_table (
Id int unsigned NOT NULL,
Name varchar(50),
PRIMARY KEY (Id),
UNIQUE INDEX (Name)
);
DELIMITER $$
CREATE TRIGGER befins_my_table
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
DECLARE v_errmsg, v_value text;
IF EXISTS(SELECT 1 FROM my_table WHERE Name <=> NEW.Name) THEN
IF NEW.Name IS NULL THEN
SET v_value = 'NULL';
ELSE
SET v_value = CONCAT('''', NEW.Name, '''');
END IF;
SET v_errmsg = CONCAT('Duplicate entry ',
v_value,
' For key ''Name''');
SIGNAL SQLSTATE '23000'
SET MESSAGE_TEXT = v_errmsg,
MYSQL_ERRNO = 1062;
END IF;
END$$
DELIMITER ;
Listing 14-1Trigger checking for unique constraint violations
这将处理Name列的任何类型的重复值。它使用NULL安全等于运算符(<=>)来确定Name的新值是否已经存在于表中。如果是的话，它引用不是NULL的值，否则不引用，所以可以区分字符串“NULL”和NULL值。最后，发出一个 SQL 状态为 23000、MySQL 错误号为 1062 的信号。错误消息、SQL 状态和错误号与正常的重复键约束错误相同。
一种特殊的唯一索引是主键。
主关键字
表的主键是唯一定义行的索引。NULL主键不允许有值。如果您的表上有多个NOT NULL唯一索引，那么任何一个都可以作为主键。出于稍后将在讨论聚集索引时解释的原因，您应该为主键选择一个或多个具有不可变值的列。也就是说，不要改变给定行的主键。
主键对于 InnoDB 来说非常特殊，而对于其他存储引擎来说，它可能更像是一个约定俗成的问题。然而，在所有情况下，最好总是有一些值来唯一地标识一行，例如，允许复制快速地确定要修改哪一行(在第 26 章中有更多关于这一点的内容)，并且组复制特性明确地要求所有表都有一个主键或一个非唯一索引。在 MySQL 8.0.13 和更高版本中，您可以启用sql_require_primary_key选项来要求所有新表都必须有一个主键。如果更改现有表的结构，该限制也适用。
Tip
启用sql_require_primary_key选项(默认禁用)。没有主键的表可能会导致性能问题，有时是以意想不到的微妙方式。如果将来要使用组复制，这还可以确保您的表准备就绪。
如果有主键，是否也有辅键？
次要索引
术语“辅助索引”用于表示不是主键的索引。它没有任何特殊的含义，因此该名称只是用来明确表示该索引不是主键，无论它是唯一的还是非唯一的索引。
如前所述，主键对于 InnoDB 有特殊的意义，因为它用于聚集索引。
聚集索引
聚集索引是 InnoDB 特有的，是 InnoDB 如何组织数据的术语。如果您熟悉 Oracle DB，您可能知道按索引组织的表；描述了同样的事情。
InnoDB 中的一切都是索引。行数据位于 B 树索引的叶页面中(B 树索引将在稍后描述)。该索引称为聚集索引。该名称来源于索引值聚集在一起的事实。主键用于聚集索引。如果不指定显式主键，InnoDB 将查找不允许使用NULL值的唯一索引。如果不存在，InnoDB 将使用一个全局(对于所有 InnoDB 表)自动增量值添加一个隐藏的 6 字节整数列，以生成一个唯一值。
主键的选择也会影响性能。这些将在本章后面的“索引策略”一节中讨论。聚集索引也可以看作是覆盖索引的一个特例。这是什么？你马上就会知道了。
覆盖索引
如果一个索引包含了给定查询所需的索引表中的所有列，则称该索引为覆盖索引。也就是说，索引是否覆盖取决于使用索引的查询。一个索引可以覆盖一个查询，但不能覆盖另一个查询。考虑一个索引列(a, b)的索引和一个选择这两列的查询:
SELECT a, b
FROM my_table
WHERE a = 10;
在这种情况下，查询只需要列a和b，因此没有必要查找其余的行——索引足以检索所有需要的数据。另一方面，如果查询还需要列c，则索引不再覆盖。当您使用EXPLAIN语句分析一个查询时(这将在第 20 章中介绍)，并且一个覆盖索引被用于该表，EXPLAIN输出中的Extra列将包括“使用索引”
覆盖索引的一个特例是 InnoDB 的聚集索引(尽管EXPLAIN不会说“使用索引”)。聚集索引包括叶节点中的所有行数据(尽管通常只对列的子集进行索引)，因此索引将总是包括所有必需的数据。一些数据库在创建可用于模拟聚集索引工作方式的索引时支持include子句。
聪明地创建索引，使它们可以用作最常执行的查询的覆盖索引，可以极大地提高性能，这将在“索引策略”一节中讨论。
添加索引时，您需要遵守一些限制。这些限制是接下来要讨论的内容。
索引限制
关于 InnoDB 索引有一些限制。这些范围从索引大小到表上允许的索引数量。最重要的限制如下:
-
B 树索引的最大宽度是 3072 字节或 767 字节，具体取决于 InnoDB 行格式。最大大小基于 16 kiB InnoDB 页面，较小的页面大小有较低的限制。
-
当指定了前缀长度时，Blob 和 text 类型的列只能用于全文索引以外的索引中。前缀索引将在本章后面的“索引功能”一节中讨论
-
功能键部分计入表中 1017 列的限制。
-
每个表上最多可以有 64 个辅助索引。
-
多列索引最多可以包含 16 列和功能键部分。
您可能会遇到的限制是 B 树索引的最大索引宽度。当使用DYNAMIC(默认)或COMPRESSED行格式时，索引不能超过 3072 字节，对于REDUNDANT和COMPACT行格式，索引不能超过 767 字节。对于使用DYNAMIC和COMPRESSED行格式的表格，8 个 KiB 页面的限制减少到一半(1536 字节)，4 个 KiB 页面的限制减少到四分之一(768 字节)。这对于字符串和二进制列上的索引来说是一个特别的限制，因为这些值不仅本质上通常很大，而且也是在大小计算中使用的最大可能存储量。这意味着使用utf8mb4字符集的varchar(10)将贡献 40 个字节，即使您从未在列中存储任何单字节字符。
当您向文本或 blob 类型的列添加 B 树索引时，您必须始终提供一个键长度，以指定要在索引中包含多少列前缀。这甚至适用于仅支持 256 字节数据的tinytext和tinyblob。对于char、varchar、binary和varbinary列，如果以字节为单位的值的最大大小超过了表允许的最大索引宽度，则只需要指定前缀长度。
Tip
对于文本和 blob 类型的列，不使用前缀索引，通常更好的方法是使用全文索引(稍后介绍)，添加带有 blob 散列的生成列，或者以其他方式优化访问。
如果向表中添加函数索引，那么每个功能键部分都将计入表中列的限制。如果您创建一个包含两个功能部分的索引，那么这将作为表限制中的两列。对于 InnoDB，一个表中最多可以有 1017 列。
最后两个限制与表中可以包含的索引数量以及单个索引中可以包含的列和功能键部分的数量有关。一个表上最多可以有 64 个辅助索引。实际上，如果您已经接近这个限制，那么重新考虑您的索引策略可能会对您有所帮助。正如在“索引的缺点是什么？”在本章的后面，有一些与索引相关的开销，所以在所有情况下，最好将索引的数量限制在那些真正有利于查询的数量上。同样，添加到索引中的部分越多，索引就越大。InnoDB 的限制是最多可以添加 16 个部分。
如果需要向表中添加索引或删除多余的索引，该怎么办？索引可以与表一起创建，也可以稍后创建，还可以删除索引，如下所述。
SQL 语法
当您第一次创建模式时，您通常会对添加哪些索引有一些想法。然后，久而久之，你的监控可能会确定一些索引不再需要，但其他的应该添加。对索引的这些更改可能是由于对所需索引的误解；数据可能已经更改，或者查询可能已经更改。
在更改表上的索引时，有三种不同的操作:在创建表本身时创建索引，向现有表添加索引，或者从表中删除索引。无论是将索引与表一起添加，还是作为后续操作添加，索引定义都是相同的。删除索引时，只需要索引名。
本节将展示添加和删除索引的一般语法。在本章的其余部分，将会有更多基于特定索引类型和特性的例子。
创建带索引的表
创建表时，可以将索引定义添加到CREATE TABLE语句中。索引紧接在列之后定义。您可以选择指定索引的名称；否则，索引将根据索引中的第一列命名。
清单 14-2 展示了一个创建了几个索引的表的例子。如果您不知道所有的索引类型在做什么，也不用担心——这将在本章的后面讨论。
CREATE TABLE db1.person (
Id int unsigned NOT NULL,
Name varchar(50),
Birthdate date NOT NULL,
Location point NOT NULL SRID 4326,
Description text,
PRIMARY KEY (Id),
INDEX (Name),
SPATIAL INDEX (Location),
FULLTEXT INDEX (Description)
);
Listing 14-2Example of creating a table with indexes
这在db1模式中创建了具有四个索引的表person(必须预先存在)。第一个是主键，它是Id列上的 B 树索引(稍后会详细介绍)。第二个也是 B 树索引，但它是所谓的二级索引，索引Name列。第三个索引是关于Location列的空间索引。第四个是Description列上的全文索引。
您也可以创建包含多个列的索引。如果您需要将条件放在多列上，将条件放在第一列上并按第二列排序，等等，这将非常有用。要创建多列索引，请以逗号分隔列表的形式指定列名:
INDEX (Name, Birthdate)
列的顺序非常重要，这将在“索引策略”中解释简而言之，MySQL 将只能使用左边的索引，也就是说，只有同时使用了Name才能使用索引的Birthdate部分。这意味着索引(Name, Birthdate)与(Birthdate, Name)不是同一个索引。
一个表上的索引通常不会保持静态，所以如果您想向一个现有的表中添加一个索引，该怎么做呢？
添加索引
如果确定需要，可以向现有表中添加索引。为此，您需要使用ALTER TABLE或CREATE INDEX语句。由于ALTER TABLE可以用于表的所有修改，您可能希望坚持这样做；然而，所做的工作是相同的。
清单 14-3 展示了如何使用ALTER TABLE创建索引的两个例子。第一个示例添加了一个索引；第二个在一条语句上添加两个索引。
ALTER TABLE db1.person
ADD INDEX (Birthdate);
ALTER TABLE db1.person
DROP INDEX Birthdate;
ALTER TABLE db1.person
ADD INDEX (Name, Birthdate),
ADD INDEX (Birthdate);
Listing 14-3Adding indexes using ALTER TABLE
第一个和最后一个ALTER TABLE语句使用ADD INDEX子句告诉 MySQL 应该向表中添加一个索引。第三条语句添加了两个这样的子句，用逗号分隔，以便在一条语句中添加两个索引。在这两者之间，索引被删除，因为拥有重复的索引是不好的做法，MySQL 也会对此发出警告。
如果使用两条语句添加两个索引，或者使用一条语句添加两个索引，会有什么不同吗？是的，可能会有很大的不同。添加索引时，需要执行全表扫描来读取索引所需的所有值。对于大型表来说，全表扫描是一项开销很大的操作，因此从这个意义上说，最好在一条语句中添加两个索引。另一方面，只要索引可以完全保存在 InnoDB 缓冲池中，创建索引就会快得多。将两个索引的创建分成两个语句可以减轻缓冲池的压力，从而提高索引创建性能。
最后一个操作是删除不再需要的索引。
删除索引
删除索引的行为类似于添加索引。您可以使用ALTER TABLE或DROP INDEX语句。当使用ALTER TABLE时，可以将删除索引与表的其他数据定义操作结合起来。
当您删除一个索引时，您需要知道该索引的名称。有几种方法可以做到这一点，如清单 14-4 所示。
mysql> SHOW CREATE TABLE db1.person\G
*************************** 1\. row ***************************
Table: person
Create Table: CREATE TABLE `person` (
`Id` int(10) unsigned NOT NULL,
`Name` varchar(50) DEFAULT NULL,
`Birthdate` date NOT NULL,
`Location` point NOT NULL /*!80003 SRID 4326 */,
`Description` text,
PRIMARY KEY (`Id`),
KEY `Name` (`Name`),
SPATIAL KEY `Location` (`Location`),
KEY `Name_2` (`Name`,`Birthdate`),
KEY `Birthdate` (`Birthdate`),
FULLTEXT KEY `Description` (`Description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0010 sec)
mysql> SELECT INDEX_NAME, INDEX_TYPE,
GROUP_CONCAT(COLUMN_NAME
ORDER BY SEQ_IN_INDEX) AS Columns
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'db1'
AND TABLE_NAME = 'person'
GROUP BY INDEX_NAME, INDEX_TYPE;
+-------------+------------+----------------+
| INDEX_NAME | INDEX_TYPE | Columns |
+-------------+------------+----------------+
| Birthdate | BTREE | Birthdate |
| Description | FULLTEXT | Description |
| Location | SPATIAL | Location |
| Name | BTREE | Name |
| Name_2 | BTREE | Name,Birthdate |
| PRIMARY | BTREE | Id |
+-------------+------------+----------------+
6 rows in set (0.0013 sec)
Listing 14-4Find the index names for a table
在您的情况下，索引可能会以不同的顺序列出。第一个查询使用SHOW CREATE TABLE语句获取完整的表定义，其中也包括索引及其名称。第二个查询查询information_schema.STATISTICS视图。这个视图对于获取关于索引的信息非常有用，将在下一章详细讨论。一旦决定了要删除哪个索引，就可以使用清单 14-5 中所示的ALTER TABLE。
ALTER TABLE db1.person DROP INDEX name_2;
Listing 14-5Dropping an index using ATLER TABLE
这将删除名为name_2的索引，即(Name, Birthdate)列上的索引。
本章的其余部分将介绍什么是索引的各种细节，在本章的最后，“索引策略”一节将讨论如何选择要索引的数据。首先，理解为什么索引有开销是很重要的。
索引的缺点是什么？
生活中很少有免费的东西——索引也不例外。虽然索引对于提高查询性能很有帮助，但是它们也需要被存储并保持最新。此外，一个不太明显的开销是，当执行查询时，索引越多，优化器需要做的工作就越多。本节将讨论索引的这三个缺点。
仓库
添加索引的一个最明显的成本是需要存储索引，以便在需要时随时可用。您不希望每次需要时都先创建索引，因为这样会降低索引的性能优势。 1 存储开销是双重的:索引存储在磁盘上以持久化它，并且它需要 InnoDB 缓冲池中的内存供查询使用。
磁盘存储意味着您可能需要向系统添加磁盘或块存储。如果您使用 MySQL 企业备份(MEB)等备份解决方案来复制原始表空间文件，您的备份也会变得更大，需要更长的时间才能完成。
InnoDB 总是使用其缓冲池来读取查询所需的数据。如果数据不存在于缓冲池中，则首先将数据读入缓冲池，然后用于查询。因此，当您使用索引时，索引和行数据通常都会被读入缓冲池(使用覆盖索引时是一个例外)。需要放入缓冲池的空间越多，容纳其他索引和数据的空间就越少——除非将缓冲池变得更大。它当然比这更复杂，因为避免全表扫描还会阻止将整个表读入缓冲池，这减轻了缓冲池的压力。与开销相比，总体的好处在于使用索引可以避免检查多少表，以及其他查询是否会读取索引避免访问的数据。
总而言之，当您添加索引时，您将需要额外的磁盘，并且通常您将需要更大的 InnoDB 缓冲池来保持相同的缓冲池命中率。另一个开销是索引只有保持最新才有用。这在更新数据时增加了工作量。
更新索引
每当您对数据进行更改时，索引都必须更新。这包括在插入或删除数据时添加或删除行链接，以及在更新值时修改索引。您可能对此不以为然，但这可能是一笔巨大的开销。事实上，在诸如恢复逻辑备份(通常包括用于创建数据的 SQL 语句的文件，例如用mysqlpump程序创建的文件)的批量数据加载期间，保持索引更新的开销通常是限制插入速率的原因。
Tip
保持索引最新的开销可能会很高，因此通常建议在向空表进行大规模导入时删除辅助索引，然后在导入完成后重新创建索引。
对于 InnoDB，开销还取决于二级索引是否适合缓冲池。只要整个索引都在缓冲池中，保持索引最新就相对便宜，不太可能成为严重的瓶颈。如果索引不合适，InnoDB 将不得不在表空间文件和缓冲池之间不断调整页面，这时开销将成为导致严重性能问题的主要瓶颈。
还有一个不太明显的性能开销。索引越多，优化器确定最佳查询计划的工作量就越大。
优化器
当优化器分析一个查询以确定它认为最佳的查询执行计划时，它需要评估每个表上的索引，以确定是否应该使用该索引，以及是否可能对两个索引进行索引合并。目标当然是尽可能快地评估查询。然而，花费在优化器上的时间通常是不可忽略的，在某些情况下甚至会成为瓶颈。
考虑一个非常简单的查询示例，从单个表中选择一些行:
SELECT ID, Name, District, Population
FROM world.city
WHERE CountryCode = 'AUS';
在这种情况下，如果表city上没有索引，显然需要进行表扫描。如果有一个索引，也有必要使用该索引评估查询成本，依此类推。如果您有一个复杂的查询，其中包含许多表，每个表都有十几个可能的索引，那么它将产生许多组合，这将反映在查询执行时间中。
Tip
如果花费在优化器上的时间成为一个问题，您可以添加优化器和连接顺序提示，如第 17 和 24 章中所讨论的，以帮助优化器，因此它不需要评估所有可能的查询计划。
虽然这些描述添加索引的开销的页面听起来好像索引不好，但是不要避免索引。对频繁执行的查询有很大选择性的索引将会有很大的好处。但是，不要为了添加索引而添加索引。我们将在本章末尾的“索引策略”一节中讨论选择索引的一些方法，在本书的其余部分也会有讨论索引的例子。在此之前，有必要讨论一下 MySQL 支持的各种索引类型以及其他索引特性。
索引类型
对于所有用途，最佳索引类型并不相同。为查找给定值范围内的行(例如，2019 年的所有日期)而优化的索引需要与在大量文本中搜索给定单词或短语的索引有很大不同。这意味着当您选择添加索引时，您必须决定需要哪种索引类型。MySQL 目前支持五种不同的索引类型:
-
b 树索引
-
全文索引
-
空间索引(R 树索引)
-
多值索引
-
哈希索引
本节将详细介绍这五种索引类型，并讨论它们可以用来加速哪种类型的问题。
b 树索引
b 树索引是 MySQL 中最常用的索引类型。事实上，所有 InnoDB 表都包含至少一个 B 树索引，因为数据是以 B 树索引(聚集索引)组织的。
B 树索引是一种有序索引，因此它很适合于查找这样的行:您要查找的列等于某个值，列大于或小于某个给定值，或者列介于两个值之间。这使它成为许多查询的一个非常有用的索引。
B 树索引的另一个好特性是它们具有可预测的性能。顾名思义，索引被组织成一棵树，从根页面开始，到叶页面结束。InnoDB 使用 B 树索引的扩展，称为 B+-tree。+表示同一级别的节点是链接的，因此在到达节点中的最后一条记录时，无需返回到父节点就可以轻松地扫描索引。
Note
在 MySQL 中，术语 B 树和 B+-树可以互换使用。
在图 14-1 中可以看到带有城市名称的索引的索引树示例。(对于索引级别，该图是从左到右定向的，这不同于 B 树索引的一些其他图示的从上到下的定向。这样做主要是因为空间的原因。)
图 14-1
B+树索引的示例
在该图中，文档形状表示 InnoDB 页面，多个文档堆叠在一起的形状(例如，在级别 0 中标有“基督城”的文档)表示几个页面。从左到右的箭头从根页面指向叶页面。根页是索引搜索开始的地方，叶页是索引记录存在的地方。中间的页面通常称为内部页面或分支页面。页面也可以称为节点。连接同一级别页面的双箭头区分了 B 树和 B+-树索引，并允许 InnoDB 快速移动到上一个或下一个兄弟页面，而不必通过父页面。
对于小型索引，可能只有一个页面同时充当根页面和叶页面。在更一般的情况下，索引有一个根页面，如图的最左边部分所示。在图的最右边部分是叶页。对于大型索引，中间可能还有更多级别。叶节点的级别为 0，其父页面的级别为 1，依此类推，直到到达根页面。
在图中，为页面标注的值，例如“A Coruñ”，表示树的该部分包含的第一个值。因此，如果您处于第 1 级，并且正在查找值“Adelaide”，您知道它将位于叶页面的最上面一页，因为该页包含以“A Coruñ”开始的值，并以值排序顺序中早于“Beijing”的最后一个值结束。这是上一章讨论的排序规则发挥作用的一个例子。
一个关键的特性是，无论您遍历哪个分支，级别的数量总是相同的。例如，在图中，这意味着无论您寻找哪个值，都将读取四个页面，四个级别中的每一个都有一个页面(如果几行具有相同的值，并且对于范围扫描，可能会读取叶级别中的更多页面)。因此，据说树是平衡的。正是这个特性提供了可预测的性能，并且级别的数量可以很好地伸缩，也就是说，级别的数量随着索引记录的数量缓慢增长。当需要从相对较慢的存储设备(如磁盘)访问数据时，这一特性尤为重要。
Note
你可能也听说过 T 树索引。虽然 B 树索引针对磁盘访问进行了优化，但 T 树索引类似于 B 树索引，只是它们针对内存访问进行了优化。因此，将所有索引数据存储在内存中的NDBCluster存储引擎使用 T 树索引，即使它们在 SQL 级别被称为 B 树索引。
在本节的开始，我们提到了 B 树索引是 MySQL 中最常用的索引类型。事实上，如果您有任何 InnoDB 表，即使您自己从未添加任何索引，您也在使用 B 树索引。InnoDB 使用聚集索引存储数据索引，这实际上意味着行存储在 B+树索引中。B 树索引也不仅仅用于关系数据库，例如，一些文件系统以 B 树结构组织它们的元数据。
B 树索引的一个重要特性是，它们只能用于比较索引列的整值或左前缀。这意味着，如果您想检查索引日期的月份是否是五月，则不能使用该索引。如果您想检查一个索引字符串是否包含一个给定的短语，这也是一样的。
当您在一个索引中包含多个列时，同样的原则也适用。考虑索引(Name, Birthdate):在这种情况下，您可以使用索引来搜索给定的姓名或者姓名和生日的组合。但是，在不知道姓名的情况下，不能使用索引来搜索具有给定出生日期的人。
有几种方法可以处理这种限制。在某些情况下，可以使用函数索引，或者可以将有关列的信息提取到可以索引的生成列中。在其他情况下，可以使用另一种索引类型。如下所述，例如，可以使用全文索引来搜索字符串中带有短语“query performance tuning”的列。
全文索引
全文索引专门用于回答诸如“哪个文档包含这个字符串？”也就是说，它们没有经过优化来查找列与字符串完全匹配的行——因此，B 树索引是更好的选择。
全文索引通过对被索引的文本进行标记来工作。具体如何实现取决于所使用的解析器。InnoDB 支持使用自定义解析器，但通常使用内置解析器。默认解析器假设文本使用空格作为单词分隔符。MySQL 包括两个可选的解析器:支持中文、日文和韩文的 ngram 解析器 2 和支持日文的 MeCab 解析器。
InnoDB 使用一个名为FTS_DOC_ID的特殊列将全文索引链接到行，该列是一个bigint unsigned NOT NULL列。如果您添加了全文索引，而该列尚不存在，InnoDB 会将其添加为隐藏列。添加隐藏列需要重新构建表，因此如果要向大型表添加全文索引，就需要考虑这一点。如果您知道您打算对一个表使用全文索引，那么您可以预先自己添加该列以及该列的惟一索引FTS_DOC_ID_INDEX。您也可以选择使用FTS_DOC_ID列作为您的主键，但是请注意不允许重用FTS_DOC_ID值。自己准备表格的示例如下:
DROP TABLE IF EXISTS db1.person;
CREATE TABLE db1.person (
FTS_DOC_ID bigint unsigned NOT NULL auto_increment,
Name varchar(50),
Description text,
PRIMARY KEY (FTS_DOC_ID),
FULLTEXT INDEX (Description)
);
如果您没有FTS_DOC_ID列，并且您将一个全文列添加到一个现有的表中，MySQL 将返回一个警告，告诉该表已经被重建以添加该列:
Warning (code 124): InnoDB rebuilding table to add column FTS_DOC_ID
如果您计划使用全文索引，从性能角度来看，建议显式添加FTS_DOC_ID列，并将其设置为表上的主键，或者为其创建一个辅助唯一索引。自己创建列的缺点是必须自己管理值。
另一种专门的索引类型是针对空间数据的。全文索引用于文本文档(或字符串)，而空间索引用于空间数据类型。
空间索引(R 树)
从历史上看，空间特性在 MySQL 中使用得不多。然而，随着 5.7 版的 InnoDB 对空间索引的支持以及 MySQL 8 中对为空间数据指定空间参考系统标识符(SRID)的支持等其他改进，您可能会在某些时候需要空间索引。
空间索引的一个典型用例是一个包含感兴趣点的表，每个点的位置与其余信息存储在一起。例如，用户可以要求获得其当前位置 50 公里内的所有电动车辆充电站。为了尽可能高效地回答这样的问题，您将需要一个空间索引。
MySQL 将空间索引实现为 R 树。R 代表矩形，暗示了索引的用法。R 树索引组织数据，使得在空间上接近的点彼此靠近地存储在索引中。这使得确定空间值是否满足某些边界条件(例如，矩形)变得有效。
只有当列声明为NOT NULL并且已经设置了空间参考系统标识符时，才能使用空间索引。空间条件是使用一个函数指定的，比如MBRContains()，它接受两个空间值，并返回第一个值是否包含另一个值。否则，对使用空间索引没有特殊要求。清单 14-6 展示了一个带有空间索引的表和一个可以使用该索引的查询的例子。
mysql> CREATE TABLE db1.city (
id int unsigned NOT NULL,
Name varchar(50) NOT NULL,
Location point SRID 4326 NOT NULL,
PRIMARY KEY (id),
SPATIAL INDEX (Location));
Query OK, 0 rows affected (0.5578 sec)
mysql> INSERT INTO db1.city
VALUES (1, 'Sydney',
ST_GeomFromText('Point(-33.8650 151.2094)',
4326));
Query OK, 1 row affected (0.0783 sec)
mysql> SET @boundary = ST_GeomFromText('Polygon((-9 112, -45 112, -45 160, -9 160, -9 112))', 4326);
Query OK, 0 rows affected (0.0004 sec)
mysql> SELECT id, Name
FROM db1.city
WHERE MBRContains(@boundary, Location);
+----+--------+
| id | Name |
+----+--------+
| 1 | Sydney |
+----+--------+
1 row in set (0.0006 sec)
Listing 14-6Using a spatial index
在本例中，一个包含城市位置的表在Location列上有一个空间索引。空间参考系统标识符(SRID)设置为 4326 来表示地球。对于这个示例，插入了一行，并定义了一个边界(如果您很好奇，那么边界包含澳大利亚)。您也可以在MBRContains()函数中直接指定多边形，但是这里分两步完成，以使查询的各个部分更加清晰。
因此，空间索引有助于回答某个几何形状是否在某个边界内。同样，多值索引可以帮助回答给定值是否在值列表中。
多值索引
MySQL 在 MySQL 5.7 中引入了对 JSON 数据类型的支持，并在 MySQL 8 中用 MySQL 文档存储扩展了该特性。您可以使用生成列的索引或函数索引来创建 JSON 文档的索引；然而，到目前为止讨论的索引类型没有涵盖的一个用例是搜索 JSON 数组包含一些值的文档。一个例子是城市的集合，每个城市都有一组郊区。前一章中的 JSON 文档例子就是这样的:
{
"name": "Sydney",
"demographics": {
"population": 5500000
},
"geography": {
"country": "Australia",
"state": "NSW"
},
"suburbs": [
"The Rocks",
"Surry Hills",
"Paramatta"
]
}
如果您想搜索城市集合中的所有城市，并返回那些郊区名为“Surry Hills”的城市，那么您需要一个多值索引。MySQL 8.0.17 增加了对多值索引的支持。
解释多值索引如何有用的最简单的方法是看一个例子。清单 14-7 从world_x示例数据库中获取countryinfo表，将其复制到mvalue_index表中，并对其进行修改，使每个 JSON 文档都包含一个城市数组，其中包含这些城市的人口及其所在的地区。最后，包含一个查询来展示检索澳大利亚所有城市名称的示例(_id = 'AUS')。这些查询也可以在本书的 GitHub 资源库的文件listing_14_7.sql中找到，并且可以在 MySQL Shell 中使用命令\source listing_14_7.sql执行。
mysql> \use world_x
Default schema set to `world_x`.
Fetching table and column names from `world_x` for auto-completion... Press ^C to stop.
mysql> DROP TABLE IF EXISTS mvalue_index;
Query OK, 0 rows affected, 1 warning (0.0509 sec)
Note (code 1051): Unknown table 'world_x.mvalue_index'
mysql> CREATE TABLE mvalue_index LIKE countryinfo;
Query OK, 0 rows affected (0.3419 sec)
mysql> INSERT INTO mvalue_index (doc)
SELECT doc
FROM countryinfo;
Query OK, 239 rows affected (0.5781 sec)
Records: 239 Duplicates: 0 Warnings: 0
mysql> UPDATE mvalue_index
SET doc = JSON_INSERT(
doc,
'$.cities',
(SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'district', district,
'name', name,
'population',
Info->'$.Population'
)
)
FROM city
WHERE CountryCode = mvalue_index.doc->>'$.Code'
)
);
Query OK, 239 rows affected (3.6697 sec)
Rows matched: 239 Changed: 239 Warnings: 0
mysql> SELECT JSON_PRETTY(doc->>'$.cities[*].name')
FROM mvalue_index
WHERE doc->>'$.Code' = 'AUS'\G
*************************** 1\. row ***************************
JSON_PRETTY(doc->>'$.cities[*].name'): [
"Sydney",
"Melbourne",
"Brisbane",
"Perth",
"Adelaide",
"Canberra",
"Gold Coast",
"Newcastle",
"Central Coast",
"Wollongong",
"Hobart",
"Geelong",
"Townsville",
"Cairns"
]
1 row in set (0.0022 sec)
Listing 14-7Preparing the mvalue_index table for multi-valued indexes
清单首先将world_x模式作为默认模式，然后删除mvalue_index表(如果存在的话),并使用与countryinfo表相同的定义和相同的数据再次创建它。您也可以直接修改countryinfo表，但是通过处理mvalue_index副本，您可以通过删除mvalue_index表来轻松地重置world_x模式。该表由一个名为doc的 JSON 文档列和一个名为_id的生成列(主键)组成:
mysql> SHOW CREATE TABLE mvalue_index\G
*************************** 1\. row ***************************
Table: mvalue_index
Create Table: CREATE TABLE `mvalue_index` (
`doc` json DEFAULT NULL,
`_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
`_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0006 sec)
UPDATE语句使用JSON_ARRAYAGG()函数为每个国家创建一个 JSON 数组，该数组包含三个 JSON 对象，即地区、名称和人口。最后，执行一个SELECT语句来返回澳大利亚城市的名称。
现在，您可以为城市名称添加多值索引:
ALTER TABLE mvalue_index
ADD INDEX (((CAST(doc->>'$.cities[*].name'
AS char(35) ARRAY))));
索引从位于doc文档根目录下的cities数组的所有元素中提取name对象。产生的数据被转换成一个由char(35)值组成的数组。数据类型被选择为城市名来源于char(35)的city表。在CAST()函数中，您将char用于char和varchar数据类型。
通过使用MEMBER OF操作符以及JSON_CONTAINS()和JSON_OVERLAPS()函数，新索引可以用于WHERE子句。MEMBER OF操作符询问给定的值是否是数组的成员。JSON_CONTAINS()非常相似，但与MEMBER OF的参考搜索相比，需要范围搜索。JSON_OVERLAPS()可用于查找至少包含几个值之一的文档。清单 14-8 显示了一个使用操作符和每个函数的例子。
mysql> SELECT doc->>'$.Code' AS Code, doc->>'$.Name'
FROM mvalue_index
WHERE 'Sydney' MEMBER OF (doc->'$.cities[*].name');
+------+----------------+
| Code | doc->>'$.Name' |
+------+----------------+
| AUS | Australia |
+------+----------------+
1 row in set (0.0032 sec)
mysql> SELECT doc->>'$.Code' AS Code, doc->>'$.Name'
FROM mvalue_index
WHERE JSON_CONTAINS(
doc->'$.cities[*].name',
'"Sydney"'
);
+------+----------------+
| Code | doc->>'$.Name' |
+------+----------------+
| AUS | Australia |
+------+----------------+
1 row in set (0.0033 sec)
mysql> SELECT doc->>'$.Code' AS Code, doc->>'$.Name'
FROM mvalue_index
WHERE JSON_OVERLAPS(
doc->'$.cities[*].name',
'["Sydney", "New York"]'
);
+------+----------------+
| Code | doc->>'$.Name' |
+------+----------------+
| AUS | Australia |
| USA | United States |
+------+----------------+
2 rows in set (0.0060 sec)
Listing 14-8Queries taking advantage of a multi-valued index
使用MEMBER OF和JSON_CONTAINS()的两个查询都寻找城市名为悉尼的国家。使用JSON_OVERLAPS()的最后一个查询查找城市名为悉尼或纽约或两者皆名的国家。
MySQL 中还剩下一种索引类型:散列索引。
哈希索引
如果您想搜索某列正好等于某个值的行，您可以使用本章前面讨论的 B 树索引。不过还有一个替代方法:为每个列值创建一个散列，并使用该散列来搜索匹配的行。你为什么要这么做？答案是，这是一种非常快速的查找行的方法。
散列索引在 MySQL 中用得不多。一个值得注意的例外是NDBCluster存储引擎，它使用散列索引来确保主键和惟一索引的惟一性，并使用这些索引来提供快速查找。在 InnoDB 方面，没有对哈希索引的直接支持；但是，InnoDB 有一个名为自适应散列索引的特性，值得多考虑一下。
自适应散列索引特性在 InnoDB 中自动工作。如果 InnoDB 检测到您正在频繁使用二级索引，并且启用了自适应散列索引，它将动态构建最常用值的散列索引。哈希索引以独占方式存储在缓冲池中，因此当您重新启动 MySQL 时，它不会被持久化。如果 InnoDB 检测到内存可以更好地用于将更多页面加载到缓冲池中，它将丢弃部分散列索引。这就是所谓的自适应索引的含义:InnoDB 将努力使它适应您的查询。您可以使用innodb_adaptive_hash_index选项启用或禁用该功能。
理论上，自适应哈希索引是一个双赢的局面。您获得了拥有散列索引的优势，而无需考虑需要为哪些列添加它，并且内存使用都是自动处理的。但是，启用它会产生开销，而且并非所有工作负载都能从中受益。事实上，对于某些工作负载，开销会变得非常大，以至于出现严重的性能问题。
有两种方法可以监控自适应散列索引:信息模式中的INNODB_METRICS表和 InnoDB 监控器。INNODB_METRICS表包括自适应散列索引的八个指标，其中两个默认启用。清单 14-9 显示了INNODB_METRICS中包含的八个指标。
mysql> SELECT NAME, COUNT, STATUS, COMMENT
FROM information_schema.INNODB_METRICS
WHERE SUBSYSTEM = 'adaptive_hash_index'\G
*************************** 1\. row ***************************
NAME: adaptive_hash_searches
COUNT: 10717
STATUS: enabled
COMMENT: Number of successful searches using Adaptive Hash Index
*************************** 2\. row ***************************
NAME: adaptive_hash_searches_btree
COUNT: 29515
STATUS: enabled
COMMENT: Number of searches using B-tree on an index search
*************************** 3\. row ***************************
NAME: adaptive_hash_pages_added
COUNT: 0
STATUS: disabled
COMMENT: Number of index pages on which the Adaptive Hash Index is built
*************************** 4\. row ***************************
NAME: adaptive_hash_pages_removed
COUNT: 0
STATUS: disabled
COMMENT: Number of index pages whose corresponding Adaptive Hash Index entries were removed
*************************** 5\. row ***************************
NAME: adaptive_hash_rows_added
COUNT: 0
STATUS: disabled
COMMENT: Number of Adaptive Hash Index rows added
*************************** 6\. row ***************************
NAME: adaptive_hash_rows_removed
COUNT: 0
STATUS: disabled
COMMENT: Number of Adaptive Hash Index rows removed
*************************** 7\. row ***************************
NAME: adaptive_hash_rows_deleted_no_hash_entry
COUNT: 0
STATUS: disabled
COMMENT: Number of rows deleted that did not have corresponding Adaptive Hash Index entries
*************************** 8\. row ***************************
NAME: adaptive_hash_rows_updated
COUNT: 0
STATUS: disabled
COMMENT: Number of Adaptive Hash Index rows updated
8 rows in set (0.0015 sec)
Listing 14-9The metrics for the adaptive hash index in INNODB_METRICS
默认情况下，使用自适应散列索引(adaptive_hash_searches)的成功搜索次数和使用 B 树索引(adaptive_hash_searches_btree)完成的搜索次数是启用的。您可以使用这些来确定与底层 B 树索引相比，InnoDB 使用散列索引解析查询的频率。其他指标不太需要，因此默认情况下是禁用的。也就是说，如果您想更详细地探索自适应散列索引的用处，您可以安全地启用这六个指标。
监控自适应散列索引的另一种方法是使用 InnoDB 监控器，如清单 14-10 所示。在您的情况下，输出中的数据会有所不同。
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1\. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2019-05-05 17:22:14 0x1a7c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 52 srv_active, 0 srv_shutdown, 25121 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 8
OS WAIT ARRAY INFO: signal count 11
RW-shared spins 12, rounds 12, OS waits 0
RW-excl spins 102, rounds 574, OS waits 8
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 1.00 RW-shared, 5.63 RW-excl, 0.00 RW-sx
...
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 2 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 2 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 2 buffer(s)
Hash table size 2267, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
...
Listing 14-10Using the InnoDB monitor to monitor the adaptive hash index
首先要检查的是信号量部分。如果自适应散列索引是争用的主要来源，那么在btr0sea.ic文件(自适应散列索引在源代码中实现的地方)周围会有信号量。如果您偶尔——但很少——看到信号量，这不一定是个问题，但是如果您看到频繁且长的信号量，您可能最好禁用自适应散列索引。
感兴趣的另一部分是插入缓冲区和自适应散列索引部分。这包括用于哈希索引的内存量，以及使用哈希和非哈希搜索回答查询的速率。请注意，这些速率是针对监控器输出顶部附近列出的时间段的，在本例中，是针对 2019-05-05 17:22:14 之前的最后 16 秒。
对支持的索引类型的讨论到此结束。索引还有更多的内容，因为有几个特性值得您熟悉。
索引功能
知道存在哪些类型的索引是一回事，但能够充分利用它们是另一回事。要做到这一点，您需要更多地了解 MySQL 中与索引相关的特性。这些范围从以逆序排序索引中的值到函数索引和自动生成的索引。本节将介绍这些功能，以便您可以在日常工作中使用它们。
功能索引
到目前为止，索引已经直接应用于列。这是添加索引的最常见方式，但也有需要使用派生值的情况。例如，一个查询要求所有生日在五月的人:
DROP TABLE IF EXISTS db1.person;
CREATE TABLE db1.person (
Id int unsigned NOT NULL,
Name varchar(50),
Birthdate date NOT NULL,
PRIMARY KEY (Id)
);
SELECT *
FROM db1.person
WHERE MONTH(Birthdate) = 5;
如果您在Birthdate列上添加了一个索引，这不能用于回答该查询，因为日期是根据它们的完整值存储的，并且您没有与该列的最左边部分进行匹配。(另一方面，搜索所有 1970 年出生的人可以在Birthdate列上使用 B 树索引。)
一种方法是生成一个包含派生值的列。在 MySQL 5.7 和更高版本中，您可以告诉 MySQL 自动更新列，例如:
CREATE TABLE db1.person (
Id int unsigned NOT NULL,
Name varchar(50) NOT NULL,
Birthdate date NOT NULL,
BirthMonth tinyint unsigned
GENERATED ALWAYS AS (MONTH(Birthdate))
VIRTUAL NOT NULL,
PRIMARY KEY (Id),
INDEX (BirthMonth)
);
在 MySQL 8.0.13 中，有一种更直接的方式来实现这一点。您可以直接索引函数的结果:
CREATE TABLE db1.person (
Id int unsigned NOT NULL,
Name varchar(50) NOT NULL,
Birthdate date NOT NULL,
PRIMARY KEY (Id),
INDEX ((MONTH(Birthdate)))
);
使用函数索引的优点是，它更明确地说明了您想要索引什么，并且您没有额外的BirthMonth列。否则，添加函数索引的两种方式工作方式相同。
前缀索引
表的索引部分变得比表数据本身大是很常见的。如果索引大型字符串值，情况尤其如此。B 树索引的索引数据的最大长度也有限制——使用DYNAMIC或COMPRESSED行格式的 InnoDB 表的最大长度为 3072 字节，其他表的最大长度更小。这实际上意味着您不能索引一个text列，更不用说一个longtext列了。减轻大型字符串索引的一种方法是只索引值的第一部分。这被称为前缀索引。
通过指定要索引的字符串的字符数或二进制对象的字节数，可以创建前缀索引。如果您想索引city表中Name列的前十个字符(来自world数据库)，您可以这样做
ALTER TABLE world.city ADD INDEX (Name(10));
请注意要索引的字符数是如何添加到括号中的。只要您选择足够多的字符来提供良好的选择性，这个索引将几乎与索引整个名称一样好，并且从好的方面来说，它使用更少的存储和内存。需要包含多少个字符？这完全取决于您要索引的数据。您可以查询数据来了解前缀的独特性。清单 14-11 展示了一个检查有多少城市名共享前十个字符的例子。
mysql> SELECT LEFT(Name, 10), COUNT(*),
COUNT(DISTINCT Name) AS 'Distinct'
FROM world.city
GROUP BY LEFT(Name, 10)
ORDER BY COUNT(*) DESC, LEFT(Name, 10)
LIMIT 10;
+----------------+----------+----------+
| LEFT(Name, 10) | COUNT(*) | Distinct |
+----------------+----------+----------+
| San Pedro | 6 | 6 |
| San Fernan | 5 | 3 |
| San Miguel | 5 | 3 |
| Santiago d | 5 | 5 |
| San Felipe | 4 | 3 |
| San José | 4 | 1 |
| Santa Cruz | 4 | 4 |
| São José d | 4 | 4 |
| Cambridge | 3 | 1 |
| Ciudad de | 3 | 3 |
+----------------+----------+----------+
10 rows in set (0.0049 sec)
Listing 14-11The frequency of city names based on the first ten characters
这表明，使用这个索引前缀，您将最多读取六个城市来找到匹配。虽然这不仅仅是一个完整的匹配，但仍然比扫描整个表要好得多。在这种比较中，您当然还需要验证前缀匹配的数量是由于前缀冲突，还是城市名称相同。例如，对于“Cambridge”，有三个城市使用了该名称，因此无论是索引前十个字符还是整个名称都没有区别。您可以对不同的前缀长度进行这种分析，以了解增加索引大小会带来微小回报的阈值。在许多情况下，您不需要那么多的字符来使索引正常工作。
如果你认为你可以删除一个索引，或者你想推出一个索引，但不能让它立即生效，你该怎么办？答案是隐形索引。
不可见索引
MySQL 8 引入了一个叫做不可见索引的新特性。它允许您拥有一个维护好并随时可以使用的索引，但是优化器会忽略这个索引，直到您决定让它可见。这允许您在复制拓扑中推出新索引，或者禁用您认为不需要或类似的索引。您可以快速启用或禁用索引，因为它只需要更新表的元数据，所以更改是“即时的”
例如，如果您认为不需要索引，那么在告诉 MySQL 删除索引之前，首先使它不可见可以让您监控数据库在没有索引的情况下如何工作。如果发现某些查询——例如，在您监控的时间段内没有执行的月度报告查询——确实需要索引，您可以快速重新启用它。
使用关键字INVISIBLE将索引标记为不可见，使用关键字VISIBLE使不可见的索引再次可见。例如，要在world.city表的Name列上创建一个不可见的索引，并在以后使其可见，您可以使用
mysql> ALTER TABLE world.city ADD INDEX (Name) INVISIBLE;
Query OK, 0 rows affected (0.0649 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE world.city ALTER INDEX Name VISIBLE;
Query OK, 0 rows affected (0.0131 sec)
Records: 0 Duplicates: 0 Warnings: 0
如果禁用索引，并且查询使用了引用隐藏索引的索引提示，则查询将返回错误:
ERROR: 1176: Key 'Name' doesn't exist in table 'city'
您可以通过启用优化器开关use_invisible_indexes(默认为off)来覆盖索引的不可见性。如果您遇到由于某个索引不可见而无法立即重新启用该索引的问题，或者如果您希望在新索引全面可用之前对其进行测试，这将非常有用。为连接临时启用不可见索引的一个示例是
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
即使启用了use_invisible_indexes优化器开关，也不允许在索引提示中引用索引。
MySQL 8 的另一个新特性是降序索引。
降序索引
在 MySQL 5.7 和更早的版本中，当你添加一个 B 树索引时，它总是以升序排序。这对于查找精确匹配、按索引升序检索行等非常有用。然而，虽然升序索引可以加快按降序查找行的查询速度，但它们并不那么有效。MySQL 8 增加了降序索引来帮助处理这些用例。
您不需要做什么特别的事情来利用降序索引。所需要的只是将关键字DESC用于索引，例如:
ALTER TABLE world.city ADD INDEX (Name DESC);
如果索引中有多列，则不需要以升序或降序包含所有列。您可以混合使用升序和降序列，这样最适合您的查询。
分区和索引
如果创建分区表，分区列必须是主键和所有唯一键的一部分。原因是 MySQL 没有全局索引的概念，所以必须保证唯一性检查只需要考虑单个分区。
关于性能调优，分区可用于有效地使用两个索引来解析查询，而无需使用索引合并。当用于分区的列在查询的条件中使用时，MySQL 将修剪分区，因此只搜索与条件匹配的分区。然后可以使用索引来解析查询的其余部分。
考虑一个表t_part,它根据作为时间戳的Created列进行分区，每个月有一个分区。如果查询 2019 年 3 月val列的值小于 2 的所有行，那么查询将首先根据Created的值修剪分区，然后使用val上的索引。清单 14-12 显示了一个这样的例子。
mysql> CREATE TABLE db1.t_part (
id int unsigned NOT NULL AUTO_INCREMENT,
Created timestamp NOT NULL,
val int unsigned NOT NULL,
PRIMARY KEY (id, Created),
INDEX (val)
) ENGINE=InnoDB
PARTITION BY RANGE (unix_timestamp(Created))
(PARTITION p201901 VALUES LESS THAN (1548939600),
PARTITION p201902 VALUES LESS THAN (1551358800),
PARTITION p201903 VALUES LESS THAN (1554037200),
PARTITION p201904 VALUES LESS THAN (1556632800),
PARTITION p201905 VALUES LESS THAN (1559311200),
PARTITION p201906 VALUES LESS THAN (1561903200),
PARTITION p201907 VALUES LESS THAN (1564581600),
PARTITION p201908 VALUES LESS THAN (1567260000),
PARTITION pmax VALUES LESS THAN MAXVALUE);
1 row in set (5.4625 sec)
-- Insert random data
-- 1546261200 is 2019-01-01 00:00:00 UTC
-- The common table expression (CTE) is just
-- a convenient way to quickly generate 1000 rows.
mysql> INSERT INTO db1.t_part (Created, val)
WITH RECURSIVE counter (i) AS (
SELECT 1
UNION SELECT i+1
FROM counter
WHERE i < 1000)
SELECT FROM_UNIXTIME(
FLOOR(RAND()*(1567260000-1546261200))
+1546261200
), FLOOR(RAND()*10) FROM counter;
Query OK, 1000 rows affected (0.0238 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> EXPLAIN
SELECT id, Created, val
FROM db1.t_part
WHERE Created BETWEEN '2019-03-01 00:00:00'
AND '2019-03-31 23:59:59'
AND val < 2\G
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: t_part
partitions: p201903
type: range
possible_keys: val
key: val
key_len: 4
ref: NULL
rows: 22
filtered: 11.110000610351562
Extra: Using where; Using index
1 row in set, 1 warning (0.0005 sec)
Listing 14-12Combining partition pruning and filtering using an index
使用Created列的 Unix 时间戳，按范围对t_part表进行分区。EXPLAIN输出(EXPLAIN将在第 20 章中详细介绍)显示只有p201903分区将被包含在查询中，并且val索引将被用作索引。鉴于示例使用随机数据，EXPLAIN的确切输出可能会有所不同。
到目前为止，所有关于索引的讨论都是针对显式创建的索引。对于某些查询，MySQL 也将能够自动生成索引。这是要讨论的最后一个索引特性。
自动生成的索引
对于包含与其他表或子查询联接的子查询的查询，联接的开销可能很大，因为子查询不能包含显式索引。为了避免对子查询生成的临时表进行全表扫描，MySQL 可以在连接条件中添加一个自动生成的索引。
例如，考虑来自sakila样本数据库的film表。它有一个名为release_year的栏目，标明了电影上映的年份。如果要查询有数据的年份中每年发行了多少部电影，可以使用下面的查询(是的，如果没有子查询，该查询可以写得更好，但这样写是为了演示自动生成索引的功能):
SELECT release_year, COUNT(*)
FROM sakila.film
INNER JOIN (SELECT DISTINCT release_year
FROM sakila.film
) release_years USING (release_year)
GROUP BY release_year;
MySQL 选择对film表进行全表扫描，并在子查询上添加一个自动生成的索引。当 MySQL 添加一个自动生成的索引时，EXPLAIN输出将包含<auto_key0>(或者用不同的值替换为 0)作为可能的键和使用的键。
自动生成的索引可以显著提高包含优化器无法重写为普通连接的子查询的查询的性能。最棒的是，它是自动发生的。
索引特性的讨论到此结束。在讨论应该如何使用索引之前，也有必要了解 InnoDB 如何使用索引。
InnoDB 和索引
自 20 世纪 90 年代中期的第一个版本以来，InnoDB 组织其表的方式一直是使用聚集索引来组织数据。这一事实导致了一种常见的说法，即 InnoDB 中的一切都是索引。数据的组织实际上就是一个索引。默认情况下，InnoDB 使用聚集索引的主键。如果没有主键，它将寻找不允许NULL值的唯一索引。作为最后的手段，将使用一种自动递增计数器向表中添加一个隐藏列。
对于索引组织的表，InnoDB 中的所有内容都是索引。聚集索引本身被组织为 B+-树索引，实际的行数据在叶页中。这对查询性能和索引有一些影响。下一节将介绍 InnoDB 如何使用主键以及主键对辅键的意义，提供一些建议，并介绍索引组织表的最佳用例。
聚集索引
因为数据是根据聚集索引(主键或其替代项)组织的，所以主键的选择非常重要。如果在现有值之间插入一个主键值的新行，InnoDB 将不得不重新组织数据，以便为新行腾出空间。在最坏的情况下，InnoDB 将不得不将现有页面一分为二，因为页面大小是固定的。页面拆分会导致底层存储上的叶页面顺序混乱，从而导致更多的随机 I/O，进而导致更差的查询性能。页面分割将在第 25 章中作为 DDL 和批量数据加载的一部分进行讨论。
次要索引
辅助索引的叶页存储对行本身的引用。因为根据聚集索引，行存储在 B+-树索引中，所以所有辅助索引都必须包括聚集索引的值。如果您选择了一个值需要很多字节的列，例如，一个具有很长且可能是多字节字符串的列，这会大大增加二级索引的大小。
这还意味着，当您使用辅助索引执行查找时，实际上会进行两次索引查找:首先是预期的辅助关键字查找，然后从叶页面获取主键值并用于主关键字查找以获取实际数据。
对于非唯一二级索引，如果您有一个显式主键或一个NOT NULL唯一索引，则添加到索引中的是用于主键的列。MySQL 知道这些额外的列，即使它们没有被显式地作为索引的一部分，如果它将改进查询计划，MySQL 将使用它们。
推荐
由于 InnoDB 使用主键的方式以及将主键添加到二级索引的方式，最好使用使用尽可能少字节的单调递增主键。自动递增的整数满足这些属性，因此是一个很好的主键。
如果表没有任何合适的索引，则用于聚集索引的隐藏列使用类似自动递增的计数器来生成新值。但是，由于这个计数器对于 MySQL 实例中的所有 InnoDB 表都是全局的，并带有一个隐藏的主键，因此它可能会成为一个争论点。隐藏键也不能在复制中用来定位受事件影响的行，组复制需要主键或NOT NULL唯一索引来进行冲突检测。因此，建议总是为所有表显式选择一个主键。
另一方面，UUID 不是单调递增的，不是一个好的选择。MySQL 8 中的一个选项是使用第二个参数设置为 1 的UUID_TO_BIN()函数，这将使 MySQL 交换第一组和第三组十六进制数字。第三组是 UUID 时间戳部分的高位字段，因此将其放在 UUID 的开头有助于确保 ID 不断增加，并将它们存储为二进制数据所需的存储量不到十六进制值的一半。
最佳使用案例
按索引组织的表对于使用该索引的查询特别有用。顾名思义,“聚集索引”将具有相似聚集索引值的行存储在彼此附近。由于 InnoDB 总是将整个页面读入内存，这也意味着主键值相似的两行可能会被一起读入。如果您的查询中需要这两个行，或者在查询中彼此紧接着执行，那么缓冲池中已经有了第二行。
现在，您应该对 MySQL 中的索引以及 InnoDB 如何使用索引(包括其数据组织)有了很好的背景知识。现在是时候将所有这些放在一起讨论索引策略了。
索引策略
对于索引来说，最大的问题是索引什么，其次是使用什么样的索引和索引特性。不可能创建最终的逐步说明来确保最佳索引；为此，需要经验和对模式、数据和查询的良好理解。然而，可以给出一些通用指南，这将在本节中讨论。
首先要考虑的是何时应该添加索引；是应该在最初创建表时进行还是以后进行。然后是主键的选择以及如何选择的注意事项。最后，还有二级索引，包括要向索引添加多少列，以及该索引是否可以用作覆盖索引。
何时应该添加或删除索引？
索引维护是一项永无止境的任务。它从您第一次创建表时开始，并在表的整个生命周期中持续。不要轻视索引工作——如前所述，好的和差的索引之间的差别可以是几个数量级。你不能通过投入更多的硬件资源来摆脱索引差的情况。索引不仅影响原始查询性能，还会影响锁定(将在第 18 章中进一步讨论)、内存使用和 CPU 使用。
当您创建表时，您应该特别花时间选择一个好的主键。在表的生命周期中，主键通常不会改变，如果您决定改变主键，对于按索引组织的表，它必然需要完全重建表。二级索引可以随着时间的推移进行更大程度的调整。事实上，如果您计划为表的初始填充导入大量数据，最好等到数据加载完毕后再添加辅助索引。一个可能的例外是唯一索引，因为它们是数据验证所必需的。
一旦创建了表并填充了初始数据，就需要监控表的使用情况。在sys模式中有两个视图可用于通过全表扫描来查找表和语句:
-
schema_tables_with_full_table_scans: 该视图显示所有不使用索引读取行的表，并根据索引号按降序排列。如果一个表在没有使用索引的情况下读取了大量的行，您可以使用这个表查找查询，看看索引是否有帮助。该视图基于table_io_waits_summary_by_index_usage性能模式表，该表也可以直接使用，例如，如果您想要进行更高级的分析，比如在不使用索引的情况下查找读取的行的百分比。 -
statements_with_full_table_scans: 该视图显示了根本不使用索引或者没有使用好的索引的语句的规范化版本。这些语句按照它们在完全没有使用索引的情况下被执行的次数排序，然后按照它们没有使用有效索引的次数排序——两者都是降序。该视图基于events_statements_summary_by_digest性能模式表。
第 19 和 20 章将更详细地介绍这些视图和底层性能模式表的使用。
当您发现查询可以从额外的索引中受益时，您需要评估在执行查询时获得额外好处的成本是否值得。
同时，您还需要留意是否有不再使用的索引。性能模式和sys模式对于查找未使用或不常使用的索引特别有用。三个有用的模式视图是
-
schema_index_statistics: 该视图统计了使用给定索引读取、插入、更新和删除行的频率。像schema_tables_with_full_table_scan视图一样，schema_index_statistics基于table_io_waits_summary_by_index_usage性能模式表。 -
schema_unused_indexes: 该视图将返回自数据上次重置以来(不超过自上次重启以来)尚未使用的索引的名称。这个视图也基于table_io_waits_summary_by_index_usage性能模式表。 -
schema_redundant_indexes: 如果有两个索引覆盖相同的列，那么 InnoDB 需要加倍努力来保持索引最新，并且增加了优化器的负担，但是没有任何收获。顾名思义,schema_redundant_indexes视图可以用来查找冗余索引。该视图基于STATISTICS信息模式表。
当您使用前两个视图时，您必须记住数据来自性能模式中的内存表。如果您有一些只是偶尔执行的查询，那么统计数据可能无法反映您的总体索引需求。这就是不可见索引特性可以派上用场的情况之一，因为它允许您禁用索引，同时保留索引，直到您确定删除它是安全的。如果发现一些很少执行的查询需要索引，您可以很容易地再次启用索引。
如前所述，首先要考虑的是选择什么作为主键。您应该包括哪些列？那是接下来要讨论的事情。
主键的选择
当您使用按索引组织的表时，主索引的选择非常重要。主键会影响随机 I/O 和顺序 I/O 之间的比率、辅助索引的大小以及需要读入缓冲池的页面数量。InnoDB 表的主键总是 B+-树索引。
与聚集索引相关的最佳主键尽可能小(以字节为单位),保持单调递增，并对您频繁查询的行进行分组，并且彼此之间的时间间隔很短。实际上，要做到这一切是不可能的，在这种情况下，你需要做出最好的妥协。对于许多工作负载，自动递增的无符号整数int或bigint是一个不错的选择，这取决于表中预期的行数；但是，可能有一些特殊的考虑，比如跨多个 MySQL 实例的唯一性要求。主键最重要的特性是它应该尽可能的有序，并且是不可变的。如果更改某一行的主键的值，则需要将整行移动到聚集索引中的新位置。
Tip
自动递增的无符号整数通常是作为主键的好选择。它保持单调递增，不需要太多存储空间，并且在聚集索引中将最近的行组合在一起。
您可能认为对于聚集索引来说，隐藏主键可能是与任何其他列一样好的选择。毕竟它是一个自动递增的整数。然而，hidden key 有两个主要缺点:它只标识本地 MySQL 实例的行，并且计数器对于所有 InnoDB 表(在实例中)是全局的，没有用户定义的主键。隐藏键仅在本地有用，这意味着在复制中，隐藏值不能用于标识在副本上更新哪一行。计数器是全局的意味着它可能成为争用点，并在插入数据时导致性能下降。
底线是你应该总是明确地定义你想要什么作为你的主键。对于二级索引，有更多的选择，这将在下面看到。
添加二级索引
辅助索引是所有那些不是主键的索引。它们可以是唯一的，也可以是不唯一的，您可以在所有受支持的索引类型和功能之间进行选择。如何选择添加哪些索引？这一部分将使你更容易做出决定。
注意不要预先给一个表添加太多的索引。索引有开销，所以当您添加最终没有被使用的索引时，查询和整个系统的性能会更差。这并不意味着在创建表时不应该添加任何辅助索引。只是你需要花点心思。
执行查询时，可以通过多种方式使用辅助索引。其中一些如下:
-
**减少检查的行:**当您有一个
WHERE子句或连接条件来查找所需的行而不扫描整个表时，可以使用这个选项。 -
排序数据: B 树索引可用于按照查询所需的顺序读取行，从而允许 MySQL 绕过排序步骤。
-
**验证数据:**这就是唯一索引中的唯一性的用途。
-
**避免读取行:**覆盖索引可以返回所有需要的数据，而不需要读取整行。
-
查找
MIN()和MAX()**值:**对于GROUP BY查询，只需检查索引中的第一条和最后一条记录，就可以找到索引列的最小值和最大值。
主键显然也可以用于所有这些目的。从查询的角度来看，主键和辅键没有区别。
当您需要决定是否添加一个索引时，您需要问自己哪个目的需要该索引，以及它是否能够实现这些目的。一旦确认了这一点，就可以查看应该为多列索引添加哪些顺序列，以及是否应该添加额外的列。接下来的两个小节将对此进行更详细的讨论。
多列索引
只要不超过索引的最大宽度，最多可以向索引中添加 16 列或功能部分。这适用于主键和辅助索引。InnoDB 的每个索引限制为 3072 字节。如果包含使用可变宽度字符集的字符串，则它是计入索引宽度的最大可能宽度。
向索引中添加多个列的一个优点是，它允许您将索引用于多个条件。这是提高查询性能的一种非常有效的方法。例如，考虑一个查询，该查询在给定的国家中寻找具有该城市人口的最低要求的城市:
SELECT ID, Name, District, Population
FROM world.city
WHERE CountryCode = 'AUS'
AND Population > 1000000;
您可以使用CountryCode列上的索引来查找国家代码设置为 AUS 的城市，并且可以使用Population列上的索引来查找人口超过 100 万的城市。更好的是，您可以将它合并到一个包含两列的索引中。
你如何做到这一点很重要。国家代码使用相等的参考，而人口是一个范围搜索。一旦索引中的列用于范围搜索或排序，则除了作为覆盖索引的一部分之外，索引中就不能再使用其他列。对于本例，您需要在Population列之前添加CountryCode列，以便对两个条件使用索引:
ALTER TABLE world.city
ADD INDEX (CountryCode, Population);
在本例中，索引甚至可以用于对使用总体的结果进行排序。
如果您需要添加几个全部用于等式条件的列，那么有两件事情需要考虑:哪些列是最常使用的，以及该列对数据的过滤效果如何。当一个索引中有多个列时，MySQL 将只使用索引的左前缀。例如，如果你有一个索引(col_a, col_b, col_c)，你只能使用索引过滤col_b，如果你也过滤col_a(这必须是一个等式条件)。所以你需要谨慎选择顺序。在某些情况下，可能需要为相同的列添加多个索引，而这些索引之间的列顺序是不同的。
如果您不能根据使用情况决定包含列的顺序，那么首先添加最具选择性的列。下一章将讨论索引的选择性，但简而言之，一个列的值越多，它的选择性就越强。通过首先添加最具选择性的列，可以更快地缩小索引部分包含的行数。
您可能还希望包含不用于筛选的列。你为什么要这么做？答案是，它可以帮助形成一个覆盖索引。
覆盖索引
覆盖索引是表上的索引，其中给定查询的索引包括该表中所需的所有列。这意味着当 InnoDB 到达索引的叶页时，它已经拥有了所需的所有信息，并且不需要读取整行。根据您的表，这可能会很好地提高查询性能，特别是如果您可以使用它来排除大部分行，如大文本或 blob 列。
还可以使用覆盖索引来模拟辅助聚集索引。请记住，聚集索引只是一个 B+-树索引，整行都包含在叶页中。覆盖索引拥有叶页中行的完整子集，因此模拟了该列子集的聚集索引。与聚集索引一样，任何 B 树索引都将相似的值组合在一起，因此它可以用来减少读入缓冲池的页面数量，并且在执行索引扫描时有助于进行顺序 I/O。
但是，与聚集索引相比，覆盖索引有一些限制。覆盖索引仅模拟用于读取的聚集索引。如果需要写入数据，更改总是必须访问聚集索引。另一件事是，由于 InnoDB 的多版本并发控制(MVCC)，即使当您使用覆盖索引时，也需要检查聚集索引以验证是否存在另一个版本的行。
添加索引时，值得考虑索引所针对的查询需要哪些列。添加 select 部分中使用的任何额外列可能是值得的，即使索引不会用于对这些列进行筛选或排序。您需要平衡覆盖索引的好处和索引增加的大小。因此，如果您只是错过了一两个小列，这种策略非常有用。覆盖索引受益的查询越多，您可以接受添加到索引中的额外数据就越多。
摘要
本章是一次索引世界之旅。一个好的索引策略可能意味着一个数据库停止运转和一台运转良好的机器之间的差别。索引有助于减少查询中检查的行数，另外包含索引可以避免读取整行。另一方面，索引在存储和日常维护方面都有开销。因此，有必要平衡对索引的需求和拥有索引的成本。
MySQL 支持几种不同的索引类型。最重要的是 B 树索引，InnoDB 也用它来组织索引表中的行，该表使用聚集索引。其他索引类型包括全文索引、空间(R 树)索引、多值索引和散列索引。后一种类型在 InnoDB 中比较特殊，因为只有使用自适应散列索引特性才支持它，该特性决定自动添加哪些散列索引。
已经讨论了一系列索引功能。函数索引可用于索引在表达式中使用列的结果。前缀索引可用于减少文本和二进制数据类型的索引大小。不可见索引可以在新索引的展示过程中使用，也可以在软删除现有索引时使用。降序索引提高了按降序遍历索引值的效率。索引也在分区方面发挥作用，您可以使用分区来有效地实现对查询中的单个表使用两个索引的支持。最后，MySQL 能够自动生成与子查询相关的索引。
本章的最后一部分从 InnoDB 的细节和使用按索引组织的表的注意事项开始。这对于与主键相关的查询来说是最佳的，但是对于以随机主键顺序插入的数据和通过辅助索引查询的数据来说，效果不太好。
最后一节讨论了索引策略。第一次创建表时，请仔细选择主键。基于对度量的观察，可以在更大程度上随着时间的推移添加和删除辅助索引。您可以使用多列索引来筛选多列和/或进行排序。最后，覆盖索引可用于模拟辅助聚集索引。
关于什么是索引以及何时使用索引的讨论到此结束。在下一章讨论索引统计时，我们会看到更多关于索引的内容。
Footnotes [1](#Fn1_source)实际上，在有些情况下，MySQL 会自动生成特定于单个查询的索引。稍后在讨论索引特性时会详细介绍这一点。
https://dev.mysql.com/doc/refman/en/fulltext-search-ngram.html
十五、索引统计
在前一章中，你学习了索引。前面提到过，优化器评估每个索引来决定是否使用该索引。它是怎么做到的？这也是本章的主题，包括索引统计、如何查看有关索引统计的信息以及如何维护统计。
本章首先讨论什么是索引统计以及 InnoDB 如何处理索引统计。然后，您将了解瞬时和持久统计。本章的其余部分将介绍如何监控统计数据并更新它们。
什么是索引统计？
当 MySQL 决定是否使用索引时，它归结为 MySQL 认为索引对查询有多有效。请记住，当您使用辅助索引时，将有效地通过额外的主键查找来获取数据。辅助索引的排序方式也不同于行，因此使用索引通常意味着随机 I/O(这可以通过使用覆盖索引来实现)。另一方面，表扫描在很大程度上是顺序 I/O。因此，对于行来说，进行表扫描比使用二级索引查找相同的行更便宜。
这意味着要使索引有效，它必须过滤掉表中的大部分内容。必须过滤掉多少取决于硬件的性能特征、缓冲池中有多少表、表定义等等。在旧的旋转磁盘时代，经验法则是，如果需要超过 30%的行，那么表扫描是首选。内存中的行数越多，磁盘的随机 I/O 性能越好，这个阈值就越高。
Note
覆盖索引改变了这种情况，因为它们减少了跳转到实际行数据所需的随机 I/O 量。
这就是索引统计发挥作用的地方。优化器——它是 MySQL 的一部分，决定使用哪个查询计划——需要一些简单的方法来确定一个索引对于给定的查询计划有多好。优化器显然知道索引包括哪些列，但是它还需要一些指标来衡量索引对行的过滤能力。这些信息就是索引统计提供的信息。因此，索引统计是对索引选择性的一种度量。有两个主要的统计数据:唯一值的数量和某个范围内的值的数量。
在讨论索引统计时，唯一值的数量是最常想到的。这就是所谓的索引的基数。基数越高，唯一值就越多。对于主键和其他不允许NULL值的唯一索引，基数是表中的行数，因为所有值都必须是唯一的。
优化器在逐个查询的基础上请求给定范围内的行数。这对于范围条件很有用，如WHERE val > 5和IN()条件或一系列OR条件。一个例外是 MySQL 8 支持的直方图，这种信息是为单个查询专门收集的。直方图将在下一章讨论。
简而言之，索引统计信息是关于索引中数据分布的近似信息。在 MySQL 中，存储引擎负责提供索引统计信息。因此，深入研究 InnoDB 如何处理索引统计数据是值得的。
InnoDB 和索引统计
存储引擎向服务器层和优化器提供索引统计信息。因此，理解 InnoDB 如何确定其统计数据是很重要的。InnoDB 支持两种存储统计数据的方式:持久和瞬时。无论哪种方式，统计数据都是以相同的方式确定的。这一节将首先讨论如何收集统计数据，然后详细介绍持久性和瞬态统计数据。
如何收集统计数据
InnoDB 通过分析索引的随机叶页面来计算其索引统计信息。例如，可以对 20 个随机索引页面进行采样(这也称为 20 次索引潜水)，并检查这些页面由哪些索引值组成。然后，InnoDB 根据索引的总大小对其进行缩放。
这意味着 InnoDB 索引统计数据并不准确。当您看到给定的查询条件意味着将读取 100 行时，这只是基于所分析的样本的估计。这甚至包括主键和其他唯一索引，以及在information_schema.TABLES视图中报告的总行数。表中的估计行数与主键的估计基数相同。
另一个考虑是如何处理NULL值，因为NULL具有不等于NULL的属性。所以，当您收集统计数据时，您应该将所有的NULL值分组到一个桶中还是将它们分开？最佳解决方案取决于您的查询。将所有的NULL值视为不同的值会增加索引的基数，特别是如果有许多行的索引列带有NULL。这对于查找非NULL值的查询来说很好。另一方面，如果您将所有的NULL都视为相同，这将减少基数，这对包含NULL的查询有意义。您可以使用innodb_stats_method选项选择 InnoDB 应该如何处理NULL值。它可以取三个值之一:
-
nulls_equal: 在这种情况下，所有的NULL值被认为是相同的。这是默认设置。如果您不确定选择哪个值，请选择nulls_equal。 -
nulls_unequal: 在这种情况下，NULL值被认为是不同的值。 -
nulls_ignored: 在这种情况下，收集统计数据时会忽略NULL值。
为什么使用估计值而不是精确的统计值(意味着全索引扫描)？原因是性能。对于大型索引，执行完整的索引扫描需要很长时间。它通常还包括磁盘 I/O，这使得性能问题更加严重。为了避免计算索引统计对查询性能产生负面影响，我们选择将扫描限制在相对较少的页面上。
样本页面
使用近似统计的缺点是，它们并不总是很好地表示值的实际分布。发生这种情况时，优化器可能会选择错误的索引或错误的连接顺序，从而导致查询速度慢于必要的速度。但是，也可以调整随机索引潜水的次数。如何做到这一点取决于是使用持久统计还是瞬态统计:
-
持久统计使用
innodb_stats_persistent_sample_pages选项作为缺省的采样页数。表格选项STATS_SAMPLE_PAGES可用于指定给定表格的页数。 -
瞬态统计对所有表使用由
innodb_stats_transient_sample_pages选项指定的页数。
关于持久统计和瞬时统计的两个小节详细介绍了处理索引统计的两种方法。
将样本页数设置为给定值是什么意思？这取决于索引中的列数。如果只有一列，该值实际上意味着对该数量的叶页面进行采样。但是，对于多列索引，页数是每列的。例如，如果将示例页数设置为 20，并且索引中有四列，则总共会对 4*20=80 页进行采样。
Note
实际上，索引统计抽样比本章描述的要复杂得多。例如，并不总是需要一直下降到叶页面。考虑两个相邻非叶节点具有相同值的情况。那么可以得出结论，最左边(按照排序)部分的所有叶页面具有相同的值。如果您有兴趣了解更多，一个很好的起点是源代码中storage/innobase/ dict/dict0stats.cc文件顶部的注释: https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/dict/dict0stats.cc 。
要得到一个好的估计，必须检查多少页？那取决于桌子。如果数据是统一的，也就是说，每个索引值的行数大致相同，那么只需要检查相对较少的页数，默认页数通常就足够了。另一方面，如果您的数据具有非常不规则的分布，您可能需要增加采样的页数。非常不规则的数据的一个例子是队列中任务的状态。随着时间的推移，大多数任务将处于已完成状态。在最坏的情况下，您可能会发现所有随机的 dives 都看到相同的状态，这使得 InnoDB 断定只有一个值，并且该索引作为过滤器毫无价值。
Tip
对于只有几行值用于过滤的数据，下一章讨论的直方图对于改进查询计划非常有用。
桌子的大小也是一个需要考虑的因素。表越大，通常需要检查的页面就越多，这样才能得到准确的估计。原因是表越大，整个叶页越有可能指向具有相同索引值的行。这降低了每个采样页面的值，因此为了补偿，需要采样更多的页面。
一个特例是 InnoDB 被配置为进行比叶页面更多的索引潜水。在这种情况下，InnoDB 会检查所有叶页面，并在该点停止。这将给出尽可能准确的统计数据。如果在分析期间没有活动的事务，则该时间点的统计数据将是准确的。这包括表格中的页数。在本章的后面，您将学习如何使用持久统计信息来查找表的索引和表中的叶页数。
实际上，不可能使用精确的值。InnoDB 支持多版本，即使事务涉及到写操作，也能实现高并发性。由于每个事务都有自己的数据视图，精确的统计意味着每个事务都有自己的索引统计。这是不可行的，那么 InnoDB 是如何处理的呢？这是接下来要考虑的事情。
事务隔离级别
一个相关的问题是在收集统计数据时使用什么事务隔离级别。InnoDB 支持四种隔离级别:未提交读、提交读、可重复读(默认)和可序列化。收集索引统计信息时，选择使用 read uncommitted。这是有意义的，因为这是一个很好的假设，即大多数事务最终都会被提交，或者如果失败，它们会被重试。统计数据是为将来的查询准备的，所以没有理由在收集统计数据时增加维护读取视图的开销。
但是，这对于对表进行较大更改的事务确实有影响。对于一种极端(但并非不可能)的情况，考虑一个缓存表，其中数据由包含两个步骤的事务刷新:
-
从表中删除所有现有数据。
-
用更新的数据重建表。
默认情况下，当表的“大部分”发生变化时，索引统计信息会更新。(构成“大部分”的内容将在本章后面的“持久索引统计信息”和“临时索引统计信息”部分中介绍。)这意味着当步骤 1 完成时，InnoDB 将重新计算统计数据。这很简单——桌子是空的，所以没有桌子。如果某个查询恰好在此时执行，优化器会将该表视为空表。但是，除非在 read uncommitted transaction 隔离级别执行查询，否则查询仍将读取所有旧行，并且查询计划很可能会导致查询执行效率低下。
对于刚才讨论的问题，您需要持久的统计数据，因为有更好的配置选项来处理特殊情况。在开始讨论持久统计的细节之前，有必要了解如何在持久统计和瞬态统计之间进行选择。
配置统计类型
如上所述，InnoDB 有两种方法来存储索引统计信息。它可以使用持久存储，也可以使用临时存储。您可以使用innodb_stats_persistent选项设置表格的默认方法。当设置为1或ON(默认值)时，则使用持久统计；将其设置为0或OFF会将方法更改为瞬态统计。您也可以使用STATS_PERSISTENT工作台选项为每个工作台配置方法。例如，要为world.city表启用持久统计，可以像下面这样使用ALTER TABLE
ALTER TABLE world.city
STATS_PERSISTENT = 1;
使用CREATE TABLE语句创建新表时，也可以设置STATS_PERSISTENT选项。对于STATS_PERSISTENT，只有0和1可以作为数值。
自从引入持久索引统计以来，它就是默认的，并且也是推荐的选择，除非您遇到测试表明瞬态统计可以解决的问题。持久性统计数据和瞬态统计数据之间存在一些差异，理解这些差异非常重要。接下来将讨论这些差异。
持久索引统计
MySQL 5.6 中引入了持久索引统计，使得查询计划比以前的临时索引统计更加稳定。顾名思义，如果启用了持久索引统计，那么统计数据将被保存，这样在 MySQL 重启时就不会丢失。除了坚持，还有更多的不同，尽管这将变得很清楚。
除了稳定的查询计划之外，持久统计允许对要采样的页面数量进行详细配置，并具有良好的监控，您甚至可以直接查询保存统计信息的表。由于监控与瞬态统计有很大的重叠，这将推迟到本章的后面，因此本节将集中讨论持久统计的配置和存储统计的表。
配置
可以配置持久性统计数据，以便在收集统计数据的成本和统计数据的准确性之间取得良好的平衡。与瞬态统计不同，可以在全局级别和每个表中配置行为。当未设置特定于表的选项时，全局配置充当缺省配置。
有三个特定于持久性统计信息的全局选项。这些是
-
innodb_stats_persistent_sample_pages: 要采样的页数。页面越多，统计越准确，但成本也越高。如果该值大于索引的叶页数，则对整个索引进行采样。默认值为 20。 -
innodb_stats_auto_recalc: 当表格中超过 10%的行发生变化时，是否自动更新统计数据。默认启用(ON)。 -
innodb_stats_include_delete_marked: 是否将标记为已删除但尚未提交的行纳入统计。稍后将更详细地讨论这个选项。默认为禁用(OFF)。
也可以按表设置innodb_stats_persistent_sample_pages和innodb_stats_auto_recalc选项。这允许您根据与特定表相关的大小、数据分布和工作负载来微调需求。虽然不推荐使用微管理，但是可以使用它来处理前面讨论的缓存表场景以及一般默认值无法覆盖的其他表。
建议尝试为innodb_stats_persistent_sample_pages找到一个好的折衷方案，给出足够好的统计信息，这样优化器可以确定最佳的查询计划，同时避免计算统计信息的过多扫描。如果您发现查询性能很差，因为不准确的索引统计信息会导致优化器选择低效的计划，那么您需要增加抽样页面的数量。另一方面，如果ANALYZE TABLE花费的时间太长，你可以考虑减少采样页数。然后，您可以使用下面介绍的特定于表的选项，根据需要减少或增加特定表的采样页数。
对于大多数表格，建议启用innodb_stats_auto_recalc。这将有助于确保统计数据不会因大量更改而过时。自动重新计算在后台进行，因此不会延迟对触发更新的应用的响应。当超过 10%的表发生更改时，该表将排队等待索引统计信息更新。为了避免不断地重新计算小表的统计数据，还需要在每次索引统计数据更新之间至少间隔 10 秒钟。
当然，也有不希望自动重新计算统计数据的例外情况，例如，如果您有一个缓存表来加快报告查询的执行速度，并且缓存表中的数据有时会完全重新创建，但在其他情况下不会改变。在这种情况下，禁用统计信息的自动重新计算并在重建完成时显式重新计算它们可能是一种优势。另一个选项是在统计数据中包含删除标记的行。
请记住，索引统计信息是使用 read uncommitted 事务隔离级别计算的。虽然在大多数情况下这是最好的统计，但也有例外。当一个事务临时完全改变数据的分布时，它可能导致不正确的统计。表的完全重建是最极端的情况，也是最常见的问题。正是为了这样的情况，才引入了innodb_stats_include_delete_marked选项。InnoDB 不会将未提交的已删除行视为已删除行，而是将它们包含在统计数据中。该选项仅作为全局选项存在，因此它将影响所有表，即使只有一个表出现该问题。如上所述，另一种方法是禁用受影响表的统计数据的自动重新计算，并自己处理。
Tip
如果您的事务对表进行了较大的更改，例如删除所有行，然后重新构建表，请考虑禁用表的索引统计信息的自动重新计算，或者启用innodb_stats_include_delete_marked。
迄今为止，只提到了全球选项。如何更改表的索引统计设置？由于您可以使用STATS_PERSISTENT table 选项来覆盖表的全局值innodb_stats_persistent,因此有一些选项可以控制表的持久性统计信息的行为。表格选项包括
-
STATS_AUTO_RECALC: 覆盖表是否启用指标统计自动重算。 -
STATS_SAMPLE_PAGES: 覆盖表格的抽样页数。
您可以在使用CREATE TABLE创建表格时或者稍后使用ALTER TABLE设置这些选项，如清单 15-1 所示。
mysql> CREATE SCHEMA IF NOT EXISTS chapter_15;
Query OK, 1 row affected (0.4209 sec)
mysql> use chapter_15
Default schema set to `chapter_15`.
Fetching table and column names from `chapter_15` for auto-completion... Press ^C to stop.
mysql> CREATE TABLE city (
City_ID int unsigned NOT NULL auto_increment,
City_Name varchar(40) NOT NULL,
State_ID int unsigned DEFAULT NULL,
Country_ID int unsigned NOT NULL,
PRIMARY KEY (City_ID),
INDEX (City_Name, State_ID, City_ID)
) STATS_AUTO_RECALC = 0,
STATS_SAMPLE_PAGES = 10;
Query OK, 0 rows affected (0.0637 sec)
mysql> ALTER TABLE city
STATS_AUTO_RECALC = 1,
STATS_SAMPLE_PAGES = 20;
Query OK, 0 rows affected (0.0280 sec)
Records: 0 Duplicates: 0 Warnings: 0
Listing 15-1Setting the persistent statistics options for a table
首先，在禁用自动重新计算的情况下创建了表city,并创建了十个示例页面。然后更改设置以启用自动重新计算，并将示例页数增加到 20。注意ALTER TABLE如何返回 0 行受影响的行。更改 persistent stats 选项只会更改表的元数据，因此它们会立即发生，不会影响数据。这意味着您可以根据需要更改设置，而不必担心执行昂贵的操作。例如，您可能希望在批量操作期间禁用自动重新计算。
有机会调优索引统计数据时，能够查看收集的数据是很重要的。在讨论了瞬态统计之后，在“监控”一节中将讨论一些通用的方法。然而，使持久统计数据持久的是它们存储在表中，并且这些表也提供有价值的信息。
索引统计表
InnoDB 在mysql模式中使用两个表来存储与持久统计相关的数据。这不仅有助于调查统计数据和采样数据，而且有助于从总体上了解更多关于索引的信息。
最常用的表是innodb_index_stats表。这个表的每个 B 树索引都有几行，提供了关于索引每个部分的唯一值(基数)的数量、索引中的叶页数以及索引的总大小的信息。表 15-1 总结了表中的列。
表 15-1
innodb_index_stats表
列名
|
数据类型
|
描述
|
| --- | --- | --- |
| database_name | varchar(64) | 包含索引的表所在的架构。 |
| table_name | varchar(199) | 带有索引的表的名称。 |
| index_name | varchar(64) | 索引的名称。 |
| last_update | timestamp | 上次更新索引统计信息的时间。 |
| stat_name | varchar(64) | stat_value所针对的统计的名称。另请参见此表后的内容。 |
| stat_value | bigint unsigned | 统计数据的值。 |
| sample_size | bigint unsigned | 取样了多少页。 |
| stat_description | varchar(1024) | 统计数据的描述。对于基数，它是计算基数时包含的列。 |
主键由列database_name、table_name、index_name和stat_name组成。数据库、表和索引名称定义了统计数据用于哪个索引。last_update列有助于查看自上次更新统计数据以来已经过去了多长时间。stat_name和stat_value是给你实际的统计数据。sample_size是为确定统计数据而检查的叶页数。这将是索引中的叶页数和为表设置的样本页数中较小的一个。最后，stat_description列给出了关于统计的更多信息。对于基数，描述显示了索引中包含了哪些列，每列有一行(稍后将提供一个示例)。
如前所述，innodb_index_stats表中包含了几个统计数据。该名称可以是下列值之一:
-
n_diff_pfxNN: 索引中前 NN 列的基数。NN 是从 1 开始的，所以对于一个有两列的索引，n_diff_pfx01和n_diff_pfx02存在。对于包含这些统计信息的行，stat_description包含了该统计信息所包含的列。 -
n_leaf_pages: 索引中的总叶页数。您可以将它与n_diff_pfxNN统计数据的样本大小进行比较，以确定已经被采样的索引部分。 -
size: 索引中的总页数。这包括非叶页面。
查看一个示例会有助于更好地理解这些数据代表了什么。world.city表有两个索引:主键在ID列，?? 索引在CountryCode列。清单 15-2 显示了这两个索引的统计数据。请注意，如果您执行相同的查询，统计值可能会不同，如果您仍然有在第 14 章中添加的额外索引，将会有更多的行。
mysql> SELECT index_name, stat_name,
stat_value, sample_size,
stat_description
FROM mysql.innodb_index_stats
WHERE database_name = 'world'
AND table_name = 'city'\G
*************************** 1\. row ***************************
index_name: CountryCode
stat_name: n_diff_pfx01
stat_value: 232
sample_size: 7
stat_description: CountryCode
*************************** 2\. row ***************************
index_name: CountryCode
stat_name: n_diff_pfx02
stat_value: 4079
sample_size: 7
stat_description: CountryCode,ID
*************************** 3\. row ***************************
index_name: CountryCode
stat_name: n_leaf_pages
stat_value: 7
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 4\. row ***************************
index_name: CountryCode
stat_name: size
stat_value: 8
sample_size: NULL
stat_description: Number of pages in the index
*************************** 5\. row ***************************
index_name: PRIMARY
stat_name: n_diff_pfx01
stat_value: 4188
sample_size: 20
stat_description: ID
*************************** 6\. row ***************************
index_name: PRIMARY
stat_name: n_leaf_pages
stat_value: 24
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 7\. row ***************************
index_name: PRIMARY
stat_name: size
stat_value: 25
sample_size: NULL
stat_description: Number of pages in the index
7 rows in set (0.0007 sec)
Listing 15-2The innodb_index_stats table for the world.city table
第 1–4 行用于索引CountryCode，而第 5–7 行用于主键。首先要注意的是，对于CountryCode索引，既有n_diff_pfx01统计数据，也有n_diff_pfx02统计数据。为什么，考虑到索引只包含一列？请记住，InnoDB 使用聚集索引，非唯一索引总是附加主键，因为无论如何都需要它来定位实际的行。这就是你在这里看到的，n_diff_pfx01代表CountryCode列，n_diff_pfx02代表CountryCode和ID列的组合。
CountryCode索引有八页大，其中七页是叶节点。这意味着索引有两个级别，叶节点是级别 0，根节点是级别 1。我们鼓励您回到上一章中关于 B 树索引的讨论，并在查看表中一些索引的大小统计时进行回顾。
主键更简单，因为它只包含一列。这里有 24 个叶页面，所以只对索引的一个子集进行了采样。(记住，对于主键，索引就是表。)这样做的后果是统计数字不准确。主键的n_diff_pfx01预测 4188 个唯一值。因为它是主键，所以这也是对总行数的估计。但是，如果您查看一下CountryCode的统计数据，就会发现CountryCode和ID值有 4079 种不同的组合。由于CountryCode索引只有七个叶页，所以所有的页都被检查过了，并且行估计是准确的。
另一个与持久统计相关的表是innodb_table_stats表。它类似于innodb_index_stats，除了它是包含的整个表的聚合统计。innodb_table_stats的栏目汇总在表 15-2 中。
表 15-2
innodb_table_stats表
列名
|
数据类型
|
描述
|
| --- | --- | --- |
| database_name | varchar(64) | 表所在的架构。 |
| table_name | varchar(199) | 表的名称。 |
| last_update | timestamp | 上次更新表统计信息的时间。 |
| n_rows | bigint unsigned | 表中估计的行数。 |
| clustered_index_size | bigint unsigned | 聚集索引中的页数。 |
| sum_of_other_index_sizes | bigint unsigned | 辅助索引的总页数。 |
主键由列database_name和table_name组成。关于表统计，需要注意的重要一点是，它们和索引统计一样近似。表中的行数就是主键的估计基数。类似地，聚集索引的大小与来自innodb_index_stats表的主键的大小相同。二级索引页数是每个二级索引大小的总和。清单 15-3 显示了world.city表的innodb_table_stats表的内容示例，使用了与上一个示例相同的索引统计。
mysql> SELECT *
FROM mysql.innodb_table_stats
WHERE database_name = 'world'
AND table_name = 'city'\G
*************************** 1\. row ***************************
database_name: world
table_name: city
last_update: 2019-05-25 13:51:40
n_rows: 4188
clustered_index_size: 25
sum_of_other_index_sizes: 8
1 row in set (0.0005 sec)
Listing 15-3The innodb_table_stats table for the world.city table
Tip
innodb_index_stats和innodb_table_stats是常规表。在备份中包含这些表是很有用的，这样，如果查询计划突然发生变化，您就可以回过头来比较统计数据。
也可以为拥有UPDATE权限的用户更新表格。这似乎是一个非常有用的属性，但是要小心。如果您不知道正确的统计数据，您将会得到非常糟糕的查询计划。几乎不应该手动修改索引统计信息。如果完成，更改仅在刷新表后生效。
如果您觉得对innodb_index_stats和innodb_table_stats中可用信息的讨论听起来与您可能习惯看到的SHOW INDEX语句以及TABLES和STATISTICS信息模式表类似，那么您是对的。有一些重叠。由于这些来源也适用于瞬态统计，所以对它们的讨论将推迟到瞬态索引统计讨论完之后。
瞬时索引统计
瞬态索引统计是 InnoDB 中实现的处理索引统计的原始方法。顾名思义，统计数据不是持久的，也就是说，当 MySQL 重新启动时，它们不会持久。相反，统计数据是在第一次打开表时计算的(在其他时候)，并且只保存在内存中。因为统计数据不是持久的，所以它们不太稳定，因此更有可能看到查询计划的变化。
有两个配置选项可以影响瞬态统计的行为。这些是
-
innodb_stats_transient_sample_pages: 更新索引统计时要采样的页数。默认值为 8。 -
innodb_stats_on_metadata: 查询表元数据时是否重新统计。缺省值是OFF，从 MySQL 5.6 开始就是这样。
除了应用于使用瞬态统计的表之外，innodb_stats_transient_sample_pages选项等同于innodb_stats_persistent_sample_pages。使用瞬态统计信息的表不仅在第一次打开时重新计算统计信息，而且当只有 6.25% (1/16)的行发生变化时也需要重新计算统计信息，要求至少发生 16 次更新。此外，当统计数据自动重新计算时，瞬态统计数据不使用后台线程，因此更新更有可能影响性能。因此，innodb_stats_transient_sample_pages的缺省值只有八页。
如果您想更频繁地更新临时索引统计信息，您可以启用innodb_stats_on_metadata选项。当启用该功能时，查询信息模式中的TABLES和STATISTICS表或者使用它们的等价SHOW语句触发索引统计信息的更新。实际上，很少会出现这种情况，关闭该选项是安全的。
没有特殊的表可用于瞬态统计。然而，MySQL 中的所有表都有可用的表和语句。
监控
索引统计信息对于优化器帮助确定执行查询的最佳方式非常重要。因此，了解如何检查表的索引统计信息也很重要。已经讨论过，对于持久统计，有mysql.innodb_index_stats和mysql.innodb_table_stats表。然而也有一些通用的方法，这里将讨论这些方法。
Tip
记住,information_schema_stats_expiry变量影响数据字典刷新与索引统计相关的数据视图的频率。
信息模式统计视图
获取索引统计详细信息的主表是信息模式中的STATISTICS视图。该视图不仅包含索引统计信息本身，还包含关于索引的元信息。事实上，您可以基于STATISTICS视图中的数据重新创建索引定义。这是上一章中用来在表上查找索引名的视图。
表 15-3 包含了视图中各列的概要。您通常只需要列的一个子集，但是在需要的时候访问案例的所有信息是很方便的。CARDINALITY列是唯一受information_schema_stats_expiry变量影响的列。
表 15-3
STATISTICS信息模式视图
列名
|
数据类型
|
描述
|
| --- | --- | --- |
| TABLE_CATALOG | varchar(64) | 该表所属的目录。该值将始终为def。 |
| TABLE_SCHEMA | varchar(64) | 表所在的架构。 |
| TABLE_NAME | varchar(64) | 索引所在的表。 |
| NON_UNIQUE | int | 索引是唯一的(0)还是不唯一的(1)。 |
| INDEX_SCHEMA | varchar(64) | 与TABLE_SCHEMA相同(因为索引总是与表位于同一位置)。 |
| INDEX_NAME | varchar(64) | 索引的名称。 |
| SEQ_IN_INDEX | int unsigned | 列在索引中的位置。对于单列索引，该值始终为 1。 |
| COLUMN_NAME | varchar(64) | 列的名称。 |
| COLLATION | varchar(1) | 索引的排序方式。值可以是NULL(未排序)、A(升序)或 D(降序)。 |
| CARDINALITY | bigint | 对索引部分的唯一值数量的估计，包括行中的列。 |
| SUB_PART | bigint | 对于前缀索引，它是被索引的字符或字节数。如果对整列进行索引，则值为NULL。 |
| PACKED | binary(0) | 对于 InnoDB 表，这始终是NULL。 |
| NULLABLE | varchar(3) | 是否允许使用NULL值。该列要么是空字符串，要么是YES。 |
| INDEX_TYPE | varchar(11) | 索引类型，例如，BTREE为 B 树索引。 |
| COMMENT | varchar(8) | 关于索引的额外信息。这不适用于 InnoDB 表。 |
| INDEX_COMMENT | varchar(2048) | 添加索引时指定的注释。 |
| IS_VISIBLE | varchar(3) | 索引是可见的(YES)还是不可见的(NO)。 |
| EXPRESSION | longtext | 对于函数索引，此列包含用于生成索引值的表达式。对于非功能性索引，该值始终为NULL。 |
STATISTICS视图不仅对索引统计有用，而且对索引本身也有用，它包括所有索引的信息，而不管索引类型如何。例如，您可以使用它来查找不可见的索引和用于函数索引的表达式。关于索引统计，最有趣的列是CARDINALITY,它是估计索引中存在的唯一值的数量。
查询STATISTICS视图时，建议按TABLE_SCHEMA、TABLE_NAME、INDEX_NAME、SEQ_IN_INDEX列对结果进行排序。这将把相关的行组合在一起，对于多列索引，将按照索引中列的顺序返回这些行。清单 15-4 显示了world.countrylanguage表上的索引示例。在这种情况下，由于表模式和表名是固定的，所以排序只基于索引名和索引中的序列。由于这些值本质上是不精确的，您的结果可能会有所不同。
mysql> SELECT INDEX_NAME, NON_UNIQUE,
SEQ_IN_INDEX, COLUMN_NAME,
CARDINALITY, INDEX_TYPE,
IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'world'
AND TABLE_NAME = 'countrylanguage'
ORDER BY INDEX_NAME, SEQ_IN_INDEX\G
*************************** 1\. row ***************************
INDEX_NAME: CountryCode
NON_UNIQUE: 1
SEQ_IN_INDEX: 1
COLUMN_NAME: CountryCode
CARDINALITY: 233
INDEX_TYPE: BTREE
IS_VISIBLE: YES
*************************** 2\. row ***************************
INDEX_NAME: PRIMARY
NON_UNIQUE: 0
SEQ_IN_INDEX: 1
COLUMN_NAME: CountryCode
CARDINALITY: 233
INDEX_TYPE: BTREE
IS_VISIBLE: YES
*************************** 3\. row ***************************
INDEX_NAME: PRIMARY
NON_UNIQUE: 0
SEQ_IN_INDEX: 2
COLUMN_NAME: Language
CARDINALITY: 984
INDEX_TYPE: BTREE
IS_VISIBLE: YES
3 rows in set (0.0010 sec)
Listing 15-4The STATISTICS view for the world.countrylanguage table
countrylanguage表有两个索引。在CountryCode和Language列上有一个主键，在CountryCode列上有一个辅助索引。与mysql.innodb_index_stats表不同，当主键被附加到辅助非唯一索引时，该表中也有一行，STATISTICS视图不包含该信息。
Note
因为CountryCode列是主键中的第一列，所以CountryCode列上的辅助索引是多余的。这意味着主键也可以用作辅助索引。最佳实践是避免冗余索引。
您可能希望在STATISTICS视图中记录数据，并比较数据随时间的变化。突然的变化可能表明数据发生了意外情况，或者索引统计信息的最新重新计算可能导致不同的查询计划。
STATISTICS视图中的一些信息也可以通过SHOW INDEX语句获得。
SHOW INDEX 语句
SHOW INDEX语句是获取 MySQL 中索引信息的原始方式。如今，它从与information_schema.STATISTICS相同的来源获取数据，所以你可以选择最适合你的来源。STATISTICS视图的一个主要优点是你可以选择你想要的信息以及如何订购；使用SHOW INDEX语句，您总是可以获得单个表的索引，并且可以选择根据可用字段进行过滤。
除了省略了表目录、表模式和索引模式之外，SHOW INDEX返回的列与STATISTICS视图中的相同。另一方面，SHOW INDEX可以选择使用EXTENDED关键字，该关键字包含关于索引隐藏部分的信息。这不应该与不可见的索引混淆，而是附加的部分，如附加到辅助索引的主键。标准输出和扩展输出对于共有的行具有相同的信息。
清单 15-5 显示了world.city表的SHOW INDEX输出的一个例子(该结果假设来自章节 14 的索引已经被移除)。首先，返回标准输出，然后是扩展输出。由于扩展输出有几页长，所以通过删除一些列和行对其进行了简化。要查看完整的输出，请自己执行该语句或查看本书 GitHub 库中的listing_15_5.txt文件。
mysql> SHOW INDEX FROM world.city\G
*************************** 1\. row ***************************
Table: city
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 4188
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2\. row ***************************
Table: city
Non_unique: 1
Key_name: CountryCode
Seq_in_index: 1
Column_name: CountryCode
Collation: A
Cardinality: 232
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.0013 sec)
mysql> SHOW EXTENDED INDEX FROM world.city\G
*************************** 1\. row ***************************
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Cardinality: 4188
*************************** 2\. row ***************************
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: DB_TRX_ID
Cardinality: NULL
*************************** 3\. row ***************************
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 3
Column_name: DB_ROLL_PTR
Cardinality: NULL
*************************** 4\. row ***************************
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 4
Column_name: Name
Cardinality: NULL
...
*************************** 8\. row ***************************
Non_unique: 1
Key_name: CountryCode
Seq_in_index: 1
Column_name: CountryCode
Cardinality: 232
*************************** 9\. row ***************************
Non_unique: 1
Key_name: CountryCode
Seq_in_index: 2
Column_name: ID
Cardinality: NULL
9 rows in set (0.0013 sec)
Listing 15-5The SHOW INDEX output for the world.city table
请注意，列名与STATISTICS视图所使用的并不相同。但是，列的顺序是相同的，名称也是相似的，因此很容易将两个输出相互映射。
在扩展输出中，主键在 InnoDB 内部有两个隐藏的列:DB_TRX_ID是 6 字节的事务标识符，而DB_ROLL_PTR是 7 字节的回滚指针，指向写入回滚段的撤销日志记录。这些是 InnoDB 多版本支持的一部分。 1 在这两个内部字段之后，表格中剩余的每一列都被添加。这反映了 InnoDB 对其行使用聚集索引，因此主键是行。
对于CountryCode上的二级索引，主键现在显示为索引的第二部分。这是意料之中的，也反映了在mysql.innodb_index_stats表中看到的情况。
虽然在研究性能问题时，人们通常对扩展输出不感兴趣，但在探索 InnoDB 如何工作时，它是有价值的。
在处理索引统计信息时，另一个有用的信息模式视图是INNODB_TABLESTATS视图。
信息模式 INNODB_TABLESTATS 视图
信息模式中的INNODB_TABLESTATS视图是位于 InnoDB 内存结构之上的视图，其中保存了关于索引的信息。它不包含任何可用于验证基数和索引大小的信息，这些信息不包含在已经描述过的表和视图中。但是，它确实提供了一些关于索引统计状态和自上次分析该表以来的修改次数的信息。该视图包括所有 InnoDB 表的信息，而不管它们是使用持久统计还是临时统计。表 15-4 总结了INNODB_TABLESTATS视图的列。
表 15-4
INNODB_TABLESTATS信息模式视图
列名
|
数据类型
|
描述
|
| --- | --- | --- |
| TABLE_ID | bigint unsigned | 内部 InnoDB 表 ID。例如，您可以使用它在INNODB_TABLES信息模式视图中查找表格。 |
| NAME | varchar(193) | 格式为<schema>/<table>的表名，例如world/city。 |
| STATS_INITIALIZED | varchar(193) | 表的内存结构是否已初始化。这与索引统计数据是否存在并不相同。可能的值是Uninitialized和Initialized。 |
| NUM_ROWS | bigint unsigned | 表中估计的行数。 |
| CLUST_INDEX_SIZE | bigint unsigned | 聚集索引中的页数。 |
| OTHER_INDEX_SIZE | bigint unsigned | 辅助索引的总页数。 |
| MODIFIED_COUNTER | bigint unsigned | 自上次更新索引统计信息以来，使用 DML 语句更改的行数。 |
| AUTOINC | bigint unsigned | 自动递增计数器的值(如果存在)。对于没有自动递增列的表，该值为 0。 |
| REF_COUNT | int | 有多少对元数据的引用。当参考计数器达到零时，InnoDB 可能会清除数据，初始化状态返回到Uninitialized。 |
初始化状态会造成混乱。这显示了索引统计信息和相关元数据(如该视图所示)是否已经加载到内存中。即使统计数据存在，状态也总是以Uninitialized开始。当某个连接或后台线程需要数据时，InnoDB 会将数据加载到内存中，状态变为Initialized。每当没有线程持有对该表的引用时，InnoDB 就可以自由地再次驱逐该信息，并且状态变为Uninitialized。例如，这可能发生在表被刷新或对表执行ANALYZE TABLE时。
修改后的计数器很有趣，因为它可以用来查看自上次更新索引统计信息以来有多少行发生了更改。只有当 DML 查询影响索引时，计数器才会增加。这意味着，如果您更新了一个非索引列，而保留该行不变，计数器将不会递增。该计数器与自动更新相关，当发生一定数量的更改时，会触发自动更新。
清单 15-6 有一个来自world.city表的INNODB_TABLESTATS视图的示例输出。如果执行相同的查询，表 ID、行数和引用计数可能会不同。
mysql> SELECT *
FROM information_schema.INNODB_TABLESTATS
WHERE NAME = 'world/city'\G
*************************** 1\. row ***************************
TABLE_ID: 1670
NAME: world/city
STATS_INITIALIZED: Initialized
NUM_ROWS: 4188
CLUST_INDEX_SIZE: 25
OTHER_INDEX_SIZE: 8
MODIFIED_COUNTER: 0
AUTOINC: 4080
REF_COUNT: 2
1 row in set (0.0009 sec)
Listing 15-6The INNODB_TABLESTATS view for the world.city table
输出显示索引统计信息是最新的，因为自上次分析以来没有修改过任何行。行数以及聚集索引和辅助索引的大小与使用mysql.innodb_index_stats表找到的相同。这些与表格大小相关的数字也用于information_schema.TABLES视图和SHOW TABLE STATUS语句。
信息模式表查看和显示表状态
索引统计信息集合还用于填充由information_schema.TABLES视图和SHOW TABLE STATUS语句使用的表中的一些列。这包括对行数以及数据和索引大小的估计。
表 15-5 显示了TABLES视图中各列的汇总。除了TABLE_CATALOG、TABLE_SCHEMA、TABLE_TYPE和TABLE_COMMENT列之外，SHOW TABLE STATUS语句的输出中有相同的列，少数列的名称略有不同。标有星号(*)的列受information_schema_stats_expiry变量影响。
表 15-5
TABLES信息模式视图
列名
|
数据类型
|
描述
|
| --- | --- | --- |
| TABLE_CATALOG | varchar(64) | 该表所属的目录。该值将始终为def。 |
| TABLE_SCHEMA | varchar(64) | 表所在的架构。 |
| TABLE_NAME | varchar(64) | 表的名称。 |
| TABLE_TYPE | enum | 是什么样的桌子。可能的值有BASE TABLE、VIEW和SYSTEM VIEW。用CREATE TABLE创建一个基表，用CREATE VIEW创建一个视图，系统视图是像 MySQL 创建的信息模式视图这样的视图。 |
| ENGINE | varchar(64) | 表使用的存储引擎。 |
| VERSION | int | 在 MySQL 8 中未使用，因为它与 MySQL 5.7 和更早版本中的.frm文件相关。版本值现在被硬编码为 10。 |
| ROW_FORMAT | enum | 用于表格的行格式。可能的值有固定、动态、压缩、冗余、压缩和分页。 |
| TABLE_ROWS * | bigint unsigned | 估计的行数。对于 InnoDB 表，这来自主键或聚集索引的基数。 |
| AVG_ROW_LENGTH * | bigint unsigned | 估计的数据长度除以估计的行数。 |
| DATA_LENGTH * | bigint unsigned | 行数据的估计大小。对于 InnoDB，它是聚集索引的大小，即聚集索引中的页数乘以页面大小。 |
| MAX_DATA_LENGTH * | bigint unsigned | 数据长度的最大允许大小。InnoDB 不使用，所以值为NULL。 |
| INDEX_LENGTH * | bigint unsigned | 辅助索引的估计大小。对于 InnoDB，这是非聚集索引中的页面总数乘以页面大小。 |
| DATA_FREE * | bigint unsigned | 该表所属的表空间中空闲空间量的估计值。对于 InnoDB，这是完全自由的扩展区的大小减去安全余量。 |
| AUTO_INCREMENT * | bigint unsigned | 表的自动递增计数器的下一个值。 |
| CREATE_TIME * | timestamp | 创建表的时间。 |
| UPDATE_TIME * | datetime | 上次更新表空间文件的时间。对于 InnoDB 系统表空间中的表，该值为NULL。由于数据是异步写入表空间的，因此时间通常不会反映最后一条更改数据的语句的时间。 |
| CHECK_TIME * | datetime | 上次检查表格的时间(CHECK TABLE)。对于分区表，InnoDB 总是返回NULL。 |
| TABLE_COLLATION | varchar(64) | 用于对字符串列的值进行排序和比较的默认排序规则(没有为列显式设置)。 |
| CHECKSUM | bigint | 表校验和。InnoDB 不使用，所以值为NULL。 |
| CREATE_OPTIONS | varchar(256) | 表格选项，如STATS_AUTO_RECALC和STATS_SAMPLE_PAGES。 |
| TABLE_COMMENT | text | 创建表时指定的注释。 |
在可用的信息中，行数以及数据和索引的大小与索引统计信息的关系最为密切。TABLES视图不仅有助于查询表大小的估计值，还可以用来查询哪些表显式设置了持久统计变量。清单 15-7 显示了一个示例chapter_15.t1表，用一百万行填充它，然后查询该表的TABLES视图的内容。
mysql> CREATE TABLE chapter_15.t1 (
id int unsigned NOT NULL auto_increment,
val varchar(36) NOT NULL,
PRIMARY KEY (id)
) STATS_PERSISTENT=1,
STATS_SAMPLE_PAGES=50,
STATS_AUTO_RECALC=1;
Query OK, 0 rows affected (0.5385 sec)
mysql> SET SESSION cte_max_recursion_depth = 1000000;
Query OK, 0 rows affected (0.0003 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
mysql> INSERT INTO chapter_15.t1 (val)
WITH RECURSIVE seq (i) AS (
SELECT 1
UNION ALL
SELECT i + 1
FROM seq WHERE i < 1000000
)
SELECT UUID()
FROM seq;
Query OK, 1000000 rows affected (15.8552 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.8306 sec)
mysql> SELECT *
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'chapter_15'
AND TABLE_NAME = 't1'\G
*************************** 1\. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: chapter_15
TABLE_NAME: t1
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 996442
AVG_ROW_LENGTH: 64
DATA_LENGTH: 64569344
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 7340032
AUTO_INCREMENT: 1048561
CREATE_TIME: 2019-11-02 11:48:28
UPDATE_TIME: 2019-11-02 11:49:25
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
CHECKSUM: NULL
CREATE_OPTIONS: stats_sample_pages=50 stats_auto_recalc=1 stats_persistent=1
TABLE_COMMENT:
1 row in set (0.0653 sec)
Listing 15-7The TABLES view for the table chapter_15.t1
该表使用递归公用表表达式填充随机数据，以确保正好插入一百万行。要做到这一点，有必要将cte_max_recursion_depth设置为 1000000，否则公共表表达式将因递归深度过高而失败。
请注意，估计的行数只有 996442 行，比实际的行数少 0.3%左右。这在预期范围之内——10%或以上的差异并不罕见。该表还设置了几个表选项，以显式配置持久统计信息用于启用了自动重新计算的表，并使用了 50 个示例页面。
如果您更喜欢使用SHOW TABLE STATUS语句，您可以使用不带参数的语句，在这种情况下，将返回默认模式中所有表的状态。或者，您可以添加一个LIKE子句来只包含表的子集。要检索非默认模式中表的状态，请使用FROM子句指定模式名。例如，假设默认模式是world，那么下面的查询将返回city表的状态:
mysql> use world
mysql> SHOW TABLE STATUS LIKE 'city';
mysql> SHOW TABLE STATUS LIKE 'ci%';
mysql> SHOW TABLE STATUS FROM world LIKE 'city';
前两个查询依赖默认模式来知道在哪里查找表。第三个查询显式地在world模式中查找city表。
如果索引统计没有数据，如何更新它们？这是在结束本章之前要探讨的最后一个话题。
更新统计数据
为了让优化器获得最佳的查询执行计划，最新的索引统计信息非常重要。有两种方法可以更新索引:自动更新，因为表已经发生了足够多的变化，可以触发统计信息的重新计算；手动更新。
自动更新
在讨论持久和瞬时统计时，已经在一定程度上讨论了自动更新机制。表 15-6 总结了基于索引统计类型的特性。
表 15-6
InnoDB 索引统计信息的自动重新计算概要
|财产
|
坚持的
|
短暂的
|
| --- | --- | --- |
| 行已更改 | 表格的 10% | 表格的 6.25% |
| 由于行更改而导致的更新的最小间隔时间 | 10 秒 | 16 次更新 |
| 引发变化的其他行动 | | 第一次打开表，可以选择在查询表元数据时打开。 |
| 背景更新 | 是 | 不 |
| 配置 | innodb_stats_auto_recalc变量和STATS_AUTO_RECALC表格选项 | 没有人 |
摘要显示，持久性统计信息通常更新频率较低，并且影响较小，因为自动更新发生在后台。持久统计也有更好的配置选项。
也可以手动触发索引统计信息的更新。您可以使用ANALYZE TABLE语句或mysqlcheck命令行程序，这将在接下来的章节中讨论。
ANALYZE TABLE 语句
当您在mysql命令行客户端或 MySQL Shell 中工作或者更新将由存储过程触发时，使用ANALYZE TABLE语句非常方便。该语句可以更新索引统计信息和直方图。后者将在下一章讨论，所以这里只讨论索引统计的更新。
ANALYZE TABLE有一个参数，即是否将语句记录到二进制日志中。如果在ANALYZE和TABLE之间指定NO_WRITE_TO_BINLOG或LOCAL，该语句将只应用于本地实例，而不会写入二进制日志。
当您执行ANALYZE TABLE时，它会强制刷新索引统计信息和表缓存值，否则这些值会受到information_schema_stats_expiry变量的影响。因此，如果您强制更新索引统计数据，您不需要更改information_schema_stats_expiry来拥有information_schema.STATISTICS视图并类似地反映更新后的值。
您可以选择指定多个表来更新它们的索引统计信息。您可以通过在逗号分隔的列表中列出这些表来实现这一点。在清单 15-8 中可以看到一个更新world模式中三个表的统计数据的例子。
mysql> ANALYZE LOCAL TABLE
world.city, world.country,
world.countrylanguage\G
*************************** 1\. row ***************************
Table: world.city
Op: analyze
Msg_type: status
Msg_text: OK
*************************** 2\. row ***************************
Table: world.country
Op: analyze
Msg_type: status
Msg_text: OK
*************************** 3\. row ***************************
Table: world.countrylanguage
Op: analyze
Msg_type: status
Msg_text: OK
3 rows in set (0.0248 sec)
Listing 15-8Analyzing the index statistics for the tables in the world schema
在示例中，LOCAL关键字用于避免将语句记录到二进制日志中。如果没有指定模式名和表名(例如，用city代替world.city)，MySQL 会在当前默认模式中查找表。
Note
虽然可以使用ANALYZE TABLE同时查询表，但是请注意，作为最后一步(在返回到客户端之后)，被分析的表将被刷新(一个隐式的FLUSH TABLES语句)。表刷新只能在所有正在进行的查询完成后发生，所以当您有长时间运行的查询时，您不应该使用ANALYZE TABLE(或mysqlcheck)。
当您确切地知道您想要分析哪些表时，ANALYZE TABLE语句非常适合临时更新。对于分析给定模式中的所有表或实例中的所有表来说，它的用处不大。为此，下面讨论的mysqlcheck是一个更好的选择。
mysqlcheck 程序
例如，如果您想通过 cron 守护进程或 Windows 任务调度程序从 shell 脚本中触发更新，那么mysqlcheck程序非常方便。它不仅可以用于更新单个表或多个表上的索引统计信息，如ANALYZE TABLE，还可以告诉mysqlcheck更新模式中所有表或实例中所有表的索引统计信息。mysqlcheck所做的是对符合您的标准的表执行ANALYZE TABLE，所以从索引统计的角度来看，手动执行ANAYZE TABLE和使用mysqlcheck没有区别。
Note
mysqlcheck程序不仅仅可以分析表来更新索引统计数据。这里只介绍分析功能。要阅读mysqlcheck程序的完整文档，请参见 https://dev.mysql.com/doc/refman/en/mysqlcheck.html 。
您使用--analyze选项让mysqlcheck更新索引统计数据，并使用--write-binlog / --skip-write-binlog参数告诉您是否希望将语句记录到二进制日志中。默认设置是记录语句。你还需要告诉如何连接到 MySQL 为此，您可以使用标准连接选项。
有三种方法可以指定要分析哪些表。默认情况下，分析同一个模式中的一个或多个表，比如对于ANALYZE TABLE语句。如果选择这种方式，就不需要添加任何额外的选项，指定的第一个值被解释为模式名，可选参数被解释为表名。清单 15-9 展示了如何以两种方式分析world模式中的所有表:显式列出表名和不列出表。
shell$ mysqlcheck --user=root --password --host=localhost --port=3306 --analyze world city country countrylanguage
Enter password: ********
world.city OK
world.country OK
world.countrylanguage OK
shell$ mysqlcheck --user=root --password --host=localhost --analyze world
Enter password: ********
world.city OK
world.country OK
world.countrylanguage OK
Listing 15-9Using mysqlcheck to analyze all tables in the world schema
在这两种情况下，输出都列出了被分析的三个表。
如果您想要分析多个模式中的所有表，但是仍然列出要包括哪些模式，那么您可以使用--databases参数。当出现这种情况时，命令行上列出的所有对象名都被解释为模式名。清单 15-10 展示了一个分析sakila和world模式中所有表的例子。
shell$ mysqlcheck --user=root --password --host=localhost --port=3306 --analyze --databases sakila world
Enter password: ********
sakila.actor OK
sakila.address OK
sakila.category OK
sakila.city OK
sakila.country OK
sakila.customer OK
sakila.film OK
sakila.film_actor OK
sakila.film_category OK
sakila.film_text OK
sakila.inventory OK
sakila.language OK
sakila.payment OK
sakila.rental OK
sakila.staff OK
sakila.store OK
world.city OK
world.country OK
world.countrylanguage OK
Listing 15-10Analyze all tables in the sakila and world schemas
最后一个选项是使用--all-databases选项来分析所有的表，不管它们位于哪个模式中。除了信息模式和性能模式之外，这还包括系统表。清单 15-11 展示了一个使用mysqlcheck和--?? 的例子。
shell$ mysqlcheck --user=root --password --host=localhost --port=3306 --analyze --all-databases
Enter password: ********
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log
note : The storage engine for the table doesn't support analyze
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log
note : The storage engine for the table doesn't support analyze
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
sakila.actor OK
sakila.address OK
sakila.category OK
sakila.city OK
sakila.country OK
sakila.customer OK
sakila.film OK
sakila.film_actor OK
sakila.film_category OK
sakila.film_text OK
sakila.inventory OK
sakila.language OK
sakila.payment OK
sakila.rental OK
sakila.staff OK
sakila.store OK
sys.sys_config OK
world.city OK
world.country OK
world.countrylanguage OK
Listing 15-11Analyzing all tables
请注意，有两个表回复说它们的存储引擎不支持 analyze。mysqlcheck程序试图分析所有的表，而不考虑它们的存储引擎，所以像示例中这样的消息是意料之中的。默认情况下，mysql.general_log和mysql.slow_log表都使用不支持索引的 CSV 存储引擎，因此ANALYZE TABLE也不支持索引。
摘要
本章通过查看 InnoDB 如何处理索引统计数据，继承了上一章的内容。InnoDB 有两种方法来存储统计数据:要么持久存储在mysql.innodb_index_stats和mysql.innodb_table_stats表中，要么暂时存储在内存中。持久统计通常是首选，因为它们提供更一致的查询计划，允许对更多页面进行采样，在后台进行更新，并且可以在更大程度上进行配置，包括支持表级选项。
有几个表、视图和SHOW语句可以用来研究和了解 InnoDB 索引及其统计数据。特别有趣的是information_schema.STATISTICS视图，它包含 MySQL 中所有索引的细节。还讨论了information_schema.INNODB_TABLESTATS和information_schema.TABLES视图、SHOW INDEX和SHOW TABLE STATUS声明。
您可以通过两种方式更新索引统计信息:使用ANALYZE TABLE语句或mysqlcheck程序。前者在交互式客户端或存储过程中很有用，而后者对于 shell 脚本和更新一个或多个模式中的所有表更有用。这两种方法还强制更新 MySQL 数据字典中的表元数据和索引基数的缓存值。
在讨论ANALYZE TABLE语句时，提到 MySQL 也支持直方图。这些与索引有关，是下一章的主题。
如果您有兴趣阅读更多关于 InnoDB 多版本控制的内容，请参见 https://dev.mysql.com/doc/refman/en/innodb-multi-versioning.html
