索引那些事

最近面试问了很多关于索引的事，本来自认为可以对答如流，什么BTREE  B+TREE啊，什么like '%abc'和like ‘abc%’的区别啊，又是联合索引a=? and b=? and c=?的判断啊，很简单呐，但没想到啊，这里面门门道道都可以玩出花来了，现在面试官问问题都是挖着陷阱让你往里跳，不讲究。

你真的懂联合索引么？ 

话不多说，实践出真理，建表建索引 （name,class,age）

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`class`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`age`  int(11) NULL DEFAULT NULL ,
`weight`  double NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX `name_class_age_index` (`name`, `class`, `age`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4
ROW_FORMAT=DYNAMIC
;

1. name=? and class= ? and age=?

地球人都知道用到了索引，且key_len最大，也就是索引全用到了

同理，name=?  and class=?  也用到了索引，只不过key_len要小一点

2.  name=?  and  age=?      ||    name=?

这里跳了一个class，能用到索引么，答案是可以的，其实只要查询条件带了name，就一定用到了索引。

观察key_len=768    如果不写age=？呢，还是768。

所以没写class=?  导致后面的索引都失效了

3. name=? and class=?    ||      class=?  and name=?

前者当然用到了索引，那后者呢？class可是写在前面耶，这不符合联合索引的前置原则吧

然而事实是也用到了索引，因为mysql会自动优化sql语句！！！

4. class=?  and age=?

由于没用到name    自然没有用到索引

5. is null   is not null    > <  like   or

为了少贴些图，我直接写结果了

 5.1 is null    is not null

is null  和 =  没区别

is not null  后面的引用失效，如果name is not null  则全部失效

explain select * from studentindex where name='123' and  class is  null and  age=22    
--1541



explain select * from studentindex where name='123' and  class ='123' and  age is not null
--1541



explain select * from studentindex where name='123' and  class is not null and  age is not null
--1536    is not null 之后的引用全失效了


explain select * from studentindex where name is not null and  class is not null and  age is not null
-- allType   name is not null 直接扫描全表了

 5.1 >   <

比较符号仍然调用引用，且和顺序无关

explain select * from studentindex where name='123' and  class ='123' and  age <123
-- 1541


explain select * from studentindex where name='123' and  class ='123' and  age >123
-- 1541


explain select * from studentindex where name='123' and  age >123 and  class ='123' 
-- 1541

5.2  ！=    <>

全部失效

explain select * from studentindex where name='123' and  age != 123 and  class ='123' 
-- all type

5.3 like      not like

explain select * from studentindex where name like '123%'  and  class like '123%' and  age = 123

-- 1541   like 'abc%' 和 =  一样的意思



explain select * from studentindex where name  like '123%'  and  class not like '123%' and  age = 123
-- 768     not like 自身不起作用  且以后的也没作用



explain select * from studentindex where name  like '123%'  and  class  like '%123' and  age = 123
-- 768     like '%abc'   和 not like 一样

5.4  or 

explain select * from studentindex where name  like '123%'  or  class  like '1234' and  age = 123

-- all type   or 导致索引失效

explain select * from studentindex where name  like '123%'  and  (class  like '1234' or  age = 123)
-- 768      or  导致括号内部索引失效