Mysql 索引优化(一)
目录
一.创建表
二.插入数据
三.创建索引
四.索引总结
一.创建表
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,用它来连接时会索引失效
还没有评论,来说两句吧...