内容: MySQL优化与索引。

Version 5.7

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

尽管可以为每个列创建索引，但是不必要的索引即浪费空间，又浪费时间来确定要使用的索引，索引还会增加插入、更新、删除的成本（因为必须更新每个索引）。所以盲目的使用索引也不是一个好的做法。

关于索引的创建，参考：《CREATE INDEX Syntax》

引用文章及文献：

	Doc: https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html
	B-tree: https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_b_tree
	hash index: https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_hash_index
	create index: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

MySQL是如何使用索引的

索引用于快速查找具有特定列值的行。没有索引，MySQL必须从第一行开始，然后全表扫描，表越大，成本越高。如果该表具有相关列的索引，MySQL可以快速确定数据的位置，而无需全表扫描，这要块很多。

大多数索引（PRIMARY KEY，UNIQUE，INDEX，FULLTEXT）存储在B-tree中。有几个例外：空间数据类型的索引使用R-tree; MEMORY表还支持hash索引; InnoDB对FULLTEXT索引使用反向列表。

MySQL对如下操作使用索引:

快速找到与WHERE子句匹配的行。
从考虑中的消除行。如果存在多个可选择的索引，MySQL通常会使用查找最小行数（最有选择性(selectivity)的索引）的索引。关于selectivity: 数据分布的属性，列中不同值的数量（其基数）除以表中的记录数。高选择性意味着列值相对唯一，并且可以通过索引有效检索。如果（查询优化器）可以预测WHERE子句中的测试仅匹配表中少量（或比例）的行，如果先使用索引评估该测试，会使整体查询将趋于有效。
如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行。例如，如果（col1，col2，col3）上有一个三列索引，则可以在（col1），（col1，col2）和（col1，col2，col3）上搜索索引索引。有关详细信息，参见“多列索引”部分。
在执行join时，从其他表中检索行。如果它们被声明为相同的类型和大小，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之前发生的所有关键部分使用WHERE key_part_N = constant，在这种情况下，MySQL对每个MIN()或MAX()表达式进行单键查找，并将其替换为常量。如果所有表达式被替换为常量，查询将立即返回。Demo:SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;
如果排序或分组在可用索引的最左前缀上完成。如果所有关键部分都跟着DESC，则按照相反的顺序读取key。参见《ORDER BY优化》和《GROUP BY优化》
在某些情况下，可以优化查询以检索值，而无需查看数据行。 （为查询提供所有必要结果的索引称为覆盖索引。）如果查询仅使用表中包含的某些索引中的列，则可以从索引树检索所选值以获得更高的速度：SELECT key_part3 FROM tbl_name WHERE key_part1=1

索引对于小表的查询或报表查询处理大多数行或全部行的大表作用不大。当查询需要访问大多数行时，按顺序读取的速度要比使用索引要快。顺序读取最小化磁盘查找，即使不是查询所需的所有行。有关详细信息，参见：《避免全表扫描》

Primary Key的使用

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

如果您的表格很大且重要，但没有明显的列或一组列用作主键，则可能会创建一个单独的列，其中包含auto_increment作为主键。当使用外键连接表时，可以使用主键惊醒外键关联。

Foreign Keys的使用

如果一个表有很多列，并且查询了许多不同的列组合，那么将较少使用的数据分割为单独的表，并将其分列为几列可能是有效的，并通过复制数字ID将其与主表相关联列从主表。这样的话，每个小表都可以有一个主键快速查找其数据，您可以使用连接操作查询所需的一组列。根据数据的分布情况，查询可能会执行较少的I/O，并占用较少的缓存内存，因为相关列在磁盘上打包在一起。（为了最大化性能，查询尝试从磁盘读取尽可能少的数据块;只有几列的表可以在每个数据块中更多的行）

单列索引

最常见的索引类型包括单个列，将该列的值的副本存储在数据结构中，允许快速查找具有相应列值的行。 B-tree数据结构允许索引在WHERE子句中快速找到对应于诸如=，>，≤，BETWEEN，IN等操作符的特定值、一组值或值范围。

每个存储引擎定义了每个表的最大索引数和最大索引长度。《InnoDB存储引擎》和《替代存储引擎》。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎具有更高的限制。

前缀索引

在字符串列的索引规范中使用col_name(N)语法，可以创建仅使用列的前N个字符的索引。以这种方式仅索引列值的前缀可以使索引文件小很多。Demo:

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

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

前缀限制以字节为单位，而CREATE TABLE，ALTER TABLE和CREATE INDEX语句中的前缀长度被解释为非二进制字符串类型（CHAR，VARCHAR，TEXT）和二进制字符串类型的字节数（BINARY， VARBINARY，BLOB）。在为使用多字节字符集的非二进制字符串列指定前缀长度时考虑到这一点。

