需求是將未分配的記錄纤泵，按照指定排序選一條分配給一個人骆姐。
最原始的實現(xiàn)是，查詢更新捏题，先加鎖逾礁，select出一條刻肄，再update颊郎，然后釋放鎖微渠，由于select很慢，還提前生成了緩存循狰，兩分鐘生成一次窟社。
經(jīng)過梳理，得到以下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左右匣吊，很穩(wěn)定。
一番優(yōu)化后寸潦，舍棄了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见转，很穩(wěn)定命雀。
以下是執(zhí)行情況和執(zhí)行計劃：
這里INNER JOIN比EXISTS快的原因，其實也看場景池户。
由于兩張表的規(guī)模是一樣的咏雌，并且主鍵相同，所以JOIN會走主鍵索引校焦，非成薅叮快。EXISTS反而會去遍歷了寨典。
掃描方式 type氛雪，從慢到快：
- all : 全表掃描
- index ： 全索引掃描，和全表掃描一樣耸成。只是掃描表的時候按照索引次序進行而不是行报亩。主要優(yōu)點就是避免了排序, 但是開銷仍然非常大。如在Extra列看到Using index井氢，說明正在使用覆蓋索引弦追，只掃描索引的數(shù)據(jù)，它比按索引次序全表掃描的開銷要小很多花竞。這個比all效率要好一點劲件，主要有幾種情況，一是當前的查詢是覆蓋索引的约急，即我們需要的數(shù)據(jù)在索引中就可以獲攘阍丁（Extra中有Using Index），或者是使用了索引進行排序厌蔽，這樣就避免數(shù)據(jù)的重排序（Extra中無 Using Index）牵辣。如果Extra中Using Index與Using Where同時出現(xiàn)的話，則是利用索引查找鍵值的意思
- range ： 范圍查詢奴饮，避免了全索引掃描纬向，限制的范圍越小，效率越高
- index_subquery ： 在 某 些 IN 查 詢 中 使 用 此 種 類 型 , 與 unique_subquery 類似,但是查詢的是非唯一 性索引
- unique_subquery ： 在某些 IN 查詢中使用此種類型,而不是常規(guī)的 ref
- index_merge ： 說明索引合并優(yōu)化被使用了
- ref_or_null ： 如同 ref, 但是 MySQL 必須在初次查找的結(jié)果 里找出 null 條目,然后進行二次查找戴卜。
- ref ： 使用了非唯一性索引進行數(shù)據(jù)的查找
- eq_ref ： 使用的唯一性索引進行數(shù)據(jù)查找罢猪，例如主鍵索引之類的
- const ： 通常情況下，將一個主鍵放置到where后面作為條件查詢叉瘩，mysql優(yōu)化器就能把這次查詢優(yōu)化轉(zhuǎn)化為一個常量膳帕，如何轉(zhuǎn)化以及何時轉(zhuǎn)化，這個取決于優(yōu)化器薇缅。這個比eq_ref效率高一點危彩。
- system ： 表只有一行。不過這種情況下就沒意義了泳桦。
- NULL ： MySQL不用訪問表或者索引就直接能到結(jié)果汤徽。
（關(guān)于覆蓋索引：MySQL系列-優(yōu)化之覆蓋索引）
測試時發(fā)現(xiàn)，由于com_info表的查詢需求很多灸撰，因此針對不同的場景谒府，對不同的字段建了各種索引拼坎，在測試數(shù)據(jù)庫和線上數(shù)據(jù)庫上測試發(fā)現(xiàn)，不同的數(shù)據(jù)分布下完疫，MySQL對索引的選取差別很大泰鸡，線上庫采用了專門為這個場景建的聯(lián)合索引（包含了WHERE中需要的字段），而測試庫中則采用了index_merge的方式壳鹤，挨個索引走一遍盛龄，取交集，實測index_merge還是要比ref慢一兩個數(shù)量級芳誓。
另外index_merge在這個場景下會引發(fā)死鎖余舶。
因為這里用到了
UPDATE ... WHERE ... ORDER BY ... LIMIT ...;
由于有ORDER BY LIMIT存在，UPDARE的時候锹淌，會鎖住命中的所有行匿值。而index_merge在高并發(fā)的時候，如果索引行數(shù)有重疊赂摆，因為加鎖的順序可能不同千扔，互相等待可能會導致死鎖。加鎖順序的原因是库正，MySQL會先用索引1進行掃表曲楚，再用索引2進行掃表，然后求交集形成一個合并索引褥符。這個使用索引掃表的過程和我們本身SQL使用索引的順序可能存在互斥龙誊，造成了死鎖。
這里有個場景可能引發(fā)死鎖：
請求的WHERE條件不完全一樣喷楣，但包含了相同的某兩個字段趟大，可能造成上述的問題。
