MySQL索引优化（超详细）篇章2--索引调优

目录

  • 1.索引失效状况
  • 2.性能分析
  • 3.表的索引信息--调整索引顺序
  • 4.删除冗余索引
  • 5.最佳左前缀法则
    • 5.1下面是一个实际的例子来说明这个概念：
  • 6.数据长度和索引长度占用空间比较

1.索引失效状况

MySQL索引失效通常指的是查询语句无法有效地利用索引，而导致全表扫描或者使用了不合适的索引，从而影响查询性能。这可能是由于查询条件不符合索引的最左匹配规则、使用了函数或表达式、数据分布不均匀等原因导致的。下面是一些常见的MySQL索引失效情况：

  1. 未使用索引：当查询语句中的条件没有与任何索引字段匹配时，MySQL将无法使用索引，从而执行全表扫描。例如，如果查询语句中的条件是一个不在索引列中的字段，或者没有条件限制。

    SELECT * FROM table WHERE non_indexed_column = 'value';

  2. 未使用最左前缀：MySQL索引采用最左前缀匹配规则，如果查询条件不是索引的最左前缀，索引也将无法被利用。例如，如果索引是 (col1, col2)，而查询条件只使用了 col2，那么索引将无法被使用。

    SELECT * FROM table WHERE col2 = 'value';

  3. 使用函数或表达式：当查询语句中使用了函数或者表达式，MySQL无法使用索引。例如，在WHERE子句中使用了函数或者对列进行了运算。

    SELECT * FROM table WHERE YEAR(date_column) = 2022;

  4. 数据分布不均匀：如果索引列上的数据分布不均匀，MySQL可能会选择不使用索引，而进行全表扫描。这种情况通常出现在某些值的重复度较高或者数据分布不均匀的情况下。

    SELECT * FROM table WHERE indexed_column = 'value_with_low_selectivity';

  5. 索引失效于范围查询：在某些情况下，MySQL无法有效利用索引执行范围查询，例如使用了LIKE、<>、!=等非等值比较操作符。like以通配符%开头索引失效

    SELECT * FROM table WHERE indexed_column LIKE '%value%'; 失效

  6. is null可以使用索引，is not null无法使用索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;无法触发索引
  1. 不等于(!= 或者<>)索引失效
SELECT * FROM financial_data  WHERE profit_amount != 0;
  1. OR 前后存在非索引的列，索引失效
  2. 范围条件右边的列索引失效
# 创建索引
ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_age;
ALTER TABLE student DROP INDEX idx_age_classid;
# 索引失效 范围右边的列不能使用。比如：(<) (<=) (>) (>=) 和 between 等
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
# 正确创建索引
create index idx_age_name_classId on student(age,name,classId);
# 正确sql查询语句操作
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

# 未使用到索引 因为classId字段上没有索引，所以上述查询语句没有使用索引。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

  1. 使用NOT操作符：在查询语句中使用了NOT操作符，MySQL可能无法有效使用索引。这是因为NOT操作符需要对索引进行反向扫描，而不是利用索引的B+树结构进行快速定位。

    SELECT * FROM table WHERE indexed_column NOT IN ('value1', 'value2');

  2. 隐式数据类型转换：当查询条件中的数据类型与索引列的数据类型不匹配时，MySQL可能会放弃使用索引。这通常发生在进行隐式数据类型转换时，例如将字符串与数字进行比较。

    SELECT * FROM table WHERE indexed_column = 123; -- 如果indexed_column是字符串类型的话

  3. 索引列上存在函数：如果索引列上存在函数，MySQL无法使用索引。这种情况通常出现在对列进行了函数操作后进行查询的情况下。

    SELECT * FROM table WHERE UPPER(indexed_column) = 'VALUE';

  4. 数据类型不匹配：索引列与查询条件的数据类型不匹配时，MySQL可能无法使用索引。例如，如果索引列是字符串类型，而查询条件是数字类型。

    SELECT * FROM table WHERE indexed_column = 123; -- 如果indexed_column是字符串类型的话

  5. 过度索引：有时候过多的索引可能会导致MySQL优化器选择不合适的索引，从而导致索引失效。这种情况下，MySQL可能会选择扫描索引而不是使用它们。

