前言

索引可以显著提高查询性能,但也会增加写操作的开销(如插入、更新和删除),选择合适的索引类型和列非常重要,以平衡查询性能和存储开销,定期分析和优化索引,以确保数据库性能的持续优化。

索引类型

  • 主键索引(Primary Key Index)
    每个 InnoDB 表必须有一个主键,主键索引是唯一的且不允许为 NULL;
    InnoDB 使用聚簇索引(Clustered Index)来存储主键索引,这意味着数据行实际存储在主键索引的叶节点中
  • 唯一索引(Unique Index)
    唯一索引确保索引列中的所有值都是唯一的;
    可以有多个唯一索引,但每个唯一索引列中的值必须是唯一的
  • 普通索引(Non-Unique Index 或 Secondary Index)
  • 全文索引(Full-Text Index)
    全文索引用于全文搜索,适用于大文本字段(如 TEXT 或 VARCHAR
  • 空间索引(Spatial Index)
    空间索引用于地理数据类型(如 GEOMETRY
  • 前缀索引(Prefix Index)
    前缀索引允许对字符串列的前 N 个字符进行索引;
    适用于长字符串列,可以减少索引大小,但可能会降低索引的选择性
  • 复合索引(Composite Index 或 Multiple-Column Index)
    复合索引是对多个列进行索引;
    适用于需要在多个列上进行查询的情况;

索引的数据结构和存储方式

B-树和 B+树的区别

  1. 数据存储位置
    • B 树:数据存储在所有节点中(包括内部节点和叶子节点)。
    • B+ 树:数据只存储在叶子节点中,内部节点只存储键值用于索引。
  2. 搜索效率
    • B 树:搜索时可能在内部节点找到目标数据,路径较短。
    • B+ 树:搜索时总是需要访问叶子节点,路径较长,但叶子节点形成链表,范围查询效率高。
  3. 范围查询
    • B 树:范围查询需要在树中进行多次搜索。
    • B+ 树:叶子节点形成链表,范围查询效率高,只需在链表中遍历。
  4. 空间利用率
    • B 树:内部节点和叶子节点都存储数据,空间利用率较高。
    • B+ 树:内部节点只存储键值,叶子节点存储数据,空间利用率较低。

B+ 树分为叶子节点和非叶子节点,非叶子节点只存放索引的值,叶子节点存放索引的值和主键,而且每个叶子节点的末尾都有一个指针指向下一个叶子节点的位置。

多列索引

选择合适的索引列顺序

多列索引的顺序应该结合where 查询条件来确定,根据最左匹配原因,比如 where a=1 and b=1 order by c asc,则索引列的顺序应该是 a, b, c

多列索引记录排序规则

上面的索引会根据字符或数字顺序排列,a 相关的情况下按照 b 排序,b 相同的情况下再按照 c 排序,通过这种方式来确定所有记录的顺序。

是否需要为每一列都创建索引

过多的索引会有以下问题

  1. 磁盘空间占用:每个索引都会占用额外的磁盘空间,过多的索引会显著增加数据库的存储需求。
  2. 写操作性能下降:每次插入、更新或删除操作都需要维护所有相关的索引,过多的索引会导致写操作性能下降。
  3. 查询优化复杂性:过多的索引可能会使查询优化器在选择最佳索引时变得复杂,反而可能导致次优的查询计划。

选择合适的索引应该遵循以下原则

  1. 分析查询模式:根据实际的查询模式和业务需求来设计索引。重点优化那些频繁使用的查询。
  2. 复合索引:对于涉及多个列的查询,使用复合索引(多列索引)可以更有效地优化查询性能。
  3. 选择性高的列:优先在选择性高的列上创建索引。选择性高意味着该列的值较为唯一,索引的效果更好。
  4. 避免冗余索引:避免在相同或相似的列组合上创建多个索引,这会导致不必要的开销。

最左匹配原则

最左匹配原则是索引(特别是复合索引)在查询优化过程中使用的一条重要规则。它规定了复合索引在查询中如何被利用。简单来说,复合索引只有在查询条件中包含了从最左边开始的一个或多个列时,才能被有效利用。原理主要与 B-tree 索引的结构和查询优化有关,在 B-tree 中,查找操作是从根节点开始,逐层向下查找,直到找到目标值或到达叶子节点。由于 B-tree 是有序的,最左边的列(即索引的第一列)决定了如何在树中导航。

查看 B 树的增删查改动态图可以使用这个工具或者观看下面的动画

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

https://www.youtube.com/watch?v=K1a2Bk8NrYQ&ab_channel=SpanningTree

当你创建一个多列索引时,MySQL 只会在查询中使用索引的最左边的列进行匹配。例如,如果你有一个复合索引 (column1, column2),那么在查询中,如果你只使用 column1,或者同时使用 column1column2,MySQL 会使用这个索引。如果只使用 column2,则不会使用索引。

对于 LIKE 操作符,最左匹配原则也适用。如果你在 LIKE 查询中使用了通配符 %,并且它位于字符串的开头,MySQL 将无法使用索引。位于字符串的结尾,则可以使用索引。

聚簇索引

聚簇索引(Clustered Index)是一种特殊类型的索引,它决定了数据表中数据的物理存储顺序。每个表只能有一个聚簇索引,因为数据行本身只能按照一种顺序存储。聚簇索引通常用于主键列,但也可以用于其他列。

  1. 物理排序:数据表中的行按照聚簇索引的键值进行物理排序。这意味着数据行在磁盘上的存储顺序与索引键的顺序一致。
  2. 唯一性:聚簇索引的键值必须是唯一的。如果键值不是唯一的,数据库系统会自动添加一个唯一标识符(如行号)来确保唯一性。
  3. 高效的范围查询:由于数据行按照索引键排序,范围查询(如 BETWEEN<> 等)非常高效。
  4. 每个表只能有一个聚簇索引:因为数据行只能按照一种顺序存储,所以每个表只能有一个聚簇索引。

索引覆盖

索引覆盖(Covering Index)是指一个索引包含了查询所需的所有列,从而使得数据库可以直接从索引中获取数据,而不需要回表(访问实际的数据表)。这减少了 I/O 操作,提高了查询速度。

回表

在使用非聚簇索引进行查询时,数据库引擎需要访问实际的数据表来获取索引中未包含的列的数据。

索引和锁

索引和锁在数据库操作中密切相关,索引可以影响锁的粒度和锁的持有时间,从而影响并发性能和数据一致性。

  1. 索引提高查询性能:索引可以显著提高查询性能,从而减少锁的持有时间。例如,在没有索引的情况下,数据库可能需要对整个表加锁并进行全表扫描,而有了索引后,只需要对相关的索引页和数据行加锁。
  2. 索引减少锁争用:索引可以减少锁争用。例如,在有索引的情况下,数据库可以使用行锁而不是表锁,从而允许更多的并发操作。
  3. 索引影响锁的粒度:索引的存在可以影响锁的粒度。例如,使用聚簇索引时,数据库可能会对数据页加锁,而使用非聚簇索引时,数据库可能会对索引页和数据行加锁。

假设有一个表 employees,包含以下列:

  • employee_id
  • first_name
  • last_name
  • hire_date

我们在 employee_id 列上创建了一个聚簇索引,在 last_name 列上创建了一个非聚簇索引。

CREATE CLUSTERED INDEX idx_employee_id ON employees (employee_id);
CREATE INDEX idx_last_name ON employees (last_name);

执行查询,在有索引的情况下,数据库引擎会使用 idx_last_name 索引快速找到所有 last_name 为 ‘Smith’ 的记录,并对相关的数据行加锁。这比对整个表加锁并进行全表扫描要高效得多。

SELECT * FROM employees WHERE last_name = 'Smith';

未使用的索引

未使用的索引是指在数据库中存在但很少或从未被查询使用的索引。这些索引可能是由于历史原因、错误的设计或业务需求变化而产生的。未使用的索引不仅占用存储空间,还可能对数据库性能产生负面影响。

可以使用以下查询来识别未使用的索引:

SELECT 
    t.table_schema,
    t.table_name,
    t.index_name,
    t.non_unique,
    t.seq_in_index,
    t.column_name,
    t.collation,
    t.cardinality,
    t.sub_part,
    t.packed,
    t.nullable,
    t.index_type,
    t.comment,
    t.index_comment
FROM 
    information_schema.statistics t
LEFT JOIN 
    information_schema.tables i 
    ON t.table_schema = i.table_schema 
    AND t.table_name = i.table_name
LEFT JOIN 
    information_schema.key_column_usage k 
    ON t.table_schema = k.table_schema 
    AND t.table_name = k.table_name 
    AND t.index_name = k.constraint_name
WHERE 
    t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
    AND k.constraint_name IS NULL
ORDER BY 
    t.table_schema, t.table_name, t.index_name;

维护索引

重建索引

  • 原因:随着数据的插入、更新和删除,索引可能会变得碎片化,导致查询性能下降。
  • 方法:定期重建索引可以减少碎片化,提高查询性能。

更新统计信息

  • 原因:数据库引擎使用统计信息来生成查询执行计划。过时的统计信息可能导致次优的查询计划。
  • 方法:定期更新统计信息可以确保查询优化器生成高效的查询计划。

MySQL 会自动更新统计信息,但可以通过 ANALYZE TABLE 命令手动触发更新

ANALYZE TABLE table_name;

删除未使用的索引

维护表

  • 表分区
  • 表压缩
  • 表清理
  • 表备份和恢复

NULL 值

  • 比较操作NULL 值在比较操作中表现特殊。任何与 NULL 的比较(如 =<><> 等)结果都是 NULL,而不是 TRUEFALSE。这意味着你需要使用 IS NULLIS NOT NULL 来检查 NULL
  • 逻辑操作:在逻辑操作中,NULL 值会导致三值逻辑(TRUE、FALSE、UNKNOWN),这可能会使查询结果变得复杂和难以预测。
  • 索引选择性NULL 值可能会影响索引的选择性,从而影响查询性能。索引选择性越高,索引的效率越高。如果列中有大量的 NULL 值,索引的选择性会降低。
  • 唯一索引:虽然唯一索引允许 NULL 值,但多个 NULL 值被视为不同的值,这可能会导致意外的结果。聚合结果:大多数聚合函数(如 SUMAVGCOUNT 等)会忽略 NULL 值,这可能会导致结果不准确。
  • 默认值处理:在插入数据时,如果没有提供某列的值且该列允许 NULL,则会插入 NULL。这可能会导致意外的结果。如果希望某列有默认值,可以在列定义中指定默认值。
  • 代码复杂性:在应用程序代码中处理 NULL 值可能会增加复杂性。需要额外的逻辑来检查和处理 NULL 值,避免潜在的错误。