mysql 如何避免索引失效

案例演示

建表及初始化数据

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';

2. 最佳左前缀法则

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

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

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

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

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

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

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

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

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

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

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

EXPLAIN
SELECT *
FROM staffs
WHERE name != 'July';

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

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

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

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

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

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

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