2.性能分析

1.设置一定阈值的慢查询记录
2.找出sql,进行EXPLAIN 分析
3.调整mysql中配置文件相关参数
4.优化sql语句，避免全查或索引失效
5.定期调整索引顺序

3.表的索引信息–调整索引顺序

SHOW INDEX FROM your_table;
或者
关注Cardinality 表示索引中不同值的数量


SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = '表所在数据库名称' AND TABLE_NAME = '表名'
ORDER BY CARDINALITY DESC;--降序排序

在 MySQL 中，SHOW INDEX 命令用于显示表的索引信息在 SHOW INDEX 的结果中，Cardinality 是一个重要的指标，表示索引中不重复的值的估计数量。

具体来说，Cardinality 表示索引中不同值的数量，也就是索引列中唯一值的估计数量。这个值对于查询优化非常重要，因为它可以帮助数据库优化器决定使用哪个索引来执行查询。

如果 Cardinality 的值较高，意味着索引列中的值大部分都是唯一的，这通常对于过滤数据很有帮助，因为它可以帮助数据库更快地定位到所需的行。相反，如果 Cardinality 的值较低，意味着索引列中的值有很多重复，这可能会导致查询优化器选择不使用索引或者选择其他更适合的索引。

在优化查询性能时，你可以根据 Cardinality 的值来评估索引的选择和效率，以确保查询能够以最佳的方式执行。
在这里插入图片描述

通过调整索引的顺序，你可以最大程度地利用索引的性能优势，从而提高查询的效率。但需要注意的是，索引的选择和顺序也受到其他因素的影响，如查询的频率、数据分布等，因此需要综合考虑来进行优化。

4.删除冗余索引

select * from sys.schema_redundant_indexes;
查询出的冗余索引 根据字段sql_drop_index 去删除索引
sql_drop_index字段中给出了 具体删除索引的sql语句
1.删除不必要的索引： 删除那些很少被使用或者几乎没有提升查询性能的索引。这样可以减少数据库的存储空间占用，并提高写操作的性能。

2.合并重叠索引： 如果存在涵盖相同列或者重叠的索引，考虑将它们合并成一个索引。这样可以减少索引的数量，并且提高查询的效率。

3.优化索引设计： 对于频繁查询的列，考虑创建适当的复合索引以满足查询需求。同时，避免创建过多的单列索引，以免造成冗余。

4.定期监控和优化索引： 定期监控数据库的索引使用情况，并根据实际情况进行调整和优化。随着数据库中数据的增长和查询模式的变化，索引的需求也会发生变化，因此需要不断进行优化和调整。
索引1：(name, email)
索引2：(name, phone)
1.可以创建复合 name, email，phone
2.删除未使用的索引

5.最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。MySQL可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有用这些字段中第一个字段时，多列（或联合）索引不会被使用。
MySQL 的多列索引（也称为联合索引）允许你基于表中的多个列创建一个单一的索引。然而，当使用多列索引进行查询时，查询条件中的列必须遵循索引中列的顺序，以便索引能够高效地工作。

5.1下面是一个实际的例子来说明这个概念：

假设我们有一个名为 orders 的表，
其中包含以下字段：order_id, customer_id, order_date, amount。

我们可以为这个表创建一个多列索引，基于 customer_id, order_date 和 amount 字段：

CREATE INDEX idx_customer_order_amount ON orders(customer_id, order_date, amount);

现在，让我们看几个查询例子来说明索引的使用：

1.完全使用索引的查询：
当查询条件包含索引中的所有列，并且顺序与索引中的列顺序一致时，索引会被充分使用。

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-01-01' AND amount > 100;

在这个查询中，我们使用了 customer_id, order_date 和 amount 字段，并且它们的顺序与索引 idx_customer_order_amount 中的顺序一致。因此，这个查询能够高效地使用索引。

