源于领导让优化 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 以内的查询?
1
OpenSea 2023-02-02 16:09:15 +08:00
生产环境是不是按照 update_date 做分区了?
|
2
djoiwhud 2023-02-02 16:10:28 +08:00 via Android
不强制用索引的话,数据库会自己分析。当命中利率太多,会走全表扫描。
|
3
Rache1 2023-02-02 16:12:18 +08:00
数据库版本呢?
|
4
v2wtf 2023-02-02 16:17:47 +08:00
数据量不一样,explain 结果也会不一样的。有时候你以为走索引快,实际上直接全表扫描更快,因为数据都在缓存里了。
|
5
statumer 2023-02-02 16:20:10 +08:00
数据库居然用机械硬盘有点抽象了,本来有状态组件对机器性能要求就高。
|
6
liprais 2023-02-02 16:28:12 +08:00
在生产上开一下 optimizer trace 就知道哪有问题了
|
8
leopod1995 2023-02-02 16:40:42 +08:00
能确定的问题是 update_date 这个 Index 在这个 sql 里面是不合适的,生产环境用这个索引查出来 15w 数据,建议优化方向放在 recipe_name 模糊查询上面
|
9
xsonglive491 2023-02-02 18:21:46 +08:00
执行 show index from tablename 查看 Cardinality 值是否合理。还有一种情况就是生产环境中的表的索引没有正确更新
重建一下索引可能会有效果 analyze table tablename |
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 |
11
cencoroll OP 现在的情况是,生产环境的 mysql 个干到 2G 缓存了。速度可以接受了, 昨天开发环境的 explain ,和同事讨论的结果认为是老测同一段 sql 语句所以(可能)被 mysql 缓存了,虽然 explain 为 null 但是 use index 应该还是走了索引的。
|