劣質(zhì)SQl優(yōu)化

寫(xiě)在開(kāi)頭

這里所說(shuō)的劣質(zhì)SQL限定在數(shù)據(jù)量未到分庫(kù)分表和使用分布式緩存程度，指那些執(zhí)行較差的查詢、插入、更新、刪除SQL。
本文從執(zhí)行計(jì)劃分析慢SQL，從表的創(chuàng)建，查詢，更新，以及事務(wù)，死鎖來(lái)分析。
分布緩存和分庫(kù)分表這里不做表述。

執(zhí)行計(jì)劃

對(duì)于慢SQL可以使用執(zhí)行計(jì)劃查看執(zhí)行情況（DESC、EXPLAIN SQL語(yǔ)句）:執(zhí)行順序，索引使用情況，預(yù)估的數(shù)據(jù)量，是否使用了filesort等，如：

image.png

基于上圖中的項(xiàng)，下面一一解釋其含義，對(duì)SQL分析十分有幫助。

  • id：一組序號(hào)，表示查詢中執(zhí)行select子句或操作表的順序，id越大則優(yōu)先級(jí)越高，越先被執(zhí)行，如果id相同，則執(zhí)行順序從上至下執(zhí)行，如果是子查詢，id的序號(hào)會(huì)遞增。id也有可能為null，表示是一個(gè)結(jié)果集，且不再使用它來(lái)進(jìn)行查詢。

    image.png
    image.png

  • select_type：
    【simple】：表示不需要union操作或者不包含子查詢的簡(jiǎn)單select查詢。有連接查詢時(shí)，外層的查詢?yōu)閟imple，且只有一個(gè)。
    【primary】：一個(gè)有union操作或者含有子查詢的select，位于最外層的單位查詢的select_type即為primary。且只有一個(gè)。
    【subquery】：在SELECT或WHERE列表中包含了子查詢，該子查詢被標(biāo)記為subquery。

mysql> desc select id,(select id from class where id=22) as kk from stu;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | stu   | NULL       | index | NULL          | idx_no  | 8       | NULL  |   24 |   100.00 | Using index |
|  2 | SUBQUERY    | class | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

mysql> desc select id from stu where exists (select * from class);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | stu   | NULL       | index | NULL          | idx_no  | 8       | NULL |   24 |   100.00 | Using index |
|  2 | SUBQUERY    | class | NULL       | index | NULL          | PRIMARY | 8       | NULL |    6 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

【dependent subquery】：與dependent union類(lèi)似，表示這個(gè)subquery的查詢要受到外部表查詢的影響。

mysql> desc select id from stu where exists(select id from class where stu.id=id);
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+
|  1 | PRIMARY            | stu   | NULL       | index  | NULL          | idx_no  | 8       | NULL       |   24 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | class | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | db1.stu.id |    1 |   100.00 | Using index              |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

【derived】：用來(lái)表示包含在from子句中的子查詢的select，mysql會(huì)遞歸執(zhí)行并將結(jié)果放到一個(gè)臨時(shí)表中。服務(wù)器內(nèi)部稱為"派生表"，因?yàn)樵撆R時(shí)表是從子查詢中派生出來(lái)的。
【union】：第二個(gè)SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION；
【dependent union】：與union一樣，出現(xiàn)在union 或union all語(yǔ)句中，但是這個(gè)查詢要受到外部查詢的影響
【union result】：包含union的結(jié)果集，在union和union all語(yǔ)句中,因?yàn)樗恍枰獏⑴c查詢，所以id字段為null。

  • table

    顯示查詢表名，如果查詢使用了別名，那么這里顯示的是別名。
    如果不涉及對(duì)數(shù)據(jù)表的操作，那么這顯示為null。
    如果顯示為尖括號(hào)括起來(lái)的<derived N>就表示這個(gè)是臨時(shí)表，后邊的N就是執(zhí)行計(jì)劃中的id，表示結(jié)果來(lái)自于這個(gè)查詢產(chǎn)生。
    如果是尖括號(hào)括起來(lái)的<union M,N>，與<derived N>類(lèi)似，也是一個(gè)臨時(shí)表，表示這個(gè)結(jié)果來(lái)自于union查詢的id為M,N的結(jié)果集。

  • type
    system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL。依次從好到差

