Mysql 索引优化（一）

刺骨的言语ヽ痛彻心扉 2022-11-03 15:28 236阅读 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 条评论，236人围观）

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

相关阅读

    相关 索引优化(一)

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

    ゞ 浴缸里的玫瑰ゞ 浴缸里的玫瑰/ 0 赞/ 72 阅读

    相关 MySQL之索引优化（一）

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

    电玩女神电玩女神/ 0 赞/ 192 阅读