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

[cencoroll]

源于领导让优化 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 以内的查询？

[OpenSea]

生产环境是不是按照 update_date 做分区了？

[djoiwhud]

不强制用索引的话，数据库会自己分析。当命中利率太多，会走全表扫描。

[Rache1]

数据库版本呢？

[v2wtf]

数据量不一样，explain 结果也会不一样的。有时候你以为走索引快，实际上直接全表扫描更快，因为数据都在缓存里了。

[statumer]

数据库居然用机械硬盘有点抽象了，本来有状态组件对机器性能要求就高。

[liprais]

在生产上开一下 optimizer trace 就知道哪有问题了

[cencoroll]

@Rache1 都是 5.7 ，用的环境都一样。
@v2wtf 数据量一样的，昨天刚拷贝的数据库到开发环境

[leopod1995]

能确定的问题是 update_date 这个 Index 在这个 sql 里面是不合适的，生产环境用这个索引查出来 15w 数据，建议优化方向放在 recipe_name 模糊查询上面

[xsonglive491]

执行 show index from tablename 查看 Cardinality 值是否合理。还有一种情况就是生产环境中的表的索引没有正确更新
重建一下索引可能会有效果 analyze table tablename

[liuxu]

直接原因是 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]

现在的情况是，生产环境的 mysql 个干到 2G 缓存了。速度可以接受了, 昨天开发环境的 explain ，和同事讨论的结果认为是老测同一段 sql 语句所以（可能）被 mysql 缓存了，虽然 explain 为 null 但是 use index 应该还是走了索引的。