MySQL的万能嵌套循环并不是对每种查询都是最优的。不过MySQL查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让MySQL高效的完成工作。

1 关联子查询
MySQL的子查询实现的非常糟糕。最糟糕的一类查询时where条件中包含in()的子查询语句。因为MySQL对in()列表中的选项有专门的优化策略，一般会认为MySQL会先执行子查询返回所有in()子句中查询的值。一般来说，in()列表查询速度很快，所以我们会以为sql会这样执行

select * from tast_user where id in (select id from user where name like '王%');
我们以为这个sql会解析成下面的形式
select * from tast_user where id in (1,2,3,4,5);
实际上MySQL是这样解析的
select * from tast_user where exists
(select id from user where name like '王%' and tast_user.id = user.id);
MySQL会将相关的外层表压缩到子查询中，它认为这样可以更高效的查找到数据行。

这时候由于子查询用到了外部表中的id字段所以子查询无法先执行。通过explin可以看到，MySQL先选择对tast_user表进行全表扫描，然后根据返回的id逐个执行子查询。如果外层是一个很大的表，那么这个查询的性能会非常糟糕。当然我们可以优化这个表的写法：

select tast_user.* from tast_user inner join user using(tast_user.id) where user.name like '王%'
另一个优化的办法就是使用group_concat()在in中构造一个由逗号分隔的列表。有时这比上面使用关联改写更快。因为使用in()加子查询，性能通常会非常糟糕。所以通常建议使用exists()等效的改写查询来获取更好的效率。

如何书写更好的子查询就不在介绍了，因为现在基本都要求拆分成单表查询了，有兴趣的话可以自行去了解下。

2 UNION的限制
有时，MySQL无法将限制条件从外层下推导内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序在合并结果集的话，就需要在union的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后在取前20条，那么MySQL会将两个表都存放到一个临时表中，然后在去除前20行。

(select first_name,last_name from actor order by last_name) union all
(select first_name,last_name from customer order by  last_name) limit 20;
这条查询会将actor中的记录和customer表中的记录全部取出来放在一个临时表中，然后在取前20条，可以通过在两个子查询中分别加上一个limit 20来减少临时表中的数据。

现在中间的临时表只会包含40条记录了，出来性能考虑之外，这里还需要注意一点：从临时表中取出数据的顺序并不是一定，所以如果想获得正确的顺序，还需要在加上一个全局的order by操作

3 索引合并优化
前面章节中已经提到过，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

4 等值传递
某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的in()列表，而MySQL优化器发现存在where/on或using的子句，将这个列表的值和顶一个表的某个列相关联。

那么优化器会将in()列表都赋值应用到关联的各个表中。通常，因为各个表新增了过滤条件，优化器可以更搞笑的从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。

5 并行执行
MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库鞥能够提供这个特性，但MySQL做不到。这里特别指出是想提醒大家不要花时间去尝试寻找并行执行查询的方法。

6 哈希关联
在20113年MySQL并不执行哈希关联，MySQL的所有关联都是嵌套循环关联。不过可以通过建立一个哈希索引来曲线实现哈希关联如果使用的是Memory引擎，则索引都是哈希索引，所以关联的时候也类似于哈希关联。另外MariaDB已经实现了哈希关联。

7 松散索引扫描
由于历史原因，MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL的索引扫描需要先定义一个起点和重点，即使需要的数据只是这段索引中很少的几个，MySQL仍需要扫描着段索引中每个条目。

例：现有索引（a,b）

select * from table where b between 2 and 3;

因为索引的签到字段是a，但是在查询中只指定了字段b，MySQL无法使用这个索引，从而只能通过全表扫描找到匹配的行。、

MySQL全表扫描：



了解索引的无力结构的话，不难发现还可以有一个更快的办法执行上面的查询。索引的无力结构不是存储引擎的API使得可以先扫描a列第一个值对应的b列的范围，然后在跳到a列第二个不同值扫描对应的b列的范围



这时就无需在使用where子句过滤，因为松散索引扫描已经跳过了所有不需要的记录。

上面是一个简单的例子，处理松散索引扫描，新增一个合适的索引当然也可以优化上述查询。但对于某些场景，增加索引是没用的，例如，对于第一个索隐裂是范围条件，第二个索隐裂是等值提交建查询，靠增加索引就无法解决问题。

