SQL优化工具SQLAdvisor使用（转）

一、简介

在数据库运维过程中，优化SQL是业务团队与DBA团队的日常任务。例行SQL优化，不仅可以提升程序性能，还能够降低线上故障的概率。

目前常用的SQL优化方式包括但不限于：业务层优化、SQL逻辑优化、索引优化等。其中索引优化通常通过调整索引或新增索引从而达到SQL优化的目的。索引优化往往可以在短时间内产生非常巨大的效果。如果能够将索引优化转化成工具化、标准化的流程，减少人工介入的工作量，无疑会大大提高DBA的工作效率

SQLAdvisor是由美团点评公司DBA团队(北京)开发维护的SQL优化工具：输入SQL，输出索引优化建议。 它基于MySQL原生词法解析，再结合SQL中的where条件以及字段选择度、聚合条件、多表Join关系等最终输出最优的索引优化建议。目前SQLAdvisor在公司内部大量使用，较为成熟、稳定。

美团点评致力于将SQLAdvisor打造成一款高智能化SQL优化工具，选择将已经在公司内部使用较为成熟的、稳定的SQLAdvisor项目开源，github地址。希望与业内有类似需求的团队，一起打造一款优秀的SQL优化产品。

目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。

主要功能：输出SQL索引优化建议

GitHup地址：https://github.com/Meituan-Dianping/SQLAdvisor

二、SQLAdvisor安装

2.1 拉取最新代码

	<div id="crayon-5a4489b117a55461357441" class="crayon-syntax crayon-theme-shell-default crayon-font-courier-new crayon-os-pc print-yes notranslate" data-settings=" minimize scroll-always" style="margin-top: 10px; margin-bottom: 12px; font-size: 14px !important; line-height: 20px !important; height: auto;">
	
		<div class="crayon-plain-wrap"><textarea wrap="soft" class="crayon-plain print-no" data-settings="dblclick" readonly="" style="tab-size: 4; font-size: 14px !important; line-height: 20px !important; z-index: 0; opacity: 0;">$ git clone https://github.com/Meituan-Dianping/SQLAdvisor.git</textarea></div>
		<div class="crayon-main" style="position: relative; z-index: 1;">
			<table class="crayon-table" style="margin-left: -20px;">
				<tbody><tr class="crayon-row">
			<td class="crayon-nums " data-settings="hide">
				<div class="crayon-nums-content" style="font-size: 14px !important; line-height: 20px !important;"><div class="crayon-num" data-line="crayon-5a4489b117a55461357441-1">1</div></div>
			</td>
					<td class="crayon-code"><div class="crayon-pre" style="font-size: 14px !important; line-height: 20px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;"><div class="crayon-line" id="crayon-5a4489b117a55461357441-1"><span class="crayon-sy">$</span><span class="crayon-h"> </span><span class="crayon-e">git </span><span class="crayon-r">clone</span><span class="crayon-h"> </span><span class="crayon-v">https</span><span class="crayon-o">:</span><span class="crayon-o">/</span><span class="crayon-o">/</span><span class="crayon-v">github</span><span class="crayon-e">.com</span><span class="crayon-o">/</span><span class="crayon-v">Meituan</span><span class="crayon-o">-</span><span class="crayon-v">Dianping</span><span class="crayon-o">/</span><span class="crayon-v">SQLAdvisor</span><span class="crayon-e">.git</span></div></div></td>
				</tr>
			</tbody></table>
		</div>
	</div>

2.2 安装依赖项

	<div id="crayon-5a4489b117a61399075953" class="crayon-syntax crayon-theme-shell-default crayon-font-courier-new crayon-os-pc print-yes notranslate" data-settings=" minimize scroll-always" style="margin-top: 10px; margin-bottom: 12px; font-size: 14px !important; line-height: 20px !important; height: auto;">
	
		<div class="crayon-plain-wrap"><textarea wrap="soft" class="crayon-plain print-no" data-settings="dblclick" readonly="" style="tab-size: 4; font-size: 14px !important; line-height: 20px !important; z-index: 0; opacity: 0;">$ yum install cmake libaio-devel libffi-devel glib2 glib2-devel</textarea></div>
		<div class="crayon-main" style="position: relative; z-index: 1;">
			<table class="crayon-table" style="margin-left: -20px;">
				<tbody><tr class="crayon-row">
			<td class="crayon-nums " data-settings="hide">
				<div class="crayon-nums-content" style="font-size: 14px !important; line-height: 20px !important;"><div class="crayon-num" data-line="crayon-5a4489b117a61399075953-1">1</div></div>
			</td>
					<td class="crayon-code"><div class="crayon-pre" style="font-size: 14px !important; line-height: 20px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;"><div class="crayon-line" id="crayon-5a4489b117a61399075953-1"><span class="crayon-sy">$</span><span class="crayon-h"> </span><span class="crayon-e">yum </span><span class="crayon-e">install </span><span class="crayon-e">cmake </span><span class="crayon-v">libaio</span><span class="crayon-o">-</span><span class="crayon-e">devel </span><span class="crayon-v">libffi</span><span class="crayon-o">-</span><span class="crayon-e">devel </span><span class="crayon-e">glib2 </span><span class="crayon-v">glib2</span><span class="crayon-o">-</span><span class="crayon-v">devel</span></div></div></td>
				</tr>
			</tbody></table>
		</div>
	</div>

跟据glib安装的路径，修改SQLAdvisor/sqladvisor/CMakeLists.txt中的两处include_directories针对glib设置的path。glib yum安装默认不需要修改路径。

另外，编译sqladvisor时依赖perconaserverclient_r, 因此需要安装Percona-Server-shared-56。

	<div id="crayon-5a4489b117a65446579014" class="crayon-syntax crayon-theme-shell-default crayon-font-courier-new crayon-os-pc print-yes notranslate" data-settings=" minimize scroll-always" style="margin-top: 10px; margin-bottom: 12px; font-size: 14px !important; line-height: 20px !important; height: auto;">
	
		<div class="crayon-plain-wrap"><textarea wrap="soft" class="crayon-plain print-no" data-settings="dblclick" readonly="" style="tab-size: 4; font-size: 14px !important; line-height: 20px !important; z-index: 0; opacity: 0;"># 配置Percona56 yum源;

$ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

安装Percona-Server-shared-56;

$ yum install Percona-Server-shared-56