MySQL 索引：提升数据库性能的关键(基本使用)

走向未来
44 阅读7分钟

在数据库的世界中，索引是提升查询性能的关键。MySQL，作为广泛使用的开源关系型数据库管理系统，提供了强大的索引功能来优化数据检索。在这篇文章中，我们将探讨MySQL索引的基本概念、类型、创建方法以及最佳实践。

索引的原理

在深入讨论索引的类型和最佳实践之前，了解索引的工作原理是非常重要的。MySQL中的索引通常是基于B树（B-tree）实现的，这种数据结构允许数据以对数时间复杂度进行检索，这意味着查询速度随着数据量的增加而缓慢下降。

B树索引

B树是一种自平衡的树形数据结构，它保持数据有序，并且可以高效地进行插入、删除和搜索操作。在MySQL中，索引的数据页被组织成B树结构，每个节点可以包含多个数据项和子节点指针。B树的层数越少，查询数据所需的磁盘I/O操作就越少，查询速度就越快。

索引的查找过程

当执行一个查询操作时，MySQL首先在索引中查找对应的键值。这个过程通常涉及以下几个步骤：

  1. 1. 从根节点开始：查询从B树的根节点开始，根节点通常存储在内存中。

  2. 2. 向下遍历：根据键值，MySQL在B树中向下遍历，直到找到包含该键值的叶节点。

  3. 3. 找到数据：一旦找到包含键值的叶节点，MySQL就可以直接访问表中对应的行数据。

为什么使用B树

B树之所以被广泛用于数据库索引，是因为它具有以下优点：

  • • 平衡性：B树始终保持平衡，这意味着任何操作的最坏情况时间复杂度都是对数级别的。

  • • 高效的磁盘I/O：B树的设计减少了查找数据所需的磁盘I/O次数，这对于数据库性能至关重要。

  • • 动态调整：B树可以根据数据的插入和删除动态调整其大小和结构。

索引的存储

在MySQL中，索引数据通常存储在磁盘上，但为了提高性能，索引的根节点和其他一些关键节点可能会被缓存到内存中。这种缓存机制可以减少磁盘I/O操作，从而提高查询速度。

索引的基本概念

索引是数据库表中一个或多个列的值存储的结构，使得数据检索更快。想象一下，索引就像书的目录，它允许你快速找到特定章节，而不必一页一页地翻阅整个书籍。

MySQL索引的类型

  1. 1. 主键索引（PRIMARY KEY）：唯一标识表中的每一行，不允许重复，不允许为空。

  2. 2. 唯一索引（UNIQUE）：保证列中的所有值都是不同的。

  3. 3. 普通索引（INDEX）：最基本的索引类型，没有唯一性的限制。

  4. 4. 全文索引（FULLTEXT）：用于搜索文本中的关键字，适用于大型文本列。

  5. 5. 组合索引（Composite Index）：在两个或多个列上创建的索引，可以覆盖多个列的查询条件。

创建索引

在MySQL中，创建索引是一个简单的过程。以下是创建不同类型索引的示例：

-- 创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column_name);

-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建全文索引
ALTER TABLE table_name ADD FULLTEXT (column_name);

-- 创建组合索引
CREATE INDEX index_name ON table_name (column_name1, column_name2);

索引的最佳实践

  1. 1. 选择合适的列：在经常作为查询条件的列上创建索引。

  2. 2. 考虑列的基数：如果列中的值重复率高，索引的效果可能不佳。

  3. 3. 使用组合索引：对于经常一起使用的多个列，可以创建组合索引。

  4. 4. 定期维护索引：随着数据的增加，索引可能会变得碎片化，定期优化索引是必要的。

  5. 5. 监控索引性能：使用EXPLAIN关键字来分析查询的执行计划，了解索引的使用情况。

什么情况下索引会失效

在MySQL中，索引是提高查询性能的重要工具，但在某些情况下，索引可能会失效，导致查询性能下降。以下是一些常见的索引失效情况：

  1. 1. 联合索引不满足最左匹配原则：
  • • 联合索引遵循最左匹配原则，即查询条件必须包含联合索引的最左侧字段，否则索引会失效。例如，索引(a, b, c)，查询条件WHERE b = 1 AND c = 2不会使用索引，而WHERE a = 1 AND b = 2会使用索引。
  1. 2. 使用了SELECT *：
  • • 在查询中使用SELECT *可能导致索引失效，特别是在联合索引的情况下。明确指定需要的列可以促使MySQL使用覆盖索引，从而提高查询性能。
  1. 3. 索引列参与运算：
  • • 如果在查询条件中对索引列进行了运算（如id + 1 = 2），索引会失效。应避免在索引列上进行运算。
  1. 4. 索引列使用了函数：
  • • 在查询条件中对索引列使用函数（如SUBSTR(id_no, 1, 3) = '100'）会导致索引失效。应尽量避免在索引列上使用函数。
  1. 5. 错误的LIKE使用：
  • • 使用LIKE进行模糊查询时，如果通配符%位于条件的首部（如LIKE '%abc'），索引会失效。应尽量避免在LIKE查询中将通配符放在首部。
  1. 6. 类型隐式转换：
  • • 如果查询条件中的数据类型与索引列的数据类型不匹配，可能会导致索引失效。例如，将字符串与整数进行比较（如id_no = 1002，其中id_no为VARCHAR类型）。
  1. 7. 使用OR操作：
  • • 在查询条件中使用OR操作时，如果OR两边的条件中有一个没有索引，整个查询将不会使用索引。此外，如果OR两边同时使用范围查询（如id > 1 OR id < 80），索引也会失效。
  1. 8. 两列做比较：
  • • 即使两列都有索引，如果在查询条件中对两列数据进行比较（如id > age），索引也会失效。
  1. 9. 不等于比较：
  • • 使用不等于操作符（如<>或!=）进行比较时，索引可能会失效，特别是当查询结果集占比较大时。
  1. 10. IS NOT NULL：
  • • 使用IS NOT NULL条件时，索引会失效，而IS NULL条件则可以使用索引。
  1. 11. NOT IN和NOT EXISTS：
  • • 使用NOT IN或NOT EXISTS时，索引可能会失效，特别是当查询结果集占比较大时。
  1. 12. ORDER BY导致索引失效：
  • • 当查询条件涉及ORDER BY和LIMIT时，索引是否失效取决于具体的查询条件和MySQL版本。通常，ORDER BY多个索引字段时，索引可能会失效。
  1. 13. 参数不同导致索引失效：
  • • 当查询条件为范围查询（如>、<、>=、<=、IN等）时，如果查询结果集占全表数据比例较大，MySQL优化器可能会放弃使用索引，进行全表扫描。

了解这些索引失效的情况，可以帮助我们在设计和优化数据库时避免这些问题，从而提高查询性能。

: 15个必知的Mysql索引失效场景，别再踩坑了-腾讯云开发者社区[1]

索引的维护和优化

索引虽然可以提高查询速度，但过多的索引会影响插入、更新和删除操作的性能。因此，定期维护索引是必要的。你可以使用以下命令来优化索引：

OPTIMIZE TABLE table_name;

结论

索引是MySQL中提升查询性能的重要工具。通过合理地创建和维护索引，可以显著提高数据库的响应速度和整体性能。然而，索引的使用也需要谨慎，以避免不必要的性能开销。希望这篇文章能帮助你更好地理解和使用MySQL索引。

引用链接

[1] 15个必知的Mysql索引失效场景，别再踩坑了-腾讯云开发者社区: cloud.tencent.com/developer/a…

本文使用 文章同步助手 同步

avatar
文章
阅读
粉丝