MySQL 优化：优化锁操作

来自Wikioe
跳到导航 跳到搜索


关于

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

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

MySQL 如何使用索引

索引用于快速查找具有特定列值的行。

  • 没有索引，MySQL 必须从第一行开始，然后通读整个表以找到相关的行。桌子越大，花费越多。如果表中有相关列的索引，MySQL 可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。
  • 大多数 MySQL 索引（“PRIMARY KEY”，“UNIQUE”，“INDEX”和“FULLTEXT”）存储在 B-trees 中。
    • 例外：空间数据类型的索引使用 R 树； MEMORY 表也支持 hash indexes; InnoDB 对 FULLTEXT 索引使用反向列表【？】。


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

  1. 快速查找与 WHERE 子句匹配的行。
  2. 从考虑中消除行。如果可以在多个索引之间进行选择，MySQL 通常会使用找到最少行数（最多selective索引）的索引。
  3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行。例如，如果在 (col1, col2, col3) 上具有三列索引，则在 (col1)，(col1, col2) 和 (col1, col2, col3) 上具有索引搜索功能。
  4. 执行联接时从其他表中检索行。如果声明相同的类型和大小，MySQL 可以更有效地在列上使用索引。在这种情况下，如果 VARCHAR 和 CHAR 声明为相同大小，则认为它们相同。


NOTE：

  • 对于非二进制字符串列之间的比较，两个列应使用相同的字符集。例如，将 utf8 列与 latin1 列进行比较会排除使用索引。
  • 如果不能不通过转换直接比较值，则比较不同的列可能会阻止使用索引（例如，将字符串列与时间或数字列进行比较）。对于数字列中的给定值（例如 1），它的值可能等于字符串列中的任何数量的值，例如'1'，' 1'，'00001'或'01.e1'。这排除了对字符串列使用任何索引的可能性。
  • 查找特定索引列 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”，则按相反顺序读取密钥。
  • 在某些情况下，可以优化查询以检索值而无需查询数据行。（为查询提供所有必要结果的索引称为covering index，即覆盖索引）如果查询仅从表中使用某些索引中包含的列，则可以从索引树中检索所选值，以提高速度：【？？？？？？】 
    SELECT key_part3 FROM tbl_name
  WHERE key_part1=1


对于报表查询处理大多数或所有行的小型表或大型表，索引的重要性不那么重要。当查询需要访问大多数行时，顺序读取要比处理索引快。顺序读取可最大程度地减少磁盘查找，即使查询不需要所有行。

主键优化

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

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

外键优化

如果一个表有许多列，并且您查询了许多不同的列组合，将不常用的数据拆分为单独的表（每个表包含几列），然后通过复制数字 ID 将它们关联回主表的列可能会比较有效。这样，每个小表都可以具有一个主键来快速查找其数据，并且您可以使用联接操作查询所需的列集。根据相关数据的分布方式，查询可能执行较少的 I/O 并占用较少的缓存，因为相关的列打包在磁盘上。（为了最大化性能，查询尝试从磁盘上读取尽可能少的数据块；只有几列的表可以在每个数据块中容纳更多的行）【拆分具有很多列的表为多个表】

列索引

索引的最常见类型涉及单个列，该列将来自该列的值的副本存储在数据结构中，从而允许快速查找具有相应列值的行。 B 树数据结构使索引可以在“WHERE”子句中快速找到与诸如“=”，“>”，“≤”，“BETWEEN”，“IN”等的运算符相对应的特定值，一组值或一系列值。【范围查询经不经过索引？？？】


不同存储引擎对每个表的最大索引数和最大索引长度定义不同。所有存储引擎每个表至少支持 16 个索引，并且总索引长度至少为 256 个字节。大多数存储引擎都有更高的限制。

索引前缀

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

  • 为“BLOB”或“TEXT”列构建索引时，必须为索引指定前缀长度。例如： 
    CREATE表test (blob_col BLOB, INDEX(blob_col(10)));


前缀最长可以为 1000 个字节（对于 InnoDB 表，则为 767 个字节，除非您设置了“innodb_large_prefix”）。

  1. 前缀限制以字节为单位，
  2. 而“CREATE TABLE”，“ALTER TABLE”和“CREATE INDEX”语句中的前缀长度被解释为：
    1. 非二进制字符串类型（CHAR，VARCHAR，TEXT）的字符数
    2. 二进制字符串类型（BINARY，VARBINARY，BLOB的字节数））的字节数