MySQL5.6之后，关于松散索引扫描的一些限制将会通过索引条件吓退的分行是解决。

8 最大值和最小值优化
对于MIN()和MAX()查询，MySQL的优化做的并不好，例：

select min(actor_id) from actor where first_name = 'wang'
因为在first_name字段上并没有索引，因此MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描，那么理论上，当MySQL读到第一个太满足条件的记录的时候就是我们需要的最小值了，因为主键是严哥按照actor_id字段的大小排序的。但是MySSQL这时只会做全表扫描，我们可以通过show status的全表扫描计数器来验证这一点。一个区县优化办法就是移除min()函数，然后使用limit 1来查询。

这个策略可以让MySQL扫描尽可能少的记录数。这个例子告诉我们有时候为了获得更高的性能，就得放弃一些原则。

9 在同一个表上查询和更新
MySQL不允许对同一张表同时进行查询和更新。这并不是优化器的限制，如果清楚MySQL是如何执行查询的，就可以避免这种情况。例：

update table set cnt = (select count(*) from table as tb where tb.type = table.type);
这个sql虽然符合标准单无法执行，我们可以通过使用生成表的形式绕过上面的限制，因为MySQL只会把这个表当做一个临时表来处理。

update table inner join
(select type,count(*) as cnt from table group by type) as tb using(type)
set table.cnt = tb.cnt;
实际上这执行了两个查询：一个是子查询中的select语句，另一个是夺标关联update，只是关联的表时一个临时表。子查询会在update语句打开表之前就完成，所以会正常执行。

10 查询优化器的提示（hint）
如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。下面将列举一些常见的提示，并简单的给出什么时候使用该提示。通过在查询中加入响应的提示，就可以控制该查询的执行计划。

① HIGH_PRIORITY 和 LOW_PRIORITY

这个提示告诉MySQL，当多个语句同时访问某一表的时候，哪些语句的优先级相对高些，哪些语句优先级相对低些。

HIGH_PRIORITY用于select语句的时候，MySQL会将此select语句重新调度到所有正在表锁以便修改数据的语句之前。实际上MySQL是将其放在表的队列的最前面，而不是按照常规顺序等待。HIGH_PRIORITY还可以用于insert语句，其效果只是简单的体校了全局LOW_PRIORITY设置对该语句的影响。

LOW_PRIORITY则正好相反，它会让语句一直处于等待状态，只要在队列中有对同一表的访问，就会一直在队尾等待。在CRUD语句中都可以使用。

这两个提示只对使用表锁的存储引擎有效，不能在InnoDB或其他有细粒度所机制和并发控制的引擎中使用。在MyISAM中也要慎用，因为这两个提示会导致并发插入被禁用，可能会严重降低性能。

HIGH_PRIORITY和LOW_PRIORITY其实只是简单的控制了MySQL访问某个数据表的队列顺序。

② DELAYED

这个提示对insert和replace有效。MySSQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入缓冲区，然后在表空闲时批量将数据写入。日志型系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成I/O的应用。这个用法有一些限制。并不是所有的存储引擎都支持，并且该提示会导致函数last_insert_id()无法正常工作。

③ STRAIGHT_JOIN

这个提示可以防止在select语句的select关键字之后，也可以防止在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。

当MySQL没能选择正确的关联顺序的时候，或者由于可能的顺序太多导致MySQL无法评估所有的关联顺序的时候，STRAIGHT_JOIN都会很有用，在MySQL可能会发给大量时间在statistics状态时，加上这个提示则会大大减少优化器的搜索空间

④ SQL_SMALLRESULT和SQL_BIG_RESULT

这个两个提示只对select语句有效。他们告诉优化器对group by或者distinct查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。如果是SQL_BIG_RESULT，则会告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。

⑤ SQL_BUFFER_RESULT

这个提示告诉优化器将查询结果放入一个临时表，然后尽可能快速释放表锁。这和前面提到的由客户端缓存结果不同。当你无法使用客户端缓存的时候，使用服务器端的缓存通常很有效。好处是无需在客户端上消耗过多内存，还能尽快释放表锁。代价是服务器端将需要更多的内存。

