首页  >  文章  >  数据库  >  自动生成索引的脚本

自动生成索引的脚本

WBOY
WBOY原创
2016-06-07 16:32:041291浏览

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明网址: http://www.penglixun.com/tech/database/autocreateindex_script_release.html 写了个自动建索引的脚本,基本原理是解析SQL 的条件,然后得出每个条件

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明网址: http://www.penglixun.com/tech/database/autocreateindex_script_release.html

写了个自动建索引的脚本,基本原理是解析SQL的条件,然后得出每个条件的运算符,最后通过优先级矩阵将可以建索引的列排序求最长的序列,也就是尽可能把更多的列包含进索引。

使用方法:
./AutoCreateIndex.pl -e “你的SQL”

对于多表连接,务必将列名前跟上表名,否则无法处理,因为脚本并不连接数据库,无法判断哪个列属于那个表。
测试版本,Bug很多,欢迎提供意见。

Note: There is a file embedded within this post, please visit this post to download the file.

下面是一些例子

plx@plinux<span style="color: #66cc66;">-</span>Laptop:~<span style="color: #66cc66;">/</span>Dropbox<span style="color: #66cc66;">/</span>MySQLScript$ <span style="color: #66cc66;">./</span>AutoCreateIndex<span style="color: #66cc66;">.</span>pl <span style="color: #66cc66;">-</span>e <span style="color: #ff0000;">"SELECT * FROM foo1 a,foo2 b WHERE 'fdsfsa'=a.col1 AND b.col2 = 'abc' AND a.col1=b.col2 AND a.col3 >1 ORDER BY b.col3,b.col1"</span>
<span style="color: #993333; font-weight: bold;">ALTER</span> <span style="color: #993333; font-weight: bold;">TABLE</span> foo1 <span style="color: #993333; font-weight: bold;">ADD</span> <span style="color: #993333; font-weight: bold;">INDEX</span> idx <span style="color: #66cc66;">&#40;</span>col1<span style="color: #66cc66;">,</span>col3<span style="color: #66cc66;">&#41;</span>;
<span style="color: #993333; font-weight: bold;">ALTER</span> <span style="color: #993333; font-weight: bold;">TABLE</span> foo2 <span style="color: #993333; font-weight: bold;">ADD</span> <span style="color: #993333; font-weight: bold;">INDEX</span> idx <span style="color: #66cc66;">&#40;</span>col2<span style="color: #66cc66;">,</span>col3<span style="color: #66cc66;">,</span>col1<span style="color: #66cc66;">&#41;</span>;
plx@plinux<span style="color: #66cc66;">-</span>Laptop:~<span style="color: #66cc66;">/</span>Dropbox<span style="color: #66cc66;">/</span>MySQLScript$ <span style="color: #66cc66;">./</span>AutoCreateIndex<span style="color: #66cc66;">.</span>pl <span style="color: #66cc66;">-</span>e <span style="color: #ff0000;">"SELECT * FROM t1 where t1 =1 and t2=2"</span>
<span style="color: #993333; font-weight: bold;">ALTER</span> <span style="color: #993333; font-weight: bold;">TABLE</span> t1 <span style="color: #993333; font-weight: bold;">ADD</span> <span style="color: #993333; font-weight: bold;">INDEX</span> idx <span style="color: #66cc66;">&#40;</span>t2<span style="color: #66cc66;">,</span>t1<span style="color: #66cc66;">&#41;</span>;
plx@plinux<span style="color: #66cc66;">-</span>Laptop:~<span style="color: #66cc66;">/</span>Dropbox<span style="color: #66cc66;">/</span>MySQLScript$ <span style="color: #66cc66;">./</span>AutoCreateIndex<span style="color: #66cc66;">.</span>pl <span style="color: #66cc66;">-</span>e <span style="color: #ff0000;">"SELECT * FROM t1 where col1=1 and col2=4"</span>
<span style="color: #993333; font-weight: bold;">ALTER</span> <span style="color: #993333; font-weight: bold;">TABLE</span> t1 <span style="color: #993333; font-weight: bold;">ADD</span> <span style="color: #993333; font-weight: bold;">INDEX</span> idx <span style="color: #66cc66;">&#40;</span>col1<span style="color: #66cc66;">,</span>col2<span style="color: #66cc66;">&#41;</span>;
plx@plinux<span style="color: #66cc66;">-</span>Laptop:~<span style="color: #66cc66;">/</span>Dropbox<span style="color: #66cc66;">/</span>MySQLScript$ <span style="color: #66cc66;">./</span>AutoCreateIndex<span style="color: #66cc66;">.</span>pl <span style="color: #66cc66;">-</span>e <span style="color: #ff0000;">"SELECT * FROM t1 where col1>1 and col2=4"</span>
<span style="color: #993333; font-weight: bold;">ALTER</span> <span style="color: #993333; font-weight: bold;">TABLE</span> t1 <span style="color: #993333; font-weight: bold;">ADD</span> <span style="color: #993333; font-weight: bold;">INDEX</span> idx <span style="color: #66cc66;">&#40;</span>col2<span style="color: #66cc66;">,</span>col1<span style="color: #66cc66;">&#41;</span>;
声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn