增加强制索引依然慢

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之InnoDB存储引擎中的索引用法及说明

《MySQL之InnoDB存储引擎中的索引用法及说明》:本文主要介绍MySQL之InnoDB存储引擎中的索引用法及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐... 目录1、背景2、准备3、正篇【1】存储用户记录的数据页【2】存储目录项记录的数据页【3】聚簇索引【4】二

全面解析MySQL索引长度限制问题与解决方案

《全面解析MySQL索引长度限制问题与解决方案》MySQL对索引长度设限是为了保持高效的数据检索性能,这个限制不是MySQL的缺陷,而是数据库设计中的权衡结果,下面我们就来看看如何解决这一问题吧... 目录引言:为什么会有索引键长度问题?一、问题根源深度解析mysql索引长度限制原理实际场景示例二、五大解决

MySQL中的索引结构和分类实战案例详解

《MySQL中的索引结构和分类实战案例详解》本文详解MySQL索引结构与分类,涵盖B树、B+树、哈希及全文索引,分析其原理与优劣势,并结合实战案例探讨创建、管理及优化技巧,助力提升查询性能,感兴趣的朋... 目录一、索引概述1.1 索引的定义与作用1.2 索引的基本原理二、索引结构详解2.1 B树索引2.2

python3如何找到字典的下标index、获取list中指定元素的位置索引

《python3如何找到字典的下标index、获取list中指定元素的位置索引》:本文主要介绍python3如何找到字典的下标index、获取list中指定元素的位置索引问题,具有很好的参考价值,... 目录enumerate()找到字典的下标 index获取list中指定元素的位置索引总结enumerat

从入门到精通MySQL 数据库索引(实战案例)

《从入门到精通MySQL数据库索引(实战案例)》索引是数据库的目录,提升查询速度,主要类型包括BTree、Hash、全文、空间索引,需根据场景选择,建议用于高频查询、关联字段、排序等,避免重复率高或... 目录一、索引是什么?能干嘛?核心作用:二、索引的 4 种主要类型(附通俗例子)1. BTree 索引(

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