sql不走索引的几种情况

深碍√TFBOYSˉ_ 2023-02-21 04:48 236阅读 0赞

总括：

type  
all  全表扫描  
index  全索引扫描  
rang   部分索引   between   >,<  
ref    非唯一索引  
eq\_ref  唯一索引  
const,system  一个匹配

肯定的走索引

union all  , between and ,<,>  
in  
null

and  最多能能一个起效

否定的不走索引

or  
not in  
not null  
!= ,    <>

我们写的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=MyISAM AUTO\_INCREMENT=11 DEFAULT CHARSET=utf8;  
   
设置索引的字段：id、name；

第二步、查看我们表的索引  
\# 查看索引  
show keys from users  
可以得到如下信息，其中id、name及为我们建的索引

![20190701152459997.png][]

第三步、通过执行计划查看我们的SQL是否使用了索引  
执行如下语句得到：

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

![20190701152821195.png][]

字段说明：

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

possible\_keys: 表示查询时可能使用的索引。

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

key\_len列，索引长度。

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

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

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

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

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

![20190701153117884.png][]  
可以看出，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'  
   
\# 使用 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执行计划。如果是oracle,并且使用的是 PLSQL工具，选中需要查询的SQL语句，按快捷键 F5 即可查看执行计划。

[20190701152459997.png]: https://img-blog.csdnimg.cn/20190701152459997.png
[20190701152821195.png]: https://img-blog.csdnimg.cn/20190701152821195.png
[20190701153117884.png]: https://img-blog.csdnimg.cn/20190701153117884.png