delete 后加 limit 是個好習慣么
在業(yè)務場景要求高的數(shù)據(jù)庫中，對于單條刪除和更新操作，在 delete 和 update 后面加 limit 1 絕對是個好習慣。比如，在刪除執(zhí)行中，第一條就命中了刪除行，如果 SQL 中有 limit 1；這時就 return 了，否則還會執(zhí)行完全表掃描才 return。效率不言而喻。
那么，在日常執(zhí)行 delete 時，我們是否需要養(yǎng)成加 limit 的習慣呢？是不是一個好習慣呢？
在日常的 SQL 編寫中，你寫 delete 語句時是否用到過以下 SQL？
delete from t where sex = 1 limit 100;
你或許沒有用過，在一般場景下，我們對 delete 后是否需要加 limit 的問題很陌生，也不知有多大區(qū)別，今天帶你來了解一下，記得 mark！
寫在前面，如果是清空表數(shù)據(jù)建議直接用 truncate，效率上 truncate 遠高于 delete，應為 truncate 不走事務，不會鎖表，也不會生產(chǎn)大量日志寫入日志文件；truncate table table_name 后立刻釋放磁盤空間，并重置 auto_increment 的值。delete 刪除不釋放磁盤空間，但后續(xù) insert 會覆蓋在之前刪除的數(shù)據(jù)上。詳細了解請?zhí)D(zhuǎn)另一篇博文《delete、truncate、drop 的區(qū)別有哪些，該如何選擇》
下面只討論 delete 場景，首先，delete 后面是支持 limit 關鍵字的，但僅支持單個參數(shù)，也就是 [limit row_count]，用于告知服務器在控制命令被返回到客戶端前被刪除的行的最大值。
delete limit 語法如下，值得注意的是，order by 必須要和 limit 聯(lián)用，否則就會被優(yōu)化掉。
delete \[low\_priority\] \[quick\] \[ignore\] from tbl\_name
\[where ...\]
\[order by ...\]
\[limit row\_count\]
加 limit 的的優(yōu)點：
以下面的這條 SQL 為例：
delete from t where sex = 1;
\1. 降低寫錯 SQL 的代價，就算刪錯了，比如 limit 500, 那也就丟了 500 條數(shù)據(jù)，并不致命，通過 binlog 也可以很快恢復數(shù)據(jù)。 \2. 避免了長事務，delete 執(zhí)行時 MySQL 會將所有涉及的行加寫鎖和 Gap 鎖（間隙鎖），所有 DML 語句執(zhí)行相關行會被鎖住，如果刪除數(shù)量大，會直接影響相關業(yè)務無法使用。 \3. delete 數(shù)據(jù)量大時，不加 limit 容易把 cpu 打滿，導致越刪越慢。
針對上述第二點，前提是 sex 上加了索引，大家都知道，加鎖都是基于索引的，如果 sex 字段沒索引，就會掃描到主鍵索引上，那么就算 sex = 1 的只有一條記錄，也會鎖表。
對于 delete limit 的使用，MySQL 大佬丁奇有一道題：
如果你要刪除一個表里面的前 10000 行數(shù)據(jù)，有以下三種方法可以做到：第一種，直接執(zhí)行 delete from T limit 10000; 第二種，在一個連接中循環(huán)執(zhí)行 20 次 delete from T limit 500; 第三種，在 20 個連接中同時執(zhí)行 delete from T limit 500。
你先考慮一下，再看看幾位老鐵的回答：
--------------------------------------------
Tony Du：
方案一，事務相對較長，則占用鎖的時間較長，會導致其他客戶端等待資源時間較長。 方案二，串行化執(zhí)行，將相對長的事務分成多次相對短的事務，則每次事務占用鎖的時間相對較短，其他客戶端在等待相應資源的時間也較短。這樣的操作，同時也意味著將資源分片使用（每次執(zhí)行使用不同片段的資源），可以提高并發(fā)性。 方案三，人為自己制造鎖競爭，加劇并發(fā)量。 方案二相對比較好，具體還要結(jié)合實際業(yè)務場景。
--------------------------------------------
肉山：
不考慮數(shù)據(jù)表的訪問并發(fā)量，單純從這個三個方案來對比的話。
第一個方案，一次占用的鎖時間較長，可能會導致其他客戶端一直在等待資源。 第二個方案，分成多次占用鎖，串行執(zhí)行，不占有鎖的間隙其他客戶端可以工作，類似于現(xiàn)在多任務操作系統(tǒng)的時間分片調(diào)度，大家分片使用資源，不直接影響使用。 第三個方案，自己制造了鎖競爭，加劇并發(fā)。
至于選哪一種方案要結(jié)合實際場景，綜合考慮各個因素吧，比如表的大小，并發(fā)量，業(yè)務對此表的依賴程度等。-------------------------------------------
～嗡嗡：
\1. 直接 delete 10000 可能使得執(zhí)行事務時間過長 \2. 效率慢點每次循環(huán)都是新的短事務，并且不會鎖同一條記錄，重復執(zhí)行 DELETE 知道影響行為 0 即可 \3. 效率雖高，但容易鎖住同一條記錄，發(fā)生死鎖的可能性比較高
-------------------------------------------
怎么刪除表的前 10000 行。比較多的朋友都選擇了第二種方式，即：在一個連接中循環(huán)執(zhí)行 20 次 delete from T limit 500。確實是這樣的，第二種方式是相對較好的。
第一種方式（即：直接執(zhí)行 delete from T limit 10000）里面，單個語句占用時間長，鎖的時間也比較長；而且大事務還會導致主從延遲。
第三種方式（即：在 20 個連接中同時執(zhí)行 delete from T limit 500），會人為造成鎖沖突。
這個例子對我們實踐的指導意義就是，在刪除數(shù)據(jù)的時候盡量加 limit。這樣不僅可以控制刪除數(shù)據(jù)的條數(shù)，讓操作更安全，還可以減小加鎖的范圍。所以，在 delete 后加 limit 是個值得養(yǎng)成的好習慣。
好了，本文就帶你了解這些，如果有相關疑問和好想法，請在下方留言，方便和小伙伴兒們一起討論。
- END -在業(yè)務場景要求高的數(shù)據(jù)庫中，對于單條刪除和更新操作，在 delete 和 update 后面加 limit 1 絕對是個好習慣。比如，在刪除執(zhí)行中，第一條就命中了刪除行，如果 SQL 中有 limit 1；這時就 return 了，否則還會執(zhí)行完全表掃描才 return。效率不言而喻。
那么，在日常執(zhí)行 delete 時，我們是否需要養(yǎng)成加 limit 的習慣呢？是不是一個好習慣呢？
在日常的 SQL 編寫中，你寫 delete 語句時是否用到過以下 SQL？
delete from t where sex = 1 limit 100;
你或許沒有用過，在一般場景下，我們對 delete 后是否需要加 limit 的問題很陌生，也不知有多大區(qū)別，今天帶你來了解一下，記得 mark！
寫在前面，如果是清空表數(shù)據(jù)建議直接用 truncate，效率上 truncate 遠高于 delete，應為 truncate 不走事務，不會鎖表，也不會生產(chǎn)大量日志寫入日志文件；truncate table table_name 后立刻釋放磁盤空間，并重置 auto_increment 的值。delete 刪除不釋放磁盤空間，但后續(xù) insert 會覆蓋在之前刪除的數(shù)據(jù)上。詳細了解請?zhí)D(zhuǎn)另一篇博文《delete、truncate、drop 的區(qū)別有哪些，該如何選擇》
下面只討論 delete 場景，首先，delete 后面是支持 limit 關鍵字的，但僅支持單個參數(shù)，也就是 [limit row_count]，用于告知服務器在控制命令被返回到客戶端前被刪除的行的最大值。
delete limit 語法如下，值得注意的是，order by 必須要和 limit 聯(lián)用，否則就會被優(yōu)化掉。
delete \[low\_priority\] \[quick\] \[ignore\] from tbl\_name
\[where ...\]
\[order by ...\]
\[limit row\_count\]
加 limit 的的優(yōu)點：
以下面的這條 SQL 為例：
delete from t where sex = 1;
\1. 降低寫錯 SQL 的代價，就算刪錯了，比如 limit 500, 那也就丟了 500 條數(shù)據(jù)，并不致命，通過 binlog 也可以很快恢復數(shù)據(jù)。 \2. 避免了長事務，delete 執(zhí)行時 MySQL 會將所有涉及的行加寫鎖和 Gap 鎖（間隙鎖），所有 DML 語句執(zhí)行相關行會被鎖住，如果刪除數(shù)量大，會直接影響相關業(yè)務無法使用。 \3. delete 數(shù)據(jù)量大時，不加 limit 容易把 cpu 打滿，導致越刪越慢。
針對上述第二點，前提是 sex 上加了索引，大家都知道，加鎖都是基于索引的，如果 sex 字段沒索引，就會掃描到主鍵索引上，那么就算 sex = 1 的只有一條記錄，也會鎖表。
對于 delete limit 的使用，MySQL 大佬丁奇有一道題：
如果你要刪除一個表里面的前 10000 行數(shù)據(jù)，有以下三種方法可以做到：第一種，直接執(zhí)行 delete from T limit 10000; 第二種，在一個連接中循環(huán)執(zhí)行 20 次 delete from T limit 500; 第三種，在 20 個連接中同時執(zhí)行 delete from T limit 500。
你先考慮一下，再看看幾位老鐵的回答：
--------------------------------------------
Tony Du：
方案一，事務相對較長，則占用鎖的時間較長，會導致其他客戶端等待資源時間較長。 方案二，串行化執(zhí)行，將相對長的事務分成多次相對短的事務，則每次事務占用鎖的時間相對較短，其他客戶端在等待相應資源的時間也較短。這樣的操作，同時也意味著將資源分片使用（每次執(zhí)行使用不同片段的資源），可以提高并發(fā)性。 方案三，人為自己制造鎖競爭，加劇并發(fā)量。 方案二相對比較好，具體還要結(jié)合實際業(yè)務場景。
--------------------------------------------
肉山：
不考慮數(shù)據(jù)表的訪問并發(fā)量，單純從這個三個方案來對比的話。
第一個方案，一次占用的鎖時間較長，可能會導致其他客戶端一直在等待資源。 第二個方案，分成多次占用鎖，串行執(zhí)行，不占有鎖的間隙其他客戶端可以工作，類似于現(xiàn)在多任務操作系統(tǒng)的時間分片調(diào)度，大家分片使用資源，不直接影響使用。 第三個方案，自己制造了鎖競爭，加劇并發(fā)。
至于選哪一種方案要結(jié)合實際場景，綜合考慮各個因素吧，比如表的大小，并發(fā)量，業(yè)務對此表的依賴程度等。-------------------------------------------
～嗡嗡：
\1. 直接 delete 10000 可能使得執(zhí)行事務時間過長 \2. 效率慢點每次循環(huán)都是新的短事務，并且不會鎖同一條記錄，重復執(zhí)行 DELETE 知道影響行為 0 即可 \3. 效率雖高，但容易鎖住同一條記錄，發(fā)生死鎖的可能性比較高
-------------------------------------------
怎么刪除表的前 10000 行。比較多的朋友都選擇了第二種方式，即：在一個連接中循環(huán)執(zhí)行 20 次 delete from T limit 500。確實是這樣的，第二種方式是相對較好的。
第一種方式（即：直接執(zhí)行 delete from T limit 10000）里面，單個語句占用時間長，鎖的時間也比較長；而且大事務還會導致主從延遲。
第三種方式（即：在 20 個連接中同時執(zhí)行 delete from T limit 500），會人為造成鎖沖突。
這個例子對我們實踐的指導意義就是，在刪除數(shù)據(jù)的時候盡量加 limit。這樣不僅可以控制刪除數(shù)據(jù)的條數(shù)，讓操作更安全，還可以減小加鎖的范圍。所以，在 delete 后加 limit 是個值得養(yǎng)成的好習慣。
好了，本文就帶你了解這些，如果有相關疑問和好想法，請在下方留言，方便和小伙伴兒們一起討論。
關注公眾號【Java技術江湖】后回復“PDF”即可領取200+頁的《Java工程師面試指南》
強烈推薦，幾乎涵蓋所有Java工程師必知必會的知識點，不管是復習還是面試，都很實用。
