V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
cencoroll
V2EX  ›  MySQL

[MySQL 相关]生产环境和开发环境同一条语句 explain 都不一样,以及到底走没走索引呢?

  •  
  •   cencoroll · 2023-02-02 16:05:20 +08:00 · 1701 次点击
    这是一个创建于 645 天前的主题,其中的信息可能已经有所发展或是发生改变。

    源于领导让优化 sql 语句,网上搜了半天自己摸索了半天

    SELECT
    	a.order_name AS "orderName",
    	a.recipe_name AS "recipeName",
    	a.part_no AS "partNo",
    	a.part_type AS "partType",
    	SUM( weight_set ) AS weightSet,
    	SUM( weight_act ) AS weightAct,
    	a.create_by AS "createBy.id",
    	a.create_date AS "createDate",
    	a.update_by AS "updateBy.id",
    	a.update_date AS "updateDate",
    	a.remarks AS "remarks",
    	a.del_flag AS "delFlag" 
    FROM
    	mes_compound_component a USE index (update_date)
    WHERE
    	a.del_flag = '0' 
    	AND a.is_invalid = '0' 
    	AND a.update_date BETWEEN "2022-12-01 00:00:00" AND "2023-02-01 16:22:57"
    	AND a.recipe_name like "%B-%"
    GROUP BY
    	a.part_no
    ORDER BY
    	a.recipe_name
    > OK
    > 查询时间: 0.934s
    

    但是 explain 的结果是这样的 开发环境 MYSQL

    生产环境用的 explain 生产环境 MYSQL

    开发环境: 如果使用 force index 的话要 20s 才能查出数据,但是使用 use index 的话就是 1s 不到。用的是普通的固态硬盘 生产环境: 不管 force index 或者是 use index 都要 180s 左右,而且目测是机械硬盘组成的服务器。

    现在想请教一下到底怎么办才可以让服务器也能优化到 20s 以内的查询?

    11 条回复    2023-02-03 09:19:11 +08:00
    OpenSea
        1
    OpenSea  
       2023-02-02 16:09:15 +08:00
    生产环境是不是按照 update_date 做分区了?
    djoiwhud
        2
    djoiwhud  
       2023-02-02 16:10:28 +08:00 via Android
    不强制用索引的话,数据库会自己分析。当命中利率太多,会走全表扫描。
    Rache1
        3
    Rache1  
       2023-02-02 16:12:18 +08:00
    数据库版本呢?
    v2wtf
        4
    v2wtf  
       2023-02-02 16:17:47 +08:00
    数据量不一样,explain 结果也会不一样的。有时候你以为走索引快,实际上直接全表扫描更快,因为数据都在缓存里了。
    statumer
        5
    statumer  
       2023-02-02 16:20:10 +08:00
    数据库居然用机械硬盘有点抽象了,本来有状态组件对机器性能要求就高。
    liprais
        6
    liprais  
       2023-02-02 16:28:12 +08:00
    在生产上开一下 optimizer trace 就知道哪有问题了
    cencoroll
        7
    cencoroll  
    OP
       2023-02-02 16:29:22 +08:00
    @Rache1 都是 5.7 ,用的环境都一样。
    @v2wtf 数据量一样的,昨天刚拷贝的数据库到开发环境
    leopod1995
        8
    leopod1995  
       2023-02-02 16:40:42 +08:00
    能确定的问题是 update_date 这个 Index 在这个 sql 里面是不合适的,生产环境用这个索引查出来 15w 数据,建议优化方向放在 recipe_name 模糊查询上面
    xsonglive491
        9
    xsonglive491  
       2023-02-02 18:21:46 +08:00
    执行 show index from tablename 查看 Cardinality 值是否合理。还有一种情况就是生产环境中的表的索引没有正确更新
    重建一下索引可能会有效果 analyze table tablename
    liuxu
        10
    liuxu  
       2023-02-02 18:38:39 +08:00
    直接原因是 mysql 优化器计算出来的成本不一样
    间接原因有点多,可能是版本、数据量、磁盘 io 差距等等一系列差别导致

    https://www.liuquanhao.com/posts/mysql%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E5%99%A8%E4%BD%BF%E7%94%A8%E6%8C%87%E5%8D%97/#%E5%9F%BA%E4%BA%8E%E6%88%90%E6%9C%AC%E7%9A%84%E4%BC%98%E5%8C%96
    cencoroll
        11
    cencoroll  
    OP
       2023-02-03 09:19:11 +08:00
    现在的情况是,生产环境的 mysql 个干到 2G 缓存了。速度可以接受了, 昨天开发环境的 explain ,和同事讨论的结果认为是老测同一段 sql 语句所以(可能)被 mysql 缓存了,虽然 explain 为 null 但是 use index 应该还是走了索引的。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3603 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 20ms · UTC 10:25 · PVG 18:25 · LAX 02:25 · JFK 05:25
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.