一:什么时候需要加索引
首先众所周知,加索引可以显著提高查询效率
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 的查询计划工具分析原有的执行计划,确定是否真的需要干预。 - 强制使用索引应作为一种调试手段,而非长期解决方案。在生产环境中长期使用前,应当充分测试其对不同查询和数据量的影响。
四:怎样避免索引失效
-
全值匹配:确保
WHERE
子句中的查询条件与索引中的列完全一致,这样可以充分利用索引进行查找。 -
最佳左前缀法则:在复合索引中,查询条件应从索引的最左侧列开始,并尽可能包含更多的左侧列,这样才能利用到索引的前缀。
-
避免在索引列上进行计算或函数操作:对索引列进行计算、函数转换或类型转换会导致数据库无法直接使用索引,从而转为全表扫描。
-
避免使用索引列参与的某些操作符:如避免在索引列上使用
LIKE
以通配符开头(如'%abc'
),这会导致索引失效。 -
使用覆盖索引:确保查询中所涉及的所有列都已经包含在索引中,这样数据库可以直接从索引中获取数据而无需回表查询,提高查询效率。
-
避免范围查询右边的列使用索引:在有范围查询(如
BETWEEN
,<
,>
)的情况下,索引中范围条件右侧的列不会被使用到。 -
限制使用SELECT *:尽量列出查询中真正需要的字段,而非使用
SELECT *
,这样有助于覆盖索引的使用。 -
使用LIMIT优化单一结果查询:如果你知道查询只会返回一行数据,使用
LIMIT 1
可以提前终止查询,提高效率。 -
确保数据类型一致:在比较操作中,两边的数据类型应该匹配,避免数据库进行隐式类型转换,这可能导致索引失效。
-
正确使用索引前缀:对于大文本字段,如果创建全文索引不实际,可以考虑使用索引前缀,但要合理选择前缀长度以平衡查询效率和索引大小。
五:其他索引问题
1.使用索引后where条件中有1=1,索引是否还生效
生效
(如有其他问题,欢迎留言,后续会补充更新,不定时更新数据库知识分享,多多点赞关注呦)