索引优化总结。

前言

下诉情况中如果没有特别说明，都是默认都是索引都是指复合索引，查看索引长度 EXPLAIN + SQL语句结果 key_len。下表中创建了一个联合索引和普通索引。

//表结构
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `collegeId` int(11) NOT NULL COMMENT '学院id',
  `classId` int(11) NOT NULL COMMENT '班级id',
  `name` varchar(24) NOT NULL COMMENT '学生名称',
  `age` int(10) unsigned NOT NULL COMMENT '年龄',
  `phone` varchar(11) DEFAULT NULL COMMENT '电话号码',
  `college` varchar(24) NOT NULL COMMENT '学院',
  `class` varchar(24) NOT NULL COMMENT '班级',
  PRIMARY KEY (`id`),
  KEY `all-index-student` (`collegeId`,`classId`,`name`) USING BTREE,
  KEY `single-index-class` (`class`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

索引长度计算公式

1.所有的索引字段，如果没有设置not null，则需要加一个字节。
2.定长字段，int占四个字节、date占三个字节、char(n)占n个字符。
3.对于变成字段varchar(n)，则有n个字符+两个字节。
4.不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占用一个字节，gbk编码的，一个字符占用两个字节，utf8编码的，一个字符占用三个字节.
5.tinyint：1字节,smallint：2字节,int：4字节,bigint：8字节,date：3字节,timestamp：4字节,datetime：8字节

1、 最左前缀法则（复合索引）

最左前缀法则，在复合索引中，应该尽量满足索引列最左边的索引应该存在于查询条件当中。例如：

//错误示例
  select * from student t where  name like '陈独%'; // 缺少左前缀 collegeId classId 不会走索引
  select * from student t where t.classId = 1235 and name like '陈独%'; // 缺少左前缀 collegeId 不会走索引
  select * from student t where t.collegeId = 1  and name like '陈独%';  // 会走索引，但是索引长度只是collegeId 的长度，因为少了name 的前缀classId 
//正确示例
  select * from student t where t.collegeId = 1 and t.classId = 1235 and name like '陈独%'; 
  select * from student t where t.collegeId = 1 and t.classId = 1235 ; 

2、 全值匹配（复合索引）

全值匹配，在复合索引中。尽量让所有字段都参与条件查询。在排在前面的索引列不使用范围查询，因为使用范围查询后将会导致后面的索引列失效。

//错误示例
  select * from student t where t.collegeId > 1 and t.classId > 1235 and name like '陈独%'; // 索引长度最长 4 
  select * from student t where t.collegeId = 1 and t.classId > 1235 and name like '陈独%'; // 索引长度较差 8
//正确示例
  select * from student t where t.collegeId = 1 and t.classId = 1235 and name like '陈独%'; // 索引长度最优 

3、不要在索引列中使用函数（适用所有索引规则）

在SQL语句中任何对索引列的函数操作，计算，or语句，类型转换都会导致索引失效。

//错误示例
select * from student t where (t.collegeId+1) = 2
//正确示例
  select * from student t where t.collegeId = 1 and t.classId = 1235 and name like '陈独%'; // 索引长度最优 

4、模糊匹配是不要使用右边查询（适用所有索引规则）

业务中我们经常使用模糊查询比如：xxx like ‘%keyWord%’ 类似这种查询也会导致索引失效，同样的复合索引也一样。同时复合索引还需要满足最左前缀法则。如果你第一个字段就破坏了那么后续索引字段也会失效。

//错误示例
  select * from student t where t.class like '%1235%' ; // 索引无法生效
  select * from student t where t.collegeId > 1 and t.classId > 1235 and name like '陈独%'; // 索引无法生效
  select * from student t where t.collegeId  = 1 and t.classId > 1235 and name like '陈独%'; // 至少能有一个索引字段生效
//正确示例
  select * from student t where t.class like '1235%' ; /
  select * from student t where t.collegeId = 1 and t.classId =1235 and name like '陈独%'; 

5、减少select * 的使用，劲量使用覆盖索引 （适用所有索引规则）

覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。就是覆盖索引

//错误示例
select * from  student t where t.collegeId = 1 and t.classId =1235 and name = '陈独秀'
//正确示例
select collegeId,classId,name from student t where t.collegeId = 1 and t.classId =1235 and name = '陈独秀'

6、不要使用<>、!=

使用<>.!= 时会导致全表扫描

//错误示例
select * from  student t where t.collegeId <> 2 and t.classId =1235 and name = '陈独秀'
//正确示例
select * from  student t where t.collegeId = 1 and t.classId =1235 and name = '陈独秀'

7、is null is not null 也会导致索引失效。

mysql索引条件要求，索引列不允许为空，所以这个条件一般不会存在。

//错误示例
select * from student t where t.collegeId  is not null and t.classId =1235 and name = '陈独秀'
//正确示例
select * from student t where t.collegeId = 1 and t.classId =1235 and name = '陈独秀'

8、其他补充

1、 字符串不加单引号索引会失效。
2、 少用or和in 这些语句会导致mysql内部优化器导致索引失效，这取决于数据多少，sql语句的具体情况。
3、 范围查询，如果跨度大也会导致全表扫描，这取决于表大小等其它因素。