2.部分使用索引的查询：
如果查询条件只包含索引中的部分列，并且这些列的顺序与索引中的顺序一致，那么索引仍然会被使用，但是效率可能不如完全使用索引的查询。

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-01-01';

在这个查询中，我们使用了 customer_id 和 order_date 字段，这两个字段在索引 idx_customer_order_amount 中是连续的，因此索引仍然会被使用。

3.跳过索引中列的查询：
如果查询条件跳过了索引中的某个列，那么索引后面的列就无法被使用。

SELECT * FROM orders WHERE order_date = '2023-01-01' AND amount > 100;

在这个查询中，我们跳过了 customer_id 字段，直接使用了 order_date 和 amount 字段。由于 order_date 不是索引的第一个字段，MySQL 无法使用这个多列索引来优化查询，即使 order_date 和 amount 在索引中。

4.未使用索引的查询：
如果查询条件中没有使用索引中的第一个字段，那么多列索引将不会被使用。

SELECT * FROM orders WHERE amount > 100;

在这个查询中，我们只使用了 amount 字段，而 amount 并不是索引 idx_customer_order_amount 的第一个字段。因此，这个查询不会使用多列索引。

通过上面的例子，你可以看到在使用多列索引时，查询条件中列的顺序和索引中列的顺序是多么重要。为了最大化索引的效率，你应该尽量使查询条件中的列顺序与索引中的列顺序相匹配，并且尽量使用索引中的第一个字段。

6.数据长度和索引长度占用空间比较

在 MySQL 表中，数据长度和索引长度取决于表的结构、列的数据类型以及创建的索引类型和大小。通常情况下，索引长度会比数据长度要小，因为索引只是表中数据的一种排序和快速检索方式，它不包含完整的行数据。

数据长度是指表中所有列的数据总长度，包括所有的行。而索引长度则是指索引所占用的存储空间大小。

当然，并不是所有情况下索引长度都比数据长度小，有时候索引长度会随着索引的复杂度增加而增加，特别是在对大量数据或者复杂数据类型进行索引时。

总的来说，索引长度一般是比数据长度要小的，因为索引通常只包含索引列的值和一些额外的元数据，而不是完整的行数据。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://xiahunao.cn/news/3032495.html

如若内容造成侵权/违法违规/事实不符，请联系瞎胡闹网进行投诉反馈，一经查实，立即删除！

相关文章

微信登录功能--网站应用

微信登录功能--网站应用

微信开发平台注册https://open.weixin.qq.com/ 账号中心-填写基本资料&#xff08;最好是公司注册&#xff09; 账号中心-开发者资质认证&#xff08;充钱&#xff0c;300&#xff09; 审核通过之后&#xff0c;管理中心-网站应用-创建网站应用&#xff08;AppSecret一定一定…
阅读更多...
国内十大免费图床推荐

国内十大免费图床推荐

国内十大免费图床推荐 近期&#xff0c;莫卡乐AI导航站汇总了国内一些出色的图床网站&#xff0c;既有知名大站&#xff0c;也有小众网站&#xff0c;用户的使用体验都非常好&#xff01; 1.路过图床 地址&#xff1a;https://imgse.com/ 我们是国内知名的图床之一&#xf…
阅读更多...
Android 如何启用user版本的adb源码分析

Android 如何启用user版本的adb源码分析

通过adb shell中执行getprop persist.sys.usb.config&#xff0c;可以看到系统usb的相关选项&#xff0c;persist.sys.usb.config显示的就是当前系统关于usb选项的系统配置【RK3188Android4.4刚移植的例子】: 全编脚本中make命令会调用build/core/main.mk,在里面可以看到一段…
阅读更多...
Ansible入门：解锁IT自动化的神

Ansible入门：解锁IT自动化的神

在当今的IT自动化领域&#xff0c;Ansible无疑是一个无法被忽视的重要角色。其便利性和高效性受到了广大开发者和系统管理员的一致好评&#xff0c;成为了配置管理和应用部署的首选工具。然而&#xff0c;对于一些初学者来说&#xff0c;Ansible的概念和架构可能会显得有些复杂…
阅读更多...
微火全域外卖团购服务，引领商家与合伙人变革行业赛道！