【system】：表中只有一行數(shù)據(jù)或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在這個(gè)情況通常都是all或者index
【const】使用唯一索引或者主鍵，返回記錄一定是1行記錄的等值where條件時(shí)，通常type是const。其他數(shù)據(jù)庫(kù)也叫做唯一索引掃描。
【eq_ref】類(lèi)似ref，區(qū)別就在使用的索引是唯一索引，對(duì)于每個(gè)索引鍵值，表中只有一條記錄匹配，簡(jiǎn)單來(lái)說(shuō)，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件。

mysql> desc select * from stu,class where stu.id=class.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | class | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    6 |   100.00 | NULL  |
|  1 | SIMPLE      | stu   | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | db1.class.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

【ref】使用非唯一索引掃描或者唯一索引的前綴掃描，使用相等條件檢索時(shí)就可能出現(xiàn)，常見(jiàn)與輔助索引的等值查找。
【range】索引范圍掃描，對(duì)索引的掃描開(kāi)始于某一點(diǎn)，返回匹配值域的行。顯而易見(jiàn)的索引范圍掃描是帶有between或者where子句里帶有<, >查詢。當(dāng)mysql使用索引去查找一系列值時(shí)，例如IN()和OR列表，也會(huì)顯示range（范圍掃描）,當(dāng)然性能上面是有差異的。
【index】索引全表掃描，把索引從頭到尾掃一遍，index與ALL區(qū)別為index類(lèi)型只遍歷索引樹(shù)。

mysql> desc select id from stu;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | stu   | NULL       | index | NULL          | idx_no | 8       | NULL |   27 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

【all】全表查詢

  • possible_keys：指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用
  • key：查詢真正使用到的索引
  • key_len：用于處理查詢的索引長(zhǎng)度，如果是單列索引，那就整個(gè)索引長(zhǎng)度算進(jìn)去，如果是多列索引，那么查詢不一定都能使用到所有的列，具體使用到了多少個(gè)列的索引，這里就會(huì)計(jì)算進(jìn)去，沒(méi)有使用到的列，這里不會(huì)計(jì)算進(jìn)去。
  • rows：表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)。
  • filtered:返回結(jié)果占所讀行的百分比。
  • Extra：包含不適合在其他列中顯示但十分重要的額外信息。
    Using filesort，Using temporary，Using where。

根據(jù)id和select_type項(xiàng)可以分析出SQL執(zhí)行步驟；type可以分析出使用何種索引類(lèi)型，要警惕ALL和INDEX，他們查詢最差，可以適當(dāng)增加索引或優(yōu)化SQL；key和key_len可以分析出哪些索引使用到了，尤其是組合索引，可以進(jìn)一步分析那一項(xiàng)使用到了；rows和filtered,一個(gè)可以估算出一共查詢了多少數(shù)據(jù)，一個(gè)可以看到查詢結(jié)果和總查詢數(shù)據(jù)的百分比；Extra，如Using filesort，使用order by做了文件排序，sing temporary，使用了臨時(shí)表等，進(jìn)而做出優(yōu)化。

SQL優(yōu)化

分析執(zhí)行計(jì)劃，可以確定慢SQL問(wèn)題所在，但是對(duì)SQL的優(yōu)化，應(yīng)該自上而下優(yōu)化，從表創(chuàng)建開(kāi)始。

