招聘网站大全,网站开发liucheng,北京网站优化外包公司,做网站需要的资料最近在线上环境遇到了一次SQL慢查询引发的数据库故障#xff0c;影响线上业务。经过排查后#xff0c;确定原因是#xff1a;SQL在执行时#xff0c;MySQL优化器选择了错误的索引(不应该说是“错误”#xff0c;而是选择了实际执行耗时更长的索引)。排查过程中#xff0c… 最近在线上环境遇到了一次SQL慢查询引发的数据库故障影响线上业务。经过排查后确定原因是SQL在执行时MySQL优化器选择了错误的索引(不应该说是“错误”而是选择了实际执行耗时更长的索引)。排查过程中查阅了许多资料也学习了下MySQL优化器选择索引的基本准则在本文中进行解决问题思路的分享。本人MySQL了解深度有限如有错误欢迎在评论区理性讨论和指正。在这次事故中也能充分看出深入了解MySQL运行原理的重要性这是遇到问题时能否独立解决问题的关键。 试想一个月黑风高的夜晚公司线上突然挂了而你的同事们都不在线就你一个人有条件解决问题这时候如果被工程师的基本功把你卡住了就问你尴不尴尬...本文的主要内容故障描述问题原因排查MySQL索引选择原理解决方案思考与总结。故障描述在7月24日11点线上某数据库突然收到大量告警慢查询数超标并且引发了连接数暴增导致数据库响应缓慢影响业务。看图表慢查询在高峰达到了每分钟14w次在平时正常情况下慢查询数仅在两位数以下如下图赶紧查看慢SQL记录发现都是同一类语句导致的慢查询(隐私数据例如表名我已经隐去)select *from sample_tablewhere 1 1 and (city_id 565) and (type 13)order by id desclimit 0, 1看起来语句很简单没什么特别的但是每个执行的查询时间达到了惊人的44s。简直耸人听闻这已经不是“慢”能形容的了...接下来查看表数据信息如下图可以看到表数据量较大预估行数在83683240也就是8000w左右千万数据量的表。大致情况就是这样下面进入排查问题的环节。问题原因排查首先当然要怀疑会不会该语句没走索引查看建表DML中的索引KEY idx_1 (city_id,type,rank),KEY idx_log_dt_city_id_rank (log_dt,city_id,rank),KEY idx_city_id_type (city_id,type)请忽略idx_1和idx_city_id_type两个索引的重复这都是历史遗留问题了。可以看到是有idx_city_id_type和idx_1索引的我们的查询条件是city_id和type这两个索引都是能走到的。但是我们的查询条件真的只要考虑city_id和type吗(机智的小伙伴应该注意到问题所在了先往下讲留给大家思考)既然有索引接下来就该看该语句实际有没有走到索引了MySQL提供了Explain可以分析SQL语句。Explain用来分析SELECT查询语句。Explain比较重要的字段有select_type : 查询类型有简单查询、联合查询、子查询等key : 使用的索引rows : 预计需要扫描的行数。更多详细Explain介绍可以参考MySQL性能优化神器Explain使用分析。我们使用Explain分析该语句select * from sample_table where city_id 565 and type 13 order by id desc limit 0,1得到结果可以看出虽然possiblekey有我们的索引但是最后走了主键索引。而表是千万级别并且该查询条件最后实际是返回的空数据也就是MySQL在主键索引上实际检索时间很长导致了慢查询。我们可以使用force index(idx_city_id_type)让该语句选择我们设置的联合索引select * from sample_table force index(idx_city_id_type) where ( ( (1 1) and (city_id 565) ) and (type 13) ) order by id desc limit 0, 1这次明显执行得飞快分析语句实际执行时间0.00175714s走了联合索引后不再是慢查询了。问题找到了总结下来就是MySQL优化器认为在limit 1的情况下走主键索引能够更快的找到那一条数据并且如果走联合索引需要扫描索引后进行排序而主键索引天生有序所以优化器综合考虑走了主键索引。实际上MySQL遍历了8000w条数据也没找到那个天选之人(符合条件的数据)所以浪费了很多时间。MySQL索引选择原理1、优化器索引选择的准则MySQL一条语句的执行流程大致如下图而查询优化器则是选择索引的地方引用参考文献一段解释首先要知道选择索引是MySQL优化器的工作。而优化器选择索引的目的是找到一个最优的执行方案并用最小的代价去执行语句。在数据库里面扫描行数是影响执行代价的因素之一。扫描的行数越少意味着访问磁盘数据的次数越少消耗的CPU资源越少。当然扫描行数并不是唯一的判断标准优化器还会结合是否使用临时表、是否排序等因素进行综合判断。总结下来优化器选择有许多考虑的因素扫描行数、是否使用临时表、是否排序等等。我们回头看刚才的两个explain截图走了主键索引的查询语句rows预估行数1833而强制走联合索引行数是45640并且Extra信息中显示需要Using filesort进行额外的排序。所以在不加强制索引的情况下优化器选择了主键索引因为它觉得主键索引扫描行数少而且不需要额外的排序操作主键索引天生有序。2、rows是怎么预估出来的同学们就要问了为什么rows只有1833明明实际扫描了整个主键索引啊,行数远远不止几千行。实际上explain的rows是MySQL预估的行数是根据查询条件、索引和limit综合考虑出来的预估行数。MySQL是怎样得到索引的基数的呢这里我给你简单介绍一下MySQL采样统计的方法。为什么要采样统计呢因为把整张表取出来一行行统计虽然可以得到精确的结果但是代价太高了所以只能选择“采样统计”。采样统计的时候InnoDB默认会选择N个数据页统计这些页面上的不同值得到一个平均值然后乘以这个索引的页面数就得到了这个索引的基数。而数据表是会持续更新的索引统计信息也不会固定不变。所以当变更的数据行数超过1/M的时候会自动触发重新做一次索引统计。在MySQL中有两种存储索引统计的方式可以通过设置参数innodb_stats_persistent的值来选择设置为on的时候表示统计信息会持久化存储。这时默认的N是20M是10。设置为off的时候表示统计信息只存储在内存中。这时默认的N是8M是16。由于是采样统计所以不管N是20还是8这个基数都是很容易不准的。我们可以使用analyze table t命令可以用来重新统计索引信息。但是这条命令生产环境需要联系DBA所以我就不做实验了大家可以自行实验。3、索引要考虑order by的字段为什么这么说因为如果我这个表中的索引是city_id,type和id的联合索引那优化器就会走这个联合索引因为索引已经做好了排序。4、更改limit大小能解决问题把limit数量调大会影响预估行数rows进而影响优化器索引的选择吗答案是会。我们执行limit 10select * from sample_table where city_id 565 and type 13 order by id desc limit 0,10图中rows变为了18211增长了10倍。如果使用limit 100会发生什么优化器选择了联合索引。初步估计是rows还会翻倍所以优化器放弃了主键索引。宁愿用联合索引后排序也不愿意用主键索引了。5、为何突然出现异常慢查询Q这个查询语句已经在线上稳定运行了非常长的时间为何这次突然出现了慢查询A以前的语句查询条件返回结果都不为空limit1很快就能找到那条数据返回结果。而这次代码中查询条件实际结果为空导致了扫描了全部的主键索引。解决方案知道了MySQL为何选择这个索引的原因后我们就可以根据上面的思路来列举出解决办法了。主要有两个大方向强制指定索引干涉优化器选择。1、强制选择索引force index就像上面我最开始的操作那样我们直接使用force index让语句走我们想要走的索引。select * from sample_table force index(idx_city_id_type) where ( ( (1 1) and (city_id 565) ) and (type 13) ) order by id desc limit 0, 1这样做的优点是见效快问题马上就能解决。缺点也很明显高耦合这种语句写在代码里会变得难以维护如果索引名变化了或者没有这个索引了代码就要反复修改。属于硬编码很多代码用框架封装了SQLforce index()并不容易加进去。我们换一种办法去引导优化器选择联合索引。2、干涉优化器选择增大limit通过增大limit我们可以让预估扫描行数快速增加比如改成下面的limit 0, 1000SELECT * FROM sample_table where city_id 565 and type 13 order by id desc LIMIT 0,1000这样就会走上联合索引然后排序但是这样强行增长limit其实总有种面向黑盒调参的感觉。我们还有更优美的解决方案吗3、干涉优化器选择增加包含order by id字段的联合索引我们这句慢查询使用的是order by id但是我们却没有在联合索引中加入id字段导致了优化器认为联合索引后还要排序干脆就不太想走这个联合索引了。我们可以新建city_id,type和id的联合索引来解决这个问题。这样也有一定的弊端比如我这个表到了8000w数据建立索引非常耗时而且通常索引就有3.4个g如果无限制的用索引解决问题可能会带来新的问题。表中的索引不宜过多。4、干涉优化器选择写成子查询还有什么办法我们可以用子查询在子查询里先走city_id和type的联合索引得到结果集后在limit1选出第一条。但是子查询使用有风险一般DBA也不建议使用子查询会建议大家在代码逻辑中完成复杂的查询。当然我们这句并不复杂啦~Select * From sample_table Where id in (Select id From newhome_db.af_hot_price_region where (city_id 565 and type 13)) limit 0, 15、还有很多解决办法SQL优化是个很大的工程我们还有非常多的办法能够解决这句慢查询问题这里就不一一展开了。总结本文带大家回顾了一次MySQL优化器选错索引导致的线上慢查询事故可以看出MySQL优化器对于索引的选择并不单单依靠某一个标准而是一个综合选择的结果。我自己也对这方面了解不深入还需要多多学习争取能够好好的做一个索引选择的总结(挖坑)。不说了拿起巨厚的《高性能MySQL》开始...压住我的泡面...最后做个文章总结该慢查询语句中使用order by id导致优化器在主键索引和city_id和type的联合索引中有所取舍最终导致选择了更慢的索引可以通过强制指定索引建立包含id的联合索引增大limit等方式解决问题平时开发时尤其是对于特大数据量的表要注意SQL语句的规范和索引的建立避免事故的发生。参考资料《高性能MySQL》MySQL优化器 limit影响的casehttps://www.cnblogs.com/xpchild/p/3878417.htmlMySQL中走与不走索引的情况汇集(待全量实验)https://www.cnblogs.com/gxyandwmm/p/13363100.htmlMySQL ORDER BY主键id加LIMIT限制走错索引https://www.jianshu.com/p/caf5818eca81【业务学习】关于MySQL order by limit 走错索引的探讨https://segmentfault.com/a/1190000020399424MySQL为什么有时候会选错索引https://www.cnblogs.com/a-phper/p/10313888.html作者丨蛮三刀把刀来源丨后端技术漫谈(IDRude3Knife)dbaplus社群欢迎广大技术人员投稿投稿邮箱editordbaplus.cn云时代下数据库将如何革新与创变金融行业核心数据库迁移与建设如何安全平稳展开开源技术如何在实际业务场景中发挥实力10月30日DAMS中国数据智能管理峰会将在上海举办专设【数据库分场】部分议题如下《从自研演进看分布式数据库》中国银联 云计算中心团队主管 周家晶《开源数据库MySQL在民生银行的应用实践》民生银行 项目经理 徐春阳《TDSQL在金融行业数据库上云实战》腾讯云 高级经理 陈琢《如何构建数据库容器化PaaS》爱可生 资深方案架构师 徐阳立即扫码享受早鸟价在数据库变迁中站稳脚跟
