jjzjj

携程SQL上线流程优化，如何从源头扼杀慢查询？

轻风博客 2023-03-28 原文

一、背景

 

慢查询指的是数据库中查询时间超过了指定的阈值的SQL，这类SQL通常伴随着执行时间长、服务器资源占用高、业务响应慢等负面影响。随着携程酒店业务的不断扩张，再加上大量的SQLServer转MySQL项目的推进，慢查询的数量正在飞速增长，每日的报警量也居高不下，因此慢查询的治理优化已经是刻不容缓，此文主要针对MySQL。

 

二、慢查询治理实践

 

1、SQL上线流程优化

 

 

之前的流程发布比较快捷，但是随着质量差的SQL发布\迁移得越来越多，告警和回退数量也随之变多，综合下来，数据库风险方面不容乐观，该流程需要优化。

 

 

和旧流程相比，新增了一个SQLReview的环节，将潜在的慢查询提前筛选出来优化，确保上线的SQL质量，在此流程保障下，所有上线到生产的SQL性能都能在DBA评估后的可控范围内，在研发提交审核后，会收到审批的事件单。

 

 

携程目前是存在自动化review审核的平台，但是由于酒店业务场景比较复杂，研发对于SQL的理解水平层次不齐，平台给出的建议并不能做到面面俱到，因此还没有被广泛使用于流程中，仅作为一个参考。

 

2、理解查询语句

 

要优化慢查询，首先要知道慢查询是如何产生的，执行计划是怎么样的，最后考虑如何去优化查询。

 

1）SQL流程及查询优化器

 

一条sql的执行主要分成如图几个步骤：

 

  • SQL语法的缓存查询（QC）

  • 语法解析（SQL的编写、关键字的语法之类）

  • 生成执行计划

  • 执行查询

  • 输出结果

 

 

通常慢查询都发生在“执行查询”这步，读懂查询计划，可以有效地帮助我们分析SQL性能差的原因。

 

2）执行计划

 

在SQL前面加上EXPLAIN，就可以查看执行计划，计划以“表”的形式展示：

 

 

具体字段含义可以参考MySQL官方的解释，这里不多赘述。

 

 

3、优化慢查询

 

通过执行计划就可以定位到问题点，通常可以分为这几种常见的原因。

 

 

1）索引层面

 

 

①索引缺失

 

这个查询由于缺少name字段索引，产生了全表扫描：

select * from hotel where name=’xc’;

 

 

补上索引之后，提示使用到了索引。

 

 

②索引失效

 

 

如图所示，索引失效的大致原因可以分为八类，这些场景通过查看执行计划都会发现产生type=ALL或者type=index的全表扫描。

 

  • Like、or、非操作符、函数

 

explain select * from hotel where name like '%酒店%';
explain select * from hotel where name like '%酒店%'or Bookable='T';
explain select * from hotel where name  <>'酒店';
explain select * from hotel where substring(name,1,2)='酒店';

 

 

  • 参数类型不匹配


create table t1 (
col1 varchar(3) primary key
)engine=innodb default charset=utf8mb4;

 

 

t1表的col1为varchar类型，但是参数传入的是数值类型，结果产生了隐形转换，索引失效导致type=index的全表扫描。

 

  • 联合索引

 

Where条件不符合“最左匹配原则”，则索引会失效。

alter table hotel add index idx_hotelid_name_isdel(hotelid,name,status);

 

以下条件均可以命中联合索引：


explain select * from hotel where hotelid=10000 and name='ctrip' and status='T';
explain select * from hotel where hotelid=10000 and name='ctrip';
explain select * from hotel where hotelid=10000;

 

 

但是以下条件无法使用到联合索引：

explain select * from hotel where name='ctrip' and status='T';
explain select * from hotel where name='ctrip';
explain select * from hotel where status='T';

 

 

  • 数据分布和数据量

 

索引字段的数据分布不均匀，表数据量过小的情况下，MYSQL查询优化器可能认为返回的数据量本身就很多，通过索引扫描并不能减少多少开销，此时选择全表扫描的权重会提高很多。

 

③查询不带where条件

 

不带where条件直接查询\修改全表是很危险的操作，表数据量够大的话，尽量拆分成多批次操作。

 

 

优化中遇到的案例：

 

某天发现有一台DB服务器IO异常，服务器链接开始堆积，引发了大量应用报错

 

 

 

监控显示此时repl延迟已经有25分钟，集群几乎处于无高可用状态，非常的危险。

 

 

登陆服务器排查后发现有一条全表删除的SQL在通过JOB系统跑，该表的数据量很大：

