[编程技术] MySQL对于千万级的大表要怎么优化?
作者:CC下载站 日期:2021-12-04 00:00:00 浏览:5 分类:编程开发
背景
目前做电商后台开发,经历了数据从零到百万到百亿级别的增长。在这个增长的过程中,我们做了很多的优化。
这里分享一下我们的优化经验,仅供参考。
优化策略:
不要过度优化,优化的过程一定是跟随着业务量的增长来慢慢调整优化策略的,比如说,没人就在一开始数据为0的时候就开始畅想10亿的数据的优化方案。
所以一般是业务量的增长在某个方向达到了性能瓶颈或者将要达到性能瓶颈,我们针对特殊的点做优化。
同时,不同的业务场景,优化的方案也不尽相同,比如读频繁的场景、写频繁的场景、分页查询场景、热点数据场景。
优化
零、建表
最开始我们针对一个单一业务只有一张表,比如用户表,存了用户的所有信息。一开始数据量少,并没有什么性能压力。
一、优化SQL语句和索引
比如是否有回表,索引是否可以优化来减少回表,查询条件是否可以修改来命中索引。
二、主从复制/读写分离
其实很多时候主从复制在一开始设计的时候就应该支持了。毕竟很多的ORM库本身也是支持的,并没有很多难度。
三、缓存
当访问量(QPS)增大,MySQL已经有了性能的压力,就该考虑加缓存了。现行的比较常见的缓存策略就是Cache Aside Pattern
,也就是如果有更新,让缓存失效(删除)。
为何不更新缓存而是删除缓存呢,主要是为了防止并发的情况下可能缓存不一致的问题。所以现在大多数缓存方案都是删除的方案。它能保证数据的最终一致性。
我们的做法是监听MySQL的binlog,某一行数据有修改,删除对应的缓存,比较常用的就是Memcached,我们也有地方会用到Redis,比如需要分页查询的时候,使用Redis的ZSET方便支持。
四、垂直切分
加了缓存之后发现缓存命中率低,为啥呢,因为我们的表里面有几列数据是常更新的数据。比如:登入时间,登出时间。当他们变化的时候会删除缓存,导致重新读DB。
这个时候需要一定的策略来增大缓存的命中率,我们使用垂直切分,把易变的数据和不易变的数据且分开来。这样大多数数据不易变的,就能充分利用缓冲的优势,易变的数据可以单独优化。
五、水平切分,分库分表
关键业务就水平切分,做sharding,分库。
非关键业务直接上TiDB吧。数据量增加到亿级别之后,我们很多业务直接切换到TiDB了。TiDB 是一个兼容MySQL 协议和语法的分布式数据库,支撑百亿数据没有问题,只是在生产环境还不太稳定,有时候会出现无法命中索引需要加force index
,还有由于它的二段提交机制,经常会有一些write conflict
之类的。
而对于一些关键业务,我们不放心使用TiDB,开始对MySQL进行水平切分。
六、热点数据本地缓存
取决于你的数据是否有热点数据,比如秒杀活动,双十一大促的活动,很多热点店铺,热点商品等等。这种热点数据,即时使用了Memcached/Redis缓存,还是可能导致缓存的单节点性能瓶颈,这个时候就需要使用内存本地缓存,我们使用的LRU缓存。
七、水平切分(写)
对于易变的数据,QPS增大很容易触发性能瓶颈,这个时候需要水平切分来降低单个DB/表的写入QPS,来降低MySQL的资源占用。
八、消息队列(写)
异步写入,进行流量削峰,降低峰值的压力。同时降低线上服务的压力。
九、批量更新(写)
我们的更新QPS一直在增涨,即使分库分表之后,每个库的update QPS
也非常高,最后我们使用批量更新的方式,把数据先更新到缓存,然后批量取固定量的数据一起更新DB。
关于批量更新:
我们由于更新的QPS过大,导致了MySQL的CPU/IO/Network
都非常大。我们就是用了一种批量更新的方式,也就是MySQL的UPDATE SET CASE WHEN
语法。
- MQ或者API写入缓存,并把主键写入到Redis ZSET,主要是为了批量的取数据。我们之前已经水平切分到了1000个table,所以这里有1000个Redis ZSET。
- 设计一个定时器的服务,定时从ZSET里面POP 20条数据,根据主键从实际的缓存里面拿具体的value数据。
- 组装MySQL批量更新语法一次性更新。
Note:我们定时器的服务可以支持分布式任务调度,我们利用了Kafka的partition机制,每个服务都消费同一个kafka的同一个group,这样kafka会给每个服务分配互斥的partition列表。我们通过实现partition->任务分片
的映射算法,最终增加或者减少实例数量的时候,通过监听的kafka的partition的变化,来决定处理那些任务分片,从而实现了无中心服务器的任务调度。
我们这种方式,使MySQL的IO/Network/CPU都降低了80%以上。这也是我们更新的终极方案了。
总结
上文只是我们优化经历的一些阶段,不同数据不同分析,比如你的查询主要是多行查询而非单行查询,那优化方式就不一样了。
像我们有服务是多行数据查询的,我们就使用Redis的List来实现了缓存,由于是更新缓存,担心不一致,我们同时监听了MySQL的binlog来校验缓存和数据库的一致性。
同时读频繁场景和写频繁场景,优化方案也不一样。
所以说,MySQL的优化,不是一个通用的方案,只能具体业务具体分析。但是大概方案就是我上面列出来的那些。希望对你的优化有启发。
<全文完>
猜你还喜欢
- 03-29 [编程相关] Winform窗体圆角以及描边完美解决方案
- 03-29 [前端问题] has been blocked by CORS policy跨域问题解决
- 03-29 [编程相关] GitHub Actions 入门教程
- 03-29 [编程探讨] CSS Grid 网格布局教程
- 10-12 [编程相关] python实现文件夹所有文件编码从GBK转为UTF8
- 10-11 [编程算法] opencv之霍夫变换:圆
- 10-11 [编程算法] OpenCV Camshift算法+目标跟踪源码
- 10-11 [Python] python 创建 Telnet 客户端
- 10-11 [编程相关] Python 基于 Yolov8 + CPU 实现物体检测
- 03-15 [脚本工具] 使用go语言开发自动化脚本 - 一键定场、抢购、预约、捡漏
- 01-08 [编程技术] 秒杀面试官系列 - Redis zset底层是怎么实现的
- 01-05 [编程技术] 《Redis设计与实现》pdf
取消回复欢迎 你 发表评论:
- 精品推荐!
-
- 最新文章
- 热门文章
- 热评文章
[英语教育] 新概念英语视频教程
[文字识别] 离线截图批量识别文字软件 Umi-OCR v2.1.1
[影视] 零食小屋 WEB-DL版下载/开个零食小铺 2023 The Snack Shack
[教育课程] 孙维刚数学课程[小学+初中+高中全套]
[游戏攻略] 云顶金铲铲:14.7版本初,实测阵容推荐,学会这几套上分不迷茫
[游戏攻略] 2024UEFN内容更新路线图
[游戏资讯] 《使命召唤:现代战争Ⅲ/战区》第三赛季 通行证一览
[实用工具] 学校班级、任课教师用今日课表(大屏、电脑均可使用)
[AI工具] AI换脸神器-AI FaceSwap v2.0.0,电脑端+本地部署,使用简单
[书籍] 海外中国研究丛书 江苏人民出版社 PDF
[资料] [大学期末救急课] 猴博士+高斯课堂+斐多课堂,全集视频合集
[云资源] 价值2万元的老男孩Python教程
[书库] 史上最全摄影书推荐(附700本PDF版打包下载)
[云资源] 花了一千多元买的私人健身教程
[下载工具] Internet Download Manager 6.42.7 (IDM)
[影视] 灌篮高手 WEB-DL版下载/Slam Dunk/スラムダンク/灌篮高手:THE FIRST/灌篮高手电影版 2022 The First Slam Dunk 61.35G
[即时通讯] 腾讯QQ PC版9.7.22.29315去广告绿色纯净版
[开发环境] PhpStorm2023中文激活版v2023.3.3 正式版
[资料] 3000 套电影电视剧 LOGO 宣传片常用音效合集包
[图像制作] Adobe Illustrator 2024 v28.1.0.141 破解版
[云资源] 价值2万元的老男孩Python教程
[影视] 灌篮高手 WEB-DL版下载/Slam Dunk/スラムダンク/灌篮高手:THE FIRST/灌篮高手电影版 2022 The First Slam Dunk 61.35G
[云资源] 花了一千多元买的私人健身教程
[书库] 史上最全摄影书推荐(附700本PDF版打包下载)
[安卓软件] Android GIF助手 v3.9.7 GIF图片编辑器破解版
[电视剧] 三体 (2024) 全8集 网飞版本 中文字幕 合集
[剧集] 繁花 (2023)[全30集][打包]
[影视] 三大队 WEB-DL版下载/Endless Journey/请转告局长,三大队任务完成了 2023 三大队 6.7G
[纪录片] 河西走廊【10集 国语 中文字幕 1080P 10.8G MP4】
[安卓软件] OfficeSuite中文版APP v14.2.50872.0破解版
- 最新评论
-
谢谢支持!!CC下载站 评论于:03-26 很棒的资源,感谢分享云体风身 评论于:03-26 感谢分享,好东西云体风身 评论于:03-26 谢谢支持!CC下载站 评论于:03-14 央视精品,感谢付出提供。qwer9009 评论于:03-14 谢谢支持!!!CC下载站 评论于:03-13 谢谢分享!Ypc9182 评论于:03-12 谢谢支持!!CC下载站 评论于:03-11 感谢本网站收集和提供这么多的资料,谢谢!Ypc9182 评论于:03-10 很棒的资源,支持一下lingzhi007 评论于:03-10
- 热门tag