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

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

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

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

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

为什么说HTTPS比HTTP安全? HTTPS是如何保证安全的？

为什么说HTTPS比HTTP安全? HTTPS是如何保证安全的？

一、安全特性 在上篇文章中&#xff0c;我们了解到HTTP在通信过程中&#xff0c;存在以下问题&#xff1a; 通信使用明文&#xff08;不加密&#xff09;&#xff0c;内容可能被窃听不验证通信方的身份&#xff0c;因此有可能遭遇伪装而HTTPS的出现正是解决这些问题&#xff0c…
阅读更多...
基于springboot + vue 实现的简易博客系统

基于springboot + vue 实现的简易博客系统

项目效果图 登陆页面 文章列表 发表文章 用户管理 栏目管理 数据统计 后端技术栈后端主要采用了&#xff1a; 1.SpringBoot 2.SpringSecurity 3.MyBatis 4.部分接口遵循Restful风格 5.MySQL 前端技术栈前端主要采用了&#xff1a; 1.Vue 2.axios 3.Elemen…
阅读更多...
ETL如何执行Java脚本

ETL如何执行Java脚本

ETLCloud提供了执行 Java 脚本的方式&#xff0c;让用户能够灵活地处理数据并实现各种复杂的数据处理任务。 ETLCloud在数据处理领域的应用优势主要体现在以下几个方面&#xff1a; 灵活性&#xff1a;通过执行Java脚本&#xff0c;用户能够灵活定制数据处理逻辑&#xff0c;满…
阅读更多...
C语言---使用共用体将double型经纬度存储到无符号数组中

C语言---使用共用体将double型经纬度存储到无符号数组中

1.在上报经纬度时由于数据协议限制需要将double型数据存储到无符号数组中&#xff0c;下边是写了一个简单C程序进行验证&#xff1b; 2.代码示例如下 #include <stdio.h> typedef union {float data;unsigned char arr[4]; } my_data;int main() {my_data test_data {…
阅读更多...
IDEA HTTP Client 插件配置空密码的 Request

IDEA HTTP Client 插件配置空密码的 Request

最近在测试一些 rest api 的时候&#xff0c;发现 IDEA 的 HTTP Client 很好用。对比 postman 更加的轻量&#xff0c;可以满足一些简单的 HTTP 请求测试。这里主要记录下&#xff0c;当用户名没有设置密码时&#xff0c;我们该如何配置这个 HTTP Client 的 Request 文件&#…
阅读更多...
综合内容运营实习生实习体验报告怎么写？

综合内容运营实习生实习体验报告怎么写？

分享一个笔灵ai生成的综合内容运营的实习体验报告&#xff0c;有别的岗位需要的可以自己去网站使用 实习体验报告AI写作助手 | AI文章智能生成器 - 笔灵AI写作 实习体验报告 尊敬的领导&#xff1a; 您好&#xff01;我很荣幸有机会向您提交我的实习体验报告。在过去的几个月…
阅读更多...
wpf中的图标字体和android的矢量图vector

wpf中的图标字体和android的矢量图vector

1.背景 在安卓程序中如下截图所示中&#xff0c;在drawable文件夹下有如下图片文件。 这是一个xml文件&#xff0c;打开后看到Vector节点下paht中有一个pathData属性有一串带字母数字和"."点的数据。这些是什么呢&#xff1f; <vector xmlns:android"http:…
阅读更多...
14个项目带你熟练学握AI2G免费分享|一门让你掌握人工智能能力的实操课（送工具）利用Midjourney

14个项目带你熟练学握AI2G免费分享|一门让你掌握人工智能能力的实操课（送工具）利用Midjourney

目录 1-一门让你掌握人工智能能力的实操课&#xff08;送工具&#xff09;.mp4 10-成为UI设计师&#xff1a;利用Midjourney进行页面UI设计.mp4 11-做室内设计师&#xff1a;利用Midjourney产出家居、室内设计方案.mp4 12-故事绘本&#xff1a;利用Midjouney准备课件、故事…
阅读更多...
PCIe协议之-TLP路由基础

PCIe协议之-TLP路由基础

✨前言&#xff1a; 在PCI Express (PCIe) 技术中&#xff0c;数据包的路由方式对于确保信息能够高效、准确地传送至目标设备至关重要。PCIe定义了几种路由方式&#xff0c;主要有以下几种。 &#x1f31f;地址路由&#xff08;Address Based Routing&#xff09; 这是最基本…
阅读更多...
自动驾驶系统中的数据闭环：挑战与前景

自动驾驶系统中的数据闭环：挑战与前景