微火全域外卖团购服务，引领商家与合伙人变革行业赛道！

在当今的数字化时代&#xff0c;外卖业务正成为越来越多人的日常生活选择。然而&#xff0c;随着市场的日益饱和和竞争的加剧&#xff0c;传统外卖模式已经难以满足商家和消费者的多元化需求。正是在这样的背景下&#xff0c;全域外卖团购业务应运而生&#xff0c;以其独特的模…
阅读更多...
山东大学软件学院创新项目实训开发日志——第11周

山东大学软件学院创新项目实训开发日志——第11周

山东大学软件学院创新项目实训开发日志——第11周 项目名称&#xff1a;ModuFusion Visionary&#xff1a;实现跨模态文本与视觉的相关推荐 -------项目目标&#xff1a; 本项目旨在开发一款跨模态交互式应用&#xff0c;用户可以上传图片或视频&#xff0c;并使用文本、点、…
阅读更多...
使用Remix部署智能合约到币安链（Remix的操作介绍 币安链合约的部署） 点赞收藏哦

使用Remix部署智能合约到币安链（Remix的操作介绍 币安链合约的部署） 点赞收藏哦

大家好&#xff0c;我是程序员大猩猩呀。 据我所知&#xff0c;很多人进入币圈之后&#xff0c;想要通过炒币一夜暴富&#xff01;另一部分人呢他们希望自己能创建一个项目&#xff0c;然后发行自己的数字货币然后暴富。 不管是什么方式吧&#xff0c;只要不违法&#xff0c;…
阅读更多...
小程序第八章总结

小程序第八章总结

1.比目后端云简介 一个完整的小程序系统, 不但需要前端的展现, 而且需要后端服务器的支撑, 以提供数据服务。 也就是说, 开发一个真正完整的小程序应用, 需要前后端的相互配合。 小程序与远程服务器之间通过&#xff28;&#xff34;&#xff34;&#xff30;&#xff33; 传输…
阅读更多...
计算机组成结构—指令和指令格式

计算机组成结构—指令和指令格式

目录 一、指令的基本格式 二、指令字长 1. 定长指令字结构 2.变长指令字结构 三、地址码 1.四地址指令 2.三地址指令 3.二地址指令 4.一地址指令 5. 零地址指令 四、操作码 1. 定长操作码指令格式 2. 扩展操作码指令格式 五、指令的操作数类型和操作类型 1. 操作…
阅读更多...
基于Python的数据分组技术：将数据按照1, 2, 3规则分为三个列表

基于Python的数据分组技术：将数据按照1, 2, 3规则分为三个列表

目录 一、引言 二、数据分组原理与意义 三、案例分析 四、代码实现与解释 五、对新手友好的解释 六、技术细节与扩展 七、实际应用场景 八、总结 一、引言 在数据处理和分析的广阔领域中&#xff0c;数据分组是一项基础且重要的任务。数据分组通常指的是将数据集中的元…
阅读更多...
程序员的归宿。。

程序员的归宿。。

大家好&#xff0c;我是瑶琴呀。 相信每个进入职场的人都考虑过自己的职业生涯规划&#xff0c;在不同的年龄段可能面临不同挑战&#xff0c;这点对于 35 的人应该更为感同身受。 对于程序员来说&#xff0c;大部分人的职业道路主要是下面三种&#xff1a;第一条&#xff0c;…
阅读更多...
数据序列包分析

数据序列包分析

基于数据序列包分析各部分的内容及含义&#xff0c;可能会考大题 基于本例分析&#xff0c;每部分含义如下&#xff1a; 时间&#xff08;Time&#xff09;&#xff1a; 时间戳显示了数据包在网络中被捕获的具体时间。在本例中&#xff0c;如"0.000000"表示第一个数据…
阅读更多...
【PG数据库】PostgreSQL 日志归档详细操作流程

【PG数据库】PostgreSQL 日志归档详细操作流程

