数据库索引失效

欢迎大家来到IT世界,在知识的湖畔探索吧!

索引为提升数据访问效率而建立，往往会花费额外的空间，并有使用场景的限定。

最简单的索引

欢迎大家来到IT世界,在知识的湖畔探索吧!

比较常见的索引

索引

数据库索引失效是指在数据库查询过程中，虽然已经为某些字段创建了索引，但是由于某些原因，数据库优化器选择不使用这些索引，而是采用全表扫描（Full Table Scan）的方式进行数据检索。这种情况通常会导致查询性能显著下降，因为全表扫描需要遍历整个表中的所有记录，而使用索引可以快速定位到所需的数据。索引失效可能由多种原因引起，包括：

1. 查询条件不匹配：如果查询条件中使用的字段没有被索引，或者使用了不等于（<>）、模糊查询（LIKE ‘%value%’）等操作，可 能导致索引失效。
2. 数据类型不一致：如果查询条件的数据类型与索引字段的数据类型不一致，数据库可能无法利用索引。查询条件中包含的数据类型与索引字段的数据类型不同，且数据库需要进行隐式类型转换时，也可能导致索引失效。
3. 索引选择性差：当索引列的值重复度高时（即，该索引列有很多重复值），数据库优化器可能会认为使用该索引不如直接进行全表扫描效率高，可能会选择全表扫描而不是使用索引。
4. 统计信息过时：数据库优化器依赖统计信息来决定是否使用索引，如果统计信息过时，可能导致选择不当。
5. 使用了函数或表达式：在查询中对索引字段使用函数或表达式时，索引可能会失效。例如，在WHERE子句中对索引字段使用函数或表达式，这会阻止数据库引擎使用索引。比如，SELECT * FROM table WHERE YEAR(date_column) = 2023; 这样的查询可能不会使用到date_column上的索引。
6. 复杂的查询条件：对于包含多个表连接、子查询等复杂条件的查询，数据库优化器可能难以确定最优的执行计划，从而选择不使用索引。

为了避免索引失效，可以通过优化查询语句、定期更新统计信息和合理设计数据库结构等方式来提高查询性能。具体可以采取以下措施：

• 定期分析和更新表的统计信息。
• 确保查询条件中的数据类型与索引字段的数据类型一致。
• 避免在索引字段上使用函数或表达式。
• 对于选择性较低的字段，考虑是否真的需要建立索引。
• 使用数据库提供的工具或命令检查查询计划，确保索引被正确使用。

通过以上方法，可以有效地避免或减少索引失效的情况，提高数据库查询的性能。

查询条件不匹配

假设有一个用户表 `users`，包含字段 `id` 和 `name`，并且在 `name` 字段上创建了索引。

SELECT * FROM users WHERE name LIKE '%John%';

欢迎大家来到IT世界,在知识的湖畔探索吧!

在这个查询中，使用了模糊查询（`LIKE ‘%John%’`），这会导致索引失效。因为数据库无法利用索引快速定位包含“John”的 记录，而是需要进行全表扫描。

数据类型不一致

假设在 `users` 表中，`id` 字段是整型，索引也在这个字段上。

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM users WHERE id = '123';

在这个查询中，虽然 `id` 字段有索引，但查询条件使用了字符串类型（’123’），而不是整型（123）。这可能导致数据库无法利用索引，从而导致全表扫描。

索引选择性差

假设 `users` 表有一个 `gender` 字段，并在其上创建了索引。假设表中大多数用户的性别都是“男”。数据库优化器可能会选择不使用索引，而是进行全表扫描，因为这样可能更有效率。

SELECT * FROM users WHERE gender = 'male';

统计信息过时

大多数情况下，在CUD操作之后，数据库表的统计信息会更新。对于频繁变更的表，有时候也需要检查下。以MySQL举例，查看和更新表的统计信息可以通过以下步骤进行：

• 查看统计信息：使用 `SHOW TABLE STATUS`，这个命令可以查看表的基本信息，包括行数、数据和索引大小等。

欢迎大家来到IT世界,在知识的湖畔探索吧!SHOW TABLE STATUS LIKE 'your_table_name';

• 使用 `ANALYZE TABLE`：在 MySQL 中，`ANALYZE TABLE` 命令不仅可以更新统计信息，还可以显示当前的统计信息。

ANALYZE TABLE your_table_name;

执行这个命令后，MySQL 会返回关于表的统计信息，包括索引的选择性等。

• 使用 `SHOW INDEX`：这个命令可以查看表中索引的详细信息，包括每个索引的列、唯一性、索引类型等。

欢迎大家来到IT世界,在知识的湖畔探索吧!SHOW INDEX FROM your_table_name;

• 使用 `OPTIMIZE TABLE`：`OPTIMIZE TABLE` 命令不仅会更新统计信息，还会对表进行优化，尤其是在进行大量删除或更新操作后。

OPTIMIZE TABLE your_table_name;

使用了函数或者表达式

假设在 `users` 表中，`created_at` 字段上有索引。

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM users WHERE YEAR(created_at) = 2023;

在这个查询中，对 `created_at` 字段使用了 `YEAR()` 函数，这会导致索引失效。数据库无法直接使用索引来查找相应的记录，而是需要扫描整个表。

复杂查询条件

• 索引与非索引字段组合条件：如果orders表有索引status，但是users表没有age的索引，索引会失效。

SELECT u.*, o.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'shipped' AND u.age > 30;

• 索引字段使用OR组合：使用OR也容易造成失效。如下即使categrory_id和price都有建立索引，查询执行时，也有可能会失效。

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM products WHERE category_id = 1 OR price < 100;

• 嵌套子查询：如果子查询返回的条目很多，可能会出发全表扫描

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'shipped');

总之，索引是建立了额外的数据用来加速对数据的处理效率，索引可以有多级。合理使用索引才能提高数据的处理效率。但使用的场景，导致无法加速时，全表扫描就成为一个选项。

索引

参考资料

1. An in-depth look at Database Indexing : https://www.freecodecamp.org/news/database-indexing-at-a-glance-bb50809d48bd/