是什么造成 explain 中显示的 type 为 ALL？

id|select_type |table             |partitions|type  |possible_keys                           |key                  |key_len|ref                                         |rows|filtered|Extra                       |
--+------------+------------------+----------+------+----------------------------------------+---------------------+-------+--------------------------------------------+----+--------+----------------------------+
 1|PRIMARY     |<derived4>        |          |ALL   |                                        |                     |       |                                            |1092|   100.0|                            |
 1|PRIMARY     |<derived3>        |          |ref   |<auto_key0>                             |<auto_key0>          |152    |a.login                                     |   6|   100.0|                            |
 3|DERIVED     |t_position        |          |ALL   |t_position_login_IDX                    |                     |       |                                            |  65|   100.0|Using where; Using temporary|
 3|DERIVED     |underlying_manager|          |eq_ref|UnderlyingCode_UNIQUE                   |UnderlyingCode_UNIQUE|302    |ezytrs.t_position.symbol                    |   1|   100.0|Using where                 |
 3|DERIVED     |variety           |          |eq_ref|PRIMARY                                 |PRIMARY              |4      |中金_ylcms.underlying_manager.UnderlyingTypeId|   1|   100.0|Using index                 |
 4|DERIVED     |t_deal            |          |ALL   |t_deal_login_IDX,t_deal_action_IDX      |                     |       |                                            | 166|   65.06|Using where; Using temporary|
 4|DERIVED     |<derived5>        |          |ref   |<auto_key0>                             |<auto_key0>          |152    |ezytrs.t_deal.login                         |  10|   100.0|                            |
 5|DERIVED     |t_entrust         |          |ALL   |t_entrust_login_IDX,t_entrust_action_IDX|                     |       |                                            | 264|   65.53|Using where; Using temporary|
 5|DERIVED     |<subquery6>       |          |eq_ref|<auto_distinct_key>                     |<auto_distinct_key>  |5      |ezytrs.t_entrust.entrust                    |   1|   100.0|Using where; Not exists     |
 6|MATERIALIZED|t_deal            |          |index |t_deal_entrust_IDX                      |t_deal_entrust_IDX   |4      |                                            | 166|   100.0|Using index                 |

using

derived

Where

auto_key0

7 条回复

ecloud

2022-02-23 14:21:38 +08:00

建立了联合索引，也没发现任何改变，是不是 mysql 引擎认为建立 temp 表速度更优？

liprais

2022-02-23 14:44:48 +08:00

optimizer trace 呗

swcat

2022-02-23 14:59:00 +08:00

set session optimizer_trace="enabled=on", end_markers_in_json=on;

你的 sql

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30;

ecloud

2022-02-23 15:38:23 +08:00

@swcat {¶ "steps": [¶ ] /* steps */¶}
除了这个就没东西了...

ecloud

2022-02-23 15:48:55 +08:00

@swcat 加了几个参数，搞出来了，在看

ecloud

2022-02-23 16:15:14 +08:00

看了一下 optimizer ，其他几个都懂，引擎依据 cost 选择了 temp 表

唯独这个

···
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t_deal`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "t_deal_entrust_IDX",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 166,
"access_type": "scan",
"resulting_rows": 166,
"cost": 17.6,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 166,
"cost_for_plan": 17.6,
"sort_cost": 166,
"new_cost_for_plan": 183.6,
"chosen": true
}
···

为啥 useable 是 false ？ cost_for_plan 和 new_cost_for_plan 分别表示啥？

swcat

2022-02-25 10:17:05 +08:00

俗称就是禁用索引来提高性能
你觉得它选择错误可以使用 force index 来纠正它的选择