【mysql】之聊聊sql索引使用的规则

精选原创

小夜的传说 2024-08-23 15:50:10 ©著作权

©著作权归作者所有：来自51CTO博客作者小夜的传说的原创作品，请联系作者获取转载授权，否则将追究法律责任

看这篇文章建议先看 Explain分析SQL性能

建表SQL

-- 建表
CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v_name` varchar(20) DEFAULT '' COMMENT '姓名',
  `age` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `pos` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_pos` (`v_name`,`age`,`pos`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

-- 建索引
ALTER TABLE sys_user ADD INDEX idx_name_age_pos(`v_name`, age, pos);

1、全值匹配，最优

第一个key_len=63，计算规则，v_name varchar(20),20*3+2(动态列类型)+1(允许为NULL)=63

第二个key_len=67，计算规则 v_name+age 长度，63+4(int，不允许为NULL)=67

第三个key_len=129，计算规则，v_name+age+pos，63+4+（20*3+2）=129

2、最佳左前缀（如果建立了联合索引，指的是从索引的最左前列开始并且不跳过索引中的列）

我们建表索引是idx_name_age_pos，sql1和sql2都没有使用到索引，而sql3没有遵循左匹配也使用到索引，是因为使用了覆盖索引，

覆盖索引：当使用覆盖索引的方式，select id,v_name,age from sys_user where age=10（where后面没有其他没有索引的字段条件），即使不是以v_name开头，也会使用联合索引，总结来说就是

select 后的字段有索引，where字段也有索引，则无关执行顺序，不需要最左边前缀法则

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

4、索引不能使用索引中范围条件右边的列

我们发现sql4和sql2使用的索引是一致的，sql4的pos索引没有用到，说明了范围右边的索引列示法使用到

5、尽量使用覆盖索引，提高查询效率，避免使用select *

6、mysql使用 !=,<>的时候无法使用索引会导致全表扫描

7、mysql的is null可以使用索引，is not null 无法使用索引

8、like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

解决like ‘%%’无法使用索引的问题

9、字符串不加单引号，导致索引失效

10、使用or导致索引字段失效

总结：

假设有索引index a_b_c(`a`,`b`,`c`)

where语句	索引是否被使用
where a = 3	a
where a = 3 and b = 5	a,b
where a = 3 and b = 5 and c = 4	a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	没有使用索引
where a = 3 and c = 5	a，因为b中断，所以c索引无法使用
where a = 3 and b > 4 and c = 5	a,b,因为c在范围索引之后，所以无法使用
where a = 3 and b like 'kk%' and c = 4	a,b,c
where a = 3 and b like '%kk' and c = 4	a
where a = 3 and b like '%kk%' and c = 4	a
where a = 3 and b like 'k%kk%' and c = 4	a,b,c