索引优化实战

• 1、 全值匹配
• 2、 最佳左前缀法则：如果索引多列，要遵守最左前缀法则。指的是查询从索引的最左钱磊开始且不跳过索引中的列
• 3、 不在索引上做任何操作（计算，函数，类型转换），会导致索引失效
• 4、 存储引擎不能使用索引中范围条件右边的列
• 5、 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
• 6、 Mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
• 7、 Is null 和is not null 也无法使用索引
• 8、 Like以通配符开头（’%abc…’）mysql索引会失效
• 9、 字符串不加单引号索引会失效
• 10、 少用or，用它来连接时索引会失效

针对上面的十种情况，实战操作一下。

建表语句：

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `stuCode` varchar(255) NOT NULL COMMENT '学号',
  `stuName` varchar(255) NOT NULL COMMENT '姓名',
  `class` varchar(255) DEFAULT NULL COMMENT '班级',
  `hobby` varchar(255) DEFAULT NULL COMMENT '爱好',
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO `Student` (`sid`, `stuCode`, `stuName`, `class`, `hobby`) VALUES ('1', '10001', 'zhangsan', '1', '2');
INSERT INTO ` Student` (`sid`, `stuCode`, `stuName`, `class`, `hobby`) VALUES ('2', '10002', 'wangwu', '2', NULL);
INSERT INTO `Student` (`sid`, `stuCode`, `stuName`, `class`, `hobby`) VALUES ('3', '10003', 'xiaohong', '2', '1');
INSERT INTO `Student` (`sid`, `stuCode`, `stuName`, `class`, `hobby`) VALUES ('4', '10004', 'zhaosi', '3', NULL);

创建联合索引stuCodeNameClass

ALTER TABLE `Student` ADD INDEX stuCodeNameClass ( `stuCode`,`stuName`,`class` )

查看索引

show INDEX from student

这里就可以看到我们创建的联合索引（主键会自动创建索引）

1、 全值匹配

explain select * from student where stuCode='10001' and stuName = 'zhangsan' and class = '2'

从结果中我可以看出我们确实走了stuCodeNameClass索引，从ref的三个const看，我们建立的联合索引，三个字段都用到了，这种匹配效率是最好的（注意一下此时的key_len是2302）

现在我们去掉class条件，看看会怎么样

explain select * from student where stuCode='10001' and stuName = 'zhangsan'

这个可以看出我们没有走全部的字段，虽然这种也走了索引，但是效率没有全值匹配好

2、 最佳左前缀法则：如果索引多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始且不跳过索引中的列

针对上面那个查询语句，我们去掉了class，依然走了索引，现在我们把stuName也去掉，看看效果怎样

explain select * from student where stuCode='10001'

不难看出，只有stuCode依然会走索引。那现在我们去掉stuCode，只用stuName和class

explain select * from student where stuName = 'zhangsan' and class = '2'

Type为All，并没有走索引，因为该语句违背了最佳左缀法则，查询从最左前列开始。

现在我们用stuCode和class来查询，看看效果如何

explain select * from student where stuCode='10001' and class = '2'

从结果中看，只用了stuCode一个字段，违背不跳过索引中的列

3、 不在索引上做任何操作（计算，函数，类型转换），会导致索引失效
当我们只使用stuCode作为条件时，会走索引，如下

explain select * from student where stuCode='10001'

如果我们在条件stuCode上使用left函数（从左截取）会怎样

explain select * from student where LEFT(stuCode,7) ='10001'

Type为All，没有走索引。

4、 存储引擎不能使用索引中范围条件右边的列（简单点来说就是条件范围后面的索引会失效）
现在我们在建一个teacher表

CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL,
  `number` int(11) DEFAULT NULL,
  `tCode` varchar(255) DEFAULT NULL,
  `tName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `teacher` (`tid`, `number`, `tCode`, `tName`) VALUES ('1', '1', '20001', 'wang');
INSERT INTO `teacher` (`tid`, `number`, `tCode`, `tName`) VALUES ('2', '2', '20001', 'yang');

创建索引numCode

ALTER TABLE `teacher` ADD INDEX numCode ( `number`,`tCode`)

按索引查询一下teacher

explain select * from teacher where number =1 and tCode = '20001'

正常走两个索引，注意key_len是773，现在稍微改一下查询语句

explain select * from teacher where number >1 and tCode = '20001'

Type是range我们从key可以看出我们还是走了索引，但是注意key_len可知我们只走了number索引，后面的tCode没有使用。

**5、 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select ***

现在还是使用student表

explain select * from student where stuCode = '10001' and stuName='zhangsan' and class = '1'

现在修改一下查询语法

explain select stuCode,stuName,class from student where stuCode = '10001' and stuName='zhangsan' and class = '1'

跟上面的区别在于Extra中有一个Using index，这表示我们扫描了索引就可以获得结果，不用再去扫描表，效果好

6、 Mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

explain select * from student where stuCode != '10001'

7、 is null和is not null 也无法使用索引

explain select * from student where stuCode is not null

8、 like以通配符开头（’%abc…’）mysql索引会失效
创建user表

CREATE TABLE `user` (
  `uid` int(11) NOT NULL,
  `uname` varchar(255) DEFAULT NULL,
  `uage` int(11) DEFAULT NULL,
  `usex` int(11) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user` (`uid`, `uname`, `uage`, `usex`) VALUES ('1', '1abc1', '1', NULL);
INSERT INTO `user` (`uid`, `uname`, `uage`, `usex`) VALUES ('2', '2abc2', '2', NULL);
INSERT INTO `user` (`uid`, `uname`, `uage`, `usex`) VALUES ('3', '3abc3', '3', NULL);

创建索引

ALTER TABLE `user` ADD INDEX nameAge ( `uname`,`uage`)

尝试使用索引查询

explain select * from user where uname like '%abc%'

但是只有右边有%的时候，是可以使用索引的

explain select * from user where uname like 'abc%'

但是有大部分需求是要求前后模糊查询的，这个时候有什么解决办法呢，这个其实可以跟第五条结合

explain select uid,uname,uage from user where uname like '%abc%'

9、 字符串不加单引号索引会失效
使用student表

explain select * from student where stuCode = 10001

10、 少用or，用它来连接时索引会失效

explain select * from student where stuCode = '10001' or stuName = 'zhangsan'