报文也不大就 214KB

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

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

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

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

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

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

spring.shardingsphere.datasource 利用反射初始化的连接池的创建参数，我一直没想连接池的事

@OldCarMan 你提醒了我，解决了哈哈，我用的 Druid 连接池 在配置文件中加入 spring.shardingsphere.datasource.defaultTransactionIsolation=4 (4 代表 REPEATABLE-READ)， 这样连接池在创建连接的时候，会把这个值赋给 ShardingSphereConnection 。

@WashFreshFresh 那我 fork 下来把 ShardingSphereConnection 默认的隔离级别预制成 REPEATABLE-READ 试试

git 提了 issues 没人理 https://github.com/apache/shardingsphere/issues/24188