×
首页数据库正文
IT序号网

SQL优化工具SQLAdvisor使用（转）

developer 2021年06月14日 数据库 353 0

一、简介

在数据库运维过程中，优化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地址：IT虾米网

二、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 IT虾米网

安装Percona-Server-shared-56;

$ yum install Percona-Server-shared-56








1
2
3
4
5

# 配置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


如果yum安装不行，可以采用rpm包手动安装。参考：https://github.com/Meituan-Dianping/SQLAdvisor/issues/12

需要配置软链接：

	<div id="crayon-5a4489b117a68995533635" 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;">$ rpm -ql Percona-Server-shared-56

/usr/lib64/libperconaserverclient.so.18
/usr/lib64/libperconaserverclient.so.18.1.0
/usr/lib64/libperconaserverclient_r.so.18
/usr/lib64/libperconaserverclient_r.so.18.1.0








1
2
3
4
5

$ rpm - ql Percona - Server - shared - 56
/ usr / lib64 / libperconaserverclient .so . 18
/ usr / lib64 / libperconaserverclient .so . 18.1.0
/ usr / lib64 / libperconaserverclient_r .so . 18
/ usr / lib64 / libperconaserverclient_r .so . 18.1.0 


	<div id="crayon-5a4489b117a6b113467534" 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;">$ cd /usr/lib64/

$ ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so








1
2

$ cd / usr / lib64 /
$ ln - s libperconaserverclient_r .so . 18 libperconaserverclient_r .so


2.3 编译依赖项sqlparser

	<div id="crayon-5a4489b117a6e169289204" 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;">$ cd /root/SQLAdvisor/

$ cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
$ make && make install








1
2
3

$ cd / root / SQLAdvisor /
$ cmake - DBUILD_CONFIG = mysql_release - DCMAKE_BUILD_TYPE = debug - DCMAKE_INSTALL_PREFIX = / usr / local / sqlparser . /
$ make && make install


注意

  • DCMAKE_INSTALL_PREFIX为sqlparser库文件和头文件的安装目录，其中lib目录包含库文件libsqlparser.so，include目录包含所需的所有头文件。
  • DCMAKE_INSTALL_PREFIX值尽量不要修改，后面安装依赖这个目录。

2.4 安装SQLAdvisor源码

	<div id="crayon-5a4489b117a72980058383" 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;">$ cd /root/SQLAdvisor/sqladvisor/

$ cmake -DCMAKE_BUILD_TYPE=debug ./
$ make








1
2
3

$ cd / root / SQLAdvisor / sqladvisor /
$ cmake - DCMAKE_BUILD_TYPE = debug . /
$ make


安装完成后，在本路径下生成一个sqladvisor可执行文件，这即是我们想要的。

可以把可执行文件sqladvisor复制到PATH路径中。

	<div id="crayon-5a4489b117a75611628530" 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;">$ cp -frp /root/SQLAdvisor/sqladvisor/sqladvisor /usr/local/bin/</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-5a4489b117a75611628530-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-5a4489b117a75611628530-1"><span class="crayon-sy">$</span><span class="crayon-h"> </span><span class="crayon-r">cp</span><span class="crayon-h"> </span><span class="crayon-o">-</span><span class="crayon-v">frp</span><span class="crayon-h"> </span><span class="crayon-o">/</span><span class="crayon-v">root</span><span class="crayon-o">/</span><span class="crayon-v">SQLAdvisor</span><span class="crayon-o">/</span><span class="crayon-v">sqladvisor</span><span class="crayon-o">/</span><span class="crayon-v">sqladvisor</span><span class="crayon-h"> </span><span class="crayon-o">/</span><span class="crayon-v">usr</span><span class="crayon-o">/</span><span class="crayon-v">local</span><span class="crayon-o">/</span><span class="crayon-v">bin</span><span class="crayon-o">/</span></div></div></td> 
				</tr> 
			</tbody></table> 
		</div> 
	</div>

三、SQLAdvisor使用

2.1 帮助输出

	<div id="crayon-5a4489b117a78791299064" 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;">$ sqladvisor --help

