mysql 查询时的优化

dust0522 · 5 小时 50 分钟前 · 787 次点击

为什么有时候用了 join 比没用 join 的还快,MySQL 做了什么优化

-- 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

15 条回复 • 2023-03-17 12:51:21 +08:00

dust0522 5 小时 46 分钟前

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

dust0522 5 小时 44 分钟前

表：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

weijancc 5 小时 41 分钟前

mer_no 有索引那应该是第一条查询更快才对, 会不会是网络问题呢

Braisdom 5 小时 40 分钟前

这难道不是 MySQL 的 Bug 吗？

opengps 5 小时 37 分钟前

0.18 与 0.25 似乎没啥可比性，有没有更显著的数字展示下？查询提速能考虑的无非就是索引合理性

opengps 5 小时 35 分钟前

重启下 mysql 服务，只记录首次查询语句耗时（释放掉索引的缓存，排除下这个因素）

xsonglive491 5 小时 35 分钟前

你直接 explain format='json' 一下看看怎么回事就好了，可以看到查询的顺序

rekulas 5 小时 32 分钟前

索引列 limit 1 百毫秒级，索引已经出问题了，别纠结快慢问题了，删除索引重建吧。。。

BiChengfei 5 小时 30 分钟前

用的 navicat 吗，单位秒太大了，不容易分析，看看有没有毫秒的时间显示，我觉得有可能是缓存导致的，使用 select sql_no_cache xxx 试试，还是不行的话，用 shell 客户端试试
个人分析，经过 MySQL 查询优化器的处理，第一种直接回表 register 查询出来，第二种需要先 register 回表，再去 join mccs 表，路径长了一步，所以第一种绝对快。第二种应该是用了第一种的缓存，再加上 json mccs 表，因为 mccs 表数据很少，所以比第一种快了一点

dust0522 4 小时 59 分钟前

我用 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

dust0522 4 小时 57 分钟前

{
"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": [
*
]
}
}
}

dust0522 4 小时 56 分钟前

{
"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)"
}
}
]
}
}

dust0522 4 小时 49 分钟前

~~~~~~~~~~用了 sql_no_cache 速度反过来了，什么鬼，应该是缓存的原因了

liuxu 3 小时 35 分钟前

@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

mysql 查询时的优化

mysql 查询时的优化

Recommend

Mysql 慢查询优化实践

MySQL慢查询优化 - 文野

MySQL索引与查询优化

Mysql优化大分页查询

【MySQL—优化】查询性能优化

一文读懂MySQL的索引结构及查询优化

MySQL: 使用explain 优化查询性能

Mysql优化之慢查询

MySQL查询Plan优化

Mysql查询优化 | GreeenSY's Blog

About Joyk