表的創(chuàng)建

  • 表設(shè)計(jì)盡量小而精，適用最小使用原則：能用5個(gè)字段就不要用6個(gè)，盡量少的使用擴(kuò)展字段。

  • 以將表中字段的寬度設(shè)得盡可能小，盡量避免text，blob等這些大家伙。

  • 設(shè)計(jì)表字段能用數(shù)字類(lèi)型就千萬(wàn)別用字符類(lèi)型。

  • 禁止使用小數(shù)存儲(chǔ)貨幣，推薦使用整數(shù)。

  • 推薦字段定義為NOT NULL并且提供默認(rèn)值
    1).null的列使索引/索引統(tǒng)計(jì)/值比較都更加復(fù)雜，對(duì)MySQL來(lái)說(shuō)更難優(yōu)化
    2). null 這種類(lèi)型MySQL內(nèi)部需要進(jìn)行特殊處理，增加數(shù)據(jù)庫(kù)處理記錄的復(fù)雜性；同等條件下，表中有較多空字段的時(shí)候，數(shù)據(jù)庫(kù)的處理性能會(huì)降低很多
    3). null值需要更多的存儲(chǔ)空，無(wú)論是表還是索引中每行中的null的列都需要額外的空間來(lái)標(biāo)識(shí)
    4). 對(duì)null 的處理時(shí)候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in這些操作符號(hào)。

  • 盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

  • 禁止使用外鍵，如果有外鍵完整性約束，需要應(yīng)用程序控制。

  • 索引應(yīng)建立在那些將用于JOIN,WHERE判斷和ORDER BY排序的字段上。

  • 禁止在更新十分頻繁、區(qū)分度不高的屬性上建立索引。
    更新會(huì)變更B+樹(shù)，更新頻繁的字段建立索引會(huì)大大降低數(shù)據(jù)庫(kù)性能
    盡量不要對(duì)數(shù)據(jù)庫(kù)中某個(gè)含有大量重復(fù)的值的字段建立索引:“性別”這種區(qū)分度不大的屬性，建立索引是沒(méi)有什么意義的，不能有效過(guò)濾數(shù)據(jù)，性能與全表掃描類(lèi)似.對(duì)于一個(gè)ENUM類(lèi)型的字段來(lái)說(shuō)，出現(xiàn)大量重復(fù)值是很有可能的情況

  • 建立組合索引，必須把區(qū)分度高的字段放在前面

查詢相關(guān)

    1. 對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及到且區(qū)分度高的列上建立索引。
    1. 不要使用select *,不使用的字段盡量不要查詢
      使用select *的話會(huì)增加解析的時(shí)間，另外會(huì)把不需要的數(shù)據(jù)也給查詢出來(lái)（尤其在天貓?zhí)詫氝@種有億級(jí)客戶的平臺(tái)上，數(shù)據(jù)傳輸費(fèi)時(shí)費(fèi)帶寬），如果增加字段，程序很有可能沒(méi)有對(duì)字段做對(duì)應(yīng)映射出現(xiàn)錯(cuò)誤。
    1. 盡量避免在 where 子句中使用 or 來(lái)連接條件,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,優(yōu)先使用UNION ALL替代，避免使用UNION
      UNION 會(huì)將各查詢子集的記錄做比較，對(duì)比UNION ALL ，速度都會(huì)慢上許多。如果使用UNION ALL能滿足要求的話，優(yōu)先使用UNION ALL。
select id from t where num=10 or num=20
替換
select id from t where num=10
union all
select id from t where num=20
  • 4.慎用not in,會(huì)導(dǎo)致全表掃描，可以考慮使用not exist代替
假定order和crm_user上user_name 有索引，不考慮大表小表，數(shù)據(jù)量一致。

1. select * from order where user_name not in (select user_name from crm_user)
這里crm_user進(jìn)行全表查詢

2. select * from order  o where  not exist (select user_name from crm_user u where o.user_name = u.user_name )
這里crm_user使用了索引

not extsts 的子查詢可以用到外表上的索引。

    1. 要注意exist和in的使用
      in 是把外表和內(nèi)表作hash 連接，而exists是對(duì)外表作loop循環(huán)，每次loop循環(huán)再對(duì)內(nèi)表進(jìn)行查詢。因此，in用到的是外表的索引， exists用到的是內(nèi)表的索引。
      如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢表大的用exists，子查詢表小的用in：
      例如：表A（小表），表B（大表）
select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B表上cc列的索引。

