一:MYSQL5.8之后索引失效的常见原因以及如何避免
之前在小公司,数据量不大,用索引也不规范,明明建立了索引,但是通过 Explain 发现语句并没有使用上索引,这可能是某些原因导致了我们的索引失效。所以想自己记录一下,以后忘记了能查看。
MYSQL5.8之后索引失效的常见原因以及如何避免
一:初始化数据库
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使用全表扫描要比使用索引快,则不会使用到索引。