如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。【？？？】

FULLTEXT 索引

FULLTEXT 索引用于全文搜索。

  • 仅 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引，并且仅支持 CHAR，VARCHAR 和 TEXT 列（非二进制字符串类型）。
  • 索引始终在整个列上进行，并且不支持列前缀索引。


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

  • 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 tables optimized away”；在这种情况下，以后执行期间无需进行表访问。

空间索引

您可以在空间数据类型上创建索引。

  1. MyISAM 和 InnoDB 支持有关空间类型的 R 树索引。
  2. 其他存储引擎使用 B 树来索引空间类型（ARCHIVE除外，不支持空间类型索引）。

MEMORY 存储引擎中的索引

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

多列索引（复合索引）

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


MySQL 可以将多列索引用于测试索引中所有列的查询，或者仅测试第一列，前两列，前三列等等的查询。

  • 如果在索引定义中以正确的顺序指定列，则单个复合索引可以加快对同一表的几种查询。

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


Note：【？？？】
作为复合索引的替代方法，您可以引入基于其他列信息“散列”的列。如果此列短，合理唯一并且已构建索引，则它可能比许多列上的“宽”索引快。在 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 值的查询，因为该列是索引的最左前缀【但不能用于仅指定 first_name 值的查询，因为其不能作为索引前缀】。

因此，name 索引用于以下查询中的查找：
SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

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

但是，name 索引不能用于查找以下查询中：【因为：first_name 列不能作为 name 索引的索引前缀】 

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';


假设您发出以下 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) 的最左前缀。

验证索引使用情况

使用 EXPLAIN 语句来检查所有查询是否真的使用您在表中创建的索引。

InnoDB 和 MyISAM 索引统计信息收集

存储引擎收集有关表的统计信息，以供优化器使用。表统计信息基于值组，其中值组是一组具有相同键前缀值的行。出于优化目的，重要的统计数据是平均值组的大小。


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

  1. 估算每次 ref 访问必须读取多少行。
  2. 估计部分联接将产生多少行。也就是说，这种形式的操作将产生的行数： 
    (...) JOIN tbl_name ON tbl_name.key = expr

随着索引的平均值组大小的增加，索引对于这两个目的的用处就越小，因为每次查找的平均行数会增加：为了使索引更好地用于优化目的，最好每个索引值都以表中的少量行为目标。【？？？】

  • 当一个给定的索引值产生大量的行时，索引就不太有用，MySQL也不太可能使用它。


平均值组的大小与表基数有关，表基数是值组的数目。 “SHOW INDEX”语句显示基于 N/S 的基数值，其中 N 是表中的行数，而 S 是平均值组的大小。该比率在表中产生大约数量的值组。


对于基于“<=>”比较运算符的联接，NULL 与任何其他值的处理方式没有区别：“NULL<=>NULL”，就像“N<=>N”对于任何其他 N 一样。

  • “<=>”NULL 安全等于运算符【类似于“=”运算符。但是，如果两个操作数均为NULL，则返回 1 而不是 NULL，如果一个操作数为 NULL 则返回 0 而不是 NULL】

但是，对于基于“=”运算符的联接，NULL 与 非NULL 值不同：当 expr1 或 expr2（或两者）均为NULL时，“expr1 = expr2”不是 true。

  • 这会影响以“tbl_name.key = expr”形式进行比较的访问：如果 expr 的当前值为 NULL，则 MySQL 将不会访问表，因为比较不能为真。

