1. 什么是索引

索引是一种用于提高数据库查询性能的数据结构，它类似于书籍的目录，可以帮助数据库快速地定位到数据记录。索引通常是一个单独的数据结构，存储了某个列或多个列的值与对应数据行的物理存储位置之间的映射关系。

在数据库中，当执行查询语句时，如果没有索引支持，数据库系统可能需要逐行扫描整个表来找到满足查询条件的数据，这会消耗大量的时间和资源。而通过使用索引，数据库系统可以快速定位到满足查询条件的数据行，从而大大提高查询性能。

在MySQL中，索引的实现方式有两种：Hash和B+Tree。

2. 索引的分类

索引通常是在表的某个列或多个列上创建的，常见的索引类型包括：

  • 单列索引： 在单个列上创建的索引，用于加速基于该列的查询操作。
  • 组合索引： 又称联合索引在多个列上创建的索引，用于加速基于这些列的组合查询操作。组合索引可以提高多列查询的性能，但也需要注意索引的顺序和选择，以确保最佳的查询效率。最左匹配原则是组合索引优化的核心原则之一，它指的是在使用组合索引进行查询时，查询条件中的列必须从索引的最左侧列开始，按照创建索引时的顺序逐一匹配。只有在查询条件中使用了索引的最左侧列，索引才能被利用。
  • 唯一索引： 确保索引列中的值是唯一的，即索引列的值不允许重复。唯一索引通常用于加速对唯一值的查询，例如主键列或唯一约束列。
  • 主键索引： 是一种特殊的唯一索引，用于标识表中的唯一记录。主键索引通常是表的主键列上创建的索引，用于加速对主键列的查询和数据访问。
  • 全文索引： 用于全文搜索，可以加速基于文本内容的查询操作。全文索引通常用于搜索引擎或需要进行文本搜索的应用场景。

3. 索引的优缺点

使用索引，可以：

  • 提升查询性能：索引可以帮助数据库引擎快速地定位到符合条件的数据行，从而减少需要扫描的数据量，特别是在处理大型数据表时。
  • 增加数据完整性和一致性：唯一索引和主键索引可以确保索引列中的值是唯一的，从而增加数据的完整性和一致性。通过唯一索引和主键索引，可以有效防止重复数据的插入，确保数据的唯一性。
  • 提高分组和排序操作的效率：对于涉及排序和分组的查询操作，索引可以加速这些操作的执行。通过使用索引，数据库系统可以更快地完成排序和分组操作，从而减少排序和分组的时间开销。
  • 提升多表联合查询的效率：索引不仅可以提高单表查询的速度，还可以提高多表联合查询的效率。在多表连接查询中，索引可以帮助数据库引擎避免全表扫描，从而提高查询性能。

尽管索引有以上的优点，但索引的使用也会导致：

  • 占用物理空间：索引需要占用额外的存储空间，因为索引本身是一种数据结构，需要存储索引列的值与对应数据行的映射关系。对于大型数据表来说，索引可能会占用大量的存储空间，增加数据库的存储成本。
  • 创建和维护索引耗时：创建索引和维护索引需要耗费时间和资源。随着数据量的增加，创建和维护索引所需的时间也会相应增加。特别是在大型数据库中，索引的设计和维护可能会成为一项繁重的工作，需要进行合理的规划和管理。
  • 增加数据更新的开销：当对索引列进行数据更新（如插入、更新、删除）时，数据库系统需要更新索引，这可能会增加数据更新的开销。特别是对于频繁更新的表来说，索引的维护成本可能会比较高。
  • 查询优化失效：如果索引设计不当，可能会导致查询优化失效。例如，如果创建了过多的索引或不必要的索引，可能会导致数据库系统选择错误的查询执行计划，从而降低查询性能。

4. 索引的使用场景

索引是数据库中用于提高查询性能的重要工具，可以在多种场景下发挥作用。下面是一些常见的索引使用场景：

  1. 频繁查询的列：对于经常被查询的列，特别是在WHERE、ORDER BY、GROUP BY和JOIN等操作中使用的列，可以考虑创建索引。通过为这些列创建索引，可以加速相关查询的执行，提高查询性能。
  2. 唯一性约束的列：对于需要确保唯一性约束的列，如主键列或唯一约束列，通常需要创建唯一索引。唯一索引可以确保索引列中的值是唯一的，从而保证数据的完整性和一致性。
  3. 外键关联的列：对于外键关联的列，通常需要创建索引以提高查询性能。外键列经常用于关联多个表之间的数据，通过为外键列创建索引，可以加速关联查询的执行。
  4. 范围查询的列：对于需要进行范围查询的列，如日期范围、数值范围等，可以考虑创建索引。索引可以加速范围查询的执行，提高查询效率。
  5. 经常使用的列：对于经常被查询的列，无论是单列查询还是组合查询，都可以考虑创建索引。通过为这些列创建索引，可以加速相关查询的执行，提高系统的响应速度。
  6. 连接操作的列：对于经常用于连接操作的列，如JOIN操作中的连接列，可以考虑创建索引。索引可以加速连接操作的执行，减少连接操作的时间开销。
  7. 全文搜索的列：对于需要进行全文搜索的列，可以考虑创建全文索引。全文索引可以加速全文搜索操作的执行，提高搜索效率。

除了上面的提及的，创建索引的列的必须拥有较高的区分度，区分度越高，索引的查询效率就越高。例如在设计存放个人信息的数据表时，使用手机号或者身份证号作为索引的会比使用姓名的查询效率高很多，因为全国范围内手机号和身份证号都是唯一的，但重名的概率会很高。

5. 操作示例

-- 查看当前表中的索引
SHOW INDEX FROM t_student;

-- 创建索引
CREATE INDEX idx_name ON t_student(name);

-- 查询索引是否生效
EXPLAIN SELECT * FROM t_student WHERE `name` = 'zhangsan';

-- 删除索引
DROP INDEX idx_name ON t_student;

-- 创建唯一索引
CREATE UNIQUE INDEX idx_card_no ON t_student(card_no);

-- 创建主键索引
ALTER TABLE t_student ADD PRIMARY KEY(id);

/*
	创建组合索引
	当查询时需要将多列组合起来进行查询时可以考虑创建组合索引；
	创建时应将区分度高的列放在前面；
    最左匹配原则是组合索引优化的核心原则之一，它指的是在使用组合索引进行查询时，查询条件中的列必须从索引的最左侧列开始，按照创建索引时的顺序逐一匹配。
    只有在查询条件中使用了索引的最左侧列，索引才能被利用。
*/
CREATE INDEX idx_name_city ON t_student(name,city);
孟斯特

声明：本作品采用署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)进行许可，使用时请注明出处。

Author: mengbin

blog: mengbin

Github: mengbin92

cnblogs: 恋水无意

腾讯云开发者社区：孟斯特