select * from B where cc in (select cc from A)
效率高：先查詢A表數(shù)據(jù)，根據(jù)查詢到的臨時(shí)表查詢B數(shù)據(jù)（使用了B表cc索引）
select * from B where exists(select cc from A where cc=B.cc)
效率低：循環(huán)查詢B數(shù)據(jù)，根據(jù)B數(shù)據(jù)查詢A數(shù)據(jù)（使用到A索引）

  • 6.禁止負(fù)向查詢，以及%開(kāi)頭的模糊查詢
    負(fù)向查詢條件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，會(huì)導(dǎo)致全表掃描
    %開(kāi)頭的模糊查詢，會(huì)導(dǎo)致全表掃描

  • 7.選擇最有效率的表名順序

    1. 刪除重復(fù)記錄
      DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
      FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

  • 9.避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，邏輯運(yùn)算，類(lèi)型轉(zhuǎn)換等，會(huì)導(dǎo)致索引不生效

  • 10.禁止使用屬性隱式轉(zhuǎn)換
    SELECT uid FROM t_user WHERE phone=13812345678 會(huì)導(dǎo)致全表掃描

  • 11.使用連接（JOIN）來(lái)代替子查詢(Sub-Queries)

1. SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)
2. SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID ISNULL
    1. 禁止大表使用JOIN查詢，禁止大表使用子查詢。

會(huì)產(chǎn)生臨時(shí)表，消耗較多內(nèi)存與CPU，極大影響數(shù)據(jù)庫(kù)性能

  • 13 使用OR條件，可以使用IN查詢，between進(jìn)行代替。
    舊版本Mysql的OR查詢是不能命中索引的，即使能命中索引，要使用or生成臨時(shí)表，比較耗費(fèi)CPU和內(nèi)存。

  • 14.limit優(yōu)化（大翻頁(yè)查詢）

 select id,uni_crm_id,phone from film uni_crm_account oder by gmt_create limit 600,20
優(yōu)化后

select id,uni_crm_id,phone from uni_crm_account where id >12000 oder by gmt_create  limit 1,20;
  • 15.建立組合索引，必須把區(qū)分度高的字段放在前面
    索引的最左邊前綴(leftmost prefix of the index)來(lái)進(jìn)行查詢：
    如索引：key(last_name, first_name, dob)
    (1) 匹配全值(Match the full value)：對(duì)索引中的所有列都指定具體的值。例如，上圖中索引可以幫助你查找出生于1960-01-01的Cuba Allen。
    (2) 匹配最左前綴(Match a leftmost prefix)：你可以利用索引查找last name為Allen的人，僅僅使用索引中的第1列。
    (3) 匹配列前綴(Match a column prefix)：例如，你可以利用索引查找last name以J開(kāi)始的人，這僅僅使用索引中的第1列。
    (4) 匹配值的范圍查詢(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之間的人，僅僅使用索引中第1列。
    (5) 匹配部分精確而其它部分進(jìn)行范圍匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name為Allen，而first name以字母K開(kāi)始的人。
    (6)僅對(duì)索引進(jìn)行查詢(Index-only queries)：如果查詢的列都位于索引中，則不需要讀取元組的值。

更新相關(guān)

批量更新

update時(shí)，where語(yǔ)句盡量要走索引，不然會(huì)全表掃描，對(duì)所有記錄鎖定，如果數(shù)據(jù)量較大，更新時(shí)間會(huì)比較長(zhǎng)，長(zhǎng)時(shí)間鎖表是應(yīng)用程序難以承受；還有最好事先計(jì)算更新行數(shù)，在update語(yǔ)句后添加limit <行數(shù)>，避免處理臟數(shù)據(jù)的同時(shí)再發(fā)生二次故障，同時(shí)找人review下，項(xiàng)目組報(bào)備,。

事務(wù)

事務(wù)在并發(fā)情況下很復(fù)雜，存在執(zhí)行慢，lost update，死鎖等。

降低事務(wù)的顆粒度

大事務(wù)執(zhí)行非常耗時(shí)，可以采取異步任務(wù)(MQ，異步線程 )等柔性事務(wù)降低事務(wù)顆粒度，采取正向、逆向?qū)~機(jī)制進(jìn)行事后補(bǔ)償。

鎖

innodb中鎖的類(lèi)型分為共享鎖，排他鎖，意向共享鎖和意向排他鎖。起哄意向鎖是為了解決表鎖和行鎖沖突問(wèn)題，官方文檔：“The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.”。
其中select xxx for update 、insert 、delete、update是排他鎖，共享鎖需要使用lock in share mode。
根據(jù)鎖的粒度分分為行鎖，表鎖。行鎖又分為Record Lock、Gap Lock、Next-Key Lock。

