版本: 阿里云RDS MySQL 8.0.25
线上数据库CPU达到100%, 定位到如下SQL
EXPLAIN
SELECT
ssd.goods_no,
ssd.goods_name,
ssd.goods_spec,
ssd.goods_unit,
ssd.create_time,
w.warehouse_name,
sb.batch_no,
swl.warehouse_region_location_name,
sc.customer_name AS goodsOwnerName,
sc2.customer_name AS supplierName,
ss.storage_id,
ss.storage_no,
ss.storage_desc,
sbdl.storage_type,
sbdl.create_time AS finishTime,
sbdl.before_quantity,
sbdl.quantity AS real_quantity,
sbdl.after_quantity,
sc3.customer_name,
sbdl.storage_category,
sb.warehouse_owner_goods_id,
sb.goods_owner_id
FROM
store_batch_details_log sbdl
INNER JOIN store_storage_details ssd ON ssd.storage_details_id = sbdl.storage_details_id
INNER JOIN store_storage ss ON ss.storage_id = ssd.storage_id
INNER JOIN store_batch_details sbd ON sbd.batch_details_id = sbdl.batch_details_id
INNER JOIN store_batch sb ON sb.batch_id = sbd.batch_id
LEFT JOIN store_warehouse_location swl ON swl.warehouse_location_id = sbd.warehouse_location_id
LEFT JOIN store_customer sc ON sc.customer_id = sb.goods_owner_id
LEFT JOIN store_customer sc3 ON sc3.customer_id = ss.customer_id
LEFT JOIN warehouse w ON w.warehouse_id = ssd.warehouse_id
LEFT JOIN store_customer sc2 ON sc2.customer_id = sb.supplier_id
WHERE
1 = 1
AND ssd.`goods_name` LIKE CONCAT('%', '【堂食专供】葡萄（计重）', '%')
AND ss.enterprise_id = 241240455403319296
AND ss.warehouse_id IN (272697524450807808 , 278854886203117568,
358283733083942912,
358310610389495808,
358316852142993408,
358317205127229440,
358317497189199872,
358319149438791680,
358320040363487232,
362996967464562688,
362998068574220288,
372377440368259072,
372377840450334720,
375321342717001728,
377847160517230592,
382166980817661952,
382167317834182656,
383586763626799104,
392392204255334400,
395668297183764480,
395668683634352128,
416633733303848960,
427869257024753664,
432595648538574848,
433271921665474560,
433660539047346176,
434765698913632256,
460080655901245440)
ORDER BY ss.create_time DESC
LIMIT 0,20 ;
执行计划如下
ss表全表扫描
因为在 ss 表上存在索引 idx_enterprise_id_warehouse_id_create_time , 既然没有使用索引, 与查询的条件有关. 于是条件上删除了一些仓库, SQL如下
EXPLAIN
SELECT
ssd.goods_no,
ssd.goods_name,
ssd.goods_spec,
ssd.goods_unit,
ssd.create_time,
w.warehouse_name,
sb.batch_no,
swl.warehouse_region_location_name,
sc.customer_name AS goodsOwnerName,
sc2.customer_name AS supplierName,
ss.storage_id,
ss.storage_no,
ss.storage_desc,
sbdl.storage_type,
sbdl.create_time AS finishTime,
sbdl.before_quantity,
sbdl.quantity AS real_quantity,
sbdl.after_quantity,
sc3.customer_name,
sbdl.storage_category,
sb.warehouse_owner_goods_id,
sb.goods_owner_id
FROM
store_batch_details_log sbdl
INNER JOIN
store_storage_details ssd ON ssd.storage_details_id = sbdl.storage_details_id
INNER JOIN
store_storage ss ON ss.storage_id = ssd.storage_id
INNER JOIN
store_batch_details sbd ON sbd.batch_details_id = sbdl.batch_details_id
INNER JOIN
store_batch sb ON sb.batch_id = sbd.batch_id
LEFT JOIN store_warehouse_location swl ON swl.warehouse_location_id = sbd.warehouse_location_id
LEFT JOIN store_customer sc ON sc.customer_id = sb.goods_owner_id
LEFT JOIN store_customer sc3 ON sc3.customer_id = ss.customer_id
LEFT JOIN warehouse w ON w.warehouse_id = ssd.warehouse_id
LEFT JOIN store_customer sc2 ON sc2.customer_id = sb.supplier_id
WHERE
1 = 1
AND ssd.`goods_name` LIKE CONCAT('%', '【堂食专供】葡萄（计重）', '%')
AND ss.enterprise_id = 241240455403319296
AND ss.warehouse_id IN (272697524450807808 , 278854886203117568,
358283733083942912,
358310610389495808,
358316852142993408,
358317205127229440,
358317497189199872,
358319149438791680,
358320040363487232,
362996967464562688,
432595648538574848,
433271921665474560,
433660539047346176,
434765698913632256,
460080655901245440)
ORDER BY ss.create_time DESC
LIMIT 0,20 ;
执行计划如下
ss表使用了索引, row值也变少了 .
于是第一步的优化, 针对第一个原始的SQL, 采用了强制索引
EXPLAIN
SELECT
ssd.goods_no,
ssd.goods_name,
ssd.goods_spec,
ssd.goods_unit,
ssd.create_time,
w.warehouse_name,
sb.batch_no,
swl.warehouse_region_location_name,
sc.customer_name AS goodsOwnerName,
sc2.customer_name AS supplierName,
ss.storage_id,
ss.storage_no,
ss.storage_desc,
sbdl.storage_type,
sbdl.create_time AS finishTime,
sbdl.before_quantity,
sbdl.quantity AS real_quantity,
sbdl.after_quantity,
sc3.customer_name,
sbdl.storage_category,
sb.warehouse_owner_goods_id,
sb.goods_owner_id
FROM
store_batch_details_log sbdl
INNER JOIN store_storage_details ssd ON ssd.storage_details_id = sbdl.storage_details_id
INNER JOIN store_storage ss force index(idx_enterprise_id_warehouse_id_create_time) ON ss.storage_id = ssd.storage_id
INNER JOIN store_batch_details sbd ON sbd.batch_details_id = sbdl.batch_details_id
INNER JOIN store_batch sb ON sb.batch_id = sbd.batch_id
LEFT JOIN store_warehouse_location swl ON swl.warehouse_location_id = sbd.warehouse_location_id
LEFT JOIN store_customer sc ON sc.customer_id = sb.goods_owner_id
LEFT JOIN store_customer sc3 ON sc3.customer_id = ss.customer_id
LEFT JOIN warehouse w ON w.warehouse_id = ssd.warehouse_id
LEFT JOIN store_customer sc2 ON sc2.customer_id = sb.supplier_id
WHERE
1 = 1
AND ssd.`goods_name` LIKE CONCAT('%', '【堂食专供】葡萄（计重）', '%')
AND ss.enterprise_id = 241240455403319296
AND ss.warehouse_id IN (272697524450807808 , 278854886203117568,
358283733083942912,
358310610389495808,
358316852142993408,
358317205127229440,
358317497189199872,
358319149438791680,
358320040363487232,
362996967464562688,
362998068574220288,
372377440368259072,
372377840450334720,
375321342717001728,
377847160517230592,
382166980817661952,
382167317834182656,
383586763626799104,
392392204255334400,
395668297183764480,
395668683634352128,
416633733303848960,
427869257024753664,
432595648538574848,
433271921665474560,
433660539047346176,
434765698913632256,
460080655901245440)
ORDER BY ss.create_time DESC
LIMIT 0,20 ;
如上, 使用了 force index(idx_enterprise_id_warehouse_id_create_time) . 执行计划如下
ss表终于使用了索引, row值也变少了 . 可在实际执行SQL语句时, 耗时14左右, 依然不理想.
继续使用 SHOW PROFILE查看具体的资源消耗使用情况
待续…