Usage:
sqladvisor [OPTION...] sqladvisor

SQL Advisor Summary

Help Options:
-?, --help Show help options

Application Options:
-f, --defaults-file sqls file
-u, --username username
-p, --password password
-P, --port port
-h, --host host
-d, --dbname database name
-q, --sqls sqls
-v, --verbose 1:output logs 0:output nothing








1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

$ sqladvisor -- help
Usage :
   sqladvisor [ OPTION . . . ] sqladvisor
 
SQL Advisor Summary
 
Help Options :
   - ? , -- help               Show help options
 
Application Options :
   - f , -- defaults - file      sqls file
   - u , -- username           username
   - p , -- password           password
   - P , -- port               port
   - h , -- host               host
   - d , -- dbname             database name
   - q , -- sqls               sqls
   - v , -- verbose            1 : output logs 0 : output nothing


2.2 命令行传参调用

	<div id="crayon-5a4489b117a7b145413975" 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;">$ sqladvisor -h xx  -P xx  -u xx -p 'xx' -d xx -q "sql" -v 1</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-5a4489b117a7b145413975-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-5a4489b117a7b145413975-1"><span class="crayon-sy">$</span><span class="crayon-h"> </span><span class="crayon-v">sqladvisor</span><span class="crayon-h"> </span><span class="crayon-o">-</span><span class="crayon-i">h</span><span class="crayon-h"> </span><span class="crayon-v">xx</span><span class="crayon-h">&nbsp;&nbsp;</span><span class="crayon-o">-</span><span class="crayon-i">P</span><span class="crayon-h"> </span><span class="crayon-v">xx</span><span class="crayon-h">&nbsp;&nbsp;</span><span class="crayon-o">-</span><span class="crayon-i">u</span><span class="crayon-h"> </span><span class="crayon-v">xx</span><span class="crayon-h"> </span><span class="crayon-o">-</span><span class="crayon-i">p</span><span class="crayon-h"> </span><span class="crayon-s">'xx'</span><span class="crayon-h"> </span><span class="crayon-o">-</span><span class="crayon-i">d</span><span class="crayon-h"> </span><span class="crayon-v">xx</span><span class="crayon-h"> </span><span class="crayon-o">-</span><span class="crayon-i">q</span><span class="crayon-h"> </span><span class="crayon-s">"sql"</span><span class="crayon-h"> </span><span class="crayon-o">-</span><span class="crayon-i">v</span><span class="crayon-h"> </span><span class="crayon-cn">1</span></div></div></td> 
				</tr> 
			</tbody></table> 
		</div> 
	</div>

2.3 配置文件传参调用

	<div id="crayon-5a4489b117a7e283858778" 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;">$ cat sql.cnf

[sqladvisor]
username=xx
password=xx
host=xx
port=xx
dbname=xx
sqls=sql1;sql2;sql3....








1
2
3
4
5
6
7
8

$ cat sql .cnf
[ sqladvisor ]
username = xx
password = xx
host = xx
port = xx
dbname = xx
sqls = sql1 ; sql2 ; sql3 . . . . 


	<div id="crayon-5a4489b117a81399811858" 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;">$ sqladvisor -f sql.cnf  -v 1</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-5a4489b117a81399811858-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-5a4489b117a81399811858-1"><span class="crayon-sy">$</span><span class="crayon-h"> </span><span class="crayon-v">sqladvisor</span><span class="crayon-h"> </span><span class="crayon-o">-</span><span class="crayon-i">f</span><span class="crayon-h"> </span><span class="crayon-v">sql</span><span class="crayon-e">.cnf</span><span class="crayon-h">&nbsp;&nbsp;</span><span class="crayon-o">-</span><span class="crayon-i">v</span><span class="crayon-h"> </span><span class="crayon-cn">1</span></div></div></td> 
				</tr> 
			</tbody></table> 
		</div> 
	</div>

2.4 测试使用

	<div id="crayon-5a4489b117a83667518117" 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;">$ cat sqladvisor.cnf

[sqladvisor]
username=root
password=zabbix
host=localhost
port=3306
dbname=blog
sqls=SELECT * FROM wp_posts where post_date>"2015-07-16 18:44:27";








