数据库系统中的“黑天鹅”

一 前言 纳西姆.尼古拉斯.塔勒布的经典著作《黑天鹅》中对“黑天鹅现象”的定义是

  1. - 不可预测,人们事前往往低估其发生的可能性
  2. - 造成极大影响
  3. - 事后回头再看,又觉得此事发生的有理

“黑天鹅” 不仅仅出现在自然的生物系统,也会出现在金融投资市场方面,也同样存在于我们工作的IT 系统层面,本文以数据库系统为切入点,说明异常的SQL给DB系统稳定性带来“黑天鹅”事件。

二 分析 稳定性是一项衡量基础系统是否永续服务的绝对指标,作为资深DBA从业人员,相信大多数公司运维团队都会制定稳定性的SLA指标达到N个9,为用户提供Full-Time 服务。然而前一段时间各种"黑天鹅”式的因素导致一系列的系统故障,严重影响了C端B端的用户的使用体验。故障是数据库系统或者说业务系统的“脆弱性”表现。什么是导致业务故障的“黑天鹅”呢?例举最近遇到的和数据库相关的场景: a 程序异常,比如异常传参导致本应该获取1行数据的结果去调用14w行,高压力下慢查询将数据库会话占满,引发”雪崩效应“。 b 正常分页调用,但是遇到大分页查询高频访问db,同样会导致慢查询引发“雪崩效应”。 c 第三方业务开发不了解api的使用方法 ,选择全量拉取而非增量拉取业务数据,导致大量慢查询。 上述三个例子的共性基本都含有慢查询,高频访问。找到导致问题发生的数据库层面的原因,剩下的就是发挥产品/开发DBA的特长了,获取到慢查询,然后各个击破之。本文举例几个具有代表性的sql。 案例一 大分页查询优化 商家会使用第三方软件拉取订单数据进行对账,使用limit N,M 分页查询每次拉取50 或者100页,小批量数据时比如N小于 10000时性能表现正常,但是遇到大的商家比如罗辑思维 ,糕妈优选等大商家,拉取数据的时间会随着N 的增加而增大。

  1. select * from so where 1 and `bb` = 'xxxxx' and `cc` in ('5') and `dd` in ('0','1','2','3') order by id desc limit 70000,100;

优化方法 1 利用索引的有序性,更确切的是利用 where条件的索引有序性,尽可能使用到组合索引的created_time有序性代替使用order by id查询,MySQL在使用索引的时候 只能利用一个有效索引,order by id 可能会导致优化器选择主键而非 cc,dd,created_time这样的组合索引。 2 通常我们推荐使用 延迟关联 的方法来优化大分页查询---利用覆盖索引获取复合条件的记录的主键id,然后驱动表根据主键来访问想要的数据,这样的访问速度要比limit 顺序扫描全索引然后回表的速度要快很多。

  1. select a.* from so a,(select id from so where 1 and `bb` = 'xxxxx' and `cc` in ('5') and `dd` in ('0','1','2','3') order by created_time desc limit 70000,101 ) b where a.id=b.id;

3 应用层优化商家本质上是想要获取全量数据,之前的方式是每天或者每周固定时间点定期获取某个时间段内的全量数据,换个思路我们的业务提供push推送任务,专门主动推送商家的增量数据,这样可以避免大批量的拉取全量数据,减少db的不稳定性也同时节约公司的带宽成本。 案例二 join 查询优化 大致的业务逻辑根据商品交易信息获取商家售卖销量,相关sql 以及表结构信息

  1. select count(o.ono) as num from so o, oi i where o.ono = i.ono and `o`.`kid` = 'xxxx' and `i`.`gid` = 'yyyy';
  2. oi 表的索引
  3. KEY `idx_sid` (`idx_sid`) USING BTREE,
  4. KEY `idx_ono` (`idx_ono`) USING BTREE,
  5. KEY `idx_created` (`created`)
  6. so 表的索引
  7. key idx_kid(kid,cc,created_time)

在MySQL中,目前而言只有一种join算法 也即是nested loop join:是通过驱动表(from后的第一个表)的结果集作为循环的基础数据,然后将结果集中的数据作为过滤条件一条条地到下一个表中查询数据,最后合并结果。本案例中可以理解为 以so kid=16553711 的结果数据 去匹配 oi 表中gid=yyyy 符合记录的数据,然后做count操作。通常我们对于join查询的优化原则是:

  1. 1 减少nested loop的循环次数,使用小结果集驱动大结果集。
  2. 2 优先优化Nested Loop的内层循环,内循环中的where条件一定要使用最优的索引。
  3. 3 保证join语句中被驱动表的join条件字段已经被索引;
  4. 4 如果无法保证被 驱动表的Join条件字段被索引且内存充足的情况下,可以通过调join_buffer_size来设置join buffer的大小 。

