我们写的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及为我们建的索引
第三步、通过执行计划查看我们的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说明:
ALL: 扫描全表
index: 扫描全部索引树
range: 扫描部分索引,索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、等的查询
ref: 使用非唯一索引或非唯一索引前缀进行的查找
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
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'
# 使用 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 即可查看执行计划。