全文索引(FULLTEXT Indexes)

FULLTEXT索引用于全文搜索。只有InnoDB和MyISAM存储引擎才支持FULLTEXT索引，并且仅支持CHAR，VARCHAR和TEXT列。索引总是发生在整个列和不支持列前缀索引。

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

	* FULLTEXT查询只返回文档ID，或者返回文档ID和搜索排名。
	* FULLTEXT查询按比例的降序对匹配的行进行排序，并应用LIMIT子句来取得前N个匹配行。要应用此优化，必须没有WHERE子句，并且只有一个ORDER BY子句的降序。
	* FULLTEXT查询仅检索与搜索项匹配的行的COUNT(*)值，而没有其他WHERE子句。将WHERE子句代码为WHERE MATCH（text）AGAINST（'other_text'），而不使用 > 0比较运算符。

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

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

EXPLAIN对于全文查询可能会显示在优化期间由于匹配发生而在Extra列中优化的Select表;在这种情况下，在后续执行期间不需要进行表访问。

空间索引(Spatial Indexes)

可以在空间数据类型上创建索引。 MyISAM和InnoDB支持空间类型的R-tree索引。其他存储引擎使用B-tree索引空间类型（ARCHIVE不支持空间类型索引）。

MEMORY存储引擎中的索引

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

多列索引

MySQL可以创建复合索引（即多列上的索引）。索引最多可包含16列。对于某些数据类型，可以对列的前缀进行索引。

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

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

注意：作为组合索引的替代方法，您可以根据其他列的信息引入“散列”列。如果此列是短的，合理的唯一性和索引，它可能比许多列上的“宽”索引更快。Demo:

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

索引name是last_name和first_name列上的索引。该索引可用于在对last_name和first_name值的组合指定已知范围内的值的查询中进行查找。也可以用于仅指定last_name值的查询，因为该列是索引的最左前缀（如本节稍后所述）。因此，在以下查询可以使用索引name进行查找：

	SELECT * FROM test WHERE last_name='Widenius';

	SELECT * FROM test
	  WHERE last_name='Widenius' AND first_name='Michael';

	SELECT * FROM test
	  WHERE last_name='Widenius'
	  AND (first_name='Michael' OR first_name='Monty');

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

但是下面的查询无法使用索引name:

	SELECT * FROM test WHERE first_name='Michael';

	SELECT * FROM test
	  WHERE last_name='Widenius' OR first_name='Michael';

对于查询:SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果在col1和col2上创建了多列索引，那么可以直接获取响应的行。如果col1和col2上是独立的单列索引，优化器会尝试使用“索引覆盖优化”（相关文章：《索引覆盖优化》），或者尝试通过确定哪个索引排除更多行来查找最严格的索引，并使用该索引来获取行。

如果表具有多列索引，则优化器可以使用索引的任何最左侧的前缀来查找行。例如，如果（col1，col2，col3）上有一个三列索引，则可以在（col1），（col1，col2）和（col1，col2，col3）上搜索索引索引。如果列不形成索引的最左侧的前缀，则MySQL不能使用索引执行查找。如下SQL：

	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）的最左前缀。

验证索引使用情况

检查索引的使用情况，参看EXPLAIN相关内容

InnoDB和MyISAM统计集合

存储引擎收集有关表格的统计信息，以供优化程序使用。表统计信息基于值组，其中值组是具有相同键前缀值的一组行。为了优化目的，重要的统计量是平均值组大小。表统计信息基于“值组”，“值组”是具有相同键前缀值的一组行。为了优化目的，重要的统计量是“平均值组”大小。

MySQL以下列方式使用平均值组大小：

	为每个ref访问估计必须读取行。
	估计部分join将产生多少行，即此形式的操作将生成的行数：(...) JOIN tbl_name ON tbl_name.key = expr

随着索引的”平均值组“大小的增加，由于每个查找的平均行数增加，以下两种情况索引不太有用：为了使索引有利于优化目的，最好是每个索引值都是小的表中的行数。当给定的索引值产生大量行时，索引不太有用，MySQL不太可能使用它。

”平均值组“大小与表的基数相关，即”值组“的数量。 SHOW INDEX语句显示基于N/S的基数值，其中N是表中的行数，S是平均值组大小，该比率产生了表中值组的近似数量。

对于基于<=>比较运算符的join，NULL不会与任何其他值有所不同：NULL <=> NULL，正如N <=> N（N为任何其他值）。

