mysql 查询时的优化

[dust0522]

为什么有时候用了 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

[dust0522]

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]

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

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

[Braisdom]

这难道不是 MySQL 的 Bug 吗？

[opengps]

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

[opengps]

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

[xsonglive491]

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

[rekulas]

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

[BiChengfei]

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

[dust0522]

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

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

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

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

[PickleFish]

<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 出现了问题呢

[liuxu]

@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