标签：name no 索引 user MySQL 失效 where select

一、创建索引的原则

1、建议创建索引的场景

where语句的查询条件

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

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

需要分组、排序的字段
distinct所使用的字段
如果字段的值，有唯一性约束，要创建唯一索引

对于某些字段，要求他不能重复，比如(用户名)，那么是可以创建唯一索引、主键索引的

多表查询，连接字段应该创建索引，并且类型务必保持一致，避免隐式转换;

隐式转换可能会导致索引无法使用；

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

where子句中用不到的字段;
表中数据非常少，是否创建索引对查询效率的影响并不大
有大量重复数据，选择性低，创建索引作用不大，

索引选择性越高，可以让mysql在查询时过滤更多的行，提升查询效率;比如性别sex字段，不建议创建索引

频繁更新的字段

如果创建索引需要考虑其索引维护开销，索引的更新维护是有开销的
某一字段修改非常频繁，查询很少，不建议创建索引

二、索引失效场景

示例表 CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '', `user_no` int(11) COLLATE utf8_bin 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

标签：name,no,索引,user,MySQL,失效,where,select
From： https://www.cnblogs.com/Windge/p/17111290.html

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