深入理解Mysql——索引优化查询

站长

2022年09月17日 11:47 · 阅读数 32

1.多张表查询的索引分析

2.在什么情况下适合/不适合建立索引

3.如何避免索引失效

4.查询优化

1.多张表查询的索引分析

假设我们现在有三张表：

CREATE TABLE `t_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '学生名字',
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生表';

CREATE TABLE `t_class` (
  `id` int NOT NULL AUTO_INCREMENT,
  `class_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='课程表';

CREATE TABLE `t_score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int DEFAULT NULL,
  `class_id` int DEFAULT NULL,
  `score` int DEFAULT NULL COMMENT '得分',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='分数表';

假设我们要对这三张表进行联查，如何才能建立优秀的索引？

1)两张表联查

假设我们有这样一个查询，查询学生的分数

select stu.name,sc.score from t_student stu left join t_score sc on stu.id = sc.student_id

我们使用执行计划分析一下：

深入理解Mysql——索引优化查询

可以看出，type都是ALL，我们在深入理解Mysql——性能分析explain里说过，ALL是最差的一种结果，基本就等于全表扫描了，所以在这里，我们适度地给这个查询做一个优化，加上索引，那么，我们应该如何增加索引？一般会有三种选项：

左表增加索引，右表增加索引，两张表都增加索引。

左表增加索引：其实我们从这个查询可以看出，student表的id字段是主键，天然自带索引，但是却没有用到，是因为，我们用的是left join(左边全有)，而且没有在where后面对左边这张表进行筛选，于是没有效果，由此可以得出，右边表是我们的关键点，一定要建立索引。

右表增加索引：

ALTER TABLE `test`.`t_score` 
ADD INDEX `idx_student_id`(`student_id`) USING BTREE;

我们来看一下执行计划：

深入理解Mysql——索引优化查询可以看出，右边这张表，type已经变成了ref(使用到了索引，但是索引不是唯一索引),就减少了被驱动表的扫描数量。

两张表都增加索引：从上面的两种索引增加方式来看，如果是left join / right join等方式，被连接的表的关联字段，加上索引之后，就可以大大减少扫描的数量，如果是join或者where后面有对主表的筛选，那也可以在筛选条件上加上字段（筛选条件如何增加索引下文会说），这样就可以减少两张表扫描的数量。

2）三张表联查

我们来看一个三表查询：

select stu.name,sc.score from t_student stu  
left join t_score sc on stu.id = sc.student_id 
left join t_class cla on cla.id  = sc.class_id

三表以及多表查询，其实建立原则也和二表查询差不多，如果是left / right join，被连接的表的字段要加上索引，主表的索引得看where条件后面是如何筛选的，如果没有where条件，可以不添加，反正都是需要全表扫描。

2.在什么情况下适合/不适合建立索引

适合：2.1.1）主键自动建立唯一索引

2.12）离散型越高的字段类型越适合建立索引

因为索引本身就是树形结构，离散型越高，就能越精确地查找出节点，就能更快地排除出不需要的数据，如果这个值所有值都接近相同，那么索引就没有意义。

2.13）频繁作为查询的条件字段应该创建索引

如果我们有一个user表，我们经常要用到phone来查询用户，那么在这个频繁使用的查询字段上建立索引就显得尤其重要。

2.1.4）查询中与其他表关联的字段，外键关系建立索引

2.1.5）单键/组合索引的选择，在高并发的情况下倾向于组合索引

2.1.6）查询中的排序字段，排序字段如果通过索引去访问将大大提高排序速度

2.1.7）查询中的统计或者分组的字段，可以建立索引

不适合：2.2.1）频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引，加重IO负担如果我们频繁更新索引中的值，那么索引树中的值就会频繁移动，可能会造成索引碎片，增加IO的复旦。

2.2.2）where条件里用不到的字段不创建索引

2.2.3）表记录太少，不适合建立

2.2.4）数据离散性不高的字段，为它建立索引没有太大的效果

3.如何避免索引失效3.1）全值匹配不会造成索引失效。

3.2）最佳左前缀法则

如果这个索引是多值索引，要遵循最佳左前缀法则，值得就是搜索条件从索引的最左边开始，保证连续性，不能跳过索引中的列。因为索引会从左到右通过这几个值进行排序，然后再找到索引树中合适的位置进行搜索。

3.3）不在索引列表上做任何操作（计算，函数，类型转换等等），会导致索引失效

3.4）当一个搜索条件是范围搜索，则不能使用这个范围条件右边的列。假设我们对a,b,c三个字段建立的索引，筛选条件为where a = 1,b > 2 。则c的搜索条件就无法通过索引去匹配，索引就会失效。

3.5）尽量使用覆盖索引，查询和筛选只访问索引的值，就可以直接从索引中返回数据。

假设我们对user表中的a和b字段建立了索引select a,b from user where a = 1 这样就会在索引中直接返回这两个字段，因为索引已经根据这两个字段排序过了。

3.6）mysql在使用不等于（!=）的时候，无法使用索引，会导致全表扫描。

3.7）is null,is not null无法使用索引

3.8）like以通配符开头，会造成全表扫描（like '%123..'）

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

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

4.查询优化

我们在早期的博客深入理解MySql——查询性能优化说过一些查询优化的类型，这里我们再提三种，连表（join）优化，order by 优化、group by优化、in/exists优化

4.1）连表（join）优化要记得一句话，使用join的时候，永远要用数据量小的表去关联数据量大的表。

假设A join B，mysql会自动帮我们做一个优化，到底是A join B (通过A表的所有数据去查询B表)，还是B join A（通过B表的所有数据去查询A表），数据少的表会被当做主表，可以减少大量的筛选。

4.2）order by 优化

order by尽可能地在索引列表上完成排序操作，请遵循索引的最佳左前缀原则。

如果不在索引列上，filesort有两种算法：单路和多路排序

双路排序：早期mysql4.1之前就是使用双路排序，要经过两次扫描，第一次将要排序的全部值进行排序，第二次再通过这些值去原表查询数据。众所周知，IO是很耗时的，所以出现了单路排序。

单路排序：将所有要用的字段和要排序的字段全部取出，一次性全排序，然后返回结果。它的效率更快一些，避免了二次读取数据，把随机IO变成了顺序IO，但是这种方式很占空间，如果设置的排序缓冲空间不够，所以可能会出现取出部分数据多次排序，从而多次IO。

在这里，我们可以增加增大sort_buffer_size，max_length_for_sort_data参数的设置，来加大排序缓冲区。

4.3）group by优化

group by实质是先排序后进行分组，遵照索引建的最佳左前缀

4.4）in/exists优化

我们查询一条数据存在不存在，往往有两种写法：

select a.* from A a where a.id in (select b.id from B b)

或者

select a.* from A a where exists (select 1 from B b where b.id = a.id)

上面两种方法有什么区别呢？

in 将查询出来的数据进行全值匹配

exists 将查询出来的数据，放到子查询做验证，根据验证结果（true or false），来决定主查询的数据结构是否得以保留。

in是子查询为驱动表，外面的表为被驱动表，故适用于子查询结果集小而外面的表结果集大的情况。

exists是外面的表位驱动表，子查询里面的表为被驱动表，故适用于外面的表结果集小而子查询结果集大的情况。

所以说，小表驱动大表当A数据量>B数据量 用in 当A数据量<B数据量 用exists