8.3 mysql 优化器和索引

8.3 优化器和索引

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

尽管为查询中使用的每个可能的列创建索引是很诱人的，但是不必要的索引会浪费空间并浪费时间来确定使用哪个索引。 索引还会增加插入，更新和删除的成本，因为每个索引都必须更新。 您必须找到适当的平衡，以使用最佳索引集合实现快速查询。

8.3.1 MYSQL 如何使用索引

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

大多数MySQL索引（PRIMARY KEY，UNIQUE，INDEX和FULLTEXT）都存储在B树中。 异常：空间数据类型的索引使用R树; MEMORY表也支持散列索引; 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”）比较相等。 这排除了字符串列的任何索引的使用。

--mysql 隐式转换字符串到数值

• 查找特定索引列key_col的MIN（）或MAX（）值。 这由预处理器进行了优化，该预处理器检查您是否在索引中的key_col之前发生的所有关键部分上使用WHERE key_part_N=constant。 在这种情况下，MySQL为每个MIN（）或MAX（）表达式执行单个键查找，并用常量替换它。 如果所有表达式都被常量替换，则查询立即返回。 例如：

SELECT MIN(key_part2),MAX(key_part2)

FROM tbl_name WHERE key_part1=10;

• 如果对可用索引的最左边的前缀进行排序或分组（例如，ORDER BY key_part1，key_part2）。 如果所有关键部分都跟随着DESC，则按相反顺序读取该键。

--这就是之前讲的order by 优化和 group by 优化

• 在某些情况下，可以优化查询以检索值，而无需查阅数据行。 （为查询提供所有必要结果的索引称为covering index）如果查询仅使用表中某些索引中包含的列，则可以从索引树中检索所选值以获得更高的速度：

SELECT key_part3 FROM tbl_name

WHERE key_part1=1

--使用组合索引消除回表

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

8.3.2 使用主键

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

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

8.3.3 使用外键

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

-------拆分表，这个必须懂业务才行

8.3.4 字段索引

