Mysql 索引优化(一)

刺骨的言语ヽ痛彻心扉 2022-11-03 15:28 229阅读 0赞

目录

一.创建表

二.插入数据

三.创建索引

四.索引总结

一.创建表

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

二.插入数据

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

三.创建索引

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70

四.索引总结

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

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

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 1

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

20210228224116178.png

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

20210228224232652.png

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

结论:

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

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

20210228224644346.png

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

20210228224847581.png

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

20210228224927700.png

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

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

2021022823013636.png

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

结论:

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

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

2021022823112966.png

总结:

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

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

20210228231704580.png

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

20210228231804154.png

总结:

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

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

20210228232152999.png

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

2021022823222978.png

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

2021022823234943.png

总结:

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

EXPLAIN SELECT * FROM staffs WHERE name =’July’

20210228232814941.png

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

20210228232757277.png

EXPLAIN SELECT * FROM staffs WHERE name <> ‘July’

20210228232859365.png

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

总结:

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

20210228233259499.png

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

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 2

20210228233440458.png

EXPLAIN SELECT * FROM staffs WHERE name is not null

2021022823351191.png

总结:

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

EXPLAIN SELECT * FROM staffs WHERE name LIKE ‘%J%’

20210228233648793.png

EXPLAIN SELECT * FROM staffs WHERE name LIKE ‘%J’

20210228233716359.png

EXPLAIN SELECT * FROM staffs WHERE name LIKE ‘J%’

20210228233746640.png

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

总结:

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

EXPLAIN SELECT * FROM staffs WHERE name = 3;

20210228234852278.png

EXPLAIN SELECT * FROM staffs WHERE name = ‘3’;

20210228234920904.png

总结:

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

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

20210228235312636.png

总结:

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

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 3

20210301104222118.png

发表评论

表情:
评论列表 (有 0 条评论,229人围观)

还没有评论,来说两句吧...

相关阅读

    相关 索引优化()

    对于任何一个后端的开发人员来说,索引优化是必备的技能,接下来会通过两篇文章来介绍如何优化索引以及如何设计好的索引。 一、数据准备 创建一个员工表,除了主键索引外,还额外

    相关 MySQL索引优化

    1. 联合索引和模糊查询的常见问题 1.1 联合索引第一个字段用范围导致不会走索引的情况 联合索引第一个字段就用范围查找不会走索引,mysql内部可能觉得第一个字段