InnoDB定義的4種隔離級(jí)別事務(wù),降低了并發(fā)沖突的可能。

  1. Read Uncommited(RU)：可以讀取未提交記錄。

  2. Read Committed (RC)：針對(duì)當(dāng)前讀更新，RC隔離級(jí)別保證對(duì)讀取到的記錄加鎖 (記錄鎖)，存在幻讀現(xiàn)象。

  3. Repeatable Read (RR)：基于快照讀，針對(duì)當(dāng)前讀更新，RR隔離級(jí)別保證對(duì)讀取到的記錄加鎖 (記錄鎖)，同時(shí)保證對(duì)讀取的范圍加鎖，新的滿足查詢條件的記錄不能夠插入 (間隙鎖)，不存在幻讀現(xiàn)象（這里指的是非聚鏃索引）。

  4. Serializable
    所有的讀操作均為當(dāng)前讀，讀加讀鎖 (S鎖)，寫(xiě)加寫(xiě)鎖 (X鎖)。Serializable隔離級(jí)別下，寫(xiě)寫(xiě)沖突，讀寫(xiě)沖突，并發(fā)度急劇下降。

MYSQL采用了兩階段鎖協(xié)議，含義是：第一個(gè)階段是獲得鎖(nsert/update/delete/select ... for update)，第二個(gè)階段是釋放鎖(commit/rollback)；保證并發(fā)調(diào)度的正確性，兩階段封鎖相對(duì)于一階段鎖（一次性獲得事務(wù)需要的所有鎖），提高了并發(fā)度，但同時(shí)也帶來(lái)了死鎖的可能。

具體加鎖機(jī)制請(qǐng)看P9大神博文：加鎖機(jī)制分析

lost update

無(wú)論是Read Commited還是Repeable Read級(jí)別，都有可能發(fā)生lost upate（即覆寫(xiě)）,詳見(jiàn)a-beginners-guide-to-database-locking-and-the-lost-update-phenomena,文中分析了lost update原因，提到解決方案：悲觀鎖和樂(lè)觀鎖。

個(gè)人覺(jué)得選擇悲觀鎖或樂(lè)觀鎖應(yīng)依具體場(chǎng)景而定。

  1. 悲觀鎖：并發(fā)沖突大的情況，會(huì)導(dǎo)致很多任務(wù)阻塞，高并發(fā)修改的場(chǎng)景就不太適合了；但好處也是有的，如有其他客戶端(如msyql console)也在并發(fā)更新情況下，程序是基于當(dāng)前讀(官方文檔的術(shù)語(yǔ)叫l(wèi)ocking read，也就是insert，update，delete,select..in share mode和select..for update)做的更新，同一時(shí)間只允許一個(gè)操作或事務(wù)提交，不會(huì)產(chǎn)生lost update。

  2. 樂(lè)觀鎖: 一般使用版本號(hào)或時(shí)間戳做比較，可以結(jié)合分布式鎖+輪詢來(lái)降低對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)次數(shù)，并行度較高；缺點(diǎn)是，如果其他客戶端也在并行上修改數(shù)據(jù)且未對(duì)樂(lè)觀鎖字段進(jìn)行判斷和更新，存在樂(lè)觀鎖被使用過(guò)一次情況，應(yīng)用程序基于此再次更新數(shù)據(jù)，出現(xiàn)lost update情況。

死鎖

死鎖，指兩個(gè)或多個(gè)事務(wù)，各自占有對(duì)方的期望獲得的資源，形成的循環(huán)等待，彼此無(wú)法繼續(xù)執(zhí)行的一種狀態(tài)。

這里例舉幾個(gè)常簡(jiǎn)死鎖情況。

  1. 如下，session1和session2中2張表更新順序不同，產(chǎn)生死鎖。
