1.表和数据
CREATE TABLE `t_user` (`id` bigint(32) NOT NULL AUTO_INCREMENT,`user_name` varchar(40) DEFAULT NULL COMMENT '用户名',`user_code` varchar(40) DEFAULT NULL COMMENT '用户编号',`phone` varchar(11) DEFAULT NULL COMMENT '电话',`age` tinyint(3) DEFAULT NULL COMMENT '年龄',`create_time` datetime DEFAULT NULL COMMENT '创建时间',`update_time` varchar(32) DEFAULT NULL COMMENT '更新时间',`address` varchar(100) DEFAULT NULL COMMENT '地址',PRIMARY KEY (`id`),KEY `idx_create_time` (`create_time`),KEY `idx_update_time` (`update_time`),KEY `idx_phone` (`phone`) USING BTREE,KEY `idx_name_code_add` (`user_name`,`user_code`,`address`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='用户表';
INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('1', '张三', '10021', '18500123322', '22', '-12-03 17:17:08', '-12-04 17:17:08', '天津市滨海区水利路11号');INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('2', '张龙', '10022', '18500123323', '23', '-12-03 17:17:08', '-12-01 17:17:08', '北京市海淀区五道口33号');INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('3', '张哥', '10023', '18500123324', '24', '-11-03 17:17:08', '-12-02 17:17:08', '北京市海淀区五道口33号');INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('4', '张明', '10024', '18500123325', '25', '-10-03 17:17:08', '-12-03 17:17:08', '北京市海淀区五道口33号');INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('5', '王子', '10025', '18500123326', '26', '-12-01 17:17:08', '-12-04 17:17:08', '北京市海淀区五道口33号');INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('6', '李明', '10026', '18500123327', '33', '-12-02 17:17:08', '-11-05 17:17:08', '北京市海淀区五道口33号');INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('7', '陈哥', '10027', '18500123328', '44', '-08-03 17:17:08', '-10-06 17:17:08', '北京市海淀区五道口33号');INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('8', '刘志', '10028', '18500123329', '55', '-07-03 17:17:08', '-09-07 17:17:08', '北京市海淀区五道口33号');INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('9', '黄强', '10029', '18500123333', '66', '-06-03 17:17:08', '-08-08 17:17:08', '北京市海淀区五道口33号');
2.key_len计算规则
EXPLAIN select * from t_user where user_name = '张龙'
user_name 类型为varchar(40)，字符集为utf8，也就是使用3个字节来表示一个完整的UTF-8。此时如果这个字段命中索引，key_len = 40* 3 =120；
由于该字段类型varchar为变长数据类型，需要再额外添加2个字节。此时，key_len = 120 + 2 = 122；
由于该字段允许为NULL（default NULL），需要再添加1个字节。此时，key_len = 122 + 1 = 123；
3.最左匹配原则
3.1.不命中的情况
EXPLAIN SELECT * FROM t_user WHERE user_code = '10022' AND address = '北京市海淀区五道口33号'
条件里面没有添加user_name，无论剩余2个条件是否添加，或者顺序是否按照索引，只要user_name 没有就无法命中索引，这就是最左匹配
注意这里有个情况，就是覆盖索引的情况，例如上面的sql不用select*，改为下面的
EXPLAIN SELECT user_name,user_code,address FROM t_user WHERE user_code = '10022' AND address = '北京市海淀区五道口33号'
可以看出，就会命中索引，此时命中的是覆盖索引
3.2.命中的情况
按照索引顺序3个添加都加上，此时看key_len的长度是3个字段都命中了EXPLAIN select * from t_user where user_name = '张龙' and user_code = '10022' and address = '北京市'
按照索引顺序只添加前面2个条件，只命中了前面2个字段
EXPLAIN select * from t_user where user_name = '张龙' and user_code = '10022'
把顺序打乱，发现还是会命中索引，而且3个字段都命中了，这是因为MySQL优化器会把顺序优化成索引的顺序
EXPLAIN SELECT * FROM t_user WHERE address = '北京市海淀区五道口33号' AND user_code = '10022' AND user_name = '张龙'
总结，只要有user_name字段就会命中索引，不管后面那2个条件是否有，或者是否按照索引顺序，都会命中索引的，只不过key_len长度不同
4.索引列参与运算
EXPLAIN SELECT * FROM t_user where id + 1 = 2
如图主键id+1=2就不会命中索引，这种情况改成where id = 2 - 1就会命中主键索引
5.索引列使用函数
EXPLAIN SELECT * FROM t_user where SUBSTR(phone,1,3) = '100';
如图phone这个字段有索引，如果使用函数的话就会失效，全表扫描
6.like %xxx%
EXPLAIN SELECT * FROM t_user where phone like '%100%';
如图like模糊匹配左右2边都是% 会索引失效，但是如果改成100%就会命中索引
7.字段类型转换
EXPLAIN SELECT * FROM t_user where phone = 100
如图，phone是varchar类型，但是where 条件是=100，变成int型了，phone就会转化成int型，字段的类型就变了，就变成全表扫描了
但是有种情况是例外，就是int型=字符串，此时MySQL会把’1’转换成int型，所以就会命中索引
EXPLAIN SELECT * FROM t_user where id = '1'
8.日期类型like
EXPLAIN SELECT * FROM t_user where create_time like '-12-03%'
日期类型，datetime，date如果使用like是不会走索引的，因为字段类型不一样，这实际上跟上面的字段转换是同一个问题
9.or操作
EXPLAIN SELECT * FROM t_user where phone = '111' or address = '222'
如图，or前后有2个条件，phone 是有索引，address 没有所索引，这会导致索引失效，全表扫描
而如果or前后都是索引字段，phone和update_time 都是索引字段，就会命中索引，如下
EXPLAIN SELECT * FROM t_user where phone = '111' or update_time = '222'
10.is not null
EXPLAIN SELECT * FROM t_user where phone is not null
is not null不会走索引，is null可以命中索引
11.not in
EXPLAIN SELECT * FROM t_user where phone not in ('11','22')
in 会走索引，not in 不会走索引
但是有个例外情况就是如果换成主键id,就会走索引
EXPLAIN SELECT * FROM t_user where id not in (1,2)
12.order by
EXPLAIN SELECT * FROM t_user ORDER BY phone
order by 普通索引会失效
这里如果不用select * ，直接查字段会走覆盖索引
EXPLAIN SELECT phone FROM t_user ORDER BY phone
但是如果换成order by id，主键索引会命中
EXPLAIN SELECT * FROM t_user ORDER BY id
13.不等于
EXPLAIN SELECT * FROM t_user where phone != '11'
EXPLAIN SELECT * FROM t_user where phone <> '11'
不等于不会走索引，但是主键索引，和覆盖索引会走索引，这2个情况是例外
14.其他情况
当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。也就是说，当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。某些场景下即便强制SQL语句走索引，也同样会失效。
类似的问题，在进行范围查询（比如>、< 、>=、<=、in等条件）时往往会出现上述情况，而上面提到的临界值根据场景不同也会有所不同。
15.索引的规范使用
索引是提高数据库查询性能的一个重要方法。
使用索引用可快速找出某个列中包含特定值的行。不使用索引，必须从第一条记录开始读，可能要读完整个表，才能找出相关的行。
使用索引就像查字典一样，我们可以根据拼音、笔画、偏旁部首等排序的目录(索引)，快速查找到需要的字。
下面总结了一些规范建议，可以用来参考，并非绝对真理。
单表的索引数建议不超过5个，组合索引的字段原则上不超过3个。
尽量不要在较长字符串的字段上建立索引，可以设置索引字段前缀长度。
选择在查询过滤中使用率较高，如where，orderby，group by的列建立索引。
不要在区分度不高的列上建立索引，比如性别等，利用不了索引性能。
不要在经常更新的列上建立索引，数据更新也会更新索引，影响数据库性能。
建立组合索引时，区分度最高，或者查询频率最高的，放在最左侧。
合理利用覆盖索引来满足查询要求，避免回表查询，减少I/O开销。
删除不再使用、少使用、或者重复的索引，减少数据更新的开销。
利用explain来判断查询语句，是使用了索引，还是走了全表扫描。
