记一次生产慢sql索引优化及思考

这篇具有很好参考价值的文章主要介绍了记一次生产慢sql索引优化及思考。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一 问题重现

夜黑风高的某一晚,突然收到一条运营后台数据库慢sql的报警,耗时竟然达到了60s。

看了一下,还好不是很频繁,内心会更加从容排查问题,应该是特定条件下没有走到索引导致,如果频繁出现慢查询,可能会将数据库连接池打满,导致数据库不可用,从而导致应用不可用。

二 问题排查

报警自带定位慢sql语句,这个是很早就上线的一条sql语句,下面对sql语句进行了简化:

select * from xxx where gear_id=xxx and examine=xxx order by id desc limit 10,这是个简单的根据流量池gear_id查询,按照主键id倒序进行分页查询10条数据的语句。

在examine=2时查询速度很快,但是在examine=3时,查询速度极慢,然后分别在不同的examine下查看执行计划,得到的执行计划都是一致的。

查看执行计划,发现possible_keys中有idx_gear_id索引,但是实际用到的key却是PRIMARY,并且extra中明确用了where条件进行数据过滤。到现在就明白了这个sql是在主键聚簇索引上进行扫描,然后用where语句条件进行过滤,时间耗费在这了。

这个也解释了为什么examine在不同状态下的耗时不一样,取决于where过滤扫描的行数,扫描行数越多,执行越慢,但同一个问题是都没走到我们已有的索引idx_gear_id。

当单表数量较小时,无论有没有索引,或者走主键索引扫描或者普通索引都很快,很容易忽略这些问题,此时的表现就是你好,我好,大家好,然后随着数据量的增大,当达到千万级别或者亿级时,慢查询的问题就凸显出来了。

三 原理剖析

为什么mysql会选择这个不合适的主键聚簇索引?

以常用的InnoDb存储引擎为例,看一下聚簇索引和非聚簇索引查询区别:

聚簇索引:通常就是按照每张表的主键构造一颗B+树,叶子节点中存放的就是整张表的行记录数据,即数据和主键都在索引上

非聚簇索引:表的二级索引字段(比如唯一索引,联合索引等)构造的一颗B+树,叶子节点存储的是Key字段+主键值,即非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据索引的指针。

聚簇索引查询原理:

非聚簇索引查询原理(二级索引查询):

由以上的索引数据结构可以看出,因为聚簇索引将索引和数据保存在同一个B+树中,因此通常从聚簇索引中获取数据比非聚簇索引更快,而非聚簇索引在获取到叶子节点的主键后,需要再次查询主键索引,即回表查询行记录数据。当然如果查询的列只是索引字段,比如查询姓名和年龄,可以创建联合索引,即索引存储的内容即为需要查询的内容,这种查询速度往往比主键索引更快,这种索引查询又称为覆盖索引。

什么是回表?

将以上的索引数据映射成常见的用户表user的索引为例,上面的聚簇索引就是以id字段为主键的索引,name字段为非聚簇索引,还有age等其他表字段是非索引字段,示例sql:select * from user where id = 1; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式,则只需要搜索 id聚簇索引这棵 B+ 树,就可以查到对应的数据。

但当我们使用非聚簇索引 name 这个索引来查询 name = b 的记录时就要用到回表。原因是通过 name 这个二级索引查询方式,则需要先搜索 name 索引树,然后得到主键 id,即PK的值为 1,再到主键id聚簇索引树再搜索一次。这种根据二级索引查询到主键id,再根据主键id查询主键聚簇索引的过程就称为回表。

回到为什么mysql会选择这个不合适的主键聚簇索引问题本身,mysql执行器认为使用二级索引查出来的数据太多了,还需要基于磁盘做临时存储进行排序,然后排序取出10条,然后进行回表查询字段,性能可能会很差,所以采用了直接采用了按顺序扫描主键聚簇索引,和where条件gear_id=xxx and examine=xxx进行对比,最多放10条即可,这种情况就是数量小的时候没问题,但是当数据量大的时候,就需要一直扫描所有的数据,直到查到符合where条件的10条数据为止,同时耗时也急剧增长。

四 解决问题

为了快速解决问题,可以采用强制索引force index,即在写sql语句时指定使用具体的索引

sql示例 :select * from xxx force index (idx_gear_id) where gear_id=xxx and examine=3 order by id desc limit 10,强制使用idx_gear_id这个索引。

以下为使用强制索引的执行计划:

可以看到实际使用的索引key就是idx_gear_id,执行耗时在几百毫秒,运营后台的业务人员完全可接受。

五 长期优化

由于表的数据越来越多,查询条件错综复杂,还有用json字段查询问题,决定将数据异构到es查询,将json字段打平,es天然支持复杂的查询条件,查询响应更快。

es数据同步方案:

在ES数据同步链路中,通过京东科技中间件DTS监听数据库的binlog,将索引字段(查询条件字段)及业务唯一id写入ES。

