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

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 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_idorder_date常常一起出现在查询条件中。为此,我们可以建立组合索引:

CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

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

3.索引优化的原因

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

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

三、索引的优缺点

1.优点

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

2.缺点

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

四、什么情况下索引失效

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

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

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

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

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

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

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

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

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

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

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

6. 数据表过度索引化:

  - 在某些情况下,如果数据库表被过度索引化,即存在过多的索引,这可能会导致索引失效和性能下降。过多的索引会导致数据库在执行更新操作时需要维护和更新多个索引,从而增加了开销。此外,过多的索引还会占用额外的存储空间,并且可能会使优化器在选择最佳索引时出现混乱。

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

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

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

 

五、最左前缀(详解)

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

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

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

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

  1. 使用first_name进行查询:
SELECT * FROM students WHERE first_name = 'John';
  1. 在这个查询中,MySQL可以利用first_name列的索引部分进行快速查找。
  2. 使用first_namelast_name进行查询:
SELECT * FROM students WHERE first_name = 'John' AND last_name = 'Doe';
  1. 在这个查询中,MySQL可以利用first_namelast_name两个列的索引部分进行查找。
  2. 使用first_namelast_nameage进行查询:
SELECT * FROM students WHERE first_name = 'John' AND last_name = 'Doe' AND age = 25;
  1. 在这个查询中,MySQL可以利用整个组合索引进行查找。

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

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

SELECT * FROM students WHERE last_name = 'Doe';

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

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

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
12 0
|
21天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
|
26天前
|
存储 自然语言处理 关系型数据库
ElasticSearch索引 和MySQL索引那个更高效实用那个更合适
ElasticSearch索引 和MySQL索引那个更高效实用那个更合适
37 0
|
27天前
|
SQL 存储 关系型数据库
MySQL not exists 真的不走索引么
MySQL not exists 真的不走索引么
24 0
|
30天前
|
SQL 存储 关系型数据库
对线面试官 - 如何理解MySQL的索引覆盖和索引下推
索引下推是MySQL 5.6引入的优化,允许部分WHERE条件在索引中处理,减少回表次数。例如,对于索引(zipcode, lastname, firstname),查询`WHERE zipcode='95054' AND lastname LIKE '%etrunia%'`时,索引下推先过滤zipcode,然后在索引中应用lastname条件,降低回表需求。索引下推可在EXPLAIN的`Using index condition`中看到。
对线面试官 - 如何理解MySQL的索引覆盖和索引下推
|
15天前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
80 1
|
21天前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
|
21天前
|
缓存 关系型数据库 MySQL
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
|
12天前
|
存储 关系型数据库 MySQL
【MySQL实战笔记】 04 | 深入浅出索引(上)-02
【4月更文挑战第9天】InnoDB数据库使用B+树作为索引模型,其中主键索引的叶子节点存储完整行数据,非主键索引则存储主键值。主键查询只需搜索一棵树,而非主键查询需两次搜索,因此推荐使用主键查询以提高效率。在插入新值时,B+树需要维护有序性,可能导致数据页分裂影响性能。自增主键在插入时可避免数据挪动和页分裂,且占用存储空间小,通常更为理想。然而,如果场景仅需唯一索引,可直接设为主键以减少查询步骤。
13 1
【MySQL实战笔记】 04 | 深入浅出索引(上)-02
|
14天前
|
关系型数据库 MySQL 数据库
6. 了解过Mysql的索引嘛 ?
了解MySQL的索引类型,包括单列索引(普通、唯一、主键和全文索引)和组合索引。单列索引用于一列,如普通索引允许重复值,唯一索引和主键索引不允许,后者不允许空值。全文索引适用于特定文本字段。组合索引是多列的,遵循左前缀原则,通常推荐用于提高查询效率,除非是主键。
12 0