浅谈SQL优化治理

原创 Richard 信也科技拍黑米

背景

公司开发项目上线初期，由于业务数据量相对较少，SQL的执行效率对程序运行效率的影响不太明显，但随着业务的快速发展，数据量的持续激增，对系统和数据库的性能压力越来越大，SQL的执行效率对程序的运行效率的影响也逐渐增大。目前80%的数据库性能问题都是因SQL而导致，SQL性能问题已经发展成为数据库性能的首要问题。面对日益增多的SQL性能问题，对SQL的审查与优化显得尤为重要。

SQL优化治理的痛点：

学习成本高，人员水平参差不齐；
团队人员更替，缺乏长效的数据治理机制；
缺少对sql质量的指标判断和整体监督，风险不可控

在项目运行中，哪些SQL是需要被治理和特别关注的呢？一般通过程序运行中不同问题的表现溯源，总结出一些常见的类型是需要被关注和治理：

项目上线后对于一次SQL优化的过程可能是大量的资源和时间投入，且没有得到很好的解决，从技术人员角度来说，问题提前被发现，修复的方案会越多，治理的成本会越低。而从企业资源来说，问题修复成本会随SQL --> 数据库 --> 系统配置 --> 硬件而提高，但优化的效果却恰好相反。

为什么要引入自动化工具？

目前企业对于SQL的治理过程基本大同小异，从项目不同时期来看，需要针对性的去对SQL进行预防、发现、分析、治理、预警。除了在问题出现后的紧急响应，在日常工作中，对于SQL的审查和治理也尤为重要。而项目中，技术人员面对众多的业务需求，通过人工干预发现和治理的投入成本过于巨大，会处于疲态应对的状态，演变成只开发不治理的循环中，项目内遗留大量的历史SQL，当计算和存储等资源达到瓶颈的问题暴露后，可能会被领导催着进行治理，缺乏长效的数据治理机制，导致阶段性治理和资源告急循环往复，治理的效果量化指标粗糙，对业务运行带来不可估量的损失。

因此，一套非人工干预，自动化监控、预警、发现和治理sql的优化方案引入，对于企业来说可以提升效率；利用工具可以迅速的定位、管理、优化sql，减少企业更多的资源浪费；能够优化和改写的自动化工具应运而生。

方案选择

节选了目前业内比较优秀的产品进行对比：

基于产品对比，选用SOAR看起来是一个不错的选择，SOAR(SQL Optimizer And Rewriter) 是一个对 SQL 进行优化和改写的自动化工具，由小米人工智能与云平台的数据库团队开发与维护，历时4年维护更新，功能强大，同时还支持多种优化治理手段。再结合SOAR WEB提供的sql分析与优化的web图形化能力，支持可视化web页面对soar配置进行添加、修改、复制，多配置切换，配置的导出、导入与导入，搭配Perfmance_schema，可组合成一套成熟可落地的SQL优化和改写自动化治理方案。

SOAR由Go语言开发，基于SQL抽象语法树、根据SQL改写军规数据、提供100+SQL改写建议；支持索引优化、正则(REGEXP)、EXPLAIN结果分析、SQL美化压缩、ALTER语句合并、量化标准，SQL打分、DML转SELECT；支持Markdown、HTML多种格式报告输出。

详见Github：https://github.com/XiaoMi/SOAR

MySQL Performance Schema 用于监控MySQL服务器的资源消耗、资源等待等情况，主要关注性能数据，使用PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现。

官方文档：
https://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

SOAR WEB是在soar提供的能力基础上，融合sql分析与优化的底层能力以图形化页面进行展示，支持soar配置的添加、修改、复制，多配置切换，配置的导出、在线结果分析、导入与导出等多样化功能。

详见Github：https://github.com/xiyangxixian/soar-web

SOAR + SOAR WEB + Performance_schema

治理自动化方案实现

第一步：SOAR安装

SOAR本身是基于GO语言开发，依赖GO1.10+ 版本，安装启用非常便捷，下载安装包之后生成二进制文件可直接启用，以下是官方提供的安装案例：

下载二进制安装包

wget https://github.com/XiaoMi/SOAR/releases/download/${tag}/SOAR.${OS}-amd64 -O SOARchmod a+x SOAR如：wget https://github.com/XiaoMi/SOAR/releases/download/0.9.0/SOAR.linux-amd64 -O SOARchmod a+x SOAR

生成二进制文件

go get -d github.com/XiaoMi/SOARcd ${GOPATH}/src/github.com/XiaoMi/SOAR && make

安装验证

echo 'select * from film' | ./SOAR

参考：https://github.com/XiaoMi/SOAR/blob/master/doc/install.md

第二步：数据库开启performance_schema

select * from setup_consumers;

show variables like 'performance_schema%history%size';

第三步：SOAR-WEB安装

环境需求

python3.x
Flask
pymysql
pycryptodome

安装与使用

下载源码（ Windows 可略过此步骤）：sudo -y apt-get install wget 或者 sudo yum -y install wget wget https://codeload.github.com/xiyangxixian/SOAR-web/zip/master -O SOAR-web-master.zip
解压缩（ Windows 可略过此步骤）：sudo -y apt-get install unzip 或者 sudo yum -y install unzip unzip SOAR-web.zipcd SOAR-web-matster
运行启动脚本：Windows：run.batLinux or Mac：bash run.sh
守护进程支持：启动服务：bash manage.sh start关闭服务：bash manage.sh stop重启服务：bash manage.sh restart