浅谈SQL不走索引的几种常见情况
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">第一步、创建一个简单的表并添加几条测试数据</a></li><li><a href="#_label1">第二步、查看我们表的索引</a></li><li><a href="#_label2">第三步、通过执行计划查看我们的SQL是否使用了索引</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_0">tryp说明：</a></li><li><a href="#_lab2_2_1">不走索引的情况，例如：</a></li></ul><li><a href="#_label3">下面是几种测试例子:</a></li><ul class="second_class_ul"></ul></ul></div><p>我们写的SQL语句很多时候where条件用到了添加索引的列，但是却没有走索引，在网上找了资料，发现不是很准确，所以自己验证了一下，记一下笔记。</p><p>这里实验数据库为 MySQL（oracle也类似）。</p>
<p>查看表的索引的语句： show keys from 表名</p>
<p>查看SQL执行计划的语句（SQL语句前面添加 explain 关键字）：explain select* from users u where u.name = 'mysql测试'</p>
<p class="maodian"><a name="_label0"></a></p><h2>第一步、创建一个简单的表并添加几条测试数据</h2>
<div class="jb51code"><pre class="brush:sql;">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;
</pre></div>
<p>设置索引的字段：id、name；</p>
<p class="maodian"><a name="_label1"></a></p><h2>第二步、查看我们表的索引</h2>
<div class="jb51code"><pre class="brush:sql;"># 查看索引
show keys from users</pre></div>
<p>可以得到如下信息，其中id、name及为我们建的索引</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202506/2025061809572418.png" /></p>
<p class="maodian"><a name="_label2"></a></p><h2>第三步、通过执行计划查看我们的SQL是否使用了索引</h2>
<p>执行如下语句得到：</p>
<div class="jb51code"><pre class="brush:sql;">explain select * from users u where u.name = 'mysql测试'</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202506/2025061809572519.png" /></p>
<p>字段说明：</p>
<ul><li><p><strong>type列</strong><strong>，</strong>连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。</p></li><li><p><strong>possible_keys: </strong>表示查询时可能使用的索引。</p></li><li><p><strong>key列，</strong>使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。</p></li><li><p><strong>key_len列，</strong>索引长度。</p></li><li><p><strong>rows列，</strong>扫描行数。估算的找到所需的记录所需要读取的行数。</p></li><li><p><strong>extra列，</strong>详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。</p></li></ul>
<p>从这里可以看出，我们使用了索引，因为name是加了索引的；</p>
<p class="maodian"><a name="_lab2_2_0"></a></p><h3>tryp说明：</h3>
<ul><li>ALL: 扫描全表</li><li>index: 扫描全部索引树</li><li>range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询</li><li>ref: 使用非唯一索引或非唯一索引前缀进行的查找</li><li>eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描</li><li>const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询的表只有一行的情况下， 使用system。</li></ul>
<p class="maodian"><a name="_lab2_2_1"></a></p><h3>不走索引的情况，例如：</h3>
<p>执行语句：</p>
<div class="jb51code"><pre class="brush:xhtml;"># like 模糊查询 前模糊或者 全模糊不走索引
explain select * from users u where u.name like '%mysql测试' </pre></div>
<p><img alt="" src="https://img.jbzj.com/file_images/article/202506/2025061809572520.png" /></p>
<p>可以看出，key 为null，没有走索引。</p>
<p class="maodian"><a name="_label3"></a></p><h2>下面是几种测试例子:</h2>
<div class="jb51code"><pre class="brush:sql;"># 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 条件都是同一个索引字段，走索引
explainselect * 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 wheredate_format(upTime,'%Y-%m-%d') = '2019-07-01'
# and 语句，多条件字段，最多只能用到一个索引，如果需要，可以建组合索引
explain select * from users where id='4' and username ='JspStudy' </pre></div>
<p>做SQL优化，我们最好用 explain 查看SQL执行计划，理论不一定正确，而且不同的数据库，不同的sql语句可能有不同的结果，最好是一边测试一边优化。</p>
頁:
[1]