explain分析后的情况解析:
一、哪些列上不适合创建索引?
在dept表中添加上索引
//添加主键索引
alter table dept add primary key(deptno);
//创建复合索引,dname是左边列,loc是右边列
create index my_index on dept(dname,loc);
1. 在较为频繁的作为查询条件的字段应该创建索引。比如:
select * from emp where empno = 123456
2. 唯一性太差的字段不适合创建单独的索引,及时频繁作为查询条件。 比如:
select * from user where sex = '男'
3. 更新非常频繁的字段不适合创建索引
select * from emp where logincount=1;//登录的次数
4. 不会出现在where字句中的字段不适合创建索引、
二、满足一下条件的可以创建索引
1. 在where字句中作为查询条件经常出现的
2. 字段内容多样的,不能如性别,只分:男和女
3. 字段内容不能频繁更新的
三、下面几种情况有可能使用到索引
1. 对于创建的多列索引,只要查询条件使用了最左边的列,这个索引一般就会使用,最左前缀原则
2. 对于使用like的查询,如果是 like %aaa%与like %aaa,是不会使用索引的,而 like aaa%会使用到索引
四、下面的几种情况将不会使用索引
1. 如果条件中有or,即使其中有一方条件带索引也不会使用索引,除非是or两边的条件字段都有索引,但是建议尽量避免使用or
比如:
explain select * from dept where dname='aaa' or loc='ccc'\G
因为loc处于索引右边的位置,它没有使用索引,所以这条sql没有使用索引,
如果换成deptno字段就可使用索引,因为deptno字段之前有添加上主键索引
explain select * from dept where dname='aaa' or deptno= 235\G
2. 对于多列索引,不是使用的第一部分,则不会使用索引,比如:
//此sql不会用到索引
select * from dept where loc='aaaa';
分析得到:
explain select * from dept where loc='aaaa'\G
3. like查询中以 % 或 _ 开头的查询,都不会使用索引
4. 如果列类型是字符串,一定要将数据在条件中使用引号引用起来,否则不会使用索引(比如:添加时,字符串必须'')
5. 如果Mysql发现使用全表扫描要比索引快,则不会使用索引。
五、查看索引的使用率
show status like 'handler_read%';
Handler_read_rnd_next为109是因为我们在建索引之前,已经查询过sql语句,如果先建索引,在查sql语句,这个值会更低