在业务运营查询时,根据复杂的查询条件,先去ES查询,将业务唯一id查出,再根据业务唯一id去DB中查询业务明细数据,同时解决了业务查询的复杂性和查询性能。

作者:京东科技 张石磊

来源:京东云开发者社区 转载请注明来源文章来源地址https://www.toymoban.com/news/detail-760247.html

到了这里,关于记一次生产慢sql索引优化及思考的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 好文分享 | 记一次Oracle12c数据库SQL短暂缓慢问题分析

    本文为墨天轮社区作者 张sir 原创作品,记录了日常运维Oracle数据库过程中遇到的一个慢SQL问题的解决、优化过程,文章内容全面具体、分析到位,且含有经验总结,分享给各位。 这次出问题的数据库比较特殊,承接的系统交易要求很高,SQL基本都是短平快,响应时间基本不

    2024年02月05日
    浏览(53)
  • Unity - 记一次非正规变体优化带来的兼容性导致部分手机卡死的问题

    在 2023.4.6 我们的 角色展示界面 就遇到了 华为手机,red mi note 11 的测试手机上的 后 2023.5.24 再次遇到类似的问题,但是这次重现的地方很多,不单止 角色展示界面 遇到 排除过: 模型 特效 场景 人物 材质 后来多次排查,发现是 PBR 所有的 变体拆分优化 的文件导致阴影部分

    2024年02月08日
    浏览(46)
  • MongoDB:记一次生产环境中mongo出现的严重出错与排查解决

    造成此种错误的原因有如下几种常见情况: * 系统磁盘已满导致mongo无法向文件系统写数据。 * 系统突然死机(或系统重启)造成mongo文件系统被损坏。 * 使用非正当方法强制停止mongo服务,如:kill -1/-9的方式。 * mongo文件系统遭篡改。 还有很多种原因............ 启动mongod失败

    2023年04月14日
    浏览(74)
  • 记一次翻页性能优化

       由于是公司项目,所以不方便给出代码或者视频,只能列一些自己画的流程图。    大致情况如上,前端有7个显示区。在对其进行滚动翻页的时候,存在以下问题:    通过分析代码,调查log发现,翻页切换平均耗时在600ms。其主要的业务逻辑如下: 主要问题有

    2024年02月05日
    浏览(45)
  • 优化记录 -- 记一次搜索引擎(SOLR)优化

    某服务根据用户相关信息,使用搜索引擎进行数据检索 solr 1台:32c 64g 数据10gb左右,版本 7.5.5 应用服务器1台:16c 64g 应用程序 3节点 1、因业务系统因处理能不足,对业务系统硬件平台进行升级,升级变更为 16c64g — 32c64g 增加 16c 2、业务系统升级,处理能力增加,对原搜索引

    2024年02月05日
    浏览(61)
  • 记一次简单的SQL调优

    相信大家对后端数据库的 SQL 都不会陌生,但是有时候我们会 无意间 就写出一堆奇怪的SQL, 可能当时还没有发现 ,但是没关系,客户后期会告诉你的,这也没有关系, 只要后期不是你负责解决bug就好 。 可是很不幸有时候这种问题就是分配给你解决 。。。。。。 下面就是我

    2024年02月09日
    浏览(44)
  • 记一次项目内存优化--内存泄漏

    主要是与某个版本作基准进行对比(一般是最新版本的前一个版本作原数据),优化后,PSS有所下降,线上OOM率减少(Bugly版本对比),泄漏点减少(从捉取一些线上上传回来的内存堆栈信息分析,或本地测试后dump下hprof文件分析)。 了解什么是内存泄漏 了解虚拟机中的对象

    2024年02月12日
    浏览(74)
  • 记一次偶然的网站sql注入

            自己学了点渗透的内容后就开始尝试挖漏洞了,偶然发现了这个yp网站,由于好奇心就浏览了一下里面的内容,突然注意到有个id的地方跳转页面,于是就想试试看有没有注入,就有了以下的内容。。。 界面如下 当时就是好奇点进去看了下,浏览了一下内容,发现

    2024年02月11日
    浏览(36)
  • 记一次rax应用用户体验性能优化

    对于前端开发攻城狮们来说,性能优化是一个永恒的话题。随着前端需求复杂度的不断升高,在项目中想始终保持着良好的性能也逐渐成为了一个有挑战的事情。本次分享简述我们在 Rax 项目中常用的一些性能优化方式,并将从近期的一个实际业务需求出发,讲述我在 Rax C端

    2024年02月21日
    浏览(41)
  • 记一次挖edusrc漏洞挖掘(sql注入)

    在利用fofa收集信息的时候发现的,我这里直接开始挖洞部分写了。 目标是一个中学的站点,在一次挖洞过程中遇到个sql注入,漏洞已报送平台进行了修复。该文章仅用于交流学习,切勿利用相关信息非法测试,我也是刚入门的小白,欢迎各位大佬指点。 访问url/gywm.asp?id=95

    2024年02月08日
    浏览(40)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包