-tarpresqls "delete from XXXXXX"

 

最后紧急Kill这条SQL后恢复正常，直接在生产删除全表是很危险的操作。

 

④强制使用索引

 

MySQL中存在force index()、ignore index（）方式来强制使用/忽略特定的索引。

 

这种方式可能会导致执行计划选择不到最优的索引，从而导致计划走偏。

 

 

⑤性能差索引的Index Merge

 

Index merge方法可以对同一个表使用多个索引分别进行条件扫描，检索多个范围扫描并将结果合并为一个。

 

 

但是，当遇到如图2个索引字段分布都很差的情况时（status与bookable的区分度都很低），2个索引的结果集存在大量数据需要merge，性能就会变得很糟糕。

 

2）SQL频率

 

 

  • 业务代码while、for循环的结束条件不正确，导致模块内产生死循环

  • 业务逻辑本身存在高并发场景，例如秒杀、短期促销活动、直播带货等

  • 通过定时JOB循环拉取全量数据，但是循环的并发节奏控制不到位

  • 缓存被击穿、业务代码发布后缓存失效等原因，导致大量请求直接打到了db

 

3）写法不规范

 

 

①分页写法

 

最常见的分页写法就是使用limit，在分页查询时，我们会在 LIMIT 后面传两个参数，一个是偏移量(offset)，一个是获取的条数(limit)。当偏移量很小时，查询速度很快，但是随着 offset 变大时，查询速度会越来越慢。

 

MySQL Limit 语法格式：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

 

例如下列分页查询：

 

 

当limit只有0，10时，执行还是很快，但是随着offset增加，可以看到深度分页的情况下，分页越深，扫描的行数就越多，性能也就越来越差了。

explain select * from testlimittable order by id limit 1000, 10;
explain select * from testlimittable order by id limit 10000, 10;
explain select * from testlimittable order by id limit 20000, 10;
explain select * from testlimittable order by id limit 30000, 10;
explain select * from testlimittable order by id limit 40000, 10;
explain select * from testlimittable order by id limit 50000, 10;
explain select * from testlimittable order by id limit 60000, 10;

 

 

*：警惕通过分页写法来实现循环分批的逻辑，limit深分页实现不了将大量数据拆分成若干小份的效果

 

分批可以采用分段拉取减少扫描的行数，如果分段拉取不连续的话可以传入上一次拉取最大的值作为下一次的起始值：

 

 

②最大最小值写法

 

由于where条件的字段数据分布问题，会导致max和min的查询非常慢：

explain select max(id) from hotel where hotelid=10000 and status='T';

 

 

由于hotelid=10000的数据分布比较多，可以看到扫描数很高：

 

  • 添加联合索引

alter table hotel add index idx_hotelid_status(hotelid,status);

 

 

在索引覆盖下，extra提示Select tables optimized away，这意味着在查询执行期间不需要读取表，可以通过索引直接返回结果。

 

  • 改写为order by的方式

explain select id from hotel where hotelid=10000 and status='T' order by id desc limit 1;

 

 

扫描数很少，虽然是type=index的索引扫描，但是由于MYSQL对limit的优化，实际上并不会全表扫描。

 

③排序聚合写法

 

通常SQL在使用Group by及Order by后，会产生临时表和文件排序操作。若查询条件的数据量非常大，temporary和filesort都会产生额外的巨大开销。

 

 

  • 使用索引来满足排序聚合

alter table hotel add index idx_name_hotelid(name,hotelid);

 

 

此时MYSQL可以通过访问索引来避免执行filesort 及temporary操作

 

  • 取消隐形排序

 

在某些情况下，Group by会默认实现隐形排序，通过添加ORDER BY NULL可以取消这种隐形排序。

 

*注意从MySQL 8.0开始，不会再有这种情况了，因此不需要ORDER BY NULL写法了

 

4）资源

 

 

①锁资源等待

 

在读写很热的表上，通常会发生锁资源争夺，从而导致慢查询的情况。

 

  • 谨慎使用for update查询

  • 增删改尽量保证使用到索引

  • 降低并发，避免对同一条数据进行反复的修改

 

②网络波动

 

往客户端发送数据时发生网络波动导致的慢查询

 

③硬件配置

 

CPU利用率高，磁盘IO经常满载，导致慢查询

 

三、总结

 

慢查询治理是一个长期且漫长的过程，不应等SQL超时报错后才开始考虑优化，从一开始就要建立完善的日常化流程体系，才能有效的控制慢查询的增长。

 

