优化select语句
- 为了使慢速
SELECT ... WHERE查询更快，首先要检查的是是否可以添加索引。为避免浪费磁盘空间，请构造组合索引。 - 调整函数调用，使函数调用从一多次减少为一次，从而极大地提高了效率。
- 定期使用
ANALYZE TABLE使表统计信息保持最新 ，为优化器提供构造有效执行计划所需的信息。 - 阅读
EXPLAIN计划并调整索引。 - 调整MySQL用于缓存的内存区域的大小和属性。
- 避免锁导致的查询性能问题。
1. WHERE子句优化
-
删除不必要的括号。
((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d) -
常量折叠。
(a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5 -
常量条件消除。
(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6) -> b=5 OR b=6 -
使用索引的常量表达式仅计算一次。
-
直接从MyISAM和MEMORY表中检索没有WHERE的单个表上的COUNT（*）。
-
无效的常量表达式。
SELECT * FROM t WHERE a<0 AND a>1; ︶︵︶ -
如果没有使用GROUP BY或者聚合函数（如
COUNT(),MIN())等）就将HAVING字句合并到WHERE字句。SELECT * FROM t WHERE a=1 HAVING b>1; -> SELECT * FROM t WHERE a=1 AND b>1; -
对于联接中的每个表，构造一个更简单的WHERE以获得表的快速WHERE评估，并尽快跳过行。（复杂的WHERE会延迟每一行数据的过滤时间）
-
在查询的所有表中，优先读取常量表。常量表可以是以下任意一个
-
空表或者只有一行数据的表；
-
与PRIMARY KEY或UNIQUE索引上的WHERE子句一起使用的表，其中所有索引部分都与常量表达式进行比较，并定义为NOT NULL；
以下所有表均用作常量表：
SELECT * FROM t WHERE primary_key=1; SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
-
-
如果
ORDER BY和GROUP BY子句中的所有列都来自同一表，则在连接时优先使用该表。 -
ORDER BY或GROUP BY包含连接队列中第一个表以外的表中的列，则会创建一个临时表（用于连接后数据集的排序，排序后自动删除）。 -
在索引中的所有列都是数字列的情况下，MySQL甚至可以在不查询数据文件的情况下从索引读取行。
-
在输出每一行之前，
HAVING将跳过不匹配该子句的那些行 。（HAVING时对结果集做裁剪，并不能减少扫描行数）
快速查询示例：
SELECT COUNT(*) FROM tbl_name;
SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
SELECT MAX(key_part2) FROM tbl_name WHERE key_part1=constant;
SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... LIMIT 10;
SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
假设索引列是数字，MySQL仅使用索引树来解析以下查询：
SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
SELECT COUNT(*) FROM tbl_name
WHERE key_part1=val1 AND key_part2=val2;
SELECT key_part2 FROM tbl_name GROUP BY key_part1;
以下查询使用索引来按排序顺序检索行，而无需单独的排序遍历：
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... ;
SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... ;
2. 范围优化
对于 BTREE 和 HASH 索引，当使用=、<=>、IN、IS NULL 或者 IS NOT NULL 操作符时 ，
关键元素与常量值的比较关系对应一个范围条件。
HASH索引使用 =， <=>， IN()，IS NULL，或IS NOT NULL操作符的等式比较会很快。BTREE索引使用 >， <， >=， <=， BETWEEN， !=，或 <> ，LIKE ‘pattern’(其中 'pattern’不以通配符开始)操作符，关键元素与常量值的比较关系对应一个范围条件。
范围优化器尝试从WHERE子句中为每个可能的索引提取范围条件 。在提取过程中，删除了不能用于构建范围条件的条件，合并了产生重叠范围的条件，并删除了产生空范围的条件。优化过程请参考单部分索引的范围访问方法。
2.1 多值比较的等距范围优化
CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
(1,1), (1,2), (1,3), (1,4), (1,5),
(2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;
EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
MySQL可以选择索引扫描以获取所有行，然后根据子句中的f2 > 40 条件WHERE以产生最终结果集。范围扫描比全索引扫描更有效，但是在这种情况下不能使用，因为没有条件在第一个索引列f1上 。但是，从MySQL 8.0.13开始，优化器可以使用一种称为“skip scan”的方法，将一次范围扫描分为多次范围扫描，再将多次扫描的结果合并返回。
算法的运行方式如下：
- 获取第一个索引的第一个不同值（
f1 = 1）。 - 根据第一和第二索引构造范围（
f1 = 1 AND f2 > 40）。 - 执行范围扫描。
- 获取第一个索引的下一个不同值（
f1 = 2）。 - 根据第一和第二索引构造范围（
f1 = 2 AND f2 > 40）。 - 执行范围扫描。
使用此策略可减少访问的行数，因为MySQL会跳过不符合每个构造范围的行。此“skip scan”访问方法适用于以下情况：
- 表T具有至少一个复合索引，其索引的形式为（[A_1，…，A_
k，] B_1，…，B_m，C [，D_1，…，D_n]）。关键部分A和D可能为空，但B和C必须为非空。。 - 该查询仅使用一个表。
- 查询不使用
GROUP BY或DISTINCT。 - 该查询仅引用索引中的列。
- 前缀A_1，…，A_
k必须是相等谓词，并且它们必须是常量。这包括IN()。 - 该查询必须是一个联合查询。即，
AND的OR条件。 - C上必须有范围条件。
- 允许在D字段上有过滤条件，但是必须和C上的范围条件一起使用。
对于使用了跳跃范围扫描特性的SQL，使用EXPLAIN查看其执行计划，可以看到：
- 在执行计划输出的Extra一栏中有： Using index for skip scan
- 在执行计划输出的possible_keys一栏中会显示可以使用到的索引
2.2 行构造函数表达式的范围优化
优化程序可以将范围扫描访问方法应用于以下形式的查询：
SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));
为了使优化器使用范围扫描，查询必须满足以下条件：
-
仅使用IN()谓词，不使用NOT IN()。
-
在IN()谓词的左侧 ，行构造器仅包含列引用。
-
在IN()谓词的右侧 ，行构造器仅包含运行时常量。
-
在IN()谓词的右侧 ，有多个行构造器。
3. 索引条件下推优化（ICP）
索引条件下推（ICP）是针对MySQL使用索引从表中检索行的情况的一种优化。
没有使用ICP的过程：
使用ICP的过程：
当使用“索引条件下推”时，EXPLAIN输出将在 Extra列中显示 Using index condition。默认情况下，索引条件下推处于启用状态。
限制：
- 对于
InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取的次数，从而减少I / O操作。 - ICP只用于单表
- ICP用于range, ref, eq_ref, and ref_or_null，并且需要访问表的全部行。
- ICP可用于
InnoDB和MyISAM表
4. Nested-Loop Join Algorithm
4.1 Simple Nested-Loop Join（NLJ）
EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.f1=t2.f1);
在这条语句里，被驱动表t2的字段f1上有索引，join过程用上了这个索引，因此这个语句的执行流程是这样的：
- 从表t1中读入一行数据 R；
- 从数据行R中，取出f1字段到表t2里去查找；
- 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
- 重复执行步骤1到3，直到表t1的末尾循环结束。
在这个流程里：
- 对驱动表t1做了全表扫描，这个过程需要扫描160行；
- 而对于每一行R，根据f1字段去表t2查找，走的是树搜索过程。由于两个表的数据都是不是一一对应的，因此每次的搜索过程要扫描26行，也是总共扫描160*26行；
- 所以，整个执行流程，总扫描行数是160+160*26。
4.2 Block Nested-Loop Join(NBL)
BNL使用对在外部循环中读取的行进行缓冲，以减少必须读取内部循环表的次数。
EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.f1=t2.f1) WHERE t1.f1 = 2;
在这个语句中，被驱动表t2的字段f1上有索引，join过程没有用上这个索引因而使用上了join buffer，因此这个语句的执行流程是这样的：
- 将表t1，t2的已用列读入join buffer中；
- 由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做160次判断，总共需要在内存中做的判断次数是：160 * 160次。
在这个流程里：
- 对驱动表t1做了全表扫描，这个过程需要扫描160行；
- 对被驱动表t2做了全表扫描，这个过程需要扫描160行。
使用Join Buffer有以下要点:
-
join_buffer_size变量决定buffer大小。join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据话，就分段放。
-
只有在join类型为
ALL、index、range的时候才可以使用join buffer。 -
连接缓冲区永远不会分配给第一个非常量表，即使它的类型是
ALL或index。 -
join buffer中只会保存参与join的列, 并非整个数据行。
-
为每个可以缓冲的连接分配一个join buffer，因此可以使用多个join buffer来处理给定查询。
eg.
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 on t1.f1 = t2.f1 LEFT JOIN t3 on t3.f2 = t2.f1 where t1.f1 = 1;
NBL的算法逻辑：
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
empty join buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
}
结论：
-
如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的。
-
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
5. 嵌套连接优化
-
在使用外部联接运算符的联接表达式中省略括号，则可能会更改原始表达式的结果集。（对于外部联接或与内部联接混合的外部联接，删除括号也可能会改变结果。）
eg.
- 表格
t1包含行(1)，(2) - 表
t2包含行(1,101) - 表
t3包含行(101)
mysql> SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | NULL | +------+------+------+------+ mysql> SELECT * FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | 101 | +------+------+------+------+ - 表格
-
对于内连接嵌套循环，支持类似“下推”条件，假设我们的
WHERE条件P(T1,T2,T3)可以用一个联合公式表示：P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3)SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3) WHERE P(T1,T2,T3)则上述语句的执行过程
FOR each row t1 in T1 such that C1(t1) { FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) { FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } }AND连接的C1(T1)，C2(T2)，C3(T3)从最内环推到最外环的地方进行过滤。如果C1(T1)是非常严格的条件，则此条件下推可能会大大减少表中T1传递给内部循环的行数。 -
对于外联接嵌套循环，只有在发现外部表中的当前行在内部表中具有匹配项之后，才检查
WHERE条件。因此，将条件从内部嵌套循环中推出的优化不能直接应用于具有外部联接的查询。在这里，引入了条件下推谓词，该条件下推谓词由遇到匹配时打开的标志保护。FOR each row t1 in T1 such that C1(t1) { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) AND (f1?C2(t2):TRUE) { BOOL f2:=FALSE; FOR each row t3 in T3 such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) { IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) { t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; } IF (!f2) { IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) { t:=t1||t2||NULL; OUTPUT t; } f1=TRUE; } } IF (!f1 && P(t1,NULL,NULL)) { t:=t1||NULL||NULL; OUTPUT t; } }所以对于外连接外联接嵌套循环，只要关联表中存在匹配的数据，查询的执行时间就可以大大改善。
内连接比外联接查询效率要好，因为内连接默认支出”下推“条件。
外联接优化：对于
LEFT JOIN，如果WHERE条件始终为false ，优化器则将LEFT JOIN更改为内联接。eg. t2.column2不存在值为5的数据。
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;因此，将查询转换为内部联接是安全的：
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
6. IS NULL优化
SELECT * FROM tbl_name WHERE key_col IS NULL;
ref_or_null通过首先读取参考键，然后单独搜索具有NULL键值的行来工作。
优化只能处理一个IS NULL。在以下查询中，MySQL仅在表达式上使用a键查找(t1.a=t2.a AND t2.a IS NULL)，而不能在b键使用：
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL)
OR (t1.b=t2.b AND t2.b IS NULL);
7. ORDER BY优化
MySQL可以使用索引排序，无法使用索引时使用的filesort排序。对于组合索引（key_part1, key_part2）：
SELECT * FROM t1
ORDER BY key_part1, key_part2;
上面这条语句，全表ORDER BY时，如果SELECT * 的查询列仅包含索引列，优化器使用索引；如果SELECT * 的查询列多于索引列，在这种情况下，扫描整个索引并查找表行以查找索引中未包含的列可能比扫描表并对结果进行排序要低效。如果是这样，优化器可能不会使用索引。
SELECT * FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;
如果WHERE子句的选择性足以使索引范围扫描比表扫描高效，则（key_part1，key_part2）上的索引将避免排序：
SELECT key_part2 FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;
在以下情况，MySQL 不能使用索引来优化ORDER BY（使用filesort排序），尽管它仍然可以使用索引来查找与该WHERE子句匹配的行 。例子：
-
该查询在
ORDER BY上有不同的索引（两个或以上）；SELECT * FROM t1 ORDER BY key1, key2; -
该查询
ORDER BY对索引的非连续使用：SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3; -
查询混合
ASC和DESC：SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; -
用于获取行的索引与在
ORDER BY中使用的索引不同：SELECT * FROM t1 WHERE key2=constant ORDER BY key1; -
该查询使用
ORDER BY的表达式包含除索引列名称以外的术语：SELECT * FROM t1 ORDER BY ABS(key); SELECT * FROM t1 ORDER BY -key; -
该查询联接了许多表，并且
ORDER BY中的列并非全部来自用于检索行的第一个非恒定表。（这是EXPLAIN输出中没有const联接类型的第一个表 。） -
查询具有
ORDER BY和GROUP BY表达式。 -
仅在前缀索引列上
ORDER BY。在这种情况下，索引不能用于排序。 -
在下面的语句中，选择列表中列的名称也为
a，但它是别名。它指的是ABS(a)，如同ORDER BY中的a，所以上的索引t1.a不能使用：SELECT ABS(a) AS a FROM t1 ORDER BY a; =>SELECT ABS(a) FROM t1 ORDER BY ABS(a);要使用上索引，
SELECT中的别名和ORDER BY中的列名不一样即可。
优化filesort排序
为了提高ORDER BY速度，请检查是否可以让MySQL使用索引而不是额外的排序阶段。如果不可能，请尝试以下策略：
- 增加 sort_buffer_size 变量值。
- 增加 read_rnd_buffer_size 变量值。
- 更改tmpdir 系统变量，使其指向具有大量可用空间的专用文件系统。
GROUP BY优化
GROUP BY子句的最常用方法是扫描整个表并创建一个新的临时表，其中每个组中的所有行都是连续的，然后使用此临时表来发现组并应用聚合函数（如果有的话）。在某些情况下，可使用索引访问避免创建临时表。
优化策略：使用索引访问避免创建临时表。
有两种使用索引进行分组的方法：
- 将分组操作与所有范围谓词（如果有的话）一起应用。
- 首先执行范围扫描，然后对结果元组进行分组。
8.1 松散索引扫描
-
单表查询。
-
最左前缀原则。
-
只支持
MIN()和MAX()，并且是同一列。该列必须在索引中，并且必须是紧跟在GROUP BY中的列之后 。 -
索引中除
GROUP BY查询中引用的那些部分以外的任何其他部分都必须是常量，MIN()和MAX()函数的参数除外 。 -
不支持前缀索引。
eg. 假设t1(c1,c2,c3,c4)table上有一个索引idx(c1,c2,c3) 。松散索引扫描访问方法可用于以下查询：
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
8.2 紧密索引扫描
紧密索引扫描可以是全索引扫描，也可以是范围索引扫描，具体取决于查询条件。
当不满足宽松索引扫描的条件时，仍然有可能避免创建用于GROUP BY查询的临时表。如果WHERE子句中有范围条件，则此方法仅读取满足这些条件的键，之后才执行分组操作。
为了使该方法起作用，对于查询中所有引用键部分之前或之间的部分的列，需要一个等式填充。
假设t1(c1,c2,c3,c4)table上有一个索引 idx(c1,c2,c3)。以下查询不适用于前面所述的“松散索引扫描”访问方法，但仍适用于“紧索引扫描”访问方法。
-
GROUP BY中存在一个缺口，但c2 = 'a'覆盖：SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3; -
GROUP BY开头不是键的第一部分，但是c1 = 'a'为该部分提供常数：SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
9. DISTINCT优化
在大多数情况下，DISTINCT子句可以视为的特殊情况GROUP BY。例如，以下两个查询是等效的：
SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;
SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;
由于这种等效性，适用于GROUP BY查询的优化 也可以应用于带有DISTINCT子句的查询。
10. LIMIT优化
如果只需要结果集中指定的行数，则在查询子句中使用LIMIT，而不是获取整个结果集并丢弃多余的数据。
MySQL有时会优化包含LIMIT row_count子句和no HAVING子句的查询（HAVING是在结果集中筛选，无法优化）：
- 如果只选择有限制的几行，MySQL在某些情况下会使用索引，而通常情况下，它更愿意进行全表扫描。
- 如果
LIMIT与ORDER BY结合使用，当MySQL找到排序结果的第一个row_count行后立刻停止排序。 - 如果
LIMIT row_count与DISTINCT结合使用，当MySQL找到row_count行唯一记录后立刻停止。 LIMIT 0快速返回一个空集。这对于检查查询的有效性很有用。
11. 全表扫描优化
当MySQL使用全表扫描来解析查询时，EXPLAIN的输出显示type列中的所有内容。通常在以下情况下发生：
-
表太小了，执行表扫描比使用键查找更快。对于行数少于10行且行长度较短的表，这种情况很常见。
-
对于索引列，ON或WHERE子句中没有可用的限制。
-
将索引列与常量值进行比较，MySQL已经计算出（基于索引树）常量覆盖了表的很大一部分，并且认为表扫描会更快。
对于小表，表扫描通常是合适的，性能影响可以忽略不计。对于大型表，请尝试以下技术以避免优化器错误地选择表扫描：
-
ANALYZE TABLE
tbl_name。 -
使用FORCE INDEX，强制MySQL使用指定的索引。
SELECT * FROM t1, t2 FORCE INDEX (index_for_column) WHERE t1.col_name=t2.col_name; -
--max-seeks-for-key=1000,告诉MySQL在超过1000个key查找之后再放弃使用key扫描。
扫一扫
2021
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
