数据库中索引的使用和注意事项

最新推荐文章于 2024-07-07 13:00:43 发布

巴啦啦能量使不出来了

最新推荐文章于 2024-07-07 13:00:43 发布

阅读量416

点赞数 5

文章标签：数据库 sql mysql sqlserver postgresql 大数据数据库开发

本文链接：https://blog.csdn.net/HelloworldCC5055/article/details/139415047

版权

一：什么时候需要加索引

首先众所周知，加索引可以显著提高查询效率

1.高频查询列：如果某个列经常出现在WHERE子句中作为筛选条件，为其创建索引可以加速查询。
-- 假设有一个users表，经常需要根据用户名查询用户信息
CREATE INDEX idx_username ON users(username);

2.关联查询的外键列：在多表联查时，被JOIN的列应该有索引，以加速数据的匹配过程。
-- 假设有orders和customers两个表，通过customer_id关联
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

3.排序和分组：如果查询中包含ORDER BY或GROUP BY子句，对排序或分组的列创建索引可以减少排序操作的成本。
-- 经常按照订单日期排序查询订单
CREATE INDEX idx_orders_order_date ON orders(order_date);

4.唯一性约束：为了保证数据的唯一性，可以创建唯一索引。
-- 确保email列的值在users表中唯一
ALTER TABLE users ADD UNIQUE INDEX idx_email_unique (email);

5.覆盖索引：如果查询只需要索引中的列而不需要回表查询其他数据，可以创建覆盖索引以进一步提高效率。
-- 假设经常需要查询用户的名字和邮箱，而不涉及其他列
CREATE INDEX idx_users_name_email ON users(name, email);

二：什么情况下索引会失效

不使用索引列进行查询：如果查询条件中没有包含任何索引列，数据库将无法利用索引来加速检索过程。
列数据类型不匹配：查询条件中的数据类型与索引列的数据类型不一致，可能导致数据库无法有效使用索引。
使用函数或表达式：在查询条件中对索引列应用函数或表达式（如 SUBSTR(column_name, 1, 3) = 'val'）会使得数据库无法直接利用索引，因为索引是基于列的原始值构建的。
LIKE操作符的不当使用：如果LIKE操作以通配符（%）开头，如 column LIKE '%search_term'，索引通常不会被使用，因为这要求对整个列进行扫描。
OR条件的使用：在使用OR连接多个条件时，如果其中一个条件没有索引或者索引不能有效覆盖所有条件，可能导致整个索引失效。特别是当OR条件跨多个列且不是所有列都有索引时。
索引列上的操作符不支持：某些操作符，如比较不等于（<>或!=）、IS NULL、NOT IN等，在特定情况下可能不会使用索引。
数据分布不均：索引列数据分布极端不均时（如大量重复值），数据库引擎可能决定全表扫描比使用索引更高效。
复合索引不完整使用：对于多列组合索引，如果查询条件没有以索引定义的最左前列开始，后面的列索引将不会被使用。
索引列含有NULL值：虽然NULL值可以被索引，但在某些数据库中，对含有NULL值的列使用等于（=）以外的操作符（如IS NULL）可能不会使用索引。
索引列上的类型转换：如果查询中对索引列进行了隐式类型转换（例如字符串与数字比较），索引可能无法应用。
超出索引范围的查询：某些类型的索引，如哈希索引或全文索引，对于超出其设计范围的查询可能不会生效。

三：如何强制走某一索引

1.对于 MySQL：

你可以使用 FORCE INDEX 来强制查询使用指定的索引。
SELECT * FROM your_table_name FORCE INDEX (index_name)
WHERE your_conditions;
将 your_table_name 替换为你的表名，index_name 替换为你希望强制使用的索引名，your_conditions 替换为你的查询条件。

2.对于 SQL Server：

在 SQL Server 中，可以使用 WITH (INDEX(index_name)) 来强制查询使用特定索引。
SELECT * FROM your_table_name WITH (INDEX(index_name))
WHERE your_conditions;
同样的，将 your_table_name 替换为你的表名，index_name 替换为你希望强制使用的索引名，your_conditions 替换为你的查询条件。

注意事项：

强制使用索引可能会导致查询性能下降，因为查询优化器通常会选择它认为最高效的执行计划。只有在明确知道查询优化器选择不当，并且手动选择的索引确实能提高性能时，才应考虑强制使用索引。
在进行这种操作之前，最好先通过 EXPLAIN 或 SQL Server 的查询计划工具分析原有的执行计划，确定是否真的需要干预。
强制使用索引应作为一种调试手段，而非长期解决方案。在生产环境中长期使用前，应当充分测试其对不同查询和数据量的影响。