查看原文
其他

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

京东科技 张石磊 京东技术
2024-08-24


Tech




01 问题重现

在今年的敏捷团队建设中,我通过Suite执行器实现了一键自动化单元测试。Juint除了Suite执行器还有哪些执行器呢?由此我的Runner探索之旅开始了!

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

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


02   问题排查  

理解,首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板,当获取到模板后进行模板加载,加载阶段会将产物转换为视图树的结构,转换完成后将通过表达式引擎解析表达式并取得正确的值,通过事件解析引擎解析用户自定义事件并完成事件的绑定,完成解析赋值以及事件绑定后进行视图的渲染,最终将目标页面展示到屏幕。
报警自带定位慢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。

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



03   原理剖析  

理解,首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板,当获取到模板后进行模板加载,加载阶段会将产物转换为视图树的结构,转换完成后将通过表达式引擎解析表达式并取得正确的值,通过事件解析引擎解析用户自定义事件并完成事件的绑定,完成解析赋值以及事件绑定后进行视图的渲染,最终将目标页面展示到屏幕。

为什么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条数据为止,同时耗时也急剧增长。



04   解决问题  

理解,首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板,当获取到模板后进行模板加载,加载阶段会将产物转换为视图树的结构,转换完成后将通过表达式引擎解析表达式并取得正确的值,通过事件解析引擎解析用户自定义事件并完成事件的绑定,完成解析赋值以及事件绑定后进行视图的渲染,最终将目标页面展示到屏幕。    
为了快速解决问题,可以采用强制索引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这个索引。

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



05   长期优化  

理解,首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板,当获取到模板后进行模板加载,加载阶段会将产物转换为视图树的结构,转换完成后将通过表达式引擎解析表达式并取得正确的值,通过事件解析引擎解析用户自定义事件并完成事件的绑定,完成解析赋值以及事件绑定后进行视图的渲染,最终将目标页面展示到屏幕。    
由于表的数据越来越多,查询条件错综复杂,还有用json字段查询问题,决定将数据异构到es查询,将json字段打平,es天然支持复杂的查询条件,查询响应更快。

es数据同步方案:

在ES数据同步链路中,通过京东科技中间件DTS监听数据库的binlog,将索引字段(查询条件字段)及业务唯一id写入ES。
在业务运营查询时,根据复杂的查询条件,先去ES查询,将业务唯一id查出,再根据业务唯一id去DB中查询业务明细数据,同时解决了业务查询的复杂性和查询性能。




推荐阅读使用Taro开发鸿蒙原生应用——当Taro遇到纯血鸿蒙
京东流水线——满足你对工作流编排的一切幻想Java浅拷贝BeanUtils.copyProperties引发的RPC异常大文件上传实践分享


求分享

求点赞

求在看

打造SAAS化服务的会员徽章体系,可以作为标准的产品化方案统一对外输出。结合现有平台的通用能力,实现会员行为全路径覆盖,并能结合企业自身业务特点,规划相应的会员精准营销活动,提升会员忠诚度和业务的持续增长。底层能力:维护用户基础数据、行为数据建模、用户画像分析、精准营销策略的制定

▪功能支撑:会员成长体系、等级计算策略、权益体系、营销底层能力支持

▪用户活跃:会员关怀、用户触达、活跃活动、业务线交叉获客、拉新促活

继续滑动看下一个
京东技术
向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存