目录 自动驾驶概况 1.1自动驾驶分级 1.2自动驾驶国内发展 ​1.3自动驾驶架构模型 数据闭环的意义 2.1 搜集corner case的数据 2.2 提高模型的泛化能力 2.3 驱动算法迭代 数据闭环落地的痛点及对策 3.1 数据采集和使用的合规性问题 3.2 数据确权问题 3.3 数据采集…
阅读更多...
【经验总结】 常用的模型优化器

【经验总结】 常用的模型优化器

优化器是一种用于优化模型权重和偏差的算法&#xff0c;它根据训练数据更新模型参数&#xff0c;以模型的预测结果更加准确。 1. 常见的优化器 SGD&#xff08;Stochastic Gradient Descent&#xff09;&#xff1a;SGD是一种基本的优化算法&#xff0c;它在每次迭代中随机选择…
阅读更多...
揭秘Ping32如何实现上网行为监控

揭秘Ping32如何实现上网行为监控

企业上网行为管理软件在现代企业管理中扮演着举足轻重的角色。它不仅能够监控和记录员工的上网行为&#xff0c;还能有效防止数据泄露和不当使用&#xff0c;从而保障企业的信息安全。 一、Ping32上网监控软件的具体功能包括&#xff1a; 1.网页浏览监控&#xff1a;对Chrome…
阅读更多...
jvm面试题30问

jvm面试题30问

什么是JVM的跨平台&#xff1f; 什么是JVM的语言无关性&#xff1f; 什么是JVM的解释执行 什么是JIT? JIT&#xff1a;在Java编程语言和环境中&#xff0c;即时编译器&#xff08;JIT compiler&#xff0c;just-in-time compiler&#xff09;是一个把Java的字节码&#xff08;…
阅读更多...
流量卡就该这么选，用起来性价比真的超高！

流量卡就该这么选，用起来性价比真的超高！

很多朋友会私信小编&#xff0c;让小编给大家推荐几款流量卡&#xff0c;在这里小编告诉大家&#xff0c;流量卡可以推荐&#xff0c;但是每个人的喜好不同&#xff0c;小编也忙不过来&#xff0c;今天&#xff0c;小编整理了一篇选购指南&#xff0c;大家可以参考选择&#xf…
阅读更多...
2024 B2B企业出海营销白皮书（展会篇）

2024 B2B企业出海营销白皮书（展会篇）

来源&#xff1a;科特勒&微吼 根据36氪研究院发布的《2023-2024年中国企业出海发展研究报告》中指出&#xff0c;随着全球化浪潮席卷以及中国智造的崛起&#xff0c;中国企业出海主力从过去的低附加值行业逐步扩展至信息技术、先进制造、医疗健康、汽车交通、新消费等附加…
阅读更多...
106短信平台疑难解答：为何手机正常却收不到短信？

106短信平台疑难解答：为何手机正常却收不到短信？

当您使用群发短信平台发送消息时&#xff0c;有时尽管系统提示发送成功&#xff0c;但手机却未能收到短信。这背后可能隐藏着一些不为人知的原因。 首先&#xff0c;我们要明确&#xff0c;在正常情况下&#xff0c;只要手机状态正常&#xff0c;都应该能够接收到短信。然而&am…
阅读更多...
为什么站长们喜欢使用新加坡站群服务器呢?

为什么站长们喜欢使用新加坡站群服务器呢?

为什么站长们喜欢使用新加坡站群服务器呢? 站群优化一直是站长们追逐的目标之一&#xff0c;而新加坡站群服务器则备受站长们的青睐。为什么会如此呢?让我们深入了解一下。 为什么站长们喜欢使用新加坡站群服务器呢? 站群&#xff0c;简单来说&#xff0c;就是一组相互关联…
阅读更多...
Python专题：十、字典（1）

Python专题：十、字典（1）

数据类型：字典，是一个集合性质的数据类型 字典的初始化 字典{关键字：数值} 新增元素 修改元素 字典元素访问 字典[关键字} in 操作符 字典关键字检测 字典元素遍历 ①遍历关键字
阅读更多...
Android build.prop生成过程源码分析

Android build.prop生成过程源码分析

Android的build.prop文件是在Android编译时刻收集的各种property【LCD density/语言/编译时间, etc.】&#xff1b;编译完成之后&#xff0c;文件生成在out/target/product/<board【OK1000】>/system/目录下&#xff1b;在Android运行时刻可以通过property_get()[c/c域] …
阅读更多...
深度学习论文: LightGlue: Local Feature Matching at Light Speed

深度学习论文: LightGlue: Local Feature Matching at Light Speed

深度学习论文: LightGlue: Local Feature Matching at Light Speed LightGlue: Local Feature Matching at Light Speed PDF: https://arxiv.org/pdf/2306.13643 PyTorch代码: https://github.com/shanglianlm0525/CvPytorch PyTorch代码: https://github.com/shanglianlm0525/…
阅读更多...
最新文章

Copyright @ 2022~2023