建表SQL
--建表
CREATE TABLE`sys_user` (
`id`int(11) NOT NULLAUTO_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
1、对于单键索引,尽量选择针对当前query过滤性更好的索引
2、在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。(避免索引过滤性好的索引失效)
3、在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的