为什么有时候用了 join 比没用 join 的还快,MySQL 做了什么优化
register InnoDB 16w 数据 mer_no 有索引 mcc_cd 没索引 mccs MyISAM 300 条数据 code 没索引
-- 0.25 秒以上 SELECT * FROM register WHERE mer_no = '10373914029XXXXXXXXX' limit 1
-- 0.18 秒
SELECT * FROM register mr LEFT JOIN mccs mc ON mc.code
= mr.mcc_cd WHERE mr.mer_no = '10373914029XXXXXXXXX' limit 1
1
dust0522 OP register InnoDB 16w 数据 mer_no 有索引
mcc_cd 没索引 mccs MyISAM 300 条数据 code 没索引 -- 0.25 秒以上 SELECT * FROM register WHERE mer_no = '10373914029XXXXXXXXX' limit 1 -- 0.18 秒 SELECT * FROM register mr LEFT JOIN mccs mc ON mc.code = mr.mcc_cd WHERE mr.mer_no = '10373914029XXXXXXXXX' limit 1 |
2
dust0522 OP 表:register InnoDB 16w 条数据 mer_no 有索引 、mcc_cd 没索引
表:mccs MyISAM 300 条数据 code 没索引 -- 0.25 秒以上 SELECT * FROM register WHERE mer_no = '10373914029XXXXXXXXX' limit 1 -- 0.18 秒 SELECT * FROM register mr LEFT JOIN mccs mc ON mc.code = mr.mcc_cd WHERE mr.mer_no = '10373914029XXXXXXXXX' limit 1 |
3
weijancc 2023-03-17 10:45:49 +08:00
mer_no 有索引那应该是第一条查询更快才对, 会不会是网络问题呢
|
4
Braisdom 2023-03-17 10:46:50 +08:00
这难道不是 MySQL 的 Bug 吗?
|
5
opengps 2023-03-17 10:49:12 +08:00
0.18 与 0.25 似乎没啥可比性,有没有更显著的数字展示下?查询提速能考虑的无非就是索引合理性
|
6
opengps 2023-03-17 10:51:35 +08:00
重启下 mysql 服务,只记录首次查询语句耗时(释放掉索引的缓存,排除下这个因素)
|
7
xsonglive491 2023-03-17 10:51:41 +08:00
你直接 explain format='json' 一下看看怎么回事就好了,可以看到查询的顺序
|
8
rekulas 2023-03-17 10:53:58 +08:00
索引列 limit 1 百毫秒级,索引已经出问题了,别纠结快慢问题了,删除索引重建吧。。。
|
9
BiChengfei 2023-03-17 10:56:03 +08:00
用的 navicat 吗,单位秒太大了,不容易分析,看看有没有毫秒的时间显示,我觉得有可能是缓存导致的,使用 select sql_no_cache xxx 试试,还是不行的话,用 shell 客户端试试
个人分析,经过 MySQL 查询优化器的处理,第一种直接回表 register 查询出来,第二种需要先 register 回表,再去 join mccs 表,路径长了一步,所以第一种绝对快。第二种应该是用了第一种的缓存,再加上 json mccs 表,因为 mccs 表数据很少,所以比第一种快了一点 |
10
dust0522 OP 我用 show profiles; 查出来的两个查询的时间差距,第二个查询没用 limit 都比第一个快,只是觉得很奇怪。
10 0.00037925 SELECT mr.* from register mr WHERE mer_no = '10373914029XXXXXXXXX' limit 1 17 0.0005085 SELECT mr2.* FROM register mr2 LEFT JOIN mccs mc ON mc.`code` = mr2.mcc_cd WHERE mr2.mer_no = '10373914029XXXXXXXXX' @BiChengfei @rekulas @opengps @weijancc |
11
dust0522 OP {
"query_block": { "select_id": 1, "cost_info": { "query_cost": "1.20" }, "table": { "table_name": "mr", "access_type": "ref", "possible_keys": [ "mer_no" ], "key": "mer_no", "used_key_parts": [ "mer_no" ], "key_length": "83", "ref": [ "const" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "1.00", "eval_cost": "0.20", "prefix_cost": "1.20", "data_read_per_join": "10K" }, "used_columns": [ * ] } } } |
12
dust0522 OP {
"query_block": { "select_id": 1, "cost_info": { "query_cost": "52.94" }, "nested_loop": [ { "table": { "table_name": "mr2", "access_type": "ref", "possible_keys": [ "mer_no" ], "key": "mer_no", "used_key_parts": [ "mer_no" ], "key_length": "83", "ref": [ "const" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "1.00", "eval_cost": "0.20", "prefix_cost": "1.20", "data_read_per_join": "10K" }, "used_columns": [ * ] } }, { "table": { "table_name": "mc", "access_type": "ALL", "rows_examined_per_scan": 231, "rows_produced_per_join": 231, "filtered": "100.00", "using_join_buffer": "Block Nested Loop", "cost_info": { "read_cost": "5.54", "eval_cost": "46.20", "prefix_cost": "52.94", "data_read_per_join": "274K" }, "used_columns": [ "code" ], "attached_condition": "<if>(is_not_null_compl(mc), (`trans-core`.`mc`.`code` = `trans-core`.`mr2`.`mcc_cd`), true)" } } ] } } |
13
dust0522 OP ~~~~~~~~~~用了 sql_no_cache 速度反过来了,什么鬼,应该是缓存的原因了
|
14
PickleFish 2023-03-17 12:39:32 +08:00
<img src="https://i.postimg.cc/8zL0wK7B/2023-03-17-12-36-07.jpg" alt="2023-03-17-12-36-07"/></a><img src="https://i.postimg.cc/MGDrJnbn/2023-03-17-12-36-18.jpg" alt="2023-03-17-12-36-18"/></a><img src="https://i.postimg.cc/mDgpLT88/2023-03-17-12-36-24.jpg" alt="2023-03-17-12-36-24"/></a>
有知道我这是啥问题嘛,数据量对不上,还是因为直增 ID 出现了问题呢 |
15
liuxu 2023-03-17 12:51:21 +08:00
@dust0522 #11 #12 从 mysql 角度来说,第一个 sql 成本 1.2 ,第二个 sql 成本 52.94 ,第一个肯定比第一个快,如果用了 sql_no_cache 更快那就对了,可能就是 query cache 的问题,mysql5.7 将它标记为弃用,mysql8 已经把 query cache 去掉了,没什么用,https://dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.html
|