版本: 阿里云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查看具体的资源消耗使用情况
综上, 虽然使用了强制索引让索引生效, 但根据执行计划, 它使用了 Using index condition , 存在回表的情况, 难怪MySQL选择了全表扫描而不使用索引, 也是因为大量的数据需要回表,效率低下. 为了提高回表的效率, 采用MRR技巧, 同时设置MRR空间128M
-- 设置大小
set session read_rnd_buffer_size=1024*1024*128;
-- 开启MRR
set optimizer_switch='mrr=on,mrr_cost_based=off';
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 ;
这次并没有显式地强制使用索引 , 再次查看执行计划
在没有开启MRR的前提下, SQL 语句会全表扫描
在没有开启MRR的前提下, 显式地强制使用索引, 会出现回表
开启MRR, 而且不再显式地使用强制索引, 执行计划显示它也会使用索引,也使用了MRR
但依然执行很慢
慢长的调优实践过程…
经过详细分析, 将优化思路整理如下
改写SQL + 添加索引 + 调整参数
1.第一步需要让执行计划中的rows值减少 (通过改写SQL 或者 使用索引 或者 强制使用索引)
2. 第二步如果出现如下三种调整参数
Using index condition 开启MRR 以及 增大MRR内存 .
Using temporary 根据 SHOW STATUS LIKE ‘%tmp%’ 的Created_tmp_disk_tables 值是否发生增长, 进而决定是否需要增大 tmp_table_size .
Using filesort 根据 SHOW STATUS LIKE ‘sort%’ 的Sort_merge_passes 值是否发生增长, 进而决定是否需要增大 sort_buffer_size .
字段上有索引也未必使用索引 .
字段上无索引也未必不用索引 SELECT COUNT(1) ... 可能会使用组合索引 .
使用索引也未必性能高,因回表让其性能变低
202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
