数据库——SQL调优

SQL调优是一项很重要的技术，实际在使用小数据集时DBMS自身的搜索优化是能满足我们的时间需求的，只有在数据集规模到10w+以后才能在秒级的层面看到SQL代码优化的效果。

本次实验环境如下：

操作系统：Mac OS X 10.13
DBMS：MySQL 8.0
图形化管理界面：MySQL Workbench

表结构

本次实验选用了一个待测试表，名为‘pv_train_part1’，其结构如下：

此数据集来源于我跟随老师做的项目，从数据实例和分布来看其明显是一个机器学习模型的训练数据样本，大体是一个八参数的分类问题（实则是一个多参数的特征选取和异常数据监测项目）。

本数据取自github上的开源数据集，其链接为：
数据集

通过MySQL Workbench的‘Table Data Import Wizard’功能，可以实现将外部csv文件导入的功能：
Alt text

通过执行结果可以观测到数据记录数如下：
Alt text

首先我使用的第一条优化规则便是建立索引，这在几乎所有的DBMS上是通用的优化策略。为了便于展示，这里的优化执行结果以执行查询操作来作为反馈。

在没建立索引前，表的设计层面如下：
Alt text
执行查询语句：

1	select feature3 from pv_train_part1 where pv_train_part1.key=59081;

结果如下：
Alt text

可以看到该查询的执行时间为： 0.056 second 。

然后我们给key属性添加主键索引，添加完之后设计层面如下：
Alt text

添加完成之后重新执行相同的查询语句，执行时间如下：
Alt text

可以看到，在添加完主键索引之后，查询时间变成了 0.00034 second，较未添加索引之间查询效率提高了 163 倍。

在MySQL中，假如只需要一条查询条件的结果，或者在事先已经知道查询语句仅仅会返回一条结果的情况下，我们可以在查询语句的结尾后添加 “limit 1” 字段以便在查询到结果后中断对剩余表单的检索，对比如下：

首先我们对表执行查询语句：

1	select feature3 from pv_train_part1 where pv_train_part1.label=3 ;

查询时间如下：
Alt text

可以看到，通过普通查询语句查询，其执行时间在 0.039 second。

然后对上述sql语句后添加 “limit 1” ，修改为：

1	select feature3 from pv_train_part1 where pv_train_part1.label=3 limit 1;

查询时间如下：
Alt text
可以看到，在添加limit 1后，其执行时间缩短为了 0.00049 second，相较于优化前的查询，效率提高了 203 倍。