MySQL索引简介(包含索引优化,索引失效,最左前缀简洁版)

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: MySQL索引简介(包含索引优化,索引失效,最左前缀简洁版)

一、索引的基本概念


1.什么是索引


  索引是一种数据结构,用于快速查找和访问数据库中的数据。它类似于书本的目录,可以帮助数据库管理系统快速定位到存储数据的位置。通过创建索引,我们可以加快数据库的查询速度并提高系统的性能。索引可以基于一个或多个列,在数据库表中对数据进行逻辑和物理排序,使得查询操作更加高效。


2.索引类型


  1. 主键索引(PRIMARY KEY):每张表只能有一个主键,确保记录的唯一性。
  2. 唯一索引(UNIQUE):确保数据的每一行在索引列上是唯一的。
  3. 普通索引(INDEX):基本的索引类型,没有唯一性的要求,用于加速查询。
  4. 全文索引(FULLTEXT):用于对文本字段进行索引,加快文本搜索操作。
  5. 组合索引(Composite Index):多列值组成一个索引,用于多条件查询。


3.索引原理


MySQL中最常用的索引类型是B-Tree索引。B-Tree索引非常适合处理大量数据的访问。它保持数据有序,允许搜索、插入、删除和顺序访问数据。


4.举例说明


假设我们有一张订单表orders,字段包括order_idcustomer_idorder_date

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT,
  customer_id INT,
  order_date DATE,
  PRIMARY KEY (order_id)
);


如果我们经常按照customer_id来查询,添加一个索引会是一个很好的选择:

CREATE INDEX idx_customer_id ON orders (customer_id);


现在,如果执行下面的查询,MySQL就能利用索引快速找到数据。

SELECT * FROM orders WHERE customer_id = 1001;


二、索引的优化与应用


1.索引的优化


索引虽然可以提高查询性能,但是也不是越多越好。不恰当的索引可能会导致性能问题,以下是几种优化策略:


  1. 选择适当的索引列:选择具有高选择性的列作为索引,这样的列包含很多唯一的值。
  2. 避免冗余索引:如果一个索引是另一个索引的前缀,则可能是多余的。
  3. 使用短索引:对于字符串类型的字段,使用前缀索引可以节省空间和提升性能。
  4. 索引维护:删除不再使用或低效的索引,重新构建碎片化的索引。


2.优化示例


假设orders表现在非常大,我们注意到customer_id和order_date常常一起出现在查询条件中。为此,我们可以建立组合索引:

CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

这样,任何同时涉及customer_id和order_date的查询都可以利用这个索引。


3.索引优化的原因


优化索引的原要目的包括:


  1. 提高查询效率:使用适当的索引可以减少查询的响应时间。
  2. 减少资源使用:有效的索引可以减少CPU和IO资源的使用。
  3. 提升写操作性能:减少不必要的索引可以加快插入和修改操作。


三、索引的优缺点


1.优点


  • 查询效率提升:这是索引最显著的优点,特别是在大数据量下。
  • 排序和分组加速:索引可以加快ORDER BY和GROUP BY操作。


2.缺点


  • 降低更新表的性能:插入、删除、修改等写操作会导致索引也需要被更新,这将消耗额外的时间。
  • 占用额外的空间:索引虽然能提高读取效率,但同时也会占用磁盘空间。
  • 索引失效:在某些情况下,比如查询中使用了函数或计算,索引可能会失效,导致性能下降。

四、什么情况下索引失效


 索引可能会失效的情况有很多。下面列举了一些常见的情况,并详细解释了每种情况下为什么索引会失效:


1. 不使用索引列进行查询:

 - 当查询条件没有包含索引列时,数据库可能会选择忽略索引而进行全表扫描。这通常发生在查询使用的是表达式、函数或其他操作,而不是直接使用索引列的原始值。因为索引只会为存储的原始值建立索引,而不会为计算结果或表达式的值建立索引。


2. 使用函数或表达式对索引列进行操作:

- 当在查询中对索引列使用函数或表达式时,数据库可能无法使用索引进行查找。例如,如果在索引列上使用`LOWER`函数将值转换为小写进行比较,索引将无法提供有效的匹配。这是因为索引只存储原始值,而不存储函数或表达式的结果。


3. 索引列上存在类型转换:

- 如果查询中的条件需要将索引列进行类型转换,例如将字符串转换为数字进行比较,数据库可能无法使用索引。类型转换可能导致无法利用索引存储的排序顺序,从而使索引失效。


4. 数据不均匀分布或数据重复性高:

- 当数据在索引列上分布不均匀或存在大量重复值时,索引可能会失效。对于数据不均匀分布的情况,如果查询涉及到的数据存储在索引的某一部分,而其他部分几乎没有被使用,索引将无法提供有效的筛选。对于存在大量重复值的情况,索引可能无法准确地缩小查询范围。