但是经过长期优化后发现，仅仅从数据库层面优化，并不能实现慢查询完全“清零”，还有很多的痛点来自于业务逻辑和应用层面本身。这也需要研发工程师着重优化业务逻辑、应用策略，并加强数据库培训，在编写SQL时切勿过于随意，贪图省事，否则事后再优化会变得相当困难。

 

作者丨xuqi 潘达鸣 康男

扼杀SQLdatasectionnbspMySQL

有关携程SQL上线流程优化，如何从源头扼杀慢查询？的更多相关文章

  1. ruby - 如何使用 Nokogiri 的 xpath 和 at_xpath 方法 - 2

    我正在学习如何使用Nokogiri，根据这段代码我遇到了一些问题:require'rubygems'require'mechanize'post_agent=WWW::Mechanize.newpost_page=post_agent.get('http://www.vbulletin.org/forum/showthread.php?t=230708')puts"\nabsolutepathwithtbodygivesnil"putspost_page.parser.xpath('/html/body/div/div/div/div/div/table/tbody/tr/td/div

  2. ruby - 如何从 ruby​​ 中的字符串运行任意对象方法？ - 2

    总的来说，我对ruby​​还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用

  3. python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2

    关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。

  4. ruby-on-rails - 如何验证 update_all 是否实际在 Rails 中更新 - 2

    给定这段代码defcreate@upgrades=User.update_all(["role=?","upgraded"],:id=>params[:upgrade])redirect_toadmin_upgrades_path,:notice=>"Successfullyupgradeduser."end我如何在该操作中实际验证它们是否已保存或未重定向到适当的页面和消息？ 最佳答案 在Rails3中，update_all不返回任何有意义的信息，除了已更新的记录数(这可能取决于您的DBMS是否返回该信息)。http://ar.ru

  5. ruby-on-rails - 'compass watch' 是如何工作的/它是如何与 rails 一起使用的 - 2

    我在我的项目目录中完成了compasscreate.和compassinitrails。几个问题:我已将我的.sass文件放在public/stylesheets中。这是放置它们的正确位置吗？当我运行compasswatch时，它不会自动编译这些.sass文件。我必须手动指定文件:compasswatchpublic/stylesheets/myfile.sass等。如何让它自动运行？文件ie.css、print.css和screen.css已放在stylesheets/compiled。如何在编译后不让它们重新出现的情况下删除它们？我自己编译的.sass文件编译成compiled/t

  6. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  7. ruby - 如何将脚本文件的末尾读取为数据文件(Perl 或任何其他语言) - 2

    我正在寻找执行以下操作的正确语法(在Perl、Shell或Ruby中):#variabletoaccessthedatalinesappendedasafileEND_OF_SCRIPT_MARKERrawdatastartshereanditcontinues. 最佳答案 Perl用__DATA__做这个:#!/usr/bin/perlusestrict;usewarnings;while(){print;}__DATA__Texttoprintgoeshere 关于ruby-如何将脚

  8. ruby - 如何指定 Rack 处理程序 - 2

    Rackup通过Rack的默认处理程序成功运行任何Rack应用程序。例如:classRackAppdefcall(environment)['200',{'Content-Type'=>'text/html'},["Helloworld"]]endendrunRackApp.new但是当最后一行更改为使用Rack的内置CGI处理程序时，rackup给出“NoMethodErrorat/undefinedmethod`call'fornil:NilClass”:Rack::Handler::CGI.runRackApp.newRack的其他内置处理程序也提出了同样的反对意见。例如Rack

  9. ruby - 如何每月在 Heroku 运行一次 Scheduler 插件？ - 2

    在选择我想要运行操作的频率时，唯一的选项是“每天”、“每小时”和“每10分钟”。谢谢!我想为我的Rails3.1应用程序运行调度程序。 最佳答案 这不是一个优雅的解决方案，但您可以安排它每天运行，并在实际开始工作之前检查日期是否为当月的第一天。 关于ruby-如何每月在Heroku运行一次Scheduler插件？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/8692687/

  10. ruby-on-rails - 如何从 format.xml 中删除 <hash></hash> - 2

    我有一个对象has_many应呈现为xml的子对象。这不是问题。我的问题是我创建了一个Hash包含此数据，就像解析器需要它一样。但是rails自动将整个文件包含在.........我需要摆脱type="array"和我该如何处理？我没有在文档中找到任何内容。 最佳答案 我遇到了同样的问题；这是我的XML:我在用这个:entries.to_xml将散列数据转换为XML，但这会将条目的数据包装到中所以我修改了:entries.to_xml(root:"Contacts")但这仍然将转换后的XML包装在“联系人”中，将我的XML代码修改为

随机推荐