V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
dust0522
V2EX  ›  程序员

mysql 查询时的优化

  •  
  •   dust0522 · 2023-03-17 10:36:22 +08:00 · 1897 次点击
    这是一个创建于 610 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    15 条回复    2023-03-17 12:51:21 +08:00
    dust0522
        1
    dust0522  
    OP
       2023-03-17 10:40:37 +08:00
    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
        2
    dust0522  
    OP
       2023-03-17 10:41:57 +08:00
    表: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
        3
    weijancc  
       2023-03-17 10:45:49 +08:00
    mer_no 有索引那应该是第一条查询更快才对, 会不会是网络问题呢
    Braisdom
        4
    Braisdom  
       2023-03-17 10:46:50 +08:00
    这难道不是 MySQL 的 Bug 吗?
    opengps
        5
    opengps  
       2023-03-17 10:49:12 +08:00
    0.18 与 0.25 似乎没啥可比性,有没有更显著的数字展示下?查询提速能考虑的无非就是索引合理性
    opengps
        6
    opengps  
       2023-03-17 10:51:35 +08:00
    重启下 mysql 服务,只记录首次查询语句耗时(释放掉索引的缓存,排除下这个因素)
    xsonglive491
        7
    xsonglive491  
       2023-03-17 10:51:41 +08:00
    你直接 explain format='json' 一下看看怎么回事就好了,可以看到查询的顺序
    rekulas
        8
    rekulas  
       2023-03-17 10:53:58 +08:00
    索引列 limit 1 百毫秒级,索引已经出问题了,别纠结快慢问题了,删除索引重建吧。。。
    BiChengfei
        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 表数据很少,所以比第一种快了一点
    dust0522
        10
    dust0522  
    OP
       2023-03-17 11:27:05 +08:00
    我用 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
        11
    dust0522  
    OP
       2023-03-17 11:29:54 +08:00
    {
    "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
        12
    dust0522  
    OP
       2023-03-17 11:30:49 +08:00
    {
    "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
        13
    dust0522  
    OP
       2023-03-17 11:37:33 +08:00
    ~~~~~~~~~~用了 sql_no_cache 速度反过来了,什么鬼,应该是缓存的原因了
    PickleFish
        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 出现了问题呢
    liuxu
        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
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2626 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 10:37 · PVG 18:37 · LAX 02:37 · JFK 05:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.