5. 复合索引未按照最佳顺序使用:

  - 当使用复合索引(多列组合的索引)时,查询中的条件未按照最佳顺序使用该复合索引,可能导致索引失效。复合索引的效果通常取决于索引列的顺序。如果查询没有按照索引列的顺序进行筛选,数据库可能无法有效使用索引。


6. 数据表过度索引化:  

- 在某些情况下,如果数据库表被过度索引化,即存在过多的索引,这可能会导致索引失效和性能下降。过多的索引会导致数据库在执行更新操作时需要维护和更新多个索引,从而增加了开销。

此外,过多的索引还会占用额外的存储空间,并且可能会使优化器在选择最佳索引时出现混乱。


7. 数据库统计信息过期:

- 数据库依赖统计信息来确定查询优化器和索引使用情况。如果统计信息过期或不准确,可能导致索引失效。统计信息包括数据分布、索引列的基数(唯一值的数量)以及索引的选择性等。如果统计信息不准确,查询优化器可能会做出错误的决策,导致索引选择不当。


总之,为了保持索引的有效性,需要综合考虑查询条件、数据分布、数据类型、查询计划和统计信息等因素,在设计和使用索引时进行细致的权衡和优化。


五、最左前缀(详解)


我们以一个示例来说明最左前缀原则:


假设我们有一张学生表(students),包含以下字段:student_id、first_name、last_name和age。我们希望针对first_name、last_name和age字段创建一个组合索引。

CREATE INDEX idx_student_name_age ON students (first_name, last_name, age);

遵循最左前缀原则,这个组合索引可以在以下查询中被有效利用:


  1. 使用first_name进行查询:
SELECT * FROM students WHERE first_name = 'John';

在这个查询中,MySQL可以利用first_name列的索引部分进行快速查找。


    2.使用first_namelast_name进行查询:

SELECT * FROM students WHERE first_name = 'John' AND last_name = 'Doe';

在这个查询中,MySQL可以利用first_namelast_name两个列的索引部分进行查找。


   3.使用first_namelast_nameage进行查询:

SELECT * FROM students WHERE first_name = 'John' AND last_name = 'Doe' AND age = 25;

在这个查询中,MySQL可以利用整个组合索引进行查找。


但是,最左前缀原则也意味着当我们只使用索引的后续列或中间列时,索引将不会被有效利用。


例如,如果只使用last_name进行查询:

SELECT * FROM students WHERE last_name = 'Doe';

   虽然存在组合索引 idx_student_name_age,但由于查询中没有使用最左边的列 first_name,MySQL将无法使用这个索引,并且必须执行全表扫描来查找匹配的记录。


综上所述,最左前缀原则告诉我们,在创建组合索引时,应该根据查询频率和查询的列顺序来选择最适合的组合索引。将最常用的列或特定查询条件置于索引的最左边,以确保索引能够最大限度地被利用,提高查询的性能和效率。


一句话来说 组合索引就是遵从了最左前缀,利用索引中最左边的字段来触发索引,这样的字段称为最左前缀。  


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
14天前
|
JSON 关系型数据库 MySQL
✅MySQL用了函数到底会不会导致索引失效
MySQL 8.0 引入了函数索引,打破了传统观念,允许在索引中使用函数,提升查询性能。通过创建基于表达式的索引,如 `CONCAT`、`SUBSTRING_INDEX`、`YEAR`、`MONTH` 等,可以优化涉及这些函数的查询。虽然提高了某些查询速度,但也会增加数据维护成本。应谨慎使用,确保表达式确定且适用于常见查询模式。示例包括基于字符串、日期、数学运算和JSON属性的索引。
✅MySQL用了函数到底会不会导致索引失效
|
14天前
|
SQL 安全 关系型数据库
MySQL的binlog日志的简介与查看
MySQL的binlog日志的简介与查看
26 4
|
5天前
|
存储 关系型数据库 MySQL
如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
|
15天前
|
关系型数据库 MySQL 数据库
MySQL索引的类型与优化方法
MySQL索引的类型与优化方法
|
15天前
|
存储 关系型数据库 MySQL
MySQL索引设计原则与优化策略
MySQL索引设计原则与优化策略
|
15天前
|
存储 关系型数据库 MySQL
MySQL删除索引的方法与注意事项
MySQL删除索引的方法与注意事项
|
20天前
|
存储 关系型数据库 MySQL
MySQL 索引优化:深入探索自适应哈希索引的奥秘
MySQL 索引优化:深入探索自适应哈希索引的奥秘
|
11天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
9天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
976 6
Mysql 数据库主从复制
|
9天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。