专栏首页用户4352451的专栏一看就会的mysql索引优化(真实案例)

一看就会的mysql索引优化(真实案例)

背景

(使用的数据库:MYSQL 5.7 版本,InnoDB 引擎) 自从服务加了Skywalking后,将大部分慢接口暴露出来。于是就有了这次慢接口的优化。大概的优化过程。

  • 优化前:
  • 优化后:

优化步骤

1. 排查

  1. 通过skywalking可以清楚的看到慢接口是在哪一步比较慢。通过调用情况可以清楚的看到其链路调用情况如下图:

如果说你的服务没有接如这种情况监控服务,那我们可以使用阿里巴巴开源的Arthas来进行链路追踪(使用trace进行查看每一步方法的调用耗时)arthas官方文档

2. 记录现有情况

  1. 通过排查后,可以准确的定位到是SQL很慢,这个时候我们就得针对于这条SQL仔细分析,先将业务SQL拿出来(通过控制台日志进行获取),通过explain查看SQL执行计划,这个很关键了。查看是否有走索引,查看预扫描行数,执行策略等。在这里的话主要还是依靠explain各个字段给我们提供的信息。(跑的是生产库)。获取到的主要信息进行记录.

使用的索引

预扫描行数

是否回表

是否排序

执行时间

结论

3. 根据情况分析原因定具体优化方案

1. 索引走错
1. - 这种情况我们呢可以查看他的索引基数通过 show index from table_name.大概看一下cardinality基数字段,大概评估一下。然后使用命令 analyze table tb_name重新计算一下。
    - 因为对与mysql选择索引其中索引基数是重要条件之一
    - 索引基数是通过抽样计算计算出来的,所以不一定是准确的,所以通过analyze table进行重新采样计算后就可以了。
2. - 如果说通过索引基数还没有OK的话,那就有可能是在这条语句中与可能有排序或者有创建临时表的情况,使用这个你认为扫描行数少的有可能产生。所以你可以考虑优化索引了,创建一个符合索引且不需要回表的

2. 索引区分度低

  • 在这里需要注意的就是尽量少用一些状态值创建索引。为什么呢?构建好B+树你大部分值都一样,在进行查找的时候很可能就和普通链表的时间复杂度差不多了。在这里我们真的可以果断干掉。
  • 还有一种就是前面半部分基本差不多,后边一部分有一些区别,这样的话我们可以采用倒叙存储,然后在使用他创建索引,并且只使用区分度大的那一部分进行存储。更或者说多存一个字段为hash 值使用hash值做索引。在进行查找的时候直接使用计算后的hash进行匹配

3. 使用了索引有回表,有排序

  • 回表,就是我们通过某个索引查出来的数据不能慢走我们所需要的那些字段,他又要将收集到的ID通过主键索引进行扫描拿到对应的字段信息。那我们的解决办法就是使用覆盖索引了。创建一个符合所需字段的索引,在这里需要注意的就是已经要评估好,不能随便一个查询语句就去创建一个联合索引。还有就是符合最左匹配原则,区分度也要大。
  • 有排序,也就是在explain的时候 有filesort,这个操作会进行IO操作,进行排序。所以我们创建一个联合索引拥有这个字段的。列如我们根据用户ID,查询id对应的订单按create_time排序的联合索引,其实还有一种情况就是说如果这个用户订单有几百万单那查询性能一下子就下来了,那这个时候我们可以根据业务需求进行优化,就是说限定一个时间区间 在where 语句里。
(id,create_time)

4. 我的SQL最终定的方案是改掉两个添加一个

  1. 改掉区分度低的索引和不遵循最左匹配原则
  2. 创建一个联合索引,消除file sort
  3. 业务逻辑的优化,将复杂SQL进行简单优化,去掉嵌套逻辑
  4. 结合业务逻辑加上时间区间(当前时间前一个月)。

3. 严格有效的验证

这是最重要的一步