1
2
3
4
5
6
7
8

$ cat sqladvisor .cnf
[ sqladvisor ]
username = root
password = zabbix
host = localhost
port = 3306
dbname = blog
sqls = SELECT * FROM wp_posts where post_date > "2015-07-16 18:44:27" ; 


	<div id="crayon-5a4489b117a86689208274" 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;">$ sqladvisor -f sqladvisor.cnf -v 1

2017-04-13 17:39:59 23848 [Note] 第1步: 对SQL解析优化之后得到的SQL:select * AS * from blog.wp_posts where (post_date > '2015-07-16 18:44:27')
2017-04-13 17:39:59 23848 [Note] 第2步：开始解析where中的条件:(post_date > '2015-07-16 18:44:27')
2017-04-13 17:39:59 23848 [Note] show index from wp_posts
2017-04-13 17:39:59 23848 [Note] show table status like 'wp_posts'
2017-04-13 17:39:59 23848 [Note] select count() from ( select post_date from wp_posts FORCE INDEX( PRIMARY ) order by ID DESC limit 3763) wp_posts where (post_date > '2015-07-16 18:44:27')
2017-04-13 17:39:59 23848 [Note] 第3步：表wp_posts的行数:7527,limit行数:3763,得到where条件中(post_date > '2015-07-16 18:44:27')的选择度:1
2017-04-13 17:39:59 23848 [Note] 第4步：表wp_posts 的SQL太逆天,没有优化建议
2017-04-13 17:39:59 23848 [Note] 第5步: SQLAdvisor结束!








1
2
3
4
5
6
7
8
9

$ sqladvisor - f sqladvisor .cnf - v 1
2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] 第 1步 : 对 SQL解析优化之后得到的 SQL : select </span><span class="crayon-o">*</span><span class="crayon-sy"> AS </span><span class="crayon-o">*</span><span class="crayon-sy"> from </span><span class="crayon-v">blog</span><span class="crayon-sy"> . </span><span class="crayon-v">wp_posts</span><span class="crayon-sy"> where ( </span><span class="crayon-v">post_date</span><span class="crayon-sy"> > '2015-07-16 18:44:27' )
2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] 第 2步：开始解析 where中的条件 : ( </span><span class="crayon-v">post_date</span><span class="crayon-sy"> > '2015-07-16 18:44:27' )
2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] show index from wp _posts
2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] show table status like 'wp_posts'
2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] select count ( ) from ( select </span><span class="crayon-v">post_date</span><span class="crayon-sy"> from </span><span class="crayon-v">wp_posts</span><span class="crayon-sy"> FORCE INDEX ( PRIMARY ) order by ID DESC limit 3763 ) </span><span class="crayon-v">wp_posts</span><span class="crayon-sy"> where ( </span><span class="crayon-v">post_date</span><span class="crayon-sy"> > '2015-07-16 18:44:27' )
2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] 第 3步：表 wp posts的行数:7527,limit行数:3763,得到where条件中(</span><span class="crayon-v">post_date</span><span class="crayon-sy"> > '2015-07-16 18:44:27')的选择度:1
2017-04-13 17:39:59 23848 [Note] 第4步：表wpposts 的 SQL太逆天 ,没有优化建议
2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] 第 5步 : SQLAdvisor结束 !


四、SQLAdvisor工作原理

详情看：SQLAdvisor架构和实践

五、SQLAdvisor Web

美团开源出来的SQLAdvisor SQL优化建议工具只有命令行，所以有网友就针对这个开发出了Web版本，告别命令行。

GitHub地址：IT虾米网

  • 项目中使用的美团SQL分析工具是在CentOS上编译的，所以建议部署到CentOS上。
  • 该项目是使用Python的Flask框架开发的。
  • 使用CentOS自带的Python版本,版本号是2.7.5。
原文地址：http://www.ywnds.com/?p=8996&viewuser=49 
</article>

评论关闭
IT序号网
IT序号网 数据库

微信公众号号：IT虾米 （左侧二维码扫一扫）欢迎添加！