Series

4、MySQL索引优化

Navy2024-11-12mysql数据库mysql数据库

INFO

从索引带来的效果以及索引执行的流程分析MySQL可能优化的环节，简单介绍如何取查看SQL的执行计划

1、索引的效果

有一个优惠券表coupon_code，表结构如下：

CREATE TABLE `coupon_code` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '优惠券码',
  `type` int DEFAULT NULL COMMENT '优惠券类型',
  `status` int DEFAULT NULL COMMENT '使用状态：0-未使用，1-已使用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='优惠券';

表里有1000050条数据

image-20241111215603842

现在我要查询某个优惠券码，使用SQL查询：

SELECT * FROM coupon_code WHERE code = '1855885657679990868';

可以看到查询时间：

image-20241111172214820

单查询383ms的查询速度还是比较慢的。为了优化查询，我们在name这一列添加一个索引：

ALTER TABLE `test001`.`coupon_code` ADD INDEX `idx_code`(`code`) USING BTREE;

接下来就是见证奇迹的时刻：

SELECT * FROM coupon_code WHERE code = '1855885657679990868';
image-20241111172659517

可以看到查询时间已经到1ms了，这就是索引的效果，索引建立得当确实可以很大程度上提升查询性能

2、索引的过程

前面的文章也介绍了通过索引去查询数据的流程，大体就是：

[二级索引树] --> [二级索引树的叶子节点找到ID] --> [回表，到主键索引树检索] --> [找到对应叶子节点上的目标数据]

后面的优化思路就是按照这个流程来贯彻的

3、索引优化

3.1、主键索引

由于数据就保存在主键索引中，所以通常情况下，走主键索引肯定比走二级索引要快，因为少了一个回表的过程。

利用主键索引来进行SQL优化的场景常见的是大表的检索，比如一个表有一千万条数据，如果要从第500万条查询接下来的10条，这个时候如果要用MySQL的limit，那查询依然会很慢，因为MySQL会首先对前500万条数据排序后再取10条，然后其它的数据就丢弃了。可能“丢西瓜捡芝麻”在这里用来比喻并不是很恰当，但费劲巴拉才拿到这10条数据成本确实有点大。

比如还是上面的那个100万的表，从80万取10条数据为例：

image-20241111214655302

可以看到这就用了293ms了，明显速度还是比较慢的

像这个场景有什么好的优化办法呢？主要有两种方案：

  • 按照ID截取

这种方式主要针对主键是数值类型（特别是连续自增）的场景。因为像InnoDB存储引擎，主键索引肯定是存在的，而且是已经排好序的，这个时候我们可以这么去查：

select * from coupon_code where id > 800000 ORDER BY id limit 10

看结果：

image-20241111214807886

这就是差距。程序在实现分页的时候只需要将每次查询的最大ID传回来就可以知道数据查询到哪里了

  • 子查询方式

除了按照ID截取的这种方式，还可以通过子查询的方式，比如：

SELECT t1.* FROM coupon_code t1 
INNER JOIN (SELECT id FROM coupon_code ORDER BY id LIMIT 800000,10) t2
ON t1.id = t2.id

查询结果：

image-20241111215312410

可以看到相对于直接limit查询是有一定的提升的，虽在在这个数据量上对比还不是特别明显，但也有293ms降到了172ms

说到主键，这里插一个题外话，比如有人问你：count(*)、count(1)、count(id)、和count(xxx)这几个有什么区别？

这里需要了解一下count的逻辑了，它是查询到值就加1，那么这几个的区别就不言而喻了：

* 肯定不会为空，因为一行记录不可能所有的列都是空，至少有一列有值

11肯定不是空

idid肯定也不会是空

xxx这可就不一定了，还是需要判断一下

你猜哪个性能会更好一点呢？

这里不得不提一句为什么MySQL建议大家使用自增ID作为InnoDB的主键，它的优势除了索引键所占的空间比较小之外也体现在这里，当然自增主键在插入数据的时候对索引树的维护上也有一定的优势，因为它只需要添加节点就行了，而如果是非自增的主键你还得对key进行分裂合并。总之这些点点滴滴都是会影响到SQL查询性能的点

3.2、索引覆盖

在我们的业务开发中，不可能都是按照主键索引来查的，更多的时候需要借助二级索引，不管是单列的二级索引还是联合索引。前面我们在体验索引的效果的时候就通过在code字段上创建了一个二级索引来感受到了索引的好处。

但行数据总归还是在主键索引上，每次通过二级索引的查询还是需要经历一次回表的过程，那能不能省去这个环节呢，如果能省去回表这个环节的话那肯定性能会有提升。

答案当然是肯定的，但也是有条件的

还是拿上面优惠券表为例，在code列的索引树上，我们经过分析顶破天也只有索引键以及对应叶子节点上的id信息。所以，如果我们要拿到code和id之外的信息就必须经过一次回表过程。

换句话说，如果我们只需要code和id信息，那么就可以不用回表啦，之前也提到过，这就是索引覆盖

讲到这里其实也就可以理解为什么有人建议我们在查询的时候不要统统select * ，而是需要什么字段就select什么字段了，因为这样的话是有可能命中索引键从而走索引覆盖不用回表，提升了查询性能优化的可能性

3.3、索引下推

还是以上面优惠券这个表为例，如果我们创建了这样一个联合索引：

