由一条SQL语句引发的思考

帮老师干活，做2020年山东高考录取情况的汇总表，纯靠Excel总归有些操作不是很好处理，所以我借助了MySQL数据库完成一些比如复杂条件合并的操作。

昨天晚上老师要我把两个Excel表里面的一些数据合并一下，但在执行修改操作时发现这条语句跑了两分钟还要多，感觉这不合理，于是来思考一下，到底是为什么会造成这样的？

确认需求

上面是两个表的数据，其中bkzy2020表代表2020年本科专业的数据，我已经将专业代号和学校代号使用子串操作取了出来。detail2020是2020年本科专业招生的细节数据，其中我需要合并的是detail2020中的major_name（专业全称）和subject_select（选科要求），要把这两条数据按照专业代号和学校代号双对应的条件合并到bkzy2020中。

初期尝试与发现的问题

这好像挺简单的，思路是使用UPDATE语句修改bkzy2020中的major_name_full和subject_select，条件是两个表的专业代号和学校代号能对应起来。然后有了下面的SQL语句：

UPDATE bkzy2020, detail2020
SET bkzy2020.major_name_full = detail2020.major_name , bkzy2020.subject_select = detail2020.subject_select
WHERE bkzy2020.major_id = detail2020.major_id and bkzy2020.school_id = detail2020.school_id

但这个SQL存在的问题是，他太慢了。跑了几十秒都没有出结果，感觉有点像是写错了，可就我这花瓶功夫的SQL技术也不知道该从哪下手，于是我去找了驼君同学。

与驼君同学的讨论

果然是基础不牢地动山摇，其实这个更新是可以在形式上简化一下的。

UPDATE bkzy2020 a , detail2020 b
SET a.major_name_full = b.major_name , a.subject_select = b.subject_select
WHERE a.major_id = b.major_id and a.school_id = b.school_id

这样写就舒服多了，其中a和b是代表bkzy2020和detail2020的别名。

实际上在第一次跑SQL的时候，我漏加了一个条件，当时没注意，后来我在后台挂着这条语句的执行去和驼君讨论，没想到过了一会后，它提示跑出来了…

135秒，两分钟多了。然而由于这里只对了专业id没有加学校id，导致到后面对应出来的成了这样子：

其实这里如果是所有学校专业代号互不重复的话，不判断学校也是没问题的，但实际上专业代号在学校之间会重复，只能保证在某个学校内的专业代号互不重复。

后来我加上学校的判断条件后（也就是执行了上面贴出来那段SQL），又跑了140多秒，这次执行应该没啥问题了…奇怪，为啥仍然有对不上的？

后来发现这其实是个低级错误…这是第一次修改遗留下来的问题！在执行第二次修改时没有对数据进行清空，所以导致出现了这样的问题。

清空一下，重新开始再跑两分钟..

update bkzy2020 set major_name_full = null, subject_select = null

这样总算是能做到结果没问题了，我将数据表导出为Excel文件，接下来就轮到复盘瞎搞的时间了。

最终解决方案与思考

为什么这条SQL会这么慢？

就数据量而言，说大不大，说小不小。两个表的规模大概都在16000条左右，但不相同。驼君同学说，因为我没加索引，这条修改走的是全表扫描，也就是规模大概有个一万六乘一万六的笛卡尔积。想想貌似挺恐怖的…

那加上索引试试吧：

索引类型选NORMAL，索引方法选BTREE，此时再分析一下刚才那条查询，对比一下添加前后的执行情况，如图所示：

能够看到，扫描的行数直接减少了一万六，所以理论上性能会得到一个很大的提升。

此时再清空数据尝试一下：

时间降低到了0.29秒，这才对味。

感觉以我的能力而言，这样的优化已经算是做到极限了。那么，为什么增加索引能够有效提升性能呢？

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

在MySQL中，通常是使用顺序访问和索引访问两种方式访问数据库表的数据。其中顺序访问即为全表扫描，在数据量大时速度会很低。索引访问相当于建立了一个快速查找的表，通过关键字直接定位到数据所在行，当然这里的关键字不应当重复。返回到我的问题中，我的情况还不太一样，我有两个id，必须得保证两两对应才行，可是如果要这样想，索引有两个，要怎么办呢？其实可以把思维换个方向，把学校代号-专业代号这一对看成一个整体的id，这样就可以保证完全不重复了！所以在索引字段中，就把major_id和school_id加上。

索引类型有FULLTEXT、NORMAL、SPATIAL、UNIQUE，分别是全文索引，普通索引，空间索引，唯一索引。

这里使用的是NORMAL索引，大部分情况下的默认选择。

UNIQUE代表不重复索引，适用于字段信息保证不会重复的情况。

FULLTEXT代表全文索引，适用于检索长文本，搜索长文章的时候效果最好，这里的ID都很短，所以就不首选使用这个了。

SPATIAL是空间索引，对空间数据类型建立的索引。这个一般用于多维度的数据，比如空间几何上或者GPS上的信息，这里也不使用。

索引方法一般有BTREE，Hash，RTREE。

BTREE索引是最常见的索引类型，我曾经也做过一个用B-TREE模拟数据库索引进行快速查询的课程设计。

Hash索引的检索效率比BTREE高，但是对查询有所限制。

RTREE索引很少使用，一般是用在SPATIAL索引里面。

这个整理数据的工作我也是做了一段时间了，做之前感觉无非就是增删改查，我写写SQL语句然后拿Excel处理处理还不就行了，有什么难的，但实际接手之后才发现其实有各种各样的问题。因为全国各种学校的数据真的是太复杂了，还赶上了新高考改革，不仅专业有改变，名称也不完全一样，这在我匹配数据时出现了相当多的麻烦。最初做成绩数据合并的时候因为很难直接用SQL语句做修改，所以我加急学了Node.js做了个小后端，挂上MySQL数据库驱动硬写了个半自动的程序，就算这样也跑了好长时间，性能优化之类的事情完全不懂。这次这个小需求又遇到了性能问题，越来越能感觉到自己其实很多东西学的仍然不是很牢固，大二下学期学的数据库全还给老师了。。即便是方向上选择了前端开发，但也不代表说其他计算机的基础知识就能一点不懂，如果真的变成了那样，那我这科班读出来的学生和花几万块钱在外面辅导班培训出来的又有什么区别呢？

不仅专业知识要过硬，基础知识也要过硬，业务能力要有，科学素养也有，这样才是一个合格的计算机行业工作者，现在的我仍然还是欠缺一些啊。。

打赏