MySQL基于规则的SQL优化

条件优化

1. 移除不必要的括号

2. 常量传递

这个表达式和其他涉及列a的表达式使⽤AND连接起来时，可以将其他表达式中的a的值替换为5，⽐如这样：
a = 5 AND b > a
就可以被转换为：
a = 5 AND b > 5

3. 等值传递

a = b and b = c and c = 5
这个表达式可以被简化为：
a = 5 and b = 5 and c = 5

4. 移除没有用的条件

(a < 1 and b = b) OR (a = 6 OR 5 != 5)
很明显，b = b这个表达式永远为TRUE，5 != 5这个表达式永远为FALSE，所以简化后的表达式就是这样的：
(a < 1 and TRUE) OR (a = 6 OR FALSE)
可以继续被简化为
a < 1 OR a = 6

5. 表达式计算

在查询开始执⾏之前，如果表达式中只包含常量的话，它的值会被先计算出来，⽐如这个：
a = 5 + 1
因为5 + 1这个表达式只包含常量，所以就会被化简成：
a = 6
但是这⾥需要注意的是，如果某个列并不是以单独的形式作为表达式的操作数时，⽐如出现在函数中，出现在某个更复杂表达式中，就像这样：
ABS(a) > 5
或者：
-a < -8

6. HAVING子句和WHERE子句的合并

如果查询语句中没有出现诸如SUM、MAX等等的聚集函数以及GROUP BY⼦句，优化器就把HAVING⼦句和WHERE⼦句合并起来。

7. 常量表检测

查询的表中⼀条记录没有，或者只有⼀条记录。
使⽤主键等值匹配或者唯⼀⼆级索引列等值匹配作为搜索条件来查询某个表。

外连接消除

子查询优化

子查询语法

• SELECT子句中

  SELECT	(SELECT	m1	FROM	t1	LIMIT	1);
  

• 在FROM子句中

  SELECT	m,	n	FROM	(SELECT	m2	+	1	AS	m,	n2	AS	n	FROM	t2	WHERE	m2	>	2)	AS	t;
  

• 在WHERE或ON子句中的表达式

  SELECT	*	FROM	t1	WHERE	m1	IN	(SELECT	m2	FROM	t2);
  

• 在ORDER BY子句中
• GROUP BY子句中

按返回的结果集区分⼦查询

• 标量⼦查询
  那些只返回⼀个单⼀值的⼦查询称之为标量⼦查询

  SELECT	*	FROM	t1	WHERE	m1	=	(SELECT	MIN(m2)	FROM	t2);
  

• ⾏⼦查询
  就是返回⼀条记录的⼦查询，不过这条记录需要包含多个列（只包含⼀个列就成了标量⼦查询了）。

  SELECT	*	FROM	t1	WHERE	(m1,	n1)	=	(SELECT	m2,	n2	FROM	t2	LIMIT	1);
  

• 列⼦查询
  查询出⼀个列的数据喽，不过这个列的数据需要包含多条记录（只包含⼀条记录就成了标量⼦查询了）

  SELECT	*	FROM	t1	WHERE	m1	IN	(SELECT	m2	FROM	t2);
  

• 表⼦查询
  就是⼦查询的结果既包含很多条记录，⼜包含很多个列

  SELECT	*	FROM	t1	WHERE	(m1,	n1)	IN	(SELECT	m2,	n2	FROM	t2);
  

按与外层查询关系来区分⼦查询

• 不相关⼦查询
• 相关⼦查询

  SELECT	*	FROM	t1	WHERE	m1	IN	(SELECT	m2	FROM	t2	WHERE	n1	=	n2);
  

⼦查询在布尔表达式中的使⽤

• 使⽤=、>、<、>=、<=、<>、!=、<=>作为布尔表达式的操作符
• [NOT] IN/ANY/SOME/ALL⼦查询
• EXISTS⼦查询

⼦查询在MySQL中是怎么执⾏的

标量子查询、行子查询的执行方式

• SELECT⼦句中，我们前边说过的在查询列表中的⼦查询必须是标量⼦查询。

• ⼦查询使⽤=、>、<、>=、<=、<>、!=、<=>等操作符和某个操作数组成⼀个布尔表达式，这样的⼦查询必须是标量⼦查询或者⾏⼦查询。
  对于上述两种场景中的不相关标量⼦查询或者⾏⼦查询来说，它们的执⾏⽅式是简单的，⽐⽅说下边这个查询语句：

  SELECT	*	FROM	s1 WHERE	key1	=	(SELECT	common_field	FROM	s2	WHERE	key3	=	'a'	LIMIT	1);
  

