1. 表与索引介绍
1.1. 表 mc_subsidyrecord（补助记录表）
共有记录数 8959 行，表结构与索引如下：
MariaDB [etdb]> desc mc_subsidyrecord;
+----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| RecNum | bigint(20) | NO | PRI | NULL | |
| CardAccNum | bigint(20) | YES | | NULL | |
| BatchNum | bigint(20) | NO | MUL | 0 | |
| AccNum | bigint(20) | NO | | NULL | |
| EpID | int(11) | NO | | NULL | |
| EWalletID | tinyint(4) | NO | | NULL | |
| SubsidyMoney | decimal(12,2) | NO | | NULL | |
| EffectTime | datetime | NO | | NULL | |
| DisableTime | datetime | YES | | NULL | |
| IsGenerateRedRedcord | tinyint(4) | NO | | 0 | |
| GrantTime | datetime | NO | | NULL | |
| SubsidySID | int(11) | NO | | NULL | |
| DealFlag | tinyint(4) | NO | PRI | NULL | |
| DealTime | datetime | YES | | NULL | |
| GrantRecNum | bigint(20) | NO | MUL | NULL | |
| DealRecNum | bigint(20) | YES | | NULL | |
| AppCode | char(3) | YES | | NULL | |
| Remark | varchar(32) | YES | | NULL | |
+----------------------+---------------+------+-----+---------+-------+
18 rows in set (0.001 sec)
MariaDB [etdb]> show index from mc_subsidyrecord;
+------------------+------------+-----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+-----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mc_subsidyrecord | 0 | PRIMARY | 1 | RecNum | A | 8960 | NULL | NULL | | BTREE | | |
| mc_subsidyrecord | 0 | PRIMARY | 2 | DealFlag | A | 8960 | NULL | NULL | | BTREE | | |
| mc_subsidyrecord | 1 | IDX_SubsidyRecord1 | 1 | DealFlag | A | 3 | NULL | NULL | | BTREE | | |
| mc_subsidyrecord | 1 | IDX_SubsidyRecord1 | 2 | CardAccNum | A | 8960 | NULL | NULL | YES | BTREE | | |
| mc_subsidyrecord | 1 | IDX_SubsidyRecord1 | 3 | EpID | A | 8960 | NULL | NULL | | BTREE | | |
| mc_subsidyrecord | 1 | IDX_SubsidyRecord1 | 4 | EWalletID | A | 8960 | NULL | NULL | | BTREE | | |
| mc_subsidyrecord | 1 | IDX_SubsidyRecord1 | 5 | SubsidySID | A | 8960 | NULL | NULL | | BTREE | | |
| mc_subsidyrecord | 1 | IDX_SubsidyRecord1 | 6 | SubsidyMoney | A | 8960 | NULL | NULL | | BTREE | | |
| mc_subsidyrecord | 1 | FK_SubsidyRecord_DealRecNum | 1 | GrantRecNum | A | 8960 | NULL | NULL | | BTREE | | |
| mc_subsidyrecord | 1 | idx_SubsidyRecord_BatchNum | 1 | BatchNum | A | 172 | NULL | NULL | | BTREE | | |
| mc_subsidyrecord | 1 | idx_SubsidyRecord_BatchNum | 2 | CardAccNum | A | 8960 | NULL | NULL | YES | BTREE | | |
| mc_subsidyrecord | 1 | idx_SubsidyRecord_BatchNum | 3 | AccNum | A | 8960 | NULL | NULL | | BTREE | | |
+------------------+------------+-----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
12 rows in set (0.000 sec)
1.2. 表mc_subsidybatch_account
共有记录数6259行，表结构与索引如下：
MariaDB [etdb]> desc mc_subsidybatch_account（批次人员表）;
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| RecNum | bigint(20) | NO | PRI | NULL | |
| BatchNum | bigint(20) | NO | MUL | NULL | |
| AccNum | bigint(20) | NO | MUL | NULL | |
| CardAccNum | bigint(20) | YES | MUL | NULL | |
| SubsidyMoney | decimal(12,2) | NO | | NULL | |
| Remark | varchar(128) | YES | | NULL | |
| GrantFlag | tinyint(4) | NO | | NULL | |
| ErrorInfo | varchar(256) | YES | | NULL | |
| IsRed | tinyint(4) | NO | | 0 | |
| RedFlag | tinyint(4) | NO | | 0 | |
| RedRecnum | bigint(20) | NO | | 0 | |
| RedRemark | varchar(64) | YES | | NULL | |
| SubsidyRecnum | bigint(20) | NO | | 0 | |
| DealTime | datetime | YES | | NULL | |
+---------------+---------------+------+-----+---------+-------+
14 rows in set (0.001 sec)
MariaDB [etdb]> show index from mc_subsidybatch_account;
+-------------------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mc_subsidybatch_account | 0 | PRIMARY | 1 | RecNum | A | 6259 | NULL | NULL | | BTREE | | |
| mc_subsidybatch_account | 1 | FK_SubsidyBatch_Account_2 | 1 | BatchNum | A | 113 | NULL | NULL | | BTREE | | |
| mc_subsidybatch_account | 1 | FK_SubsidyBatch_Account_1 | 1 | AccNum | A | 6259 | NULL | NULL | | BTREE | | |
| mc_subsidybatch_account | 1 | FK_SubsidyBatch_Account_3 | 1 | CardAccNum | A | 6259 | NULL | NULL | YES | BTREE | | |
+-------------------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.000 sec)
1.3. 关联关系
- mc_subsidybatch_account（批次人员表）中字段 SubsidyRecnum 来源于 mc_subsidyrecord（补助记录表）中字段 RecNum，但是字段 SubsidyRecnum上没有索引。
- mc_subsidyrecord（补助记录表）中字段BatchNum 来源于mc_subsidybatch_account（批次人员表）中字段 BatchNum，其中BatchNum字段在两个表中都有索引。
- mc_subsidyrecord（补助记录表）中 BatchNum > 0 的记录都来自于mc_subsidybatch_account（批次人员表）
1.4. 需要解决的问题
查询出 mc_subsidyrecord（补助记录表）中 BatchNum > 0的记录，且它的 RecNum 不在 mc_subsidybatch_account（批次人员表）中的 SubsidyRecnum字段值中。
2. 分析JOIN的执行计划
2.1. 方法一
SELECT ms.*
FROM mc_subsidyrecord ms
LEFT join mc_subsidybatch_account msa ON ms.RecNum = msa.SubsidyRecnum
WHERE msa.RecNum IS NULL
AND ms.BatchNum > 0;
- 执行结果与耗时
31 行 - 4.452s (+5ms) -
执行计划
+------+-------------+-------+------+----------------------------+------+---------+------+------+-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+----------------------------+------+---------+------+------+-------------------------------------------------------------+
| 1 | SIMPLE | ms | ALL | idx_SubsidyRecord_BatchNum | NULL | NULL | NULL | 8960 | Using where |
| 1 | SIMPLE | msa | ALL | NULL | NULL | NULL | NULL | 6259 | Using where; Not exists; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+----------------------------+------+---------+------+------+-------------------------------------------------------------+
分析这个执行计划：
- 两个表的 select_type 均为 SIMPLE，即查询中不包含子查询或者UNION。
- 对两个表都做了全表扫描（type 都为 ALL），没有用到任何索引（key 都为 NULL）。
- mc_subsidyrecord（补助记录表）表为驱动表，优先查询该表。（在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表为驱动表，出现在后边的表为被驱动表。）
- 使用了缓存块嵌套循环连接 Using join buffer （BNL join）：即将 mc_subsidybatch_account（批次人员表）表中记录缓存到 join_buffer 中再与 mc_subsidyrecord（补助记录表）进行比较。需要将 6259 行记录与 join_buffer 中的数据作比较，耗时体现在这里。
- Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
2.2. 方法二
SELECT ms.*
FROM mc_subsidyrecord ms
LEFT join mc_subsidybatch_account msa ON ms.RecNum = msa.SubsidyRecnum AND ms.BatchNum = msa.BatchNum
WHERE msa.RecNum IS NULL
AND ms.BatchNum > 0;
- 执行结果与耗时
31 行 - 13.200s (+3ms) -
执行计划
+------+-------------+-------+------+----------------------------+---------------------------+---------+------------------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+----------------------------+---------------------------+---------+------------------+------+-------------------------+
| 1 | SIMPLE | ms | ALL | idx_SubsidyRecord_BatchNum | NULL | NULL | NULL | 8960 | Using where |
| 1 | SIMPLE | msa | ref | FK_SubsidyBatch_Account_2 | FK_SubsidyBatch_Account_2 | 8 | etdb.ms.BatchNum | 55 | Using where; Not exists |
+------+-------------+-------+------+----------------------------+---------------------------+---------+------------------+------+-------------------------+
分析这个执行计划：
- 两个表的 select_type 均为 SIMPLE，即查询中不包含子查询或者UNION。
- mc_subsidyrecord（补助记录表）表为驱动表，优先查询该表。
- 对 mc_subsidyrecord（补助记录表）表做了全表扫描（type 为 ALL），没有用到任何索引（key 为 NULL）。
- 对 mc_subsidybatch_account（批次人员表）表做了非唯一索引扫描（type 为 ref）,索引为：FK_SubsidyBatch_Account_2，该索引的唯一值条数为 56（与rows当中的55接近）。这样的话就是在两表中先取出BatchNum相同数据，再对 RecNum 进行比较（实际上当 ms.RecNum = msa.SubsidyRecnum 时，ms.BatchNum 与 msa.BatchNum 一定相等），至少要扫描被驱动表55次，这样就比BNL join更耗时。另外 Extra中并没有出现了 Using index，说明什么呢？
- Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
2.3. 方法三
SELECT ms.*
FROM mc_subsidyrecord ms
WHERE ms.BatchNum > 0
AND NOT EXISTS(SELECT 1
FROM mc_subsidybatch_account msa
WHERE ms.recnum = msa.subsidyrecnum);
或
SELECT ms.*
FROM mc_subsidyrecord ms
WHERE ms.BatchNum > 0
AND ms.RecNum NOT IN(SELECT msa.SubsidyRecNum
FROM mc_subsidybatch_account msa);
- 执行结果与耗时
31 行 - 26ms (+6ms)
这个执行速度比方法一快了 170 倍。 -
执行计划
+------+--------------+-------+------+----------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------+------+----------------------------+------+---------+------+------+-------------+
| 1 | PRIMARY | ms | ALL | idx_SubsidyRecord_BatchNum | NULL | NULL | NULL | 8960 | Using where |
| 2 | MATERIALIZED | msa | ALL | NULL | NULL | NULL | NULL | 6259 | |
+------+--------------+-------+------+----------------------------+------+---------+------+------+-------------+
分析这个执行计划：
- id相同表示加载表的顺序是从上到下。id不同id值越大，优先级越高，越先被执行。id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。这里 mc_subsidybatch_account（批次人员表）被优先执行。
- mc_subsidybatch_account（批次人员表）表的 select_type 发生了变化：从 SIMPLE 变为 PRIMARY，查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）
- mc_subsidyrecord（补助记录表）表的 select_type 发生了变化：从 SIMPLE 变为 MATERIALIZED，子查询物化，不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表(物化表)里。（相关子查询：如果子查询的执行需要依赖于外层查询的值，我们就把这个子查询称之为相关子查询，反之称为不相关子查询。）执行耗时少，这里是关键，等价于在mc_subsidyrecord（补助记录表）表中移除已知的行记录（即物化的视图）。
- 对两个表都做了全表扫描（type 都为 ALL），没有用到任何索引（key 都为 NULL）。
2.4. 方法四
根据关联查询的原则，用来进行 join 的字段要加索引，我们在 mc_subsidybatch_account（批次人员表）表中增加对字段 SubsidyRecnum 的索引。
CREATE INDEX idx_mc_subsidybatch_account1 ON mc_subsidybatch_account(SubsidyRecnum);
再按方法一的查询来分析执行计划
SELECT ms.*
FROM mc_subsidyrecord ms
LEFT join mc_subsidybatch_account msa ON ms.RecNum = msa.SubsidyRecnum
WHERE msa.RecNum IS NULL
AND ms.BatchNum > 0;
- 执行结果与耗时
31 行 - 25ms (+1ms) -
执行计划
+------+-------------+-------+------+------------------------------+------------------------------+---------+----------------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+------------------------------+------------------------------+---------+----------------+------+--------------------------------------+
| 1 | SIMPLE | ms | ALL | idx_SubsidyRecord_BatchNum | NULL | NULL | NULL | 8960 | Using where |
| 1 | SIMPLE | msa | ref | idx_mc_subsidybatch_account1 | idx_mc_subsidybatch_account1 | 8 | etdb.ms.RecNum | 1 | Using where; Using index; Not exists |
+------+-------------+-------+------+------------------------------+------------------------------+---------+----------------+------+--------------------------------------+
分析这个执行计划：
- 两个表的 select_type 均为 SIMPLE，即查询中不包含子查询或者UNION。
- mc_subsidyrecord（补助记录表）表为驱动表，优先查询该表。
- 对 mc_subsidyrecord（补助记录表）表做了全表扫描（type 为 ALL），没有用到任何索引（key 为 NULL）。
- 对 mc_subsidybatch_account（批次人员表）表做了非唯一索引扫描（type 为 ref）,索引为：idx_mc_subsidybatch_account1，该索引的唯一值条数为 6259（但是rows却显示 1，不知道为什么？），同时该索引ref所指向的是 mc_subsidyrecord（补助记录表）表的 RecNum 的值，这个字段是该表的主键。这样的话就是根据索引 RecNum 进行比较，Extra中出现了 Using index。
- Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
参考：
MySQL执行计划Explain详解：https://jishuin.proginn.com/p/763bfbd2c67e
参考本站：MariaDB执行计划分析（1. Join执行过程详解）