⑥ SQL_CACHE和SQL_NO_CACHE

这个提示告诉MySQL这个结果集是否应该放入查询缓存中。

⑦ SQL_CALC_FOUND_ROWS

严哥来说，这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西。它会让MySQL返回的结果集包含更多的信息。查询中加上该提示MySQL会计算limit子句之后这个查询要返回的结果集总数，而实际上值返回limit要求的结果集。可以通过函数found_row()获得这个值。慎用，后面会说明为什么。

⑧ FOR UPDATE和LOCK IN SHARE MODE

这也不是真正的优化器提示。这两个提示主要控制select语句的锁机制，但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。对于insert/select语句是不需要这两个提示的因为5.0以后会默认给这些记录加上读锁。

唯一内置的支持这两个提示的引擎就是InnoDB，可以禁用该默认行为。另外需要记住的是，这两个提示会让某些优化无法正常使用，例如索引覆盖扫描。InnoDB不能在不访问主键的情况下排他的锁定行，因为行的版本信息保存在主键中。

如果这两个提示被经常滥用，很容易早晨服务器的锁争用问题。

⑨ USE INDEX、IGNORE INDEX和FORCE INDEX

这几个提示会告诉优化器使用或者不使用那些索引来查询记录。

在5.0版本以后新增了一些参数来控制优化器的行为：

① optimizer_search_depth

这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于statistics状态，那么可以考虑调低此参数。

② optimizer_prune_level

该参数默认是打开的，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。

③optimizer_switch

这个变量包含了一些开启/关闭优化器特性的标志位。

前面两个参数时用来控制优化器可以走的一些捷径。这些捷径可以让优化器在处理非常复杂的SQL语句时，可以更高效，但也可能让优化器错过一些真正最优的执行计划，所以慎用。

修改优化器提示可能在MySQL更新后让新版的优化策略失效，所以一定要谨慎
---------------------
作者：yongqi_wang
来源：CSDN
原文：https://blog.csdn.net/yongqi_wang/article/details/86527819
版权声明：本文为博主原创文章，转载请附上博文链接！

MySQL的万能嵌套循环并不是对每种查询都是最优的。不过MySQL查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让MySQL高效的完成工作。

1 关联子查询
MySQL的子查询实现的非常糟糕。最糟糕的一类查询时where条件中包含in()的子查询语句。因为MySQL对in()列表中的选项有专门的优化策略，一般会认为MySQL会先执行子查询返回所有in()子句中查询的值。一般来说，in()列表查询速度很快，所以我们会以为sql会这样执行

select * from tast_user where id in (select id from user where name like '王%');
我们以为这个sql会解析成下面的形式
select * from tast_user where id in (1,2,3,4,5);
实际上MySQL是这样解析的
select * from tast_user where exists
(select id from user where name like '王%' and tast_user.id = user.id);
MySQL会将相关的外层表压缩到子查询中，它认为这样可以更高效的查找到数据行。

这时候由于子查询用到了外部表中的id字段所以子查询无法先执行。通过explin可以看到，MySQL先选择对tast_user表进行全表扫描，然后根据返回的id逐个执行子查询。如果外层是一个很大的表，那么这个查询的性能会非常糟糕。当然我们可以优化这个表的写法：

select tast_user.* from tast_user inner join user using(tast_user.id) where user.name like '王%'
另一个优化的办法就是使用group_concat()在in中构造一个由逗号分隔的列表。有时这比上面使用关联改写更快。因为使用in()加子查询，性能通常会非常糟糕。所以通常建议使用exists()等效的改写查询来获取更好的效率。

如何书写更好的子查询就不在介绍了，因为现在基本都要求拆分成单表查询了，有兴趣的话可以自行去了解下。

2 UNION的限制
有时，MySQL无法将限制条件从外层下推导内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序在合并结果集的话，就需要在union的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后在取前20条，那么MySQL会将两个表都存放到一个临时表中，然后在去除前20行。

(select first_name,last_name from actor order by last_name) union all
(select first_name,last_name from customer order by  last_name) limit 20;
这条查询会将actor中的记录和customer表中的记录全部取出来放在一个临时表中，然后在取前20条，可以通过在两个子查询中分别加上一个limit 20来减少临时表中的数据。