• 先单独执⾏(SELECT common_field FROM s2 WHERE key3 = ‘a’ LIMIT 1)这个⼦查询。

• 然后在将上⼀步⼦查询得到的结果当作外层查询的参数再执⾏外层查询SELECT * FROM s1 WHERE key1 = …。

对于相关的标量⼦查询或者⾏⼦查询来说，⽐如下边这个查询：

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2	WHERE s1.key3 = s2.key3	LIMIT 1);

• 先从外层查询中获取⼀条记录，本例中也就是先从s1表中获取⼀条记录。
• 然后从上⼀步骤中获取的那条记录中找出⼦查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.key3列的值，然后执⾏⼦查询。
• 最后根据⼦查询的查询结果来检测外层查询WHERE⼦句的条件是否成⽴，如果成⽴，就把外层查询的那条记录加⼊到结果集，否则就丢弃。
• 再次执⾏第⼀步，获取第⼆条外层查询中的记录，依次类推

IN⼦查询优化

物化表的提出

对于不相关的IN⼦查询，⽐如这样：

SELECT	*	FROM	s1
   	WHERE	key1	IN	(SELECT	common_field	FROM	s2	WHERE	key3	=	'a');

不直接将不相关⼦查询的结果集当作外层查询的参数，⽽是将该结果集写⼊⼀个临时表⾥。

• 临时表的列就是⼦查询结果集中的列。
• 写⼊临时表的记录会被去重。
• ⼀般情况下⼦查询结果集不会⼤的离谱，所以会为它建⽴基于内存的使⽤Memory存储引擎的临时表，⽽且会为该表建⽴哈希索引。如果⼦查询的结果集⾮常⼤，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转⽽使⽤基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

物化表转连接

将子查询的结果集转化为雾化表(materialized_table)后，再将子查询转连接查询。

	SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

⼦查询转换为semi-join(半连接)

将之前的sql转化一下成半联动

	SELECT	s1.*	FROM	s1	SEMI	JOIN	s2 ON	s1.key1	=	s2.common_field WHERE	key3	=	'a';

半联动的实现方式：

• Table pullout （⼦查询中的表上拉）
  ⼦查询的查询列表处只有主键或者唯⼀索引列时，可以直接把⼦查询中的表上拉到外层查询的FROM⼦句中

###原始sql
SELECT	*	FROM	s1
   	WHERE	key2	IN	(SELECT	key2	FROM	s2	WHERE	key3	=	'a');
### 优化后
SELECT	s1.*	FROM	s1	INNER	JOIN	s2 ON	s1.key2	=	s2.key2 WHERE	s2.key3	=	'a';

semi-join的适⽤条件

• 该⼦查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON⼦句中出现。
• 外层查询也可以有其他的搜索条件，只不过和IN⼦查询的搜索条件必须使⽤AND连接起来。
• 该⼦查询必须是⼀个单⼀的查询，不能是由若⼲查询由UNION连接起来的形式。
• 该⼦查询不能包含GROUP BY或者HAVING语句或者聚集函数。

对于⼀些不能将⼦查询转位semi-join的情况，典型的⽐如下边这⼏种：

• 外层查询的WHERE条件中有其他搜索条件与IN⼦查询组成的布尔表达式使⽤OR连接起来
• 使⽤NOT IN⽽不是IN的情况
• 在SELECT⼦句中的IN⼦查询的情况
• ⼦查询中包含GROUP BY、HAVING或者聚集函数的情况
• ⼦查询中包含UNION的情况
• 对于不相关⼦查询来说，可以尝试把它们物化之后再参与查询
• 不管⼦查询是相关的还是不相关的，都可以把IN⼦查询尝试专为EXISTS⼦查询

双路排序与单路排序

单路排序：一次取出所有字段进行排序，内存不够用的时候就会使用磁盘
双路排序：取出排序字段进行排序，排序完成后再次回表查询所需要的其他字段。

受：sort_buffer_size和max_length_for_sort_data影响

groupby与orderby在索引使用上的区别？

• groupby没有过滤条件，也可以使用索引。order by必须要有过滤条件才能使用上索引。
• groupby

MySQL超大分页该怎么处理

• 分页语句解释

  select id, name from user limit offset,N; #在 使用的时候并不是跳过 offset 行，而是取offset +N 行，然后返回放弃前 offset，返回N行。

• 通过索引优化
  • 如果是自增可以select name from user where id > XX limit N;
  • 延迟关联 # select a.* from user a,(select id from user where LIMIT offset ,N) b where a.id = b.id;
  • 需要order by时
    • 增加筛选条件，避免全表排序
    • 减少select 字段
    • 优化相关参数避免filesort
• 可以使用缓存前几页的查询效率。