全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
Like百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难。
一、准备工作:
首先创建一个用户表:
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`age` int(11) NOT NULL,
`position` varchar(50) NOT NULL,
`add_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
向表中插入基础数据
INSERT INTO `user` (`id`, `name`, `age`, `add_time`, `position`) VALUES (1, 'name1', 25, '2019-8-12 09:56:21', '搬砖的');
INSERT INTO `user` (`id`, `name`, `age`, `add_time`, `position`) VALUES (2, 'name2', 25, '2019-8-12 09:59:18', '搬砖的2');
INSERT INTO `user` (`id`, `name`, `age`, `add_time`, `position`) VALUES (3, 'name3', 53, '2019-8-12 09:59:28', '搬砖的3');
INSERT INTO `user` (`id`, `name`, `age`, `add_time`, `position`) VALUES (4, 'name4', 52, '2019-8-12 10:00:05', '搬砖的4');
INSERT INTO `user` (`id`, `name`, `age`, `add_time`, `position`) VALUES (5, 'name5', 36, '2019-8-12 10:00:09', '搬砖的5');
创建索引
create index index_user_nameAgePosition on user(name,age,position);
查询索引
show index from user;
二、开始分析
1. 全值匹配我最爱
建立几个复合索引字段,最好是都使用上并且按照顺序来。(走全部索引,当然不按照顺序来也可以实现按照顺序查询出来的结果)
2. 最左前缀原则
如果设置了多列索引,要遵循最左前缀原则。具体指查询从索引的最左列开始,不跳过索引中间列,最左侧的列不可以没有。(带头大哥不能死,中间兄弟不能断。)这个原则我在之前的博客有写过。
走全部索引:
explain select * from user where name='name1';
explain select * from user where name='name1' and age='25';
explain select * from user where name='name1' and age='25' and position='搬砖的';
不走索引:这种情况就是带头大哥死掉了,后面的索引全部失效。
explain select * from user where age='25' and position='搬砖的';
explain select * from user where position='搬砖的';
走部分索引:这种情况就是中间兄弟断掉的情况,只走了name索引。
explain select * from user where name='name1' and position='搬砖的';
总结:第一个索引是最重要的,第一个索引就好比是一个车头,其他的索引是车厢,车头没了只有车厢,火车跑不起来。车头没丢但是中间车厢断了,尾厢部分跑不起来。
3. 索引列上少计算
不要在索引列上做任何操作计算、函数、(自动或手动)类型转换,这样会导致索引失效而转向全表扫描。
explain select * from user where name='name1';
explain select * from user where right(name,5)='name1';
4. 范围之后全失效
存储引擎不能使用索引中范围条件右边的列。范围条件的列相当于中间的兄弟断了,但是有点区别,范围列的索引还是有效的,范围列之后的索引全部失效。
explain select * from user where name='name1' and age=25 and position='搬砖的';
explain select * from user where name='name1' and age>25 and position='搬砖的';
explain select * from user where name='name1' and age>25;
以上就是将age字段的=条件变为了>,查的就是一个范围,type类型变低,key_len变短,ref变空。通过加上position字段查询和不加该字段进行查询可以发现三个字段position索引失效了。
5. Like百分写最右,覆盖索引不写星(“Like百分写最右”这种情况需要使用覆盖索引,索引才可以生效)
explain select * from user where name like '%name%';
explain select * from user where name like '%name';
explain select * from user where name like 'name%';
由上面分析可知,要模糊查询只有在查询条件的后面加%索引才生效。但是只在查询条件后面加%明显不符合我们的需求,可以通过覆盖索引的方式解决索引丢失的问题,但是也是存在局限性的,如果查询字段中包含了非索引字段,那么索引还是会失效。
不是覆盖索引
explain select * from user where age=25 and position='搬砖的' and name like '%a%';
使用覆盖索引:
explain select name,age,position from user where name like '%a%' and age=25 and position='搬砖的';
6. 不等空值还有or,索引失效要少用
MySQL在使用不等于(!=或者<>)的时候无法使用索引导致索引范围扫描。
使用不等号
explain select * from user where name='name';
explain select * from user where name!='name';
explain select * from user where name <> 'name';
is null和not null也不可以使用索引
explain select * from user where name is null;
explain select * from user where name is not null;
使用or
explain select * from user where name='name' or name='name1';
7. VAR引号不可丢,SQL高级也不难。
var引号不可丢是说查询的索引字段的如果是字符串类型的需要加上单引号否则索引失效。
insert into `user`(id,name,age,position)values(6,200,12,'包工头');
首先在表中添加一条数据,这里我添加的name值没有加单引号,但是也添加成功了这是因为MySQL自动给数值200做了类型转换。当然要是name值换成添加字符串类型的值不加单/双引号就会报错的。
select * from user where name='200';
select * from user where name=200;
以上两条查询语句查询结果一致。
explain select * from user where name=200;
explain select * from user where name='200';
由上可知,第一条SQL语句索引失效,进行的是全表扫描。
知识补充:
MySQL explain解析extra中的using index,using where,using index condition。
using index和using where只要使用了索引我们基本都能看到,而using index condition则是在mysql5.6后添加的新特性。
- using index:使用覆盖索引的时候就会出现,不扫描整个表。
- using where:优化器需要通过索引回表查询数据;可以对非索引列执行表扫描,但是如果在where条件中要有索引列。
- using index condition:在5.6版本后加入的新特性(Index Condition Pushdown); Using index condition 会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行;(条件包含索引和非索引列,优化器将首先解析索引列,并在表中查找其他条件的行)
- Using index & using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据。
借鉴自:https://blog.csdn.net/why15732625998/article/details/80409164