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

一个比较复杂的查询，explain 一共有 10 条记录

其中有几条的 type 为 ALL ，key 也为空

但是该字段的确建立了索引，并且查询条件是=

是不是 group by 里面的字段跟 where/join on 里面的字段要在一起建联合索引？

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 年 2 月 23 日

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

F281M6Dh8DXpD1g2

2022 年 2 月 23 日

optimizer trace 呗

swcat

2022 年 2 月 23 日

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

你的 sql

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30;

ecloud

2022 年 2 月 23 日

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

ecloud

2022 年 2 月 23 日

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

ecloud

2022 年 2 月 23 日

看了一下 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 年 2 月 25 日

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

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具