session1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update STU set name='小杭' where id=2;
Query OK, 1 row affected (43.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update teacher set name='老師2' where id=1;
Query OK, 1 row affected (5.98 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session2:
mysql> begin;
Query OK, 0 rows affected (0.14 sec)

mysql> update teacher set name='老師1' where id=1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update STU set name='小明' where id=2;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

解決：調(diào)整成一致的更新順序，死鎖解決。

2.一個(gè)事務(wù)中，在同一張表中對(duì)不同記錄進(jìn)行更新，如果更新順序不一致，也有可能發(fā)生死鎖。

session1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update STU set name='剛' where id=13;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update STU set name='行'where id=2;
Query OK, 1 row affected (2.48 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.22 sec)

session2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update STU set name='航'where id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update STU set name='好' where id=13;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

解決：如上問(wèn)題中，對(duì)ID進(jìn)行排序，在根據(jù)排序順序進(jìn)行依次更新。

  1. RR級(jí)別下，插入死鎖
    三個(gè)事務(wù)同時(shí)執(zhí)行，session1最先獲得鎖,session2和session3檢測(cè)到了主鍵沖突錯(cuò)誤但session1未提交所以hold，隨后session1上執(zhí)行回滾，session2順利獲得鎖，session3死鎖了。
session1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into STU(id,stu_no,name,idy,version) values(21,21,'21',21,21);
Query OK, 1 row affected (0.07 sec)

mysql> rollback;
Query OK, 0 rows affected (0.09 sec)

session2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into STU(id,stu_no,name,idy,version) values(21,21,'21',21,21);
Query OK, 1 row affected (11.54 sec)

session3:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into STU(id,stu_no,name,idy,version) values(21,21,'21',21,21);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  1. 同時(shí)執(zhí)行session1和session2,session1和session2加鎖順序相反：session1從name索引出發(fā)，讀到的[hdc, 1]，[hdc, 6]均滿足條件，不僅會(huì)加name索引上的記錄X鎖，而且會(huì)加聚簇索引上的記錄X鎖，加鎖順序?yàn)橄萚1,hdc,100]，后[6,hdc,10]。而Session 2，從pubtime索引出發(fā)，[10,6],[100,1]均滿足過(guò)濾條件，同樣也會(huì)加聚簇索引上的記錄X鎖，加鎖順序?yàn)閇6,hdc,10]，后[1,hdc,100]。如果兩個(gè)Session恰好都持有了第一把鎖，請(qǐng)求加第二把鎖，死鎖就發(fā)生了。
image.png

其他(待完善)

  1. show processlist --可以看到長(zhǎng)時(shí)間執(zhí)行的SQL，死鎖等信息。


    image.png

2.慢sql開(kāi)啟
開(kāi)啟慢查詢?nèi)罩荆簊et global slow_query_log=on;
修改慢查詢時(shí)間：set global long_query_time=1;
查詢慢查詢下sql存放目錄: show global variables like "%slow%";

3.調(diào)用鏈日志
阿里鷹眼，google Dapper，到家守望者等等。

參考

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 轉(zhuǎn) # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    呂品?閱讀 10,123評(píng)論 0贊 44
  • 2018年3月10日 星期六 晴 今天天氣很好，早上我要出門(mén)，走之前給兒子說(shuō)了今天要完成的作業(yè)。 還在外面就...
    高錦澤閱讀 203評(píng)論 0贊 0
  • 不知道為什么有時(shí)候會(huì)想起老爸年輕的時(shí)候，每次想起小時(shí)候爸爸還年輕的樣子我就會(huì)很感慨光陰的力量，那時(shí)候他總是騎摩托車(chē)...
    南市四牌樓鹿閱讀 359評(píng)論 0贊 1
  • 剛好下過(guò)一場(chǎng)雨，就在剛才。 考完免疫學(xué)感覺(jué)像是放下了一個(gè)近期特別重大的工程，但是以前痛苦的經(jīng)驗(yàn)告訴了我學(xué)醫(yī)真的不能...
    你在炎熱里我是你的冰閱讀 476評(píng)論 0贊 3
  • 姓名:冉喬琪~公司:天興醫(yī)藥 【日精進(jìn)打卡第※289※天】 262期努力三組學(xué)員2017 0609-0611 27...
    小小新醬閱讀 210評(píng)論 0贊 0

友情鏈接更多精彩內(nèi)容