文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL大表优化的示例分析

2024-04-02 19:55

关注

小编给大家分享一下MySQL大表优化的示例分析,希望大家阅读完这篇文章之后都有所收获,下面让我们一起去探讨吧!


背景

阿里云RDS FOR MySQL(MySQL5.7版本)数据库业务表每月新增数据量超过千万,随着数据量持续增加,我们业务出现大表慢查询,在业务高峰期主业务表的慢查询需要几十秒严重影响业务

方案概述

MySQL大表优化的示例分析

一、数据库设计及索引优化

MySQL数据库本身高度灵活,造成性能不足,严重依赖开发人员的表设计能力以及索引优化能力,在这里给几点优化建议

最左索引匹配规则

顾名思义就是最左优先,在创建组合索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。复合索引很重要的问题是如何安排列的顺序,比如where后面用到c1, c2 这两个字段,那么索引的顺序是(c1,c2)还是(c2,c1)呢,正确的做法是,重复值越少的越放前面,比如一个列 95%的值都不重复,那么一般可以将这个列放最前面

二、数据库切换到PloarDB读写分离

PolarDB是阿里云自研的下一代关系型云数据库,100%兼容MySQL存储容量最高可达100 TB,单库最多可扩展到16个节点,适用于企业多样化的数据库应用场景。PolarDB采用存储和计算分离的架构,所有计算节点共享一份数据,提供分钟级的配置升降级、秒级的故障恢复、全局数据一致性和免费的数据备份容灾服务。

在离线混合场景:不同业务用不同的连接地址,使用不同的数据节点,避免相互影响

MySQL大表优化的示例分析

Sysbench性能压测报告:

MySQL大表优化的示例分析
MySQL大表优化的示例分析

MySQL大表优化的示例分析
MySQL大表优化的示例分析

三、分表历史数据迁移到MySQL8.0 X-Engine存储引擎

分表业务表保留3个月数据(这个根据公司需求来),历史数据按月分表到历史库X-Engine存储引擎表, 为什么要选用X-Engine存储引擎表,它有什么优点?

  1. 节约成本, X-Engine的存储成本约为InnoDB的一半

  2. X-Engine分层存储提高QPS, 采用层次化的存储结构,将热数据与冷数据分别存放在不同的层次中,并默认对冷数据所在层次进行压缩

X-Engine是阿里云数据库产品事业部自研的联机事务处理OLTP(On-Line Transaction Processing)数据库存储引擎。
X-Engine存储引擎不仅可以无缝对接兼容MySQL(得益于MySQL Pluginable Storage Engine特性),同时X-Engine使用分层存储架构。因为目标是面向大规模的海量数据存储,提供高并发事务处理能力和降低存储成本,在大部分大数据量场景下,数据被访问的机会是不均等的,访问频繁的热数据实际上占比很少,X-Engine根据数据访问频度的不同将数据划分为多个层次,针对每个层次数据的访问特点,设计对应的存储结构,写入合适的存储设备

MySQL大表优化的示例分析

四、阿里云PloarDB MySQL8.0版本并行查询

分表之后我们的数据量依然很大,并没有完全解决我们的慢查询问题,只是降低了我们业务表的体量,这部分慢查询我们需要用到PolarDB的并行查询优化

PolarDB MySQL 8.0重磅推出并行查询框架,当您的查询数据量到达一定阈值,就会自动启动并行查询框架,从而使查询耗时指数级下降
在存储层将数据分片到不同的线程上,多个线程并行计算,将结果流水线汇总到总线程,最后总线程做些简单归并返回给用户,提高查询效率。
并行查询(Parallel Query)利用多核CPU的并行处理能力,以8核32 GB配置为例,示意图如下所示。

MySQL大表优化的示例分析

并行查询适用于大部分SELECT语句,例如大表查询、多表连接查询、计算量较大的查询。对于非常短的查询,效果不太显著。

并行查询用法,使用Hint语法可以对单个语句进行控制,例如系统默认关闭并行查询情况下,但需要对某个高频的慢SQL查询进行加速,此时就可以使用Hint对特定SQL进行加速。

SELECT /+PARALLEL(x)/ … FROM …; – x >0

SELECT * FROM … // n > 0

查询测试:数据库配置 16核32G 单表数据量超3千万

没加并行查询之前是4326ms,加了之后是525ms,性能提升8.24倍

MySQL大表优化的示例分析

MySQL大表优化的示例分析

五、交互式分析Hologre

大表慢查询我们虽然用并行查询优化提升了效率,但是一些特定的需求实时报表、实时大屏我们还是无法实现,只能依赖大数据去处理。
这里推荐大家阿里云的交互式分析Hologre(
https://help.aliyun.com/product/113622.html)

MySQL大表优化的示例分析

看完了这篇文章,相信你对“MySQL大表优化的示例分析”有了一定的了解,如果想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