最常见的索引类型涉及单列，将该列中的值的副本存储在数据结构中，从而允许快速查找具有相应列值的行。 B树数据结构使索引能够在WHERE子句中(例如=，>，≤，BETWEEN，IN等） 对应的特定值，一组值或一系列值进行快速的查找.

每个表的索引的最大数目和最大索引长度是根据存储引擎定义的。

所有存储引擎每个表至少支持16个索引，总索引长度至少为256个字节。 大多数存储引擎有更高的限制。

前缀索引

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

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

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

--具体确定一个合适的索引字段长处，也就是说没必要对列上整个都索引起来。这个长度的大小和 cardinality有关，可以通过 SHOW INDEX FROM 看到。随着数据变化，之前合适的索引可能后面又需要调整了。

注意：

前缀限制以字节为单位，而CREATE TABLE语句中的前缀长度则被解释为字符数。 在为使用多字节字符集的列指定前缀长度时要考虑到这一点。

FULLTEXT 索引

您也可以创建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比较运算符。

空间索引

您也可以在空间数据类型上创建索引。 目前，MyISAM和（从MySQL 5.7.5开始）InnoDB支持空间类型的R-tree索引。 其他存储引擎使用B树来索引空间类型（除了ARCHIVE，它不支持空间类型索引）。

MEMORY 存储引擎中的索引

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

8.3.5 复合索引(Mutiple-column Indexes)

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

MySQL可以对测试索引中所有列的查询使用复合索引，或者只测试第一列，前两列，前三列等等的查询。 如果您在索引定义中以正确的顺序指定列，则一个组合索引可以加速同一个表上的多种查询。

可以将复合索引视为排序数组，其行包含通过连接索引列的值创建的值。

注意：

作为组合索引的替代方法，您可以引入基于来自其他列的信息的“哈希”列。 如果这个列是短的，合理的唯一的和被索引的，它可能比复合列上的“宽”索引更快。 在MySQL中，使用这个额外的列是非常容易的：

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语句：

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

8.3.7 INnoDB 和MYISAM 索引 统计信息收集

存储引擎收集关于优化器使用的表的统计信息。 表统计信息基于value group，其中value group 是具有相同关键字前缀值的一组行。 为了优化器的目的，一个重要的统计是平均value group 的大小。

MySQL通过以下方式使用平均value group 大小：

• 评估每个ref访问必须读取的行数
• 评估部分连接会产生多少行; 也就是说，这个表单的操作将会产生的行数：

(...) JOIN tbl_name ON tbl_name.key=expr

随着索引的平均值组大小增加，索引对于这两个目的的用处不大，因为每次查找的平均行数增加：为了索引对于优化目的是有利的，最好的每个索引值的目标是缩小 表中的行数。 当给定的索引值产生大量的行时，索引不太有用，MySQL不太可能使用它。

平均value group 大小与表基数有关，即表value group 的数目。 SHOW INDEX语句显示基于N / S的基数值，其中N是表中的行数，S是平均value group 大小。 该比率在表格中产生近似的value group 数量。

对于基于<=>比较运算符的连接，NULL与其他值的处理方式不同：NULL <=> NULL，就像其他N的N <=> N一样。

但是，对于基于=操作符的连接，NULL与非NULL值不同：当expr1或expr2（或两者）都为NULL时，expr1=expr2不为真。 这会影响ref的访问，以便比较表单tbl_name.key=expr：如果expr的当前值为NULL，则MySQL将不访问表，因为比较结果不能为true

对于=比较，表中有多少个NULL值无关紧要。 为了优化目的，相关值是非空value group 的平均大小。 但是，MySQL目前不支持收集或使用平均大小。

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

• 当变量设置为nulls_equal时，所有的NULL值都被视为相同（也就是说，它们都构成一个单一的值组）。

如果NULL value group大小远高于平均非NULL值组大小，则此方法向上倾斜平均值组大小。这使得索引对优化程序而言对于查找非 -NULL值没有帮助。 因此，nulls_equal方法可能会导致优化器不使用索引进行访问。

• 当变量设置为nulls_unequal时，NULL值不被视为相同。 相反，每个NULL值形成一个大小为1的单独值组。

如果有多个NULL值，则此方法向下倾斜平均值组大小。 如果平均非空值组大小较大，则将NULL值作为一组大小1进行计数会导致优化程序高估查找非空值的连接的索引值。 因此，当其他方法可能更好时，nulls_unequal方法可能会导致优化器使用此索引进行引用查找。

--有点oracle 中直方图的味道

• 当变量设置为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方法。

8.3.8 B树和哈希索引的比较

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

B-tree 索引的特征

B树索引可用于使用=，>，>=，<，<=或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 <'Patrick'的行被考虑。 在第二条语句中，只考虑'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无论如何都会使用索引，因为它可以更快地找到 结果中返回的几行。

哈希索引特征

哈希索引与刚才讨论的特征有些不同：

• 它们仅用于使用=或<=>运算符（但速度非常快）的等式比较。 它们不用于比较运算符，如<找到一范围值。 依赖这种单值查找的系统被称为“键值存储”。 要将MySQL用于此类应用程序，请尽可能使用hash索引。
• 优化器不能使用哈希索引来加速ORDER BY操作。 （这种类型的索引不能用于按顺序搜索下一个条目。）
• MySQL不能确定两个值之间有多少行（范围优化器用来决定使用哪个索引）。 如果将MyISAM或InnoDB表更改为哈希索引的MEMORY表，这可能会影响一些查询。
• 只有整个键可以用来搜索一个行。 （使用B树索引，可以使用键的任何最左边的前缀来查找行。）

8.3.9 优化器使用生成的列索引

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

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

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

SELECT * FROM t1 WHERE gc > 9;

从MySQL 5.7.8开始，优化器可以在生成的列上使用索引来生成执行计划，即使在没有按名称查询这些列的情况下直接引用也是如此。 如果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（），只有第一个参数可以被匹配的生成列替换，其他参数必须具有相同的结果类型。 BETWEEN和IN（）还不支持涉及JSON值的比较。

• 生成的列必须定义为至少包含一个函数调用或前面提到的操作符之一的表达式。该表达式不能包含对另一列的简单引用。例如，gc INT AS（f1）STORED 只有列引用，所以不考虑gc上的索引（gc和f1就是一样的列）
• 为了将字符串与索引生成的列进行比较，这些字段从返回带引号的字符串的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（），那么优化器只会检测到第一个比较的匹配项。

• 如果优化器未能选择所需的索引，则可以使用索引提示来强制优化器做出不同的选择。