1.1 日志归档的目的 pg数据库日志归档是将PostgreSQL数据库的日志文件进行归档的过程。 归档的主要目的是为了保留历史数据&#xff0c;确保数据的一致性和完整性&#xff0c;同时为数据恢复提供必要的支持。 pg数据库日志归档的目的包括&#xff1a; 1.数据恢复&#xff1…
阅读更多...
【IC前端虚拟项目】axi ddr/sram验证组件思路与编写

【IC前端虚拟项目】axi ddr/sram验证组件思路与编写

【IC前端虚拟项目】数据搬运指令处理模块前端实现虚拟项目说明-CSDN博客 在完成了所有的utils包括apb_utils之后，就要进行验证环境的整体搭建，因此我们再把验证环境拿出来看一下： 按照由底至顶层的思路，接下主要有五大部分需要我们完成：ram_model、reference_model、env、…
阅读更多...
Github 配置 SSH key

Github 配置 SSH key

一、前言 问题描述 通过 ssh 的 url 使用 git 命令克隆 github 上私有项目出现 fatal: Could not read from remote repository. 本地仓库无法从远程仓库读取数据克隆失败 问题定位 一般是仓库 URL 错误或者权限问题这里排除 URL 错误&#xff0c;初步定位为访问权限问题 解决…
阅读更多...
力扣HOT100 - 84. 柱状图中最大的矩形

力扣HOT100 - 84. 柱状图中最大的矩形

解题思路&#xff1a; 单调栈 对于一个高度height[ i ]&#xff0c;找左右两边均严格小于它的值。 class Solution {public int largestRectangleArea(int[] heights) {int n heights.length;int[] left new int[n];int[] right new int[n];Deque<Integer> mono_st…
阅读更多...
RS3236-3.3YUTDN4功能和参数介绍及PDF资料

RS3236-3.3YUTDN4功能和参数介绍及PDF资料

RS3236-3.3YUTDN4功能和参数介绍及PDF资料-公司新闻-配芯易-深圳市亚泰盈科电子有限公司 品牌: RUNIC(润石) 封装: XDFN-4-EP(1x1) 描述: 带过温保护 输出类型: 固定 最大输入电压: 7.5V 输出电压: 3.3V 最大输出电流: 500mA RS3236-3.3YUTDN4 是一款低压差线性稳压器&#x…
阅读更多...
商务分析方法与工具（八）：Python的趣味快捷-年少不知numpy好，再见才觉很简单

商务分析方法与工具（八）：Python的趣味快捷-年少不知numpy好，再见才觉很简单

Tips&#xff1a;"分享是快乐的源泉&#x1f4a7;&#xff0c;在我的博客里&#xff0c;不仅有知识的海洋&#x1f30a;&#xff0c;还有满满的正能量加持&#x1f4aa;&#xff0c;快来和我一起分享这份快乐吧&#x1f60a;&#xff01; 喜欢我的博客的话&#xff0c;记得…
阅读更多...
3分钟掌握Suno API！音痴也能创作热门曲！免费拥有个人爆款音乐！

3分钟掌握Suno API！音痴也能创作热门曲！免费拥有个人爆款音乐！

Suno API 的申请及使用 随着 AI 的应用变广&#xff0c;各类 AI 程序已逐渐普及。AI 已逐渐深入到人们的工作生活方方面面。而 AI 涉及的行业也越来越多&#xff0c;从最初的写作&#xff0c;到医疗教育&#xff0c;再到现在的音乐。 Suno 是一个专业高质量的 AI 歌曲和音乐创…
阅读更多...
[AutoSar]lauterbach_001_ORTI_CPUload_Trace

[AutoSar]lauterbach_001_ORTI_CPUload_Trace

目录 关键词平台说明一、ORTI概述二、ORTI文件的生成三、ORTI文件的导入四、Trace 功能4.1 Trace 功能菜单介绍4.2 Trace功能的配置4.3 Trace MCDS 设置4.4 Task Switches断点的设置4.5 Trace 数据的录取4.6 CPU 负载和Task调度的查看 关键词 嵌入式、C语言、autosar、OS、BSW…
阅读更多...
最新文章

Copyright @ 2022~2023