SQL不走索引的几种常见情况

本文深入探讨了在MySQL中如何有效利用索引提升SQL查询效率,通过实例演示了各种SQL语句下索引的使用情况,包括like模糊查询、or条件、in/not in、is null/is not null、!=/<>以及隐式转换和函数运算等场景,并提供了优化建议。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

我们写的SQL语句很多时候where条件用到了添加索引的列,但是却没有走索引,在网上找了资料,发现不是很准确,所以自己验证了一下,记一下笔记。

这里实验数据库为 MySQL(oracle也类似)。

查看表的索引的语句: show keys from 表名

查看SQL执行计划的语句(SQL语句前面添加 explain 关键字):explain select* from users u where u.name = 'mysql测试'

 

第一步、创建一个简单的表并添加几条测试数据

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `upTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pk_users_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

设置索引的字段:id、name;

第二步、查看我们表的索引

# 查看索引
show keys from users

可以得到如下信息,其中id、name及为我们建的索引

第三步、通过执行计划查看我们的SQL是否使用了索引

执行如下语句得到:

explain select * from users u where u.name = 'mysql测试'

字段说明:

  • type列连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。

  • possible_keys: 表示查询时可能使用的索引。

  • key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。

  • key_len列,索引长度。

  • rows列,扫描行数。估算的找到所需的记录所需要读取的行数。

  • extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。

从这里可以看出,我们使用了索引,因为name是加了索引的;

tryp说明:

  1. ALL: 扫描全表
  2. index: 扫描全部索引树
  3. range: 扫描部分索引,索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询
  4. ref: 使用非唯一索引或非唯一索引前缀进行的查找
  5. eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  6. const, system: 单表中最多有一个匹配行,查询起来非常迅速,例如根据主键或唯一索引查询。system是const类型的特例,当查询的表只有一行的情况下, 使用system。

不走索引的情况,例如:

执行语句:

# like 模糊查询 前模糊或者 全模糊不走索引
explain select * from users u where u.name like '%mysql测试' 

可以看出,key 为null,没有走索引。

下面是几种测试例子:

# like 模糊查询 前模糊或者 全模糊不走索引
explain select * from users u where u.name like '%mysql测试' 

# or 条件不走索引,只要有一个条件字段没有添加索引,都不走,如果条件都添加的索引,也不一定,测试
的时候发现有时候走,有时候不走,可能数据库做了处理,具体需要先测试一下
explain select * from users u where u.name = 'mysql测试' or u.password ='JspStudy'

# or 条件都是同一个索引字段,走索引
explain  select * from users u where u.name= 'mysql测试' or u.name='333'

# 使用 union all 代替 or 这样的话有索引例的就会走索引
explain
select * from users u where u.name = 'mysql测试' 
union all
select * from users u where u.password = 'JspStudy'

# in 走索引
explain select * from users u where u.name in ('mysql测试','JspStudy')

# not in 不走索引
explain select * from users u where u.name not in ('mysql测试','JspStudy')

# is null 走索引
explain select * from users u where u.name is null 

# is not null  不走索引
explain select * from users u where u.name is not null 

# !=、<> 不走索引
explain select * from users u where u.name <> 'mysql测试'

# 隐式转换-不走索引(name 字段为 string类型,这里123为数值类型,进行了类型转换,所以不走索引,改为 '123' 则走索引)
explain select * from users u where u.name = 123

# 函数运算-不走索引
explain select *  from users u where  date_format(upTime,'%Y-%m-%d') = '2019-07-01'
# and 语句,多条件字段,最多只能用到一个索引,如果需要,可以建组合索引
explain select * from users where id='4' and username ='JspStudy' 

做SQL优化,我们最好用 explain 查看SQL执行计划,理论不一定正确,而且不同的数据库,不同的sql语句可能有不同的结果,最好是一边测试一边优化。

导致数据库索引失效的常见情况包括: 1. **使用`LIKE`查询时以`%`开头**,例如`LIKE '%abc'`,这种情况下无法利用索引进行快速匹配[^1]。 2. **使用`OR`关键字**,如果`OR`条件中存在未加索引的列,则整个查询可能会使用索引。即使部分条件有索引,只要有一个条件没有索引,也可能导致索引失效。为确保索引生效,`OR`条件中的每个列都应加上索引[^2]。 3. **对索引字段进行运算或函数操作**,例如`WHERE id + 1 = 10`,这会阻止数据库直接使用索引查找数据[^1]。 4. **使用等于操作符**如`!=`、`<>`或者`NOT IN`等,这些操作通常会导致全表扫描而非使用索引[^1]。 5. **多列复合索引的使用当**,如果查询中没有使用复合索引的第一列(最左前缀原则),则该索引可能会被使用[^2]。 6. **类型转换问题**,当查询条件的数据类型与索引列的数据类型一致时,数据库可能会进行隐式类型转换,从而导致索引失效[^4]。 7. **范围查询影响后续条件**,在`WHERE`子句中,范围查询(如`>`、`<`)之后的条件通常无法使用索引,因此建议将范围条件放在最后[^4]。 8. **IS NULL 或 IS NOT NULL 操作**,某些情况下使用`IS NULL`或`IS NOT NULL`可能导致索引失效,尤其是`IS NOT NULL`和`NOT LIKE`操作[^4]。 9. **优化器选择全表扫描**,如果MySQL估计使用全表扫描比使用索引更快,它会选择使用索引,这种情况通常发生在小表或高重复值的情况下。 ### 示例:避免索引失效的SQL写法 ```sql -- 正确使用索引的方式 SELECT * FROM users WHERE name LIKE 'John%'; SELECT * FROM orders WHERE order_date > '2023-01-01'; -- 错误写法,可能导致索引失效 SELECT * FROM users WHERE name LIKE '%John'; SELECT * FROM orders WHERE order_id + 1 > 100; SELECT * FROM products WHERE price != 100; ``` ### 总结 为了避免索引失效,编写SQL语句时应尽量遵循最佳实践,如避免对索引列进行运算、合理使用复合索引、注意数据类型一致性等。同时,可以使用`EXPLAIN`命令分析SQL执行计划,确认是否有效利用了索引。 ---
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值