浅析MySQL查询优化器的局限性

5.Mysql查询优化器的局限性

1.关联子查询

1. 一旦使用DISTINCT和GROUP BY，那么在查询的执行过程中，通常需要产生临时中间表。

2.UNION的限制

1. 有时，mysql无法将限制条件从外层下推到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上
2. 如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句分别使用这些子句。

3.索引合并优化

1. 当WHERE子句中包含多个复杂条件的时候，mysql能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

4.等值传递

1. 通常，因为各个表新增了过滤条件，优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。

5.并行执行

1. mysql无法利用多核特性来并行执行查询。

6.哈希关联

1. mysql并不支持哈希关联，mysql的所有关联都是嵌套循环关联。不过可以通过建立一个哈希索引来曲线地实现哈希关联。如果使用的是Memory存储引擎，则索引都是哈希索引。

7.松散索引扫描

1. mysql不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，索引扫描需要先定义一个起点和终点，即使需要的数据只是这段索引中很少数的几个，仍需要扫描这段索引中每一个条目。

8.最大值和最小值优化

1. 如果查询的列没有主键时，即使查询的是最大或最小值，仍会进行一次全表扫描，解决办法是使用LIMIT。

9.在同一个表上查询和更新

1. mysql不允许在同一张表同时进行查询和更新。
2. 可以通过使用生成表的形式来绕过上面的限制，因为mysql只会把这个表当做一个临时表来处理。

6.查询优化器的提示

1. 如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示来控制最终的执行计划。
2. HIGH_PRIORITY和LOW_PRIORITY
   1、这个提示告诉mysql，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些
   2、HIGH_PRIORITY用于SELECT语句的时候，会将此SELECT语句重新调度到所有正在等待表锁以便修改数据的语句之前。实际上是将其放在表的队列的最前面，而不是按照常规顺序等待。还可以用于INSERT语句，其效果只是简单地抵消了全局LOW_PRIORITY设置堆该语句的影响。
   3、LOW_PRIORITY则会让语句一直处于等待状态，只要队列中还有需要访问同一个表的语句。这个提示在SELECT，INSERT，UPDATE，DELETE语句中都可以使用。
   4、这2个提示只对使用表锁的存储引擎有效。即使是在MyISAM中使用也要注意，因为这2个提示会导致并发插入被禁用，可能会严重降低性能。 它们只是简单地控制了访问某个数据表的队列顺序。
3. DELAYED
   这个提示对INSERT和REPLACE有效。会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入。日志系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成IO的应用。并不是所有的存储引擎都支持这样的做法，而且还会导致含住LAST_INSERT_ID()无法正常工作。
4. STRAIGHT_JOIN
   1、这个提示可以放置在SELECT语句的SELECT关键字之后(让查询中所有的表按照在语句中出现的顺序进行关联)，也可以放置在任何2个关联表的名字之间(固定其前后2个表的关联顺序)。
   2、可以以先使用EXPLAIN语句来查看优化器选择的关联顺序，然后使用该提示来重写查询，再看看它的关联顺序。
5. SQL_SMALL_RESULT和SQL_BIG_RESULT
   1、这2个提示只对SELECT语句有效。它们告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。如果是SQL_BIG_RESULT，则告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。
6. SQL_BUFFER_RESULT
   这个提示告诉优化器将查询结果放入到一个临时表，然后尽可能快地释放表锁。这和由客户端缓存结果不同。当你没法使用客户端缓存的时候，使用服务器端的缓存通常很有效。带来的好处是无须再客户端上消耗太多的内存，还可以尽可能快地释放对应的表锁。
7. SQL_CACHE和SQL_NO_CACHE
   这个提示告诉mysql这个结果集是否应该缓存在查询缓存中。
8. SQL_CALC_FOUND_ROWS
   它不会告诉优化器任何关于执行计划的东西。它会让mysql返回的结果集包含更多的信息。查询中加上该提示会计算除去LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回LIMIT要求的结果集。(不使用)
9. FOR UPDATE和LOCK IN SHARE MODE
   这2个提示主要控制SELECT语句的锁机制。但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。
   唯一内置的支持这2个提示的引擎就是InnoDB。这2个提示会让某些优化无法正常使用，例如索引覆盖扫描。InnoDB不能在不访问主键的情况下排他地锁定行，因为行的版本信息保存在主键中。
   这2个提示经常被滥用，很容易造成服务器的锁争用问题。
10. USE INDEX、IGNORE INDEX、FORCE INDEX
    1、这几个提示会告诉优化器使用或者不使用哪些索引来查询记录。在5.1版本以后可以新增选项FOR ORDER BY和FOR GROUP BY来指定是否对排序和分组有效。
    2、FORCE INDEX和USE INDEX基本相同，除了一点：FOECE INDEX会告诉优化器全表扫描的成本会远远高于索引扫描，哪怕实际上该索引用处不大。当发现优化器选择了错误的索引，可以使用该提示。
11. optimizer_search_depth
    这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于Statistics状态，那么可以考虑调低此参数。
12. optimizer_prune_level
    默认打开，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。
13. optimizer_switch
    这个变量包含了一些开启/关闭优化器特性的标志位