性能优化｜索引优化最佳实战（一）查看索引长度是74=(3*24+2),可以算出联合索引中只使用了name前缀索引. 查看

CREATE TABLE `student` (
 `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
 `stu_name` VARCHAR ( 24 ) NOT NULL DEFAULT '' COMMENT '学生姓名',
 `stu_age` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '学生年龄',
 `stu_addr` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '地址',
 `birthday` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '出生日期',
 PRIMARY KEY ( `id` ),
 KEY `idx_name_age_addr` ( `stu_name`, `stu_age`, `stu_addr` ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8 COMMENT = '学生档案表';
INSERT INTO student ( stu_name, stu_age, stu_addr, birthday )
VALUES
 (
  '刘备',
  100,
  '三国蜀',
 NOW());
INSERT INTO student ( stu_name, stu_age, stu_addr, birthday )
VALUES
 (
  '张飞',
  110,
  '三国蜀',
 NOW());
INSERT INTO student ( stu_name, stu_age, stu_addr, birthday )
VALUES
 (
  '关羽',
  90,
 '三国蜀',
 NOW());

表描述：创建了一个联合索引和一个主键索引

索引优化常见手段

全值匹配

EXPLAIN SELECT * FROM student WHERE stu_name= '关羽';

查看索引长度是74=(3*24+2),可以算出联合索引中只使用了name前缀索引.

EXPLAIN SELECT * FROM student WHERE stu_name= '关羽' and stu_age = 20;

查看索引长度是78=(3*24+2)+4,可以算出联合索引中只使用了stu_name和stu_age前缀索引.

EXPLAIN SELECT * FROM student WHERE stu_name= '关羽' and stu_age = 20 and stu_addr = '三国蜀'

查看索引长度是140=(324+2)+4+(320+2),可以算出联合索引中只使用了完整的联合索引

最左前缀索引

EXPLAIN SELECT * FROM student WHERE stu_name= '关羽'

可以看出使用到了前缀索引。

EXPLAIN SELECT * FROM student WHERE stu_age= 20;

key_len为空，说明没有使用索引，因为违背了最左前缀规则，跳过了name，直接使用age，mysql就直接使用全表扫描了。

EXPLAIN SELECT * FROM student WHERE stu_age= 20 and stu_name = '刘备' and stu_addr ='三国';

看截图，mysql使用了联合索引，但是命名不遵循最左前缀原则啊，这是因为mysql5.7中。在执行器中，mysql会对sql语句进行优化，调整三个字段的顺序，满足最左前原则。

不要在索引列上使用函数计算和类型转换，否则会导致索引失效，使用全表扫描

EXPLAIN SELECT * FROM student WHERE left(stu_name,2) = '张飞'

按照最左前缀法则，上面的语句应该会使用索引的，但是由于在索引字段上面使用了函数，导致索引失效，mysql在使用优化器的时候，发现索引字段上面使用了函数，将会放弃索引查找，因为它觉得全表扫描会更快。

explain select * from student where stu_name =1000

stu_name 发生了隐式类型转换，导致索引失效。

索引字段使用了范围查询，右边索引字段将不会使用索引查询，因为使用范围查询后，索引不能按顺序进行比较，则会直接使用全表扫描

EXPLAIN SELECT * FROM student WHERE stu_name = '张飞' and stu_age > 100 and stu_addr = '吴国';

从索引长度上看，应该只走了name+age的联合索引，addr没用联合索引查询。

select查询中，最好只包括索引字段，不要全字段查询

explain select stu_name from student where stu_name = '刘备'

explain select * from student where stu_name = '刘备'

第二条语句extra字段值为空，但是第一条是using index，说明使用了索引覆盖，第二条语句为空的原因是mysql在索引树上面查询到数据之后，还需要把去主键索引表中提取数据完整记录；而第一条定位到数据后，mysql看到select 里面的字段在索引内，所以就直接能拿到数据。

在where语句中，尽量不要使用不等于、is null,is not null等比较语句，因为mysql只能使用全表扫描完成查询，非空判断的替换语句可以为字段设置默认值，查询的时候和这个默认值进行比较。

explain select * from student where stu_name != '刘备'

索引失效

不要用范围字段 in、or等，mysql会根据检索数据量、表大小等多个因素决定是否使用索引

以 ‘%’开头的模糊查询语句，会导致索引失效。

explain select * from student where stu_name like '%备%'

因为我们开发过程中模糊查询肯定是必须的怎么优化呢？

使用索引覆盖，select 查询的字段包括在索引中

explain select stu_name from student where stu_name like '%备%'

❝
微信搜一搜【乐哉开讲】关注帅气的我，回复【干货领取】，将会有大量面试资料和架构师必看书籍等你挑选，包括java基础、java并发、微服务、中间件等更多资料等你来取哦。

性能优化｜索引优化最佳实战（一）