mysql - MySql 查询在控制台中优化，但在 Node/Python 客户端中未优化

Question

我有一个相当复杂的查询，使用许多连接和一个not in子句。我已经使用 MySql 控制台对这个查询进行了优化，我确信DEPENDEND SUBQUERY执行计划中没有。但是，令我惊讶的是，当我使用 Node 和 Python 客户端在同一个 MySql 实例中执行完全相同的查询时，查询速度要慢得多。explain通过这样的客户使用声明，DEPENDENT SUBQUERY出现了两个。直接在控制台中发出相同的查询，而不是通过此类客户端，为什么会成功优化相同的查询？我该如何调试这个问题？

编辑：请求的详细信息。

SELECT @@optimizer_switch来自控制台和 python 客户端 的输出是相同的：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

MySql 控制台的执行计划：

+----+-------------+-------------+------------+------+---------------------------------------------------------------------------+---------------------+---------+------+---------+----------+----------------------------------------------------+
| id | select_type | table       | partitions | type | possible_keys                                                             | key                 | key_len | ref  | rows    | filtered | Extra                                              |
+----+-------------+-------------+------------+------+---------------------------------------------------------------------------+---------------------+---------+------+---------+----------+----------------------------------------------------+
|  1 | PRIMARY     | c           | NULL       | ALL  | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL                | NULL    | NULL | 1912779 |     5.00 | Using where                                        |
|  1 | PRIMARY     | <derived2>  | NULL       | ALL  | NULL                                                                      | NULL                | NULL    | NULL |  956388 |     1.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | c           | NULL       | ALL  | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL                | NULL    | NULL | 1912779 |    50.00 | Using where; Using temporary; Using filesort       |
|  4 | UNION       | c           | NULL       | ALL  | NULL                                                                      | NULL                | NULL    | NULL | 1912779 |    10.00 | Using where                                        |
|  4 | UNION       | <derived7>  | NULL       | ALL  | NULL                                                                      | NULL                | NULL    | NULL |  956388 |     1.00 | Using where; Using join buffer (Block Nested Loop) |
|  4 | UNION       | a           | NULL       | ref  | idx_accounts_domain                                                       | idx_accounts_domain | 202     | func |      26 |   100.00 | Using where; Using index                           |
|  9 | SUBQUERY    | c           | NULL       | ALL  | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL                | NULL    | NULL | 1912779 |     5.00 | Using where                                        |
|  9 | SUBQUERY    | <derived11> | NULL       | ALL  | NULL                                                                      | NULL                | NULL    | NULL |  956388 |     1.00 | Using where; Using join buffer (Block Nested Loop) |
| 11 | DERIVED     | c           | NULL       | ALL  | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL                | NULL    | NULL | 1912779 |    50.00 | Using where; Using temporary; Using filesort       |
|  7 | DERIVED     | c           | NULL       | ALL  | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL                | NULL    | NULL | 1912779 |    50.00 | Using where; Using temporary; Using filesort       |
+----+-------------+-------------+------------+------+---------------------------------------------------------------------------+---------------------+---------+------+---------+----------+----------------------------------------------------+

执行计划，来自 python 客户端：

[
  (1, 'PRIMARY', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 5.0, 'Using where'),
  (1, 'PRIMARY', '<derived2>', None, 'ALL', None, None, None, None, 956388, 1.0, 'Using where; Using join buffer (Block Nested Loop)'),
  (2, 'DERIVED', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 50.0, 'Using where; Using temporary; Using filesort'),
  (4, 'UNION', 'c', None, 'ALL', None, None, None, None, 1912779, 10.0, 'Using where'),
  (4, 'UNION', '<derived7>', None, 'ALL', None, None, None, None, 956388, 1.0, 'Using where; Using join buffer (Block Nested Loop)'),
  (4, 'UNION', 'a', None, 'ref', 'idx_report_settings_domain', 'idx_report_settings_domain', '202', 'func', 26, 100.0, 'Using where; Using index'),
  (9, 'DEPENDENT SUBQUERY', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 5.0, 'Using where'),
  (9, 'DEPENDENT SUBQUERY', '<derived11>', None, 'ALL', None, None, None, None, 956388, 1.0, 'Using where; Using join buffer (Block Nested Loop)'),
  (11, 'DERIVED', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 50.0, 'Using where; Using temporary; Using filesort'),
  (7, 'DERIVED', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 50.0, 'Using where; Using temporary; Using filesort')
]

查询的形式为：

(
  <<account_report_settings>>
)
union all
(
  select *
  from (
    <<inherited_report_settings>>
  ) as inherited
  where inherited.account not in (
    select direct.account
    from (
      <<account_report_settings>>
    ) as direct
  )
)

where<<account_report_settings>>和<<inherited_report_settings>>是一些相当长的子查询。如有必要，我可以提供完整的查询，但它非常复杂，我不知道它是否会有很大帮助。

标签： mysqlmysql-pythonnode-mysql