mysql索引实践小总结

– 确认使用的版本

show variables like 'version%';

– 确认使用的引擎

show variables like '%engine%';

– 创建表 使用utf8

CREATE TABLE `my_test` (
  `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(5) NOT NULL,
  `e` varchar(5) DEFAULT NULL,
  `f` char(2) NOT NULL,
  `g` char(2) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

– 添加数据

insert my_test(b,c,d,e,f,g) values(1,1,'d01','e01','f1','g1');
insert my_test(b,c,d,e,f,g) values(2,2,'d02','e02','f2','g2');
insert my_test(b,c,d,e,f,g) values(3,3,'d03','e03','f3','g3');
insert my_test(b,c,d,e,f,g) values(4,4,'d04','e04','f4','g4');
insert my_test(b,c,d,e,f,g) values(5,5,'d05','e05','f5','g5');
insert my_test(b,c,d,e,f,g) values(6,6,'d06','e06','f6','g6');

– 查询数据
select * from my_test;

– 添加索引前，主键查询，会使用主键索引

explain  select * from my_test where a=1;

– 添加索引前，非主键查询，无使用索引

explain  select * from my_test where c=1;

– 添加索引 cdf

alter table my_test add index idx_cdf(c,d,f);

– 删除索引 cdf
– drop index idx_bdf on my_test;

– 需要明确的前置条件 在utf8编码情况下，其他编码类型，如gbk 只不过是*2

-- 基本数据类型对应的索引长度为基本数据类型字节长度，如int 为4
-- varchar(n)类型 对应的索引长度为：n*3+2
-- char(n)类型 对应的索引长度为：n*3
-- 如果对应的类型为空的情况，需额外再加1

explain  select * from my_test where c=1;-- 5=4+1
explain  select * from my_test where c=1 and d='1';-- 22 = 5 + 17(5*3+2)
explain  select * from my_test where c=1 and d='1' and f='1';-- 28 = 22 + 6(3*2)

– innodb引擎会根据索引字段顺进行优化，sql书写的顺序 不会影响索引字段的使用

explain  select * from my_test where d='1'and c=1;-- 22
explain  select * from my_test where f='1' and c=1 and d='1' ;-- 28
explain  select * from my_test where f like '1%' and c=1 and d='1' ;-- 28 涉及到 like
explain  select * from my_test where f = '1' and c=1 and d like '1%' ;-- 28 涉及到 like
explain  select * from my_test where f = '1' and c=1 and d like '%1%' ;-- 5 涉及到 like

– 数据类型不匹配也会导致索引字段失效

explain  select * from my_test where c=1 and d='1' and f=1;-- f字段 未使用

– 范围之后 索引字段失效

explain  select * from my_test where c>5 and d='1' and f='1' ;-- 5，d、f字段失效

– 特殊说明 b当前范围：1-6，如果大于1，引擎会直接全表查询，因为经过辅助索引会浪费时间，增加IO 经过测试 大于4就开始使用索引

explain  select * from my_test where c>1 and d='1' and f='1' ;

– like %在前,索引字段失效

explain  select * from my_test where  d like '%1%' ;-- 不会使用索引
explain  select * from my_test where c =1 and d like '1' and f='1' ;-- 28 like 不加% 也使用索引
explain  select * from my_test where c =1 and d like '1%' and f='1' ;-- 28 like %在后会使用索引

– 特殊说明 如果必须使用 %在前，同时还让索引生效，可以使用覆盖索引

explain  select c,d,f from my_test where  d like '%1%' ;-- 5