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.字符串不加单引号索引失效 （对应上边第三条）
加上单引号 会发现走索引了
