MySQL——优化（二）：索引创建和失效

一、创建索引的原则

1、建议创建索引的场景

• where语句的查询条件
• select语句，对于某些字段经常作为 where语句的查询条件；

• Update/delete语句的where条件频繁使用时的字段

• 需要分组、排序的字段
• distinct所使用的字段
• 如果字段的值，有唯一性约束，要创建唯一索引
• 对于某些字段，要求他不能重复，比如(用户名)，那么是可以创建唯一索引、主键索引的
• 多表查询，连接字段应该创建索引，并且类型务必保持一致，避免隐式转换;
• 隐式转换可能会导致索引无法使用；

2、不建议创建索引的场景

• where子句中用不到的字段;
• 表中数据非常少，是否创建索引对查询效率的影响并不大
• 有大量重复数据，选择性低，创建索引作用不大，
• 索引选择性越高，可以让mysql在查询时过滤更多的行，提升查询效率;比如性别sex字段，不建议创建索引
• 频繁更新的字段
• 如果创建索引需要考虑其索引维护开销，索引的更新维护是有开销的
• 某一字段修改非常频繁，查询很少，不建议创建索引

 

二、索引失效场景

mysql版本 5.6.39 ，示例表

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_no` int(11) DEFAULT 0,
  `name_reverse` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '',
  `role` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `mobile` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1.索引字段参加表达式计算

反例：select * from t_user where user_no +1 = 10000

解决方案：事先计算好表达式的值再传过来

正例：select * from t_user where user_no = 9999

 

2.索引字段是函数的参数

反例：select * from t_user where SUBSTRING(name,1,3) = 'win'

解决方案：预先计算好结果再传过来，不使用函数；使用等价的SQL去实现

正例：select * from t_user where name like 'win%'

 

3.like查询使用左模糊

反例：select * from t_user where name like '%win'

解决方案：使用搜索引擎或者增加逆向字段

正例：select * from t_user where name_reverse like 'niw%'

 

4.使用or查询的部分字段没有索引

反例：select * from t_user where name ='windge' or role='程序员'

解决方案：把没有索引的字段加上索引，两个索引会各自去查询，最后进行合并

将role加上索引后，explain后 type = index_merge

 

5.字符串未使用''引起来

反例：select * from t_user where role=3

解决方案：规范编写SQL，参数类型与字段类型一致

正例：select * from t_user where role='3'

 

6.组合索引不符合最左前缀的查询

反例：比如只有组合索引是 index(idx_role_user_no) select * from t_user where user_no='n001'

解决方案：调整组合索引的顺序 index(idx_user_no_role) 或者独立索引

 

7.采用is not null 或者 is null条件时，可能不走索引

反例：select * from t_user where role is not null

网上很多文章说用到is null 就不走索引，本人验证后其实不完全正确

采用is null 条件时，可能走索引也可能不走索引(MySQL中决定使不使用某个索引执行查询的依据就是成本够不够小)

具体也可参考文章：https://blog.csdn.net/qq_43842093/article/details/124810536

解决方案：索引字段建议添加NOT NULL约束；官方建议索引字段设置为not null , 尽量把非索引的字段也定义为not null

https://dev.mysql.com/doc/refman/8.0/en/data-size.html

 

 

8.Join隐式转换导致索引失效

反例：如tor.user_no为varchar类，tu.user_no为int

select tor.order_no from t_order tor left join t_user tu on tor.user_no = tu.user_no WHERE tor.user_no = 1

解决方案：规范建表，保持字段类型一致

 

 

其他参考：

https://blog.csdn.net/qq_43842093/article/details/124810536

https://dev.mysql.com/doc/refman/8.0/en/data-size.html