mysql 翻译系列 三十
10.3.8 InnoDB和MyISAM索引统计信息收集
存储引擎会收集表的统计信息，以供优化器使用。表统计信息基于值组，值组是指具有相同键前缀值的一组行。对于优化器而言，一个重要的统计信息是平均 值组大小。
MySQL以以下方式使用平均 值组大小：
- 估计每次
ref访问必须读取的行数。 - 估计部分连接产生的行数，即形如
(...) JOIN tbl_name ON tbl_name.key = expr的操作产生的行数。
随着索引的平均 值组大小增加，该索引对于上述两个目的的作用就会降低，因为每次查找的平均行数增加了。为了让索引对优化更有帮助，最好是每个索引值能定位到表中的少量行。当一个给定的索引值产生大量行时，该索引的作用就会降低，MySQL使用它的可能性也会降低。
平均 值组大小与表的基数相关，基数即值组的数量。SHOW INDEX语句会显示一个基于N/S的基数，其中N是表中的行数，S是平均 值组大小。这个比率给出了表中值组的大致数量。
对于基于<=>比较运算符的连接，NULL与其他任何值的处理方式没有区别：NULL <=> NULL，就像对于任何其他N，N <=> N一样。
然而，对于基于=运算符的连接，NULL与非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值组的大小很大，将每个NULL值都计为大小为1的组，会导致优化器高估对于查找非NULL值的连接的索引价值。因此，nulls_unequal方法可能会导致优化器在其他方法可能更好的情况下，仍使用这个索引进行ref查找。 - 当变量设置为
nulls_ignored时，NULL值会被忽略。
如果你倾向于使用许多基于<=>而不是=的连接，在比较中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方法。
10.3.9 B树索引与哈希索引的比较
理解B树和哈希数据结构，有助于预测不同查询在使用这些数据结构构建索引的不同存储引擎上的性能表现，特别是对于允许选择B树索引或哈希索引的MEMORY存储引擎。
B树索引的特点
-
适用运算符：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 < '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%'且string长度超过三个字符，MySQL会使用Turbo Boyer-Moore算法初始化字符串的模式，然后利用该模式更快地执行搜索。 -
IS NULL查询：如果col_name被索引，使用col_name IS NULL的搜索会使用索引。 -
WHERE子句限制：任何未覆盖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;
/* 优化为 "index_part1='hello'" */... WHERE index_part1='hello' AND index_part3=5;
/* 可使用index1上的索引，但不能使用index2或index3上的索引 */... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
这些WHERE子句不会使用索引：
/* index_part1未被使用 */... WHERE index_part2=1 AND index_part3=2;
/* 索引未在WHERE子句的两个部分中使用 */... WHERE index=1 OR A=10;
/* 没有索引覆盖所有行 */... WHERE index_part1=1 OR index_part2=10;
-
索引未使用情况：有时，即使存在可用索引，MySQL也不会使用。一种情况是，当优化器估计使用索引需要MySQL访问表中很大比例的行时（在这种情况下，全表扫描可能会快得多，因为查找次数更少）。不过，如果这样的查询使用
LIMIT仅检索部分行，MySQL仍会使用索引，因为它可以更快地找到要返回的少量行。
哈希索引的特点
哈希索引与上述B树索引的特点有所不同：
-
适用运算符：哈希索引仅用于使用
=或<=>运算符的相等比较（但速度非常快），不用于诸如<等查找值范围的比较运算符。依赖这种单值查找的系统被称为 “键值存储”；若要在这类应用中使用MySQL，应尽可能使用哈希索引。 -
ORDER BY操作：优化器无法使用哈希索引加速ORDER BY操作（这种类型的索引不能用于按顺序搜索下一个条目）。 - 行数估算：MySQL无法确定两个值之间大约有多少行（范围优化器会利用这一点来决定使用哪个索引）。如果将MyISAM或InnoDB表更改为哈希索引的MEMORY表，这可能会影响某些查询。
- 搜索限制：只能使用完整的键来搜索行（而B树索引可以使用键的任何最左前缀来查找行）。