但是，对于基于=的join，NULL与非空值不同：在expr1或expr2（或两者都）为NULL时，expr1=expr2时不为真。对于比较形式tbl_name.key = expr，这会影响ref访问：如果expr值为NULL，那么MySQL将不会访问该表，因为该比较不可能为真。

对于`=’比较，表中有多少NULL值并不重要。对于优化目的，”相关值“是”非NULL值组“的平均大小。但是，MySQL当前不支持平均大小的收集或使用。

对于InnoDB和MyISAM表，您可以通过innodb_stats_method和myisam_stats_method系统变量分别控制表统计信息的集合。这些变量有三个可能的值，区别如下：

	当变量设置为nulls_equal时，所有NULL值都被视为相同（即它们都形成一个单一的值组）。如果NULL值组大小远高于平均非NULL值组大小，则此方法会向上偏移平均值组大小。这使得优化器显示的索引对于寻找非NULL值的联接实际上不太有用。因此，nulls_equal方法可能会导致优化器在应用时不使用ref访问的索引。

	当变量设置为nulls_unequal时，NULL值不被认为是相同的。相反，每个NULL值形成一个大小为1的单独的值组。如果有很多NULL值，则此方法会将平均值组大小向下倾斜。如果“平均非NULL值组”很大，则以每个大小为1的组计算NULL值的做法会导致优化程序高估（寻找非NULL值的联接的）索引值。因此，当其他方法可能更好时，nulls_unequal方法可能会使优化器使用此索引进行ref查找。

	当变量设置为nulls_ignored时，将忽略NULL值。

如果您倾向于使用许多带<=>而不是=的连接，则NULL值在比较中不是特殊的，一个NULL等于另一个。在这种情况下，nulls_equal是适当的统计方法。

innodb_stats_method系统变量具有全局值; myisam_stats_method系统变量具有全局值和会话值。设置全局值会影响来自相应存储引擎的表的统计信息收集。设置会话值仅影响当前客户端连接的统计信息收集。这意味着您可以强制使用给定方法重新生成表的统计信息，而不会通过设置myisam_stats_method的会话值来影响其他客户端。

要重新生成MyISAM表统计信息，可以使用以下任一方法：

	执行: myisamchk --stats_method=method_name --analyze
	更改表以使其统计信息过期（例如，插入一行然后将其删除），然后设置myisam_stats_method并发出ANALYZE TABLE语句

关于使用innodb_stats_method和myisam_stats_method的一些注意事项：

	您可以强制明确收集表统计信息，如上所述。但是，MySQL也可能自动收集统计信息:如果在执行表的语句的过程中，有些语句修改表，MySQL可能会收集统计信息。（例如，这可能是批量插入或删除，或某些ALTER TABLE语句发生的）。如果发生这种情况，不管innodb_stats_method或myisam_stats_method的值是什么都会收集统计信息。因此，如果使用一种方法收集统计信息，但是当稍后自动收集表的统计信息时系统变量将设置为其他方法，那么将使用其他方法。

	无法确定哪个方法用于生成给定表的统计信息。

	这些变量仅适用于InnoDB和MyISAM表。其他存储引擎只有一种收集表统计信息的方法。通常它更接近于nulls_equal方法。

B-Tree和Hash索引的比较

了解B树和散列数据结构可以帮助预测不同的查询在不同的存储引擎上使用这些数据结构在索引中的运行情况，特别是对于允许您选择B-Tree和Hash索引的MEMORY存储引擎。

B-Tree索引的特点

B-tree索引可用于使用=，>，> =，<，<=或BETWEEN运算符的表达式中的列比较。如果LIKE的参数是不以通配符开头的常量字符串，那么索引也可用于LIKE比较。例如，以下SELECT语句使用索引：

	SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
	SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第一个语句中，只考虑带有’Patrick'<= key_col <‘Patricl’的行。在第二个语句中，只考虑带有’Pat'<= key_col <‘Pau’的行。

以下SELECT语句不使用索引：

	SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
	SELECT * FROM tbl_name WHERE key_col LIKE other_col;

在第一个语句中，LIKE值以通配符开头。在第二个语句中，LIKE值不是常数。

如果您使用… LIKE’％string％’，并且字符串长度超过三个字符，MySQL使用Turbo Boyer-Moore算法初始化字符串的模式，然后使用此模式更快地执行搜索。

使用col_name IS NULL的搜索使用索引，如果col_name被索引。

没有跨越WHERE子句中所有AND级别的任何索引都不用于优化查询。换句话说，为了能够使用索引，必须在每个AND组中使用索引的前缀。

以下WHERE子句使用索引：

	... WHERE index_part1=1 AND index_part2=2 AND other_column=3

		/* index = 1 OR index = 2 */
	... WHERE index=1 OR A=10 AND index=2

		/* optimized like "index_part1='hello'" */
	... WHERE index_part1='hello' AND index_part3=5

		/* Can use index on index1 but not on index2 or index3 */
	... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

这些WHERE子句不使用索引：

		/* index_part1 is not used */
	... WHERE index_part2=1 AND index_part3=2

		/*  Index is not used in both parts of the WHERE clause  */
	... WHERE index=1 OR A=10

		/* No index spans all rows  */
	... WHERE index_part1=1 OR index_part2=10

有时，MySQL不使用索引，即使有可用索引。发生这种情况的一个情况是当优化器估计使用索引将需要MySQL访问表中很大比例的行时。 （在这种情况下，表扫描可能会更快，因为它需要更少的搜索。）然而，如果这样的查询使用LIMIT来检索一些行，MySQL会使用索引，因为它可以更快地找到结果中返回的几行。

Hash 索引的特点

Hash索引与刚才讨论的特点有如下几点不同的地方：

	它们仅用于使用=或<=>运算符（但非常快）的等式比较。它们不用于比较运算符，例如<找到一系列值。依赖此类型单值查找的系统称为“键值存储”;要为这样的应用程序使用MySQL，尽可能使用散列索引。

	优化器不能使用散列索引来加快ORDER BY操作。 （这种类型的索引不能用于按顺序搜索下一个条目。）

	MySQL无法确定两个值之间有多少行（范围优化程序用于决定要使用哪个索引）。如果将MyISAM或InnoDB表更改为Hash索引的MEMORY表，这可能会影响一些查询。

	只有整个键可以用来搜索一行。 （使用B-tree索引，可以使用键的任何最左侧的前缀来查找行。）

使用索引扩展

InnoDB通过将主键列附加到每个辅助索引的方式来自动扩展它。对于如下表定义：

	CREATE TABLE t1 (
	  i1 INT NOT NULL DEFAULT 0,
	  i2 INT NOT NULL DEFAULT 0,
	  d DATE DEFAULT NULL,
	  PRIMARY KEY (i1, i2),
	  INDEX k_d (d)
	) ENGINE = InnoDB;

此表定义列（i1，i2）上的主键。它还在列（d）上定义了辅助索引k_d，但内部InnoDB扩展了此索引并将其视为列（d，i1，i2）。

当确定如何以及是否使用该索引时，优化器会考虑扩展辅助索引的主键列。这可以导致更有效的查询执行计划和更好的性能。

优化器可以使用扩展辅助索引进行ref，range和index_merge索引访问，用于松散索引扫描，用于连接和排序优化以及MIN()/MAX()优化。

以下示例显示执行计划如何受优化器是否使用扩展辅助索引影响。假设t1填充了这些行：

	INSERT INTO t1 VALUES
	(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
	(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
	(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
	(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
	(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
	(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
	(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
	(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
	(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
	(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
	(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
	(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
	(5, 5, '2002-01-01');

对于如下查询：

	EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

在这种情况下，优化器不能使用主键，因为它包含列（i1，i2），查询不涉及i2。相反，优化器可以使用（d）上的辅助索引k_d，并且执行计划取决于是否使用扩展索引。

当优化器不考虑索引扩展时，它将索引k_d视为（d）。 EXPLAIN查询产生此结果：

	mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
	*************************** 1. row ***************************
			   id: 1
	  select_type: SIMPLE
			table: t1
			 type: ref
	possible_keys: PRIMARY,k_d
			  key: k_d
		  key_len: 4
			  ref: const
			 rows: 5
			Extra: Using where; Using index

当优化器考虑到索引扩展时，它将k_d视为（d，i1，i2）。在这种情况下，它可以使用最左侧的索引前缀（d，i1）来产生更好的执行计划：

	key_len从4字节到8字节，表示键查找使用列d和i1，而不仅仅是d。

	ref值从const更改为const，因为键查找使用两个关键部分，而不是一个。

	rows数从5减少到1，表明InnoDB需要检查较少的行来产生结果。

	Extra值从Using where; Using index变为Using index。这意味着只能使用索引读取行，而不需要查询数据行中的列。

使用扩展索引的优化器行为的差异也可以在SHOW STATUS中看到：

	FLUSH TABLE t1;
	FLUSH STATUS;
	SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
	SHOW STATUS LIKE 'handler_read%'

前面的语句包括FLUSH TABLE和FLUSH STATUS来刷新表缓存并清除状态计数器。

没有索引扩展名，SHOW STATUS生成此结果：

	+-----------------------+-------+
	| Variable_name         | Value |
	+-----------------------+-------+
	| Handler_read_first    | 0     |
	| Handler_read_key      | 1     |
	| Handler_read_last     | 0     |
	| Handler_read_next     | 5     |
	| Handler_read_prev     | 0     |
	| Handler_read_rnd      | 0     |
	| Handler_read_rnd_next | 0     |
	+-----------------------+-------+

使用索引扩展名，SHOW STATUS生成此结果。 Handler_read_next值从5减少到1，表示更有效地使用索引：

	+-----------------------+-------+
	| Variable_name         | Value |
	+-----------------------+-------+
	| Handler_read_first    | 0     |
	| Handler_read_key      | 1     |
	| Handler_read_last     | 0     |
	| Handler_read_next     | 1     |
	| Handler_read_prev     | 0     |
	| Handler_read_rnd      | 0     |
	| Handler_read_rnd_next | 0     |
	+-----------------------+-------+

在确定如何使用InnoDB表的辅助索引时，optimizer_switch系统变量的use_index_extensions标志可以控制优化器是否考虑主键列。默认情况下，use_index_extensions已启用。要检查是否禁用使用索引扩展将提高性能，请使用以下语句：

	SET optimizer_switch = 'use_index_extensions=off';

优化器使用索引扩展受到索引（16）中的关键部分数量和最大密钥长度（3072字节）的通常限制。

优化器使用生成的列索引

MySQL支持生成列上的索引。例如：

	CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

生成的列gc被定义为表达式f1 + 1。列也被索引并且优化器可以在执行计划构建期间考虑该索引。在以下查询中，WHERE子句引用gc，优化器会考虑该列上的索引是否产生更有效的计划：

	SELECT * FROM t1 WHERE gc > 9;

优化器可以使用生成的列上的索引来生成执行计划，即使在按名称查询那些列时没有直接引用。如果WHERE，ORDER BY或GROUP BY子句引用与某些索引生成列的定义匹配的表达式，则会发生这种情况。以下查询不直接指向gc，但使用与gc定义相匹配的表达式：

	SELECT * FROM t1 WHERE f1 + 1 > 9;

优化器识别表达式f1 + 1与gc的定义匹配，并且gc被索引，因此在执行计划构建期间认为该索引。你可以使用EXPLAIN看到：

	mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G
	*************************** 1. row ***************************
			   id: 1
	  select_type: SIMPLE
			table: t1
	   partitions: NULL
			 type: range
	possible_keys: gc
			  key: gc
		  key_len: 5
			  ref: NULL
			 rows: 1
		 filtered: 100.00
			Extra: Using index condition

实际上，优化器已将表达式f1 + 1替换为与表达式匹配的生成列的名称。在SHOW WARNINGS显示的扩展EXPLAIN信息中可用的重写查询中也显而易见：

	mysql> SHOW WARNINGS\G
	*************************** 1. row ***************************
	  Level: Note
	   Code: 1003
	Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
			 AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)

以下限制和条件适用于优化器使用生成的列索引：

	* 对于要匹配生成的列定义的查询表达式，表达式必须相同，并且必须具有相同的结果类型。例如，如果生成的列表达式为f1 + 1，则如果查询使用1 + f1，或者如果将f1 + 1（整数表达式）与字符串进行比较，则优化器将无法识别匹配项。

	* 优化适用于这些运算符：=，<，<=，>，> =，BETWEEN和IN（）。 对于除BETWEEN和IN（）之外的运算符，任何一个操作数都可以被匹配的生成列替换。对于BETWEEN和IN（），只有第一个参数可以被匹配的生成列替换，而其他参数必须具有相同的结果类型。对于涉及JSON值的比较，BETWEEN和IN（）不支持。

	* 生成的列必须定义为至少包含函数调用的表达式或上述项中提到的运算符之一。该表达式不能包含对另一列的简单引用。例如，gc INT AS（f1）STORED仅由列引用组成，因此不考虑gc上的索引。

	* 为了比较字符串到索引的生成列，计算从返回引用字符串的JSON函数的值，列定义中需要JSON_UNQUOTE（），以从函数值中除去额外的引号。 （为了直接比较一个字符串与函数结果，JSON比较器处理引号删除，但这不会发生在索引查找中。）例如，不是像这样编写列定义：
			doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED

		写这样：

			doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED

		使用后一种定义，优化器可以检测这两种比较的匹配：

			... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ...
			... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...

		列定义中没有JSON_UNQUOTE（），优化器仅针对第一个比较检测匹配。

	* 如果优化程序无法选择所需的索引，则可以使用索引提示强制优化程序进行不同的选择。