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

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

  1. - 不可预测,人们事前往往低估其发生的可能性
  2. - 造成极大影响
  3. - 事后回头再看,又觉得此事发生的有理
二 分析
  稳定性是一项衡量基础系统是否永续服务的绝对指标,作为资深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';
  1. oi 表的索引
  2.   KEY `idx_sid` (`idx_sid`) USING BTREE,
  3.   KEY `idx_ono` (`idx_ono`) USING BTREE,
  4.   KEY `idx_created` (`created`)
  5. so 表的索引
  6.   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。
     关于count的优化文章可以参考 拙作 《性能优化之 count(*) VS count(col) 》 
三 小结
    最近一个多月一直紧跟公司的慢查询这块做集中优化,到目前为止效果相当不错,基本将慢查询减少了90%左右。从slow log文件大小来看,此次优化将文件大小从1M 减少到4k 左右,解决了绝大多数的潜在的系统风险。 

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

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落在了DB这边,希望DB能够给点意见,是否存在一些性能瓶颈。     我们从开发同学那里得到的一个基本的SQL语句,根据关键字从v$sql中做了提取,发现对应的SQL语句的执行时间还是OK的。 得到的SQL语句如下: SQL_ID        SQL_FULLTEXT ------------- ---------------------------

Oracle之不可见索引 - 2016-08-27 17:08:09

Oracle 之不可见索引 1    BLOG 文档结构图   2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  Oracle 不可见索引的使用   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lhrbest )和微信公众号( xiaomaimiaolhr )有同步更新
【故障处理】序列 cache 值过小导致 CPU 利用率过高 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① enq: SQ - contention 等待事件的解决 ② 一般等待事件的解决办法 ③  DFS lock handle 等待事件 ④ 与序列有关的等待事件   Tips: ① 本文在 ITpub ( http://blog.itpub.
这两天做性能测试碰见一个问题,比较有意思。 一条SQL,使用了绑定变量,查看V$SQLAREA发现version_count是2,  查看V$SQL,发现有两条记录,分别对应了0和1两个child cursor:  再查看这两个child cursor对应的执行计划:  child cursor:0 -----------------------------------------------------------------------------------------------------| I

OCP如何查看历史成绩(2) - 2016-08-25 14:08:08

OCP如何查看历史成绩( 2 ) 之前写过一篇文章 OCP如何查看历史成绩: http://blog.itpub.net/26736162/viewspace-1624996/  , 但是现在界面好像有所变动,而且有的连接也找不见了,今天有朋友问如何查询历史的成绩,晚上小麦苗就抽了点时间来找找,顺便记录下来。最近心情不好,不想写技术文章了。       首先登陆网址: https://education.oracle.com/ https://education.oracle.com/pls/eval-e
下午的时候收到这么一条报警。 ZABBIX- 监控系统 : ------------------------------------ 报警内容 : Too many parallel sessions on xxxxx_ xx机房 _xxxxx ------------------------------------ 报警级别 : PROBLEM ------------------------------------ 监控项目 : parallel_session_cnt : 66 -----------

【MySQL】Tokudb安装测试初探 - 2016-08-24 14:08:02

一 前言    TokuDB 是一个高性能、支持MVCC的MySQL 和 MariaDB 的存储引擎。TokuDB 的主要特点是数据压缩功能出色,对高写压力的支持,由美国TokuTek公司(http://www.tokutek.com/) 研发,该公司于2015年4月份被Percona收购,理所当然地提供了TokuDB版本的Percona Server。本文使用Peronca server 5.6.30 版本进行测试安装。 二 安装前的准备     请参考官方文档 Percona Server YUM源

通过shell脚本添加备库日志 - 2016-08-21 17:08:14

今天下午的时候,准备顺手写一个简单的脚本,但是发现很多事情较真起来真是寸步难行。在写脚本的过程中碰到了太多的问题,很多时候感觉像要实现的功能更通用,就得做更多的检查,更多的校验也就意味着有更多的预先条件,这些条件里面有些是规范和建议,有些是按照已有的配置情况,尽管如此,自己感觉还是缺少了太多的检查。   先来说说今天尝试的简单脚本,就是给主库添加standby logfile,这个需求听起来非常简单,都甚至在我的半自动化脚本中隐去了,但是把这个需求要落到纸面上来,简直了。 首先这个需求会涉及到下面的几个数据
今天在测试时遇到一个ORA-38706ORA-38707报错,乍一看到报错内容竟然没有回过神儿来。 ORA-38706ORA-38707报错 点击( 此处 )折叠或打开 SYS @ HOEGH select banner from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition R

Oracle之虚拟索引 - 2016-08-21 14:08:07

Oracle 之虚拟索引 1    BLOG 文档结构图     2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  Oracle 虚拟索引的使用   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lhrbest )和微信公众号( xiaomaimiaolhr )有同步更新 ②