阅读 149

MySQL如何避免索引失效?

文章首发于GitHub开源项目: Java成长之路 欢迎大家star!

案例演示

建表及初始化数据

CREATE TABLE staffs
(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    NAME     VARCHAR(24) NOT NULL DEFAULT '',
    age      INT         NOT NULL DEFAULT 0,
    pos      VARCHAR(20) NOT NULL DEFAULT '',#职位
    add_time TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP #入职日期
) CHARSET utf8 COMMENT '员工记录表';

#插入数据
INSERT INTO staffs(NAME, age, pos, add_time)
VALUEs ('z3', 22, 'manager', NOW());
INSERT INTO staffs(NAME, age, pos, add_time)
VALUES ('July', 23, 'dev', NOW());
INSERT INTO staffs(NAME, age, pos, add_time)
VALUES ('2000', 23, 'dev', NOW());
INSERT INTO staffs(NAME, age, pos, add_time)
VALUES ('张三', 23, 'dev', NOW());

#建立复合索引
CREATE INDEX idx_staffs_nap ON staffs (name, age, pos);
复制代码

1. 全值匹配: 筛选条件字段和复合索引的个数顺序完全相同。

EXPLAIN
SELECT *
FROM staffs
WHERE NAME = 'z3'
AND age = 23
AND pos = 'dev';
复制代码

image-20211004172447493

2. 最佳左前缀法则

筛选条件中必须有复合索引的第一个字段。并且不能跳过中间的字段。但是可以去掉最后的字段。

以上案例中,去掉条件中的name,或者去掉age,都会使索引失效。

即:带头大哥不能死,中间兄弟不能断。

3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

比如如下条件中索引字段使用了函数,因此,索引全部失效。

EXPLAIN
SELECT *
FROM staffs
WHERE LEFT(name, 4) = 'July';
复制代码

image-20211004175521149

4. 在筛选条件的索引列表中,范围筛选之后的索引会失效

EXPLAIN
SELECT *
FROM staffs
WHERE name = 'July'
AND age > 24
AND pos = 'manager';
复制代码

image-20211004194756525

结果中可以看到name索引用到了。但是pos没有用到。

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致),减少 select*

6. MySQL在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

EXPLAIN
SELECT *
FROM staffs
WHERE name != 'July';
复制代码

image-20211004195917103

7.IS NULL / IS NOT NULL无法使用索引

8.Like以通配符开头(‘%abc’)mysq索引失效会变成全表扫描的操作

image-20211004202444775

面试题:解决LIKE ’%X%‘时索引不被使用的方法?

答:使用覆盖索引,查询的字段覆盖创建的索引,即可。

image-20211004202422479

9.字符串不加单引号索引失效 (对应上边第三条)

image-20211004202609150

加上单引号 会发现走索引了

image-20211004202634053

10.少用or,用它来连接时会索引失效

文章首发于GitHub开源项目: Java成长之路 欢迎大家star!

文章分类
后端
文章标签