增加强制索引依然慢

2024-06-07 22:52
文章标签 索引 强制 增加 依然

本文主要是介绍增加强制索引依然慢,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!


版本: 阿里云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_idINNER JOIN store_storage ss  ON ss.storage_id = ssd.storage_idINNER JOIN store_batch_details sbd ON sbd.batch_details_id = sbdl.batch_details_idINNER 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_idLEFT JOIN store_customer sc ON sc.customer_id = sb.goods_owner_idLEFT JOIN store_customer sc3 ON sc3.customer_id = ss.customer_idLEFT JOIN warehouse w ON w.warehouse_id = ssd.warehouse_idLEFT JOIN store_customer sc2 ON sc2.customer_id = sb.supplier_id
WHERE 1 = 1AND ssd.`goods_name` LIKE CONCAT('%', '【堂食专供】葡萄(计重)', '%')AND ss.enterprise_id = 241240455403319296AND 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 DESCLIMIT 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 JOINstore_storage_details ssd ON ssd.storage_details_id = sbdl.storage_details_idINNER JOINstore_storage ss  ON ss.storage_id = ssd.storage_idINNER JOIN store_batch_details sbd ON sbd.batch_details_id = sbdl.batch_details_idINNER 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_idLEFT JOIN store_customer sc ON sc.customer_id = sb.goods_owner_idLEFT JOIN store_customer sc3 ON sc3.customer_id = ss.customer_idLEFT JOIN warehouse w ON w.warehouse_id = ssd.warehouse_idLEFT JOIN store_customer sc2 ON sc2.customer_id = sb.supplier_id
WHERE 1 = 1AND ssd.`goods_name` LIKE CONCAT('%', '【堂食专供】葡萄(计重)', '%')AND ss.enterprise_id = 241240455403319296AND ss.warehouse_id IN (272697524450807808 , 278854886203117568,358283733083942912,358310610389495808,358316852142993408,358317205127229440,358317497189199872,358319149438791680,358320040363487232,362996967464562688,432595648538574848,433271921665474560,433660539047346176,434765698913632256,460080655901245440)ORDER BY ss.create_time DESCLIMIT 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_idINNER JOIN store_storage ss force index(idx_enterprise_id_warehouse_id_create_time) ON ss.storage_id = ssd.storage_idINNER JOIN store_batch_details sbd ON sbd.batch_details_id = sbdl.batch_details_idINNER 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_idLEFT JOIN store_customer sc ON sc.customer_id = sb.goods_owner_idLEFT JOIN store_customer sc3 ON sc3.customer_id = ss.customer_idLEFT JOIN warehouse w ON w.warehouse_id = ssd.warehouse_idLEFT JOIN store_customer sc2 ON sc2.customer_id = sb.supplier_id
WHERE 1 = 1AND ssd.`goods_name` LIKE CONCAT('%', '【堂食专供】葡萄(计重)', '%')AND ss.enterprise_id = 241240455403319296AND 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 DESCLIMIT 0,20 ;

如上, 使用了 force index(idx_enterprise_id_warehouse_id_create_time) . 执行计划如下

在这里插入图片描述ss表终于使用了索引, row值也变少了 . 可在实际执行SQL语句时, 耗时14左右, 依然不理想.

继续使用 SHOW PROFILE查看具体的资源消耗使用情况

在这里插入图片描述

待续…

这篇关于增加强制索引依然慢的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1040534

相关文章

MySQL 添加索引5种方式示例详解(实用sql代码)

《MySQL添加索引5种方式示例详解(实用sql代码)》在MySQL数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中,下面给大家分享MySQL添加索引5种方式示例详解(实用sql代码),... 在mysql数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中。索引可以在创建表时定义,也可

CentOS7增加Swap空间的两种方法

《CentOS7增加Swap空间的两种方法》当服务器物理内存不足时,增加Swap空间可以作为虚拟内存使用,帮助系统处理内存压力,本文给大家介绍了CentOS7增加Swap空间的两种方法:创建新的Swa... 目录在Centos 7上增加Swap空间的方法方法一:创建新的Swap文件(推荐)方法二:调整Sww

MySQL索引失效问题及解决方案

《MySQL索引失效问题及解决方案》:本文主要介绍MySQL索引失效问题及解决方案,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql索引失效一、概要二、常见的导致MpythonySQL索引失效的原因三、如何诊断MySQL索引失效四、如何解决MySQL索引失

C# foreach 循环中获取索引的实现方式

《C#foreach循环中获取索引的实现方式》:本文主要介绍C#foreach循环中获取索引的实现方式,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、手动维护索引变量二、LINQ Select + 元组解构三、扩展方法封装索引四、使用 for 循环替代

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

电脑死机无反应怎么强制重启? 一文读懂方法及注意事项

《电脑死机无反应怎么强制重启?一文读懂方法及注意事项》在日常使用电脑的过程中,我们难免会遇到电脑无法正常启动的情况,本文将详细介绍几种常见的电脑强制开机方法,并探讨在强制开机后应注意的事项,以及如何... 在日常生活和工作中,我们经常会遇到电脑突然无反应的情况,这时候强制重启就成了解决问题的“救命稻草”。那

C语言中的数据类型强制转换

《C语言中的数据类型强制转换》:本文主要介绍C语言中的数据类型强制转换方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录C语言数据类型强制转换自动转换强制转换类型总结C语言数据类型强制转换强制类型转换:是通过类型转换运算来实现的,主要的数据类型转换分为自动转换

Java强制转化示例代码详解

《Java强制转化示例代码详解》:本文主要介绍Java编程语言中的类型转换,包括基本类型之间的强制类型转换和引用类型的强制类型转换,文中通过代码介绍的非常详细,需要的朋友可以参考下... 目录引入基本类型强制转换1.数字之间2.数字字符之间引入引用类型的强制转换总结引入在Java编程语言中,类型转换(无论

SpringBoot项目使用MDC给日志增加唯一标识的实现步骤

《SpringBoot项目使用MDC给日志增加唯一标识的实现步骤》本文介绍了如何在SpringBoot项目中使用MDC(MappedDiagnosticContext)为日志增加唯一标识,以便于日... 目录【Java】SpringBoot项目使用MDC给日志增加唯一标识,方便日志追踪1.日志效果2.实现步

Mysql中InnoDB与MyISAM索引差异详解(最新整理)

《Mysql中InnoDB与MyISAM索引差异详解(最新整理)》InnoDB和MyISAM在索引实现和特性上有差异,包括聚集索引、非聚集索引、事务支持、并发控制、覆盖索引、主键约束、外键支持和物理存... 目录1. 索引类型与数据存储方式InnoDBMyISAM2. 事务与并发控制InnoDBMyISAM