ALTER TABLE `test001`.`coupon_code` ADD INDEX `idx_code_type`(`code`, `type`) USING BTREE;

假如有以下查询，请问这个联合索引会生效吗？

select * from coupon_code where type = 3 and code like '%612511'

因为code列是%前缀，明显不会生效，但是存储引擎会扫描整颗二级索引索引树，大概流程如下图所示：

image-20241111222507135

① 客户端提交SQL到server

② server去调存储引擎

③ 存储引擎首先走二级索引用索引key去检索，由于code是%前缀，所以会扫描整颗索引树，并将type=3的id都找出来

④ 从二级索引中拿到对应的id之后回表，从主键索引中拿到具体的数据行，并返回给server层

⑤ server层会用where 条件去过去过滤，拿到最终满足条件的数据，并返回给客户端

在我们上面“优惠券”这个例子中，type是固定的几个值，这样一来通过type=3查到的id就会非常多，回表后拿到的行数据也就会非常多。这么多的数据在存储引擎和server层进行交互，势必会消耗一定的时间。

从上面的图也可以看出，server拿到的也并不都是自己想要的数据，它想要的只是code以后缀612511结尾的数据，这突然一下子把这么多数据怼过来需要消耗性能不说，过后server还得去再一次过滤。这个环节有没有可以优化的点呢？

当然是有的。

前面有一个关键环节就是扫描整个二级索引，既然后面也需要server去挨个过滤，而这棵索引树也包含了所有的code，为什么不能在这个时候就将以612511结尾的code并且type为3的挑出来呢？

这个技术就叫索引下推

索引下推只对InnoDB和MyISAM存储引擎有效，默认情况下这个功能就是开启的，也可以手动关闭。开启索引下推后上面的流程就变成如下图所示了：

image-20241111230356839

这里的关键步骤就是在二级索引的扫描的时候，先就把以612511结尾的择出来了，这样回表的数据也少了，引擎层返回给server层的数据也少了，server层也不用再用这个code like '%612511'去过滤了，一举多得。伴随的性能自然能得到提升了。

3.4、如何选择索引

  • 索引不是越多越好

    ① 因为索引是有大小限制的，官网介绍了InnoDB中一个表最多有一千多列，最多有64个二级索引。如果索引过多，会占用过多的内存空间

    ② 每个索引都会对应一棵索引树，每次数据变更相应的索引树也需要同步进行变更，所谓牵一发而动全身，过多的索引维护起来会特别麻烦

  • 尽量控制索引键的长度

    索引键的长度会影响一个页中能保存的key的数量，而数量越多，索引树的深度越低，检索的时候I/O的次数就会越少，性能相对会越好。

    从这个角度来说，因为像InnoDB的主键索引树也会保存行数据，如果一个行特别大，比如有特别大的列的时候，能够放的行数据就会越少，所以如果有特别大的列的话可以考虑拆分出去

  • 离散性不是很好的列不建议加索引

    像上面“优惠券”的示例中的type列，总共也就那么几个值，即使按照type去检索也和全表扫描差不多嘞，还得单独维护一棵索引树，有点不划算

  • 尽量采用联合索引

    联合索引只需要维护一棵索引树就可以在多个列上进行索引，只要结合我们的业务场景索引建立得当

3.5、索引失效的场景

索引失效的场景有离散型不行的列上的索引、不满足最左匹配（如上面的%前缀）。对于联合索引的查询哪些列可以走索引可以参考最左匹配原则，总的来说就是从哪一列开始，后续的列数据是无序的，如果后面是无序了，那它肯定不走索引了

4、执行计划

如果要做SQL优化，怎么可能不看执行计划呢，所以接下来介绍一下执行计划都需要关注哪些点

4.1、如何查看执行计划

查看执行计划非常简单，直接执行explain就行，如

explain select * from coupon_code where id > 800000 ORDER BY id limit 10

image-20241111233243115

4.2、执行计划需要关注点

参考链接open in new window

4.2.1、select_type

select_type可以看出查询类型，比如是简单的单表查询（simple）呢，还是带有子查询（subquery），取值范围如下：

image-20241111234522749

4.2.2、table

有联合查询或者子查询的情况

4.2.3、type

性能从好到坏的取值：参考链接open in new window

  • system：一个表只有一行记录
  • const：最多只有一个匹配行
  • eq_ref：主键索引或者唯一索引，一般用=号
  • ref：主键索引或者唯一索引，一般用=号或者<==>，和eq_ref的区别就是一个针对关联表一个针对被关联的关系
  • fulltext：全文索引
  • ref_or_null：和ref不同在于会额外搜索包含null值的行，通常用于解决子查询
  • index_merge：输出行中的键列包含所使用的索引列表，key_len包含所使用索引的最长键部分列表
  • unique_subquery：IN的主键子查询
  • index_subquery：IN的普通列子查询
  • range：范围查询
  • index：只是扫描了索引树，Extra列对应Using Index
  • all：全表扫描

4.2.4、possible_keys

可能用到的索引

4.2.5、key

推测SQL走的索引，如果有值，可以关注一下key_len这列的值

4.2.6、ref

将哪些列或常量与键列中指定的索引进行比较

4.2.7、rows

推测的要扫描的数据行数，不准

4.2.8、filtered

表示按表条件筛选的表行的估计百分比

4.2.9、Extra

MySQL如何解析查询的其他信息，参考链接open in new window

扫码关注，一起学习吧

编辑当前页面
页面导航