MYSQL5.8之后索引失效的常见原因以及如何避免

一：MYSQL5.8之后索引失效的常见原因以及如何避免

之前在小公司，数据量不大，用索引也不规范，明明建立了索引，但是通过 Explain 发现语句并没有使用上索引，这可能是某些原因导致了我们的索引失效。所以想自己记录一下，以后忘记了能查看。

一：初始化数据库

1：创建表

-- 创建表
CREATE TABLE `user` (
  `user_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '用户名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `pass` varchar(64) DEFAULT NULL COMMENT '密码',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`user_id`),
  KEY `name_pass` (`name`,`pass`) USING BTREE,
  KEY `age` (`age`) USING BTREE
) ;

2：创建函数

-- 创建函数 快速添加插入用户信息
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`( 
IN record INTEGER, -- record参数用来传需要插入数据的条数
IN dt TIMESTAMP -- dt参数用来传入时间戳，开始插入的第一条数据的时间（格式为：'2020-10-24 14:31:44'）
)
BEGIN
DECLARE
	number INTEGER;-- 声明一个number，用来控制循环的次数
SET number = 1;-- 将number的值赋值为1，代表循环从1开始
START TRANSACTION;
WHILE
	number <= record DO-- 使用while进行循环，满足条件进入循环
SELECT
date_add( dt, INTERVAL 1 SECOND ) INTO dt;-- 使用date_add()函数将时间进行转换为秒数，并赋值给dt参数
-- 插入数据
INSERT INTO localhost.user -- 库名.表名
-- 由于id是自动增长，不需要额外赋值，所以需要将剩余的其他字段全部列出进行一一对应赋值插入
( `name`, `age`, `pass`, `create_time` )
VALUES
	( 'layman'+number, null, '123456', dt );-- number参数进行自增
SET number = number + 1;-- dt参数进行自增
SET dt = date_add( dt, INTERVAL 1 SECOND );
END WHILE;
COMMIT;
END

二：索引失效的原因

**重点：mysql5.7和mysql5.8不同的版本效果不一样，mysql5.7中范围判断表达式这种情况sql执行结果是全表扫描，而5.8中范围判断表达式使用了 range 类型索引，但是范围查询不能超过总数据的30%左右，超过就会进行全表搜索。

1. 以下情况可能会导致索引失效，应避免使用；

a. 使用 <> 、not in 、not exist、!=、in
b. like “%_” 百分号在前。
c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。
d. 字符型字段为数字时在where条件里不添加引号.
e. 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
f. 当进行联表查询时，如果关联字段对应的两张表各自的字符集不同，可能会将字段进行字符集转换，这里字符集转换用的是内部函数操作，此时优化器会放弃走树搜索的功能。

2. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

错误的例子：explain select * from user where user_id+1 = 1;

正确的例子：explain select * from user where user_id = 1;

3. 对索引列使用函数会导致索引失效,我所指的对索引列进行函数包括(month，ifnull，left 等)

错误的例子：EXPLAIN SELECT * FROM USER WHERE LEFT(NAME,1)='layman';

错误的例子：EXPLAIN SELECT * FROM USER WHERE IFNULL(USER_ID,0)=1;

错误的例子：EXPLAIN SELECT COUNT(*) FROM USER WHERE MONTH(CREATE_TIME)=3;

4. 不要将空的变量值直接与比较运算符（符号）比较。

如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。
如果字段允许为空，则is null走 ref 类型的索引，而is not null走 range 类型的索引(范围内数据不能很多)。

5. 不要在 SQL 代码中使用双引号。

因为字符常量使用单引号。如果没有必要限定对象名称，可以使用（非 ANSI SQL 标准）括号将名称括起来。

三：索引使用原则：

在根据执行计划对SQL进行分析之后，逐步的去优化每个子查询的索引及成本使用情况，执行计划我的理解就是用来对索引进行逐步的优化的一个过程，让每个查询都尽量使用索引，在索引过程中，索引会经常性失效。
口诀

全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE符号写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
var引号不能丢，SQL高级也不难
分组之前必排序，一定要上索引啊
1234567

四：建索引注意事项

1、单值索引，尽量选择过滤性更好的字段，例如：性别字段，过滤度为50%，识别率很差，不建议建索引
2、组合索引，索引字段的顺序可以按照识别度进行排序，识别度越高，放在越靠前
3、组合索引，尽量包含where语句中的更多字段
4、尽可能的根据分析执行计划、统计信息，去调整query的写法达到合适索引的目的

五：索引容易失效的几个注意点：

1、不在索引列上做任何的操作（计算、函数、类型转换），会导致索引失效而转向全表扫描
2、组合索引中，如果中间某个字段使用了范围条件，则右边的列索引失效
3、尽量使用覆盖索引（索引列和查询列一致），减少使用select *
4、mysql在使用不等于（！= 或者<>）的时候，无法使用索引列会导致全表扫描
5、is null ，is not null 也无法使用索引
6、like通配符必须放在索引列的右边，否则索引失效，编程全表扫描
7、字符串不加单引号索引失效
8、mysql中，如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
9、如果mysql使用全表扫描要比使用索引快,则不会使用到索引。