我的验正方案:

  1. 通过自己对业务的理解,预估会使用的修改过的索引的语句,进行测试,在这里的话可以和生产库进行对比
  2. 将原来的索引使用情况已经记录下来,然后在SIT环境数据库将原来的SQL进行跑,还有(我们SIT库是从PROD有同步数据过来的,但是还是有部分数据缺失的)
  3. 跑SQL的时候尽量选择筛选条件是:使用索引,并且会筛不出来数据的,防止数据排在前面导致很早就命中的偶然情况。
  4. 然后就是测试同学随我们优化的接口进行压测。
  5. 就是全量回归测试。各个查询页面随便点点
  6. 还算幸运最后性能提高了有1000被从几十秒到不到1ms 对其他的业务接口也没有影响。

4. 索引上线

  1. 变更索引无非就是 删除索引,和创建索引。在这里需要注意一下就算是变更过程中有可能导致的锁表情况,在这里我们可以产看一下关于Online DDL的这个语法(https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html)
  2. 公司业务允许,我是半夜停服搞得。但是也执行了有1个小时(900万数据)

幻觉

在实际工作中有很多同学都认为加索引会影响更新效率。但是这个影响想说对于查询很慢的影响来说很是小的一个影响。

总结

  • 我自己优化的一个过程
  • explain的使用(https://segmentfault.com/a/1190000008131735)
  • 索引出现问题的几种情况
  • 严格的测试很重要(我的测试个人认为一般)

资料

  • 美团的优化索引:https://tech.meituan.com/2014/06/30/mysql-index.html
  • expalin的使用:https://dev.mysql.com/doc/refman/5.7/en/using-explain.html
  • Online DDL 语法:https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
  • arthas官方文档:https://alibaba.github.io/arthas/trace.html)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • mysql 中的innoDB 引擎的B+树索引

    在优化慢接口的时候,遇到一个问题,在通过索引查询数据库表的时候根据时间区间去扫描表的时候,开始时间时表扫描的其实位置吗?或者说根据时间日期B+索引能一次性定位到...

    居士
  • 什么是索引?(补充)

    索引是一种算法(B-tree,HASH等),索引也就是将数据列以某种算法的方式进行排列(如B-tree)本是通过全表查询(ALL) 而通过建立索引而使列数据变为...

    居士
  • 稀疏索引和稠密索引你了解吗?

    最近参加了一个面试,面试官先问了mysql的数据库的索引的底层数据接口,我回答了:平时都用的是innodb引擎,所以其底层的索引数据类型是B+树。面试官问我用没...

    居士
  • MySQL索引入门简述

    用来加快查询的技术很多,其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引,MYSQL必须从第一条记录开始读完整个表,直到找出相关的行。表越大,花...

    wangxl
  • 你真的了解索引了吗(一)

    给大家介绍下,我是集美貌与才华于一身的程序媛钱六六,人在美国,刚下飞机,就收到了男神无双的短信轰炸

    黑洞代码
  • 这么简单的ES索引生命周期管理,不了解一下吗~

    对于日志或指标(metric)类时序性强的ES索引,因为数据量大,并且写入和查询大多都是近期时间内的数据。我们可以采用hot-warm-cold架构将索引数据切...

    Jared.Tan
  • Mysql 索引

    除了b+树索引外,还有hash索引,使用hash表去实现<key,value>,对于每一行数据,存储引擎都会对所有的索引列计算出一个hash code,将has...

    猎户星座1
  • MySQL进阶篇(02):索引体系划分,B-Tree结构说明

    首先要明确索引是什么:索引是一种数据结构,数据结构是计算机存储、组织数据的方式,是指相互之间存在一种或多种特定关系的数据元素的集合,例如:链表,堆栈,队列,二叉...

    知了一笑
  • Mysql - 组合索引的B+树存储结构(最左前缀原理)

    那组合索引的B+树存储结构是什么样的呢,为什么会有最左前缀原理,看了很多帖子找到了答案

    夹胡碰
  • 创建索引的原则与索引失效的情况你真的了解吗

    为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。本小节将向读者介绍一些索引的设计原则。

    java乐园

扫码关注云+社区

领取腾讯云代金券