Mysql 索引优化(一)

Source

目录

一.创建表

二.插入数据

三.创建索引

四.索引总结

 

一.创建表

CREATE TABLE `staffs` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `pos` varchar(20) DEFAULT NULL COMMENT '职位',
  `add_time` timestamp NULL DEFAULT NULL COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_n_a_p` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工记录表';

二.插入数据

INSERT INTO `explain`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('1', 'z3', '22', 'manager', '2021-02-28 22:08:14');
INSERT INTO `explain`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('2', 'July', '23', 'dev', '2021-02-28 22:08:41');
INSERT INTO `explain`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('3', '2000', '23', 'dev', '2021-02-28 22:08:56');

三.创建索引

四.索引总结

EXPLAIN SELECT * FROM staffs WHERE `name`='July' AND age ='23' AND pos='dev';

EXPLAIN SELECT * FROM staffs WHERE `name`='July'

EXPLAIN SELECT * FROM staffs WHERE `name`='July' AND age ='23'

EXPLAIN SELECT * FROM staffs WHERE `name`='July' AND age ='23' AND pos='dev';

三个SQL的精度越来越高. 花费的代价也是递增的 如图key_len长度递增. 在精度一致的情况下key_len越短越好.

结论:

全值匹配我最爱(最好查询字段和索引字段全部匹配)

EXPLAIN SELECT * FROM staffs WHERE   age ='23' AND pos='dev';

EXPLAIN SELECT * FROM staffs WHERE  pos='dev';

EXPLAIN SELECT * FROM staffs WHERE    pos='dev' AND age ='23' ;

 

索引失效.全表扫描.创建的索引没用使用到.

EXPLAIN SELECT * FROM staffs WHERE  name='July' AND pos ='dev' ;

这个key_len的长度和跟之前name的一致。所以索引只用到了一部分,到name后面没有用到。

结论:

最佳左前缀法则——如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列.(中间字段不能跳过)

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

总结:

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

SELECT * FROM staffs WHERE name ='July' AND age > 20 AND pos = 'dev'  

EXPLAIN  SELECT * FROM staffs WHERE name ='July' AND age > 20 AND pos = 'dev'  

总结:

存储引擎不能使用索引中范围条件右边的列(范围之后全失效,上篇Mysql EXPLAIN优化有介绍)

EXPLAIN  SELECT * FROM staffs WHERE name ='July' AND age = 23 AND pos = 'dev'  

EXPLAIN  SELECT name,age,pos FROM staffs WHERE name ='July' AND age = 23 AND pos = 'dev'  

EXPLAIN  SELECT name,age,pos FROM staffs WHERE name ='July' AND age >= 23 AND pos = 'dev'

总结:

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

EXPLAIN  SELECT * FROM staffs WHERE name ='July' 

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

EXPLAIN SELECT * FROM staffs WHERE name <> 'July' 

PS: 不要因为创建了索引一定要用上,根据业务需求,若无法避免索引失效,且不影响性能情况下可以使用。能实现需求的情况下再进行优化。

总结:

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

PS:若name 字段没设置不为空,则走索引。我上面创建表sql有个小坑。

EXPLAIN SELECT * FROM staffs WHERE name is not null

总结:

is null ,is not null也无法使用索引

EXPLAIN  SELECT * FROM staffs WHERE name LIKE '%J%'

EXPLAIN SELECT * FROM staffs WHERE name LIKE '%J'

EXPLAIN SELECT * FROM staffs WHERE name LIKE 'J%'

%加右边走索引,左边不走 业务需求两边必须都加%XX%用覆盖索引来实现 具体实现过程 后期写篇博客来分析解决like "%字符串%'时索引不被使用的方法

总结:

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

EXPLAIN SELECT * FROM staffs WHERE name = 3;

 

EXPLAIN SELECT * FROM staffs WHERE name = '3';

总结:

字符串不加单引号索引失效

EXPLAIN SELECT * FROM staffs WHERE name = '3' or name ='July';

总结:

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