optimizer

动不动就所谓的 sql 语句优化，搞得好像不会点优化没法活似的。 其实吧，可能连优化器的配置都没看过，优化啥啊。

查看 optimizer 配置

mysql> select @@optimizer_switch\G;

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

1 row in set (0.00 sec)

优化器配置列表

1. index_merge=on,
2. index_merge_union=on,
3. index_merge_sort_union=on,
4. index_merge_intersection=on,
5. engine_condition_pushdown=on,
6. index_condition_pushdown=on,
7. mrr=on,
8. mrr_cost_based=on,
9. block_nested_loop=on,
10. batched_key_access=off,
11. materialization=on,
12. semijoin=on,
13. loosescan=on,
14. firstmatch=on,
15. duplicateweedout=on,
16. subquery_materialization_cost_based=on,
17. use_index_extensions=on,
18. condition_fanout_filter=on,
19. derived_merge=on,
20. use_invisible_indexes=off,
21. skip_scan=on,hash_join=on,
22. subquery_to_derived=off,
23. prefer_ordering_index=on,
24. hypergraph_optimizer=off,
25. derived_condition_pushdown=on

Refs

1. zhuanlan.zhihu.com/p/110154066
2. www.mysqltutorial.org/mysql-index…