MySQL 8.0 官方文档第八章优化（二十二）—— 行构造器表达式优化和避免全表扫描

本文链接：https://blog.csdn.net/qq_39785418/article/details/116910948

第八章优化（二十二）—— 行构造器表达式优化和避免全表扫描

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.22 行构造器（Row Constructor）表达式优化

行构造器允许同时比较多个值。例如，以下两个语句在语义上是等价的：

SELECT * FROM t1 WHERE (column1, column2) = (1,1);

SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

此外，优化器也以相同的方式处理这两个表达式。

如果行构造器列没有覆盖索引的前缀，则优化器不太可能使用可用的索引。考虑下表，它的主键在(c1, c2, c3)上：

CREATE TABLE t1 (
	c1 INT, c2 INT, c3 INT, c4 CHAR(100),
	PRIMARY KEY(c1, c2, c3)
);

在以下查询中，WHERE子句使用了索引中的所有列。但是，行构造器本身并没有覆盖索引前缀，结果优化器只使用c1（key_len=4, c1的字节大小）:

mysql> EXPLAIN SELECT * FROM t1
       		WHERE c1 = 1 AND (c2, c3) > (1, 1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 3
     filtered: 100.00
        Extra: Using where

在这种情况下，使用等效的非构造器表达式重写原行构造器表达式可能会导致使用更完整的索引。对于给定的查询，行构造器表达式和等效的非构造器表达式表示如下：

(c2, c3) > (1, 1)

c2 > 1 OR ((c2 = 1) AND (c3 > 1))

将该查询重写成使用非构造器表达式导致优化器使用了索引中所有的三列（key_len=12）：

mysql> EXPLAIN SELECT * FROM t1
       WHERE c1 = 1 AND (c2 > 1 OR ((c2 = 1) AND (c3 > 1)))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 12
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where

因此，为了获得更好的结果，请避免将行构造其与AND/OR表达式混合使用。可以使用其中的一个。

在某些条件下，优化器可以将范围访问方法应用于含有行构造器参数的IN()表达式。参见行构造器表达式的范围优化。

8.2.1.23 避免全表扫描

如果MySQL使用全表扫描解析查询，则在EXPLAIN输出的type列中显示ALL。这通常发生在以下情况：

这个表很小，这时执行表扫描比进行键查找更快。这对于行数少于10，且行长度较短的表很常见。
对于索引列而言，在ON或WHERE子句中没有可用的限制。
您将索引列与常量值进行比较，而MySQL已经计算出（基于索引树）常量将覆盖了表中的很大一部分，认为表扫描会更快。见第8.2.1.1节，“WHERE子句优化”。
您正在通过另一列使用基数较低的键（有许多行会匹配键值）。在这种情况下，MySQL假设使用键可能需要很多次的键查找，认为表扫描会更快。

对于小表，表扫描通常是合适的，性能影响可以忽略不计。对于大型表，请尝试以下技术以避免优化器错误地选择表扫描：
使用ANALYZE TABLE tbl_name更新被扫描表的键分布。参见13.7.3.1节，“分析表语句”。
对要扫描的表使用FORCE INDEX（强制索引），告诉MySQL表扫描比使用给定的索引成本更高：

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
  		WHERE t1.col_name = t2.col_name;

参见 8.9.4节，“索引提示”。

带--max-seek -for-key=1000（查找键最多次数=1000）选项启动mysqld，或者使用SET max_seeks_for_key=1000来告诉优化器：假设通过键扫描不会导致超过1000次键的寻找。参见5.1.8节，“服务器系统变量”。

上一集 MySQL 8.0 官方文档第八章优化（二十一）—— 窗口函数优化

下一集 MySQL 8.0 官方文档第八章优化（二十三）—— 优化子查询、派生表、视图引用和公共表表达式