是什么造成 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 表速度更优？

F281M6Dh8DXpD1g2

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 来纠正它的选择