[编程技术] MySQL对于千万级的大表要怎么优化?
作者:CC下载站 日期:2021-12-04 00:00:00 浏览:25 分类:编程开发
背景
目前做电商后台开发,经历了数据从零到百万到百亿级别的增长。在这个增长的过程中,我们做了很多的优化。
这里分享一下我们的优化经验,仅供参考。
优化策略:
不要过度优化,优化的过程一定是跟随着业务量的增长来慢慢调整优化策略的,比如说,没人就在一开始数据为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
取消回复欢迎 你 发表评论:
- 精品推荐!
-
- 最新文章
- 热门文章
- 热评文章
[课程] 《大师级航拍教程》63节课程视频 MP4格式 5.9G
[资料] 中医鬼才倪海厦全集完整版【PDF/MP4/RMVB】
[课程] 聂佳判断推理绝版课程大集合【8G】
[电视剧] 芈月传 【全集81集全】【未删减版】【国语中字】【2015】【HD720P】【75G】
[电视剧] 封神榜 梁丽版 (1989) 共5集 480P国语无字 最贴近原著的一版【0.98 G】
[影视] 【雪山飞孤4个版本】【1985、1991、1999、2007】【1080P、720P】【中文字幕】【167.1G】
[资料] 24秋初中改版教材全集(全版本)[PDF]
[电影] 高分国剧《康熙王朝》(2001)4K 2160P 国语中字 全46集 78.2G
[动画] 迪士尼系列动画139部 国英双语音轨 【蓝光珍藏版440GB】
[电影] 莫妮卡贝鲁奇为艺术献身电影大合集 1080P超清 双语字幕
[书籍] 彭子益医书合集 [PDF/DOC]
[游戏] 《黑神话悟空》免安装学习版【全dlc整合完整版】+Steam游戏解锁+游戏修改工具!
[动画] 《名侦探柯南》名侦探柯南百万美元的五菱星 [TC] [MP4]
[电视剧集] [BT下载][黑暗城市- 清扫魔 Dark City: The Cleaner 第一季][全06集][英语无字][MKV][720P/1080P][WEB-RAW]
[动画] 2002《火影忍者》720集全【4K典藏版】+11部剧场版+OVA+漫画 内嵌简日字幕
[剧集] 《斯巴达克斯》1-4季合集 无删减版 1080P 内嵌简英特效字幕
[CG剧情] 《黑神话:悟空》158分钟CG完整剧情合集 4K120帧最高画质
[游戏] 黑神话悟空离线完整版+修改器
[短剧] 被下架·禁播的羞羞短剧·午夜短剧合集
[图像处理] 光影魔术手v4.6.0.578绿色版
[影视] 美国内战 4K蓝光原盘下载+高清MKV版/内战/帝国浩劫:美国内战(台)/美帝崩裂(港) 2024 Civil War 63.86G
[影视] 一命 3D 蓝光高清MKV版/切腹 / 切腹:武士之死 / Hara-Kiri: Death of a Samurai / Ichimei 2011 一命 13.6G
[影视] 爱情我你他 蓝光原盘下载+高清MKV版/你、我、他她他 2005 Me and You and Everyone We Know 23.2G
[影视] 穿越美国 蓝光原盘下载+高清MKV版/窈窕老爸 / 寻找他妈…的故事 2005 Transamerica 20.8G
[电影] 《黄飞鸿》全系列合集
[Android] 开罗游戏 ▎像素风格的模拟经营的游戏厂商安卓游戏大合集
[游戏合集] 要战便战 v0.9.107 免安装绿色中文版
[书籍] 彭子益医书合集 [PDF/DOC]
[资源] 精整2023年知识星球付费文合集136篇【PDF格式】
[系统]【黑果小兵】macOS Big Sur 11.0.1 20B50 正式版 with Clover 5126 黑苹果系统镜像下载
- 最新评论
-
谢谢分享感谢ppy2016 评论于:11-05 谢谢分享感谢ppy2016 评论于:11-05 怎么没有后续闲仙麟 评论于:11-03 怎么没后续闲仙麟 评论于:11-03 有靳东!嘻嘻奥古斯都.凯撒 评论于:10-28 流星花园是F4处女作也是4人集体搭配的唯一一部!奥古斯都.凯撒 评论于:10-28 找了好久的资源,终于在这里找到了。感谢本站的资源和分享。谢谢AAAAA 评论于:10-26 找了好久的资源,终于在这里找到了。感谢本站的资源和分享。谢谢password63 评论于:10-26 找了好久的资源,终于在这里找齐了!!!!blog001 评论于:10-21 找了好久的资源,终于在这里找齐了!!!!blog001 评论于:10-21
- 热门tag