现在中间的临时表只会包含40条记录了，出来性能考虑之外，这里还需要注意一点：从临时表中取出数据的顺序并不是一定，所以如果想获得正确的顺序，还需要在加上一个全局的order by操作

3 索引合并优化
前面章节中已经提到过，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

4 等值传递
某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的in()列表，而MySQL优化器发现存在where/on或using的子句，将这个列表的值和顶一个表的某个列相关联。

那么优化器会将in()列表都赋值应用到关联的各个表中。通常，因为各个表新增了过滤条件，优化器可以更搞笑的从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。

5 并行执行
MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库鞥能够提供这个特性，但MySQL做不到。这里特别指出是想提醒大家不要花时间去尝试寻找并行执行查询的方法。

6 哈希关联
在20113年MySQL并不执行哈希关联，MySQL的所有关联都是嵌套循环关联。不过可以通过建立一个哈希索引来曲线实现哈希关联如果使用的是Memory引擎，则索引都是哈希索引，所以关联的时候也类似于哈希关联。另外MariaDB已经实现了哈希关联。

7 松散索引扫描
由于历史原因，MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL的索引扫描需要先定义一个起点和重点，即使需要的数据只是这段索引中很少的几个，MySQL仍需要扫描着段索引中每个条目。

例：现有索引（a,b）

select * from table where b between 2 and 3;

因为索引的签到字段是a，但是在查询中只指定了字段b，MySQL无法使用这个索引，从而只能通过全表扫描找到匹配的行。、

MySQL全表扫描：



了解索引的无力结构的话，不难发现还可以有一个更快的办法执行上面的查询。索引的无力结构不是存储引擎的API使得可以先扫描a列第一个值对应的b列的范围，然后在跳到a列第二个不同值扫描对应的b列的范围



这时就无需在使用where子句过滤，因为松散索引扫描已经跳过了所有不需要的记录。

上面是一个简单的例子，处理松散索引扫描，新增一个合适的索引当然也可以优化上述查询。但对于某些场景，增加索引是没用的，例如，对于第一个索隐裂是范围条件，第二个索隐裂是等值提交建查询，靠增加索引就无法解决问题。

MySQL5.6之后，关于松散索引扫描的一些限制将会通过索引条件吓退的分行是解决。

8 最大值和最小值优化
对于MIN()和MAX()查询，MySQL的优化做的并不好，例：

select min(actor_id) from actor where first_name = 'wang'
因为在first_name字段上并没有索引，因此MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描，那么理论上，当MySQL读到第一个太满足条件的记录的时候就是我们需要的最小值了，因为主键是严哥按照actor_id字段的大小排序的。但是MySSQL这时只会做全表扫描，我们可以通过show status的全表扫描计数器来验证这一点。一个区县优化办法就是移除min()函数，然后使用limit 1来查询。

这个策略可以让MySQL扫描尽可能少的记录数。这个例子告诉我们有时候为了获得更高的性能，就得放弃一些原则。

9 在同一个表上查询和更新
MySQL不允许对同一张表同时进行查询和更新。这并不是优化器的限制，如果清楚MySQL是如何执行查询的，就可以避免这种情况。例：

update table set cnt = (select count(*) from table as tb where tb.type = table.type);
这个sql虽然符合标准单无法执行，我们可以通过使用生成表的形式绕过上面的限制，因为MySQL只会把这个表当做一个临时表来处理。

update table inner join
(select type,count(*) as cnt from table group by type) as tb using(type)
set table.cnt = tb.cnt;
实际上这执行了两个查询：一个是子查询中的select语句，另一个是夺标关联update，只是关联的表时一个临时表。子查询会在update语句打开表之前就完成，所以会正常执行。

10 查询优化器的提示（hint）
如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。下面将列举一些常见的提示，并简单的给出什么时候使用该提示。通过在查询中加入响应的提示，就可以控制该查询的执行计划。

① HIGH_PRIORITY 和 LOW_PRIORITY

这个提示告诉MySQL，当多个语句同时访问某一表的时候，哪些语句的优先级相对高些，哪些语句优先级相对低些。