对于“=”比较，表中有多少 NULL 个值都没有关系。为了优化目的，相关值是非NULL值组的平均大小。但是，MySQL 当前不支持收集或使用该平均大小。【？？？】


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

  1. 当变量设置为“nulls_equal”时，所有“NULL”值都被视为相同（即，它们全部形成一个值组）。
    如果“NULL”值组的大小比平均非“NULL”值组的大小大得多，则此方法会使平均值组的大小向上倾斜。这使得索引在优化器中似乎没有那么有用，而对于查找非“NULL”值的联接而言，索引的作用实际上不那么有用。因此，“nulls_equal”方法可能会导致优化器在应该使用ref访问时不使用索引。
  2. 当变量设置为“nulls_unequal”时，“NULL”的值被认为是不相同的。而是每个“NULL”值形成一个单独的大小为 1 的值组。
    如果您有许多“NULL”值，则此方法会使平均值组的大小向下倾斜。如果平均非“NULL”值组大小很大，则将每个“NULL”值作为一组大小 1 进行计数会导致优化器过高估计寻找非“NULL”值的联接的索引值。因此，当其他方法可能更好时，“nulls_unequal”方法可能会使优化器将此索引用于ref查找。
  3. 当变量设置为“nulls_ignored”时，将忽略“NULL”值。
  • 如果您倾向于使用许多使用“<=>”而不是“=”的联接，则“NULL”的值在比较中并不特殊，一个“NULL”等于另一个。在这种情况下，“nulls_equal”是适当的统计方法。
  • “innodb_stats_method”系统变量具有全局值；“myisam_stats_method”系统变量同时具有全局值和会话值。设置全局值会影响从相应存储引擎收集表的统计信息。设置会话值只会影响当前 Client 端连接的统计信息收集。这意味着您可以通过将会话值设置为“myisam_stats_method”来强制使用给定的方法重新生成表的统计信息，而不会影响其他 Client 端。


要重新生成 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 树和哈希索引的比较

了解 B 树和哈希数据结构可以帮助预测不同查询如何对在索引中使用这些数据结构的不同存储引擎执行不同的查询，尤其是对于使您选择 B 树或哈希索引的 MEMORY 存储引擎而言。

B 树索引 Feature

B 树索引可用于使用“=”，“>”，“>=”，“<”，“<=”或“BETWEEN”运算符的表达式中的列比较。如果“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 算法初始化字符串的模式，然后使用该模式更快地执行搜索。
  • 如果 col_name 被索引，则使用“col_name IS NULL”的搜索将使用索引。
  • 不能使用“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 仍会使用索引，因为它可以更快地找到返回结果的几行。

哈希指数 Feature

哈希索引与刚刚讨论的索引具有一些不同的 Feature：

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

索引扩展的使用

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;

该 table 在 (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'
  • 执行计划取决于是否使用扩展索引。
  1. 当优化器不考虑索引扩展时，它会将索引 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
  2. 当优化器考虑索引扩展时，会将 k_d 视为 (d, i1, i2)。在这种情况下，它可以使用最左边的索引前缀 (d, i1) 来制定更好的执行计划： 
    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: 8
          ref: const,const
         rows: 1
        Extra: Using index

在这两种情况下，key 表示优化器将使用辅助索引 k_d，但是 EXPLAIN 的输出显示了使用扩展索引的以下改进：

  • key_len 从 4 字节变为 8 字节，表示键查找使用的是 d 和 i1 列，而不仅仅是 d。
  • ref值从 const 变为 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 TABLES”和“FLUSH STATUS”以刷新表缓存并清除状态计数器。
  1. 没有索引扩展，“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     |
+-----------------------+-------+
  2. 使用索引扩展，“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     |
+-----------------------+-------+


“optimizer_switch”系统变量的“use_index_extensions”标志允许控制在确定如何使用 InnoDB 表的辅助索引时优化器是否考虑主键列。

  • 默认情况下，启用“use_index_extensions”。

要检查禁用索引扩展的使用是否可以提高性能，请使用以下语句： 

SET optimizer_switch = 'use_index_extensions=off';


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

优化器对生成的列索引的使用【？？？】

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

CREATE表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()”的情况下，优化器仅针对这些比较中的第一个比较检测到匹配项。【？】
  • 如果优化器无法选择所需的索引，则可以使用索引提示来强制优化器做出其他选择。

从 TIMESTAMP 列进行索引查找【？？？哈？】

时间值作为 UTC 值存储在“TIMESTAMP”列中，并且在会话时区和 UTC 之间转换插入和从 TIMESTAMP 列中检索的值。（这与“CONVERT_TZ()”函数执行的转换类型相同。如果会话时区为 UTC，则实际上没有时区转换。）


由于诸如夏令时（DST）等本地时区更改的约定，UTC 和非 UTC 时区之间的转换在两个方向上都不是一对一的。不同的 UTC 值在另一个时区可能不会不同。

以下示例显示了不同的 UTC 值，它们在非 UTC 时区中变得相同：【？？？】 

mysql> CREATE TABLE tstable (ts TIMESTAMP);
mysql> SET time_zone = 'UTC'; -- insert UTC values
mysql> INSERT INTO tstable VALUES
       ('2018-10-28 00:30:00'),
       ('2018-10-28 01:30:00');
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 00:30:00 |
| 2018-10-28 01:30:00 |
+---------------------+
mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+
  • 要使用诸如 'MET' 或 'Europe/Amsterdam' 之类的命名时区，必须正确设置时区表。【？】


您可以看到两个不同的 UTC 值在转换为 'MET' 时区时是相同的。对于给定的 TIMESTAMP 列查询，此现象可能导致不同的结果，具体取决于优化器是否使用索引来执行查询。


示例：
假设查询使用 WHERE 子句从前面显示的表中选择值，以在 ts 列中搜索单个特定值，例如用户提供的时间戳 Literals： 

SELECT ts FROM tstable
WHERE ts = 'literal';

进一步假设查询在以下条件下执行：

  1. 会话时区不是 UTC，并且具有 DST 偏移。例如： 
    SET time_zone = 'MET';
  2. 由于 DST 偏移，在 TIMESTAMP 列中存储的唯一 UTC 值在会话时区中不是唯一的。 (前面显示的示例说明了这种情况的发生.)
  3. 该查询指定了在会话时区中输入 DST 小时内的搜索值。

在这种情况下，对于未构建索引和构建索引的查找，WHERE 子句中的比较以不同的方式发生，并导致不同的结果：

  1. 如果没有索引或优化器无法使用索引，则会在会话时区中进行比较。优化器执行表扫描，其中检索每个 ts 列值，将其从 UTC 转换为会话时区，并将其与搜索值（也在会话时区中解释）进行比较： 
    mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+
    由于已存储的 ts 值已转换为会话时区，因此查询有可能返回两个时间戳值，这些时间戳值与 UTC 值不同，但在会话时区中相等：更改时钟时，在 DST 移位之前出现的一个值，以及 DST 移位后出现的一个值。
  2. 如果有可用的索引，则以 UTC 进行比较。优化器执行索引扫描，首先将搜索值从会话时区转换为 UTC，然后将结果与 UTC 索引条目进行比较： 
    mysql> ALTER TABLE tstable ADD INDEX (ts);
mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
+---------------------+
    在这种情况下，（转换后的）搜索值仅与索引条目匹配，并且由于不同存储的 UTC 值的索引条目也不同，因此搜索值只能匹配其中之一。


由于针对非索引和索引查找的优化器操作不同，因此在每种情况下查询都会产生不同的结果。非索引查找的结果将返回在会话时区中匹配的所有值。索引查找不能这样做：

  • 它在仅了解 UTC 值的存储引擎内执行。
  • 对于映射到相同 UTC 值的两个不同的会话时区值，索引查找仅匹配相应的 UTC 索引条目，并且仅返回单行。【！！！】

在前面的讨论中，存储在 ts 表中的数据集恰好由不同的 UTC 值组成。在这种情况下，所示形式的所有使用索引的查询最多匹配一个索引条目。


如果索引不是 UNIQUE，则表（和索引）可以存储给定 UTC 值的多个实例。例如，ts 列可能包含多个 UTC 值 '2018-10-28 00:30:00' 的实例。在这种情况下，使用索引的查询将返回它们中的每一个（在结果集中转换为 MET 值 '2018-10-28 02:30:00'）。仍然使用索引的查询将转换后的搜索值与 UTC 索引条目中的单个值进行匹配，而不是将在会话时区中转换为搜索值的多个 UTC 值进行匹配。


如果返回在会话时区中匹配的所有 ts 值很重要，解决方法是使用“IGNORE INDEX”提示禁止使用索引： 

mysql> SELECT ts FROM tstable
       IGNORE INDEX (ts)
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

在其他情况下，例如使用“FROM_UNIXTIME()”和“UNIX_TIMESTAMP()”函数执行的转换，在两个方向上也存在相同的缺少时空转换的双向映射。【？】

取自“http://wiki.eijux.com/index.php?title=MySQL_优化：优化锁操作&oldid=6555”