优化方法 1 根据优化原则我们将 oi表的idx_ono 索引调整为 idx_gid_ono(gid,ono),使用覆盖索引解决内循环回表的IO消耗。可能会有人会咨询为什么不调整表的顺序,其实第一个想到优化的就是调整顺序,但是在现有索引条件下调整驱动表的顺序并没有提高查询效率。 2 其实作为一个服务电商业务线的老司机,我认为涉及C端应用调用应该避免或者说禁止使用join查询,业务增长带来访问量透传给DB的压力,很可能将上面的优化结果轻松覆盖。最优化的方式尽可能的使用kv查询,单表查询。好在我们公司给力的开发同学王野已经将该优化业务迁移到es中,直接通过es获取结果。 案例三 并发count(*) 优化 因为开发对业务逻辑处理不力,导致数据库并发count 进程数飙高到200左右,严重影响到其他业务的正常请求。其实对于count操作的优化相对比较有限 1 确保where条件一定利用到最优索引。 2 业务层面避免并发count操作,可以使用缓存来规避直接访问db。 三 小结 最近一个多月一直紧跟公司的慢查询这块做集中优化,到目前为止效果相当不错,基本将慢查询减少了90%左右。从slow log文件大小来看,此次优化将文件大小从1M 减少到4k 左右,解决了绝大多数的潜在的系统风险。

诚然通过优化慢查询,使用缓存 ,并无法绝对避免“黑天鹅”式故障发生,系统的稳定性是应用层的健壮性,底层基础服务 网络,机器硬件,数据库层面等各个环节息息相关的,我们要做的就是通过提高数据库系统和业务系统的 “反脆弱性”,提高抗击打能力,为用户提供可持续的稳定的服务。 四 推荐文章 [1] 《黑天鹅:如何应对不可知的未来》 [2] 《反脆弱:从不确定性中获益》 [3] 《关于高可用的系统》

原文发表时间:2017-10-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大数据文摘

小白学数据:一文看懂NoSQL数据库

2326
来自专栏码匠的流水账

聊聊系统设计中的trade-off

trade-off翻译过来大致是折中的意思,也就是说系统设计通常牵扯的点比较多,有的设计方案这个方面比较好,但是又有其他缺点,没有十全十美的方案,只是在特定的上...

1873
来自专栏杨建荣的学习笔记

自动化平台开发小结(四)

今天对备份恢复和元数据的功能点进行了改进,突然发现需要做的事情远比想象的要多。 技术方面,目前Django的框架使用开始有一些需求的瓶颈了,因为有些需求从业务的...

3775
来自专栏杨建荣的学习笔记

闪回区报警引发的性能问题分析(r11笔记第11天)

自从有了Zabbix+Orabbix,很多监控都有了一种可控的方式,当然对于报警处理来说,报警是表象,很可能通过表象暴露出来的是一些更深层次的问题。这不又来一个...

36810
来自专栏程序猿

周五推荐时间:博客3期

“ 已经推荐两期了,这是第三期,菜单可以查看往期推荐,以下博客有Java Web专题:SSM系列讲解;墙裂推荐;还有第三位,讲解了比较复杂的SQL查询是如何完成...

1091
来自专栏腾讯大数据的专栏

SQL On Storm ——EasyCount系统概述

1. 摘要: TDW很好的解决了海量数据离线处理问题,但是在如下场景下:实时报表,实时监控,实时推荐,实时分析,TDW无法满足需求。而storm是应对这些场景的...

3139
来自专栏mini188

数据库SQL,NoSQL之小感悟

遇到1000万数据表 最近遇到一个问题,就是单表数据过1000万的存储及查询问题。举个例子:1000万的数据存在一个表中,字段4-5个样子,日常 开发中难免要做...

18810
来自专栏杨建荣的学习笔记

关于大数据和数据库的讨论(r5笔记第30天)

前几天上了水木社区,发现还是有大牛的,看了关于大数据和数据库的讨论,还是蛮有意思的,限于篇幅和版面,我做了部分的提取和整理。 先看看这位人士的分析,对于行业的现...

4686
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(37)-文章发布系统④-百万级数据和千万级数据简单测试

我想测试EF在一百万条数据下的显示时间!这分数据应该有很多同学想要,看看EF的性能! 服务器 ? 现在来向SQL2008R2插入1000000条数据吧 decl...

26110
来自专栏牛客网

美团前端春招三面面经

记一次应该凉了的美团前端春招 昨天晚上6点的视频面试,一二三面一次性面完,接近八点结束,然后就等通知,估计凉了 一面 做了下自我介绍,问了下几个项目和用到的技术...

4308

扫码关注云+社区

领取腾讯云代金券