SQL优化INNER JOIN子查询代替EXISTS

需求是将未分配的记录，按照指定排序选一条分配给一个人。
最原始的实现是，查询更新，先加锁，select出一条，再update，然后释放锁，由于select很慢，还提前生成了缓存，两分钟生成一次。

经过梳理，得到以下SQL，用的exists语法，直接update，利用MySQL的锁机制保证不会重复分配。

UPDATE com_info c SET infoOperatorName='wy-test', infoOperatorFlag=1 
WHERE deleteFlag=0 AND infoType='F道路封闭' AND infoOperatorFlag=0 AND infoOperatorName='' 
AND EXISTS (SELECT 1 FROM ext_info e WHERE e.infoCode=c.infoCode AND e.infoCheckResult='有效') 
ORDER BY DATE(infoCreateTime) DESC, catagory_priority, city_priority, infoLevel DESC, infoCreateTime DESC LIMIT 1 ;

实测耗时4s左右，很稳定。

一番优化后，舍弃了exists，改用inner join + 子查询，如下：

UPDATE com_info t1 INNER JOIN (
    SELECT c.infoCode FROM com_info c INNER JOIN ext_info e ON c.infoCode=e.infoCode  
    WHERE deleteFlag=0 AND infoType='F道路封闭' AND infoOperatorFlag=0 AND infoOperatorName='' 
    AND e.infoCheckResult='有效'
    ORDER BY DATE(infoCreateTime) DESC, catagory_priority, city_priority, infoLevel DESC, infoCreateTime DESC LIMIT 1
) t2 ON t1.infoCode=t2.infoCode SET infoOperatorName='wy-test', infoOperatorFlag=1;

实测耗时0.28s，很稳定。

以下是执行情况和执行计划：

执行耗时
执行计划

这里INNER JOIN比EXISTS快的原因，其实也看场景。
由于两张表的规模是一样的，并且主键相同，所以JOIN会走主键索引，非常快。EXISTS反而会去遍历了。

扫描方式 type，从慢到快：

1. all : 全表扫描
2. index ： 全索引扫描，和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多。这个比all效率要好一点，主要有几种情况，一是当前的查询是覆盖索引的，即我们需要的数据在索引中就可以获取（Extra中有Using Index），或者是使用了索引进行排序，这样就避免数据的重排序（Extra中无 Using Index）。如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思
3. range ： 范围查询，避免了全索引扫描，限制的范围越小，效率越高
4. index_subquery ： 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引
5. unique_subquery ： 在某些 IN 查询中使用此种类型,而不是常规的 ref
6. index_merge ： 说明索引合并优化被使用了
7. ref_or_null ： 如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。
8. ref ： 使用了非唯一性索引进行数据的查找
9. eq_ref ： 使用的唯一性索引进行数据查找，例如主键索引之类的
10. const ： 通常情况下，将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器。这个比eq_ref效率高一点。
11. system ： 表只有一行。不过这种情况下就没意义了。
12. NULL ： MySQL不用访问表或者索引就直接能到结果。

（关于覆盖索引：MySQL系列-优化之覆盖索引）

测试时发现，由于com_info表的查询需求很多，因此针对不同的场景，对不同的字段建了各种索引，在测试数据库和线上数据库上测试发现，不同的数据分布下，MySQL对索引的选取差别很大，线上库采用了专门为这个场景建的联合索引（包含了WHERE中需要的字段），而测试库中则采用了index_merge的方式，挨个索引走一遍，取交集，实测index_merge还是要比ref慢一两个数量级。

另外index_merge在这个场景下会引发死锁。
因为这里用到了

UPDATE ... WHERE ... ORDER BY ... LIMIT ...;

由于有ORDER BY LIMIT存在，UPDARE的时候，会锁住命中的所有行。而index_merge在高并发的时候，如果索引行数有重叠，因为加锁的顺序可能不同，互相等待可能会导致死锁。加锁顺序的原因是，MySQL会先用索引1进行扫表，再用索引2进行扫表，然后求交集形成一个合并索引。这个使用索引扫表的过程和我们本身SQL使用索引的顺序可能存在互斥，造成了死锁。

这里有个场景可能引发死锁：
请求的WHERE条件不完全一样，但包含了相同的某两个字段，可能造成上述的问题。

index_merge引发的死锁排查