需求是將未分配的記錄，按照指定排序選一條分配給一個人。
最原始的實(shí)現(xiàn)是，查詢更新，先加鎖，select出一條，再update，然后釋放鎖，由于select很慢，還提前生成了緩存，兩分鐘生成一次。
經(jīng)過梳理，得到以下SQL，用的exists語法，直接update，利用MySQL的鎖機(jī)制保證不會重復(fù)分配。
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 ;
實(shí)測耗時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;
實(shí)測耗時0.28s，很穩(wěn)定。
以下是執(zhí)行情況和執(zhí)行計劃：
這里INNER JOIN比EXISTS快的原因，其實(shí)也看場景。
由于兩張表的規(guī)模是一樣的，并且主鍵相同，所以JOIN會走主鍵索引，非常快。EXISTS反而會去遍歷了。
掃描方式 type，從慢到快：
- all : 全表掃描
- index ： 全索引掃描，和全表掃描一樣。只是掃描表的時候按照索引次序進(jìn)行而不是行。主要優(yōu)點(diǎn)就是避免了排序, 但是開銷仍然非常大。如在Extra列看到Using index，說明正在使用覆蓋索引，只掃描索引的數(shù)據(jù)，它比按索引次序全表掃描的開銷要小很多。這個比all效率要好一點(diǎn)，主要有幾種情況，一是當(dāng)前的查詢是覆蓋索引的，即我們需要的數(shù)據(jù)在索引中就可以獲?。‥xtra中有Using Index），或者是使用了索引進(jìn)行排序，這樣就避免數(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 條目,然后進(jìn)行二次查找。
- ref ： 使用了非唯一性索引進(jìn)行數(shù)據(jù)的查找
- eq_ref ： 使用的唯一性索引進(jìn)行數(shù)據(jù)查找，例如主鍵索引之類的
- const ： 通常情況下，將一個主鍵放置到where后面作為條件查詢，mysql優(yōu)化器就能把這次查詢優(yōu)化轉(zhuǎn)化為一個常量，如何轉(zhuǎn)化以及何時轉(zhuǎn)化，這個取決于優(yōu)化器。這個比eq_ref效率高一點(diǎn)。
- 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的方式，挨個索引走一遍，取交集，實(shí)測index_merge還是要比ref慢一兩個數(shù)量級。
另外index_merge在這個場景下會引發(fā)死鎖。
因?yàn)檫@里用到了
UPDATE ... WHERE ... ORDER BY ... LIMIT ...;
由于有ORDER BY LIMIT存在，UPDARE的時候，會鎖住命中的所有行。而index_merge在高并發(fā)的時候，如果索引行數(shù)有重疊，因?yàn)榧渔i的順序可能不同，互相等待可能會導(dǎo)致死鎖。加鎖順序的原因是，MySQL會先用索引1進(jìn)行掃表，再用索引2進(jìn)行掃表，然后求交集形成一個合并索引。這個使用索引掃表的過程和我們本身SQL使用索引的順序可能存在互斥，造成了死鎖。
這里有個場景可能引發(fā)死鎖：
請求的WHERE條件不完全一樣，但包含了相同的某兩個字段，可能造成上述的問題。