HIGH_PRIORITY用于select语句的时候，MySQL会将此select语句重新调度到所有正在表锁以便修改数据的语句之前。实际上MySQL是将其放在表的队列的最前面，而不是按照常规顺序等待。HIGH_PRIORITY还可以用于insert语句，其效果只是简单的体校了全局LOW_PRIORITY设置对该语句的影响。

LOW_PRIORITY则正好相反，它会让语句一直处于等待状态，只要在队列中有对同一表的访问，就会一直在队尾等待。在CRUD语句中都可以使用。

这两个提示只对使用表锁的存储引擎有效，不能在InnoDB或其他有细粒度所机制和并发控制的引擎中使用。在MyISAM中也要慎用，因为这两个提示会导致并发插入被禁用，可能会严重降低性能。

HIGH_PRIORITY和LOW_PRIORITY其实只是简单的控制了MySQL访问某个数据表的队列顺序。

② DELAYED

这个提示对insert和replace有效。MySSQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入缓冲区，然后在表空闲时批量将数据写入。日志型系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成I/O的应用。这个用法有一些限制。并不是所有的存储引擎都支持，并且该提示会导致函数last_insert_id()无法正常工作。

③ STRAIGHT_JOIN

这个提示可以防止在select语句的select关键字之后，也可以防止在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。

当MySQL没能选择正确的关联顺序的时候，或者由于可能的顺序太多导致MySQL无法评估所有的关联顺序的时候，STRAIGHT_JOIN都会很有用，在MySQL可能会发给大量时间在statistics状态时，加上这个提示则会大大减少优化器的搜索空间

④ SQL_SMALLRESULT和SQL_BIG_RESULT

这个两个提示只对select语句有效。他们告诉优化器对group by或者distinct查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。如果是SQL_BIG_RESULT，则会告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。

⑤ SQL_BUFFER_RESULT

这个提示告诉优化器将查询结果放入一个临时表，然后尽可能快速释放表锁。这和前面提到的由客户端缓存结果不同。当你无法使用客户端缓存的时候，使用服务器端的缓存通常很有效。好处是无需在客户端上消耗过多内存，还能尽快释放表锁。代价是服务器端将需要更多的内存。

⑥ SQL_CACHE和SQL_NO_CACHE

这个提示告诉MySQL这个结果集是否应该放入查询缓存中。

⑦ SQL_CALC_FOUND_ROWS

严哥来说，这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西。它会让MySQL返回的结果集包含更多的信息。查询中加上该提示MySQL会计算limit子句之后这个查询要返回的结果集总数，而实际上值返回limit要求的结果集。可以通过函数found_row()获得这个值。慎用，后面会说明为什么。

⑧ FOR UPDATE和LOCK IN SHARE MODE

这也不是真正的优化器提示。这两个提示主要控制select语句的锁机制，但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。对于insert/select语句是不需要这两个提示的因为5.0以后会默认给这些记录加上读锁。

唯一内置的支持这两个提示的引擎就是InnoDB，可以禁用该默认行为。另外需要记住的是，这两个提示会让某些优化无法正常使用，例如索引覆盖扫描。InnoDB不能在不访问主键的情况下排他的锁定行，因为行的版本信息保存在主键中。

如果这两个提示被经常滥用，很容易早晨服务器的锁争用问题。

⑨ USE INDEX、IGNORE INDEX和FORCE INDEX

这几个提示会告诉优化器使用或者不使用那些索引来查询记录。

在5.0版本以后新增了一些参数来控制优化器的行为：

① optimizer_search_depth

这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于statistics状态，那么可以考虑调低此参数。

② optimizer_prune_level

该参数默认是打开的，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。

③optimizer_switch

这个变量包含了一些开启/关闭优化器特性的标志位。

前面两个参数时用来控制优化器可以走的一些捷径。这些捷径可以让优化器在处理非常复杂的SQL语句时，可以更高效，但也可能让优化器错过一些真正最优的执行计划，所以慎用。

修改优化器提示可能在MySQL更新后让新版的优化策略失效，所以一定要谨慎
---------------------
作者：yongqi_wang
来源：CSDN
原文：https://blog.csdn.net/yongqi_wang/article/details/86527819
版权声明：本文为博主原创文章，转载请附上博文链接！