关于 MySQL 的一些骚操作——提升正确性

IGNORE 还有些副作用，感兴趣的可以自行查询。

[](()使用 JOIN 替换子查询

MySQL 的子查询优化不是太好，它的运行有点反我们的直觉（我们写的代码终究会在某些时候和我们的直觉相悖，这大概就是优化产生的根源之一吧）。其中最糟糕的一类是 WHERE 子句中包含 IN 的子查询语句（详情可见《高性能 MySQL》一书的 6.5 章节,标题名字起得就很 nice，为 MySQL 查询优化器的局限性）。概括下就是在部分情况下，在部分情况下 MySQL 可能会在挨个执行外部记录时执行子查询，如果外部记录数量较大，那么性能就会堪忧。

SELECT * FROM student WHERE no > (SELECT no FROM student WHERE name='xiaoming');

SELECT s.* FROM student s JOIN (SELECT no FROM student WHERE name='xiaoming') t ON s.no > t.no;

看上述代码，可以知道使用 JOIN 还是比较容易替换子循环，代码虽然会稍显晦涩，但是也许可以避免在并发量大的某个晚上你被叫起来检讨自己的错误。MySQL 一直在优化子查询，在部分条件下子查询可能会比 JOIN 具有更高的效率，因此在有时间进行验证的情况下选择最佳的 SQL 语句。

[](()JOIN 中的 WHERE 和 AND 坑

为了更好的说明坑，我这里需要创建一个新的表，并在原来的学生表中添加字段:

CREATE TABLE class (

id smallint(6) unsigned NOT NULL AUTO_INCREMENT,

no int(10) unsigned NOT NULL COMMENT '编号',

name varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名称',

PRIMARY KEY (id),

UNIQUE KEY unq_no (no)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE student

ADD COLUMN cls_no smallint(6) unsigned NOT NULL DEFAULT 0 AFTER no;

伪造一些数据，假设有 4 个班级，4 班没有相对应的学生。使用如下的查询语句就能发现不同之处：

select c.*, s.name from class c left join student s on c.no = s.cls_no and c.no < 4 order by c.no asc;

查询结果如下图所示：

需要注意的是，此处我再查询条件中设置了c.no < 4这一 JOIN 条件，但是明显的没有起到作用，查询结果中仍然显示了 no=4 的结果，这是因为此次查询使用的 JOIN 是 LEFT JOIN，class 作为左表，在匹配条件无法完全满足的情况下，亦会将左表的所有数据显示出来，引入了 NULL 值。

换成使用 WHERE 呢，参照下句：

select c.*, s.name from class c left join student s on c.no = s.cls_no where c.no < 4 order by c.no asc;

查询结果如下图所示：

为什么同样是使用 LEFT JOIN，查询结果就不同了呢？这是因为可以认为 SQL 是分成两部分进行执行的(伪 SQL，意思到位)：

(1) select 《一线大厂 Java 面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》无偿开源 威信搜索公众号【编程进阶路】 c._, s.name?from class c left join student s on c.no = s.cls_no as tmn;

(2)select c._, s.name?from tmp where c.no < 4 order by c.no asc;

需要注意的是，此处首先执行 JOIN 部分查询，再对查询结果执行 WHERE。在执行 INNER JOIN 时，以上问题还可以忽略，但是如果使用的是 LEFT JOIN 或者 RIGHT JOIN，则需要加倍小心查询条件了。

[](()分页查询优化

查询的优化，最初是在研究 MongoDB 的分页查询时学到的，只能说大多数的数据库都是差不多的(当然现在存在时序数据库，分页查询那是更加骚气的)。大多数的分页查询都是类似如下的写法：

SELECT * FROM student WHERE cls_no > 1 LIMIT 1000, 10 ORDER BY id;

这样的写法存在性能损耗，数据库会将所有符合条件的数据查询出来，挨个数到第 1000 条记录，最后选取前 10 条记录进行交差。前面的 1000 条数据，就会显得很浪费，在 LIMIT 数值很大的情况下，这个性能损耗就是无法忍受的了（百度就会默认禁止查询 76 页以后的数据）。

因为分页一般是逐页翻下去的(如果是跳页进行查询，那就只能用上面的查询语句慢慢查询搜寻结果了)，那么每次分页完都能获取当前的最大 ID，我们可以基于 ID 确定我们的搜索起始点，基于此点向后查询 10 条满足要求的结果，改动如下(让前端多传一个当前页的最大 ID，这个小小的要求当然是可以满足的)：

SELECT * FROM student WHERE id > 1000 AND cls_no > 1 LIMIT 10 ORDER BY id;

以上是基于当前的 ID 是连续 ID（其中若干记录没有被物理删除掉），如果是非连续 ID，那么基于 ID 确定起始查询点是不恰当的，此时我们就可以使用 JOIN：

SELECT s.* FROM student s JOIN (SELECT id FROM student LIMIT 1000, 10) t ON s.id = t.id;

其实，此处我们是 id 的索引表，从而快速的确定 ID，因此查询简化成根据索引表查询的 ID 确定数据记录（不过需要注意，此处的索引表是无法添加 WHERE 子句的），因此这种写法在实际环境中几乎是个鸡肋。

[](()UPDATE/DELETE 改动多个表记录