前面已經介紹了MYSQL的索引和鎖機制，本文主要講述MYSQL查詢/更新語句該怎麼寫

建索引的原則

前面已經有章節講述索引優化使用,本章節略有重複，主要講述建立索引原則

最左字首比對

指在使用複合索引（即由多個列組成的索引）進行查詢時，隻有按照索引中最左邊的列開始依次比對，才能充分利用索引的優勢。

比如有一個複合索引（a, b, c），那麼在查詢時，隻有按照以下方式進行查詢，才能充分利用該索引：

WHERE a = 'value_a'
WHERE a = 'value_a' AND b = 'value_b'
WHERE a = 'value_a' AND b = 'value_b' AND c = 'value_c'

又比如下面查詢是用不到索引

WHERE b = 'value_b' AND c = 'value_c'

注意不要重複建立索引，有了複合索引（a, b, c），就不用再建立(a)、(a,b)索引

覆寫索引，避免回表

參考覆寫索引

選擇區分度高列作為索引

區分度的公式是count(distinct col)/count(*)，表示字段不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀态、性别字段可能在大資料面前區分度就是0

=和in可以亂序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識别的形式。

編寫SQL技巧

基本技巧

查詢時禁止使用*

使用select * 除了寫起來較為簡單，其他方面都不友好

如果新加了字段，代碼不改動可能會報錯，不利于灰階更新
傳回不必要的字段，增加了網絡開銷和記憶體占用
分析成本變高，分析器解析時需要查詢表上*對應哪些字段

不建議使用like左模糊和全模糊查詢

%xxx、%xxx% 會導緻索引失效，2c接口盡量不要使用此查詢條件

管理台等低頻調用可以，否則就需要采用其他機制來查詢，會增加額外硬體成本

查詢時盡量不要對字段做空值判斷

sql複制代碼select * from xxx where yyy is null;
select * from xxx where yyy not is null;

當出現基于字段做空值判斷的情況時，會導緻索引失效，因為判斷null的情況不會走索引，是以切記要避免這樣的情況

一般在設計字段結構的時候，請使用not null來定義字段，同時如果想為空的字段，可以設計一個0、""這類空字元代替

禁止在條件查詢=前對字段做任何運算

sql複制代碼select * from zz_users where user_id * 2 = 8;
select * from zz_users where trim(user_name) = "熊貓";

也是不走索引

!=、!<>、not in、not like、or...要慎用

也會導緻索引失效

or可以使用union all來代替

聯合索引查詢確定字段的順序性

遵循最左比對原則

建議明确傳回一條資料的語句使用limit 1

加上limit 1關鍵字後，當程式比對到一條資料時就會停止掃描，如果不加的情況下會将所有資料都掃描一次。

多表查詢優化

多表查詢盡量不要關聯太多表

主要原因如下：

資料量會随表數量呈直線性增長，資料量越大檢索效率越低。
當關聯的表數量過多時，無法控制好索引的比對，涉及的表越多，索引不可控風險越大。
表越多，SQL邏輯越複雜，維護改造成本變大

多表查詢時一定要以小驅大

以小驅大即是指用小的資料集去驅動大的資料集，說簡單一點就是先查小表，再用小表的結果去大表中檢索資料，其實在MySQL的優化器也會有驅動表的優化，當執行多表聯查時，MySQL的關聯算法為Nest Loop Join，該算法會依照驅動表的結果集作為循環基礎資料，然後通過該結果集中一條條資料，作為過濾條件去下一個表中查詢資料，最後合并結果得到最終資料集，MySQL優化器選擇驅動表的邏輯如下：

如果指定了連接配接條件，滿足查詢條件的小資料表作為驅動表。
如果未指定連接配接條件，資料總行數少的表作為驅動表。
被驅動表字段盡量加上索引

//JOIN查詢邏輯待繼續細化

業務側優化

必要情況下可以強制指定索引

在表中存在多個索引時，有些複雜SQL的情況下，或者在存儲過程中，必要時可強制指定某條查詢語句走某個索引，因為MySQL優化器面對存儲過程、複雜SQL時并沒有那麼智能，有時可能選擇的索引并不是最好的，這時我們可以通過force index

sql複制代碼select * from t1 force index(a) where a = "a";

這樣就能夠100%強制這條SQL走某個索引查詢資料

盡量将大事務拆分為小事務執行

一個事務在執行時，如果其中包含了寫操作，會先擷取鎖再執行，直到事務結束後MySQL才會釋放鎖。

而一個事務占有鎖之後，會導緻其他要操作相同資料的事務被阻塞，如果當一個事務比較大時，會導緻一部分資料的鎖定周期較長，在高并發情況下會引起大量事務出現阻塞，進而最終拖垮整個MySQL系統。

show status like 'innodb_log_waits';檢視是否有大事務由于redo_log_buffer不足，而在等待寫入日志。

解決方案是将大事務改成小事務

從業務設計層面減少大量資料傳回的情況

大量傳回資料就會引起網絡阻塞、記憶體占用過高、資源開銷過大的各類問題出現，是以如果項目中存在這類業務，一定要記住拆分掉它，比如分批傳回給用戶端。

分批查詢的方式也被稱之為增量查詢，每次基于上次傳回資料的界限，再一次讀取一批資料傳回給用戶端，這也就是經典的分頁場景，通過分頁的思想能夠提升單次查詢的速度，以及避免大資料量帶來的一系列後患問題。

盡量避免深分頁的情況出現

如下：

sql複制代碼select xx,xx,xx from t1 limit 100000,10;

可以改成

sql複制代碼select xx,xx,xx from t1 where id in (select id from t1 limit 100000,10);

如果字段有序并且連續，可以直接通過有序字段來判斷

用戶端的一些操作可以批量化完成

一些連續插入更新操作，可以采用适中事務批量完成

其他場景

避免頻繁建立、銷毀臨時表

MySQL中的一些查詢操作會産生臨時表，主要包括以下情況：

排序操作：如果一個查詢中包含ORDER BY子句，但是索引不能完全滿足排序的條件，MySQL就會使用一個臨時表來進行排序操作。
分組操作：如果一個查詢中包含GROUP BY子句，MySQL會使用一個臨時表來存儲分組後的結果集，然後再進行聚合操作。
連接配接操作：如果一個查詢中包含JOIN子句，MySQL會根據連接配接條件将兩個表連接配接在一起，然後将結果儲存到一個臨時表中。
子查詢操作：如果一個查詢中包含子查詢，MySQL會先執行子查詢，然後将子查詢的結果儲存到一個臨時表中，再進行外部查詢操作。

慢查詢優化-Explain

是MySQL自帶的一個執行分析工具，可使用于select、insert、update、delete、repleace等語句上，需要使用時隻需在SQL語句前加上一個explain關鍵字即可，然後MySQL會對應語句的執行計劃列出

mysql複制代碼mysql> explain delete from t2 where a='a' and b='a' and c='c';
+----+-------------+-------+------------+-------+---------------+------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+-------------------+------+----------+-------------+
|  1 | DELETE      | t2    | NULL       | range | uniq_a_b_c    | uniq_a_b_c | 276     | const,const,const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------------+---------+-------------------+------+----------+-------------+
1 row in set (0.01 sec)

id

這是執行計劃的ID值，一條SQL語句可能會出現多步執行計劃，是以會出現多個ID值，這個值越大，表示執行的優先級越高，同時還會出現四種情況：

ID相同：當出現多個ID相同的執行計劃時，從上往下挨個執行。
ID不同時：按照ID值從大到小依次執行。
ID有相同又有不同：先從大到小依次執行，碰到相同ID時從上往下執行。
ID為空：ID=null時，會放在最後執行。

select_type

目前執行的select語句其具體的查詢類型:

SIMPLE：簡單的select查詢語句，不包含union、子查詢語句。
PRIMARY：union或子查詢語句中，最外層的主select語句。
SUBQUEPY：包含在主select語句中的第一個子查詢，如select ... xx = (select ...)。
DERIVED：派生表，指包含在from中的子查詢語句，如select ... from (select ...)。
DEPENDENT SUBQUEPY：複雜SQL中的第一個select子查詢（依賴于外部查詢的結果集）。
UNCACHEABLE SUBQUERY：不緩存結果集的子查詢語句。
UNION：多條語句通過union組成的查詢中，第二個以及更後面的select語句。
UNION RESULT：union的結果集。
DEPENDENT UNION：含義同上，但是基于外部查詢的結果集來查詢的。
UNCACHEABLE UNION：含義同上，但查詢出的結果集不會加入緩存。
MATERIALIZED：采用物化的方式執行的包含派生表的查詢語句。

table

表示目前這個執行計劃是基于哪張表執行的，這裡會寫出表名，但有時候也不一定是實體磁盤中存在的表名，還有可能出現如下格式：

<derivenN>：基于id=N的查詢結果集，進一步檢索資料。
<unionM,N>：會出現在查詢類型為UNION RESULT的計劃中，表示結果由id=M,N...的查詢組成。
<subqueryN>：基于id=N的子查詢結果，進一步進行資料檢索。
<tableName>：基于磁盤中已建立的某張表查詢。

一句話總結就是：這個字段會寫明，目前的這個執行計劃會基于哪個資料集查詢，有可能是實體表、有可能是子查詢的結果、也有可能是其他查詢生成的派生表。

partitions

用來顯示分區,該列的值表示檢索資料的分區

type

字段表示目前語句執行的類型:

all：全表掃描，基于表中所有的資料，逐行掃描并過濾符合條件的資料。
index：全索引掃描，和全表掃描類似，但這個是把索引樹周遊一次，會比全表掃描要快。
range：基于索引字段進行範圍查詢，如between、<、>、in....等操作時出現的情況。
index_subquery：和上面含義相同，差別：這個是基于非主鍵、唯一索引字段進行in操作。
unique_subquery：執行基于主鍵索引字段，進行in操作的子查詢語句會出現的情況。
index_merge：多條件查詢時，組合使用多個索引來檢索資料的情況。
ref_or_null：基于次級(非主鍵)索引做條件查詢時，該索引字段允許為null出現的情況。
fulltext：基于全文索引字段，進行查詢時出現的情況。
ref：基于非主鍵或唯一索引字段查找資料時，會出現的情況。
eq_ref：連表查詢時，基于主鍵、唯一索引字段比對資料的情況，會出現多次索引查找。
const：通過索引一趟查找後就能擷取到資料，基于唯一、主鍵索引字段查詢資料時的情況。
system：表中隻有一行資料，這是const的一種特例。
null：表中沒有資料，無需經過任何資料檢索，直接傳回結果。

通路資料的方式，性能從好到壞依次為：

完整的性能排序：null → system → const → eq_ref → ref → fulltext → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → all
常見的性能排序：system → const → eq_ref → ref → fulltext → range → index → all

一般在做索引優化時，一般都會要求最好優化到ref級别，至少也要到range級别，也就是最少也要基于次級索引來檢索資料，不允許出現index、all這類全掃描的形式。

possible_keys

顯示目前執行計劃，在執行過程中可能會用到哪些索引來檢索資料，但要注意的一點是：可能會用到并不代表一定會用，在某些情況下，就算有索引可以使用，MySQL也有可能放棄走索引查詢。

key

前面的possible_keys字段表示可能會用到的索引，而key這個字段則會顯示具體使用的索引，一般情況下都會從possible_keys的值中，綜合評判出一個性能最好的索引來進行查詢，但也有兩種情況會出現key=null的這個場景：

possible_keys有值，key為空：出現這種情況多半是由于表中資料不多，是以MySQL會放棄索引，選擇走全表查詢，也有可能是因為SQL導緻索引失效。
possible_keys、key都為空：表示目前表中未建立索引、或查詢語句中未使用索引字段檢索資料。

預設情況下，possible_keys有值時都會從中選取一個索引，但這個選擇的工作是由MySQL優化器自己決定的，如果你想讓查詢語句執行時走固定的索引，則可以通過force index、ignore index的方式強制指定。

key_len

表示對應的執行計劃在執行時，使用到的索引字段長度，一般情況下都為索引字段的長度，但有三種情況例外：

如果索引是字首索引，這裡則隻會使用建立字首索引時，聲明的前N個位元組來檢索資料。
如果是聯合索引，這裡隻會顯示目前SQL會用到的索引字段長度，可能不是全比對的情況。
如果一個索引字段的值允許為空，key_len的長度會為：索引字段長度+1。

ref

顯示索引查找過程中，查詢時會用到的常量或字段：

const：如果顯示這個，則代表目前是在基于主鍵字段值或資料庫已有的常量（如null）查詢資料。

select ... where 主鍵字段 = 主鍵值;
select ... where 索引字段 is null;

顯示具體的字段名：表示目前會基于該字段查詢資料。
func：如果顯示這個，則代表當與索引字段比對的值是一個函數，如：

select ... where 索引字段 = 函數(值);

rows

代表執行時，預計會掃描的行數，這個數字對于InnoDB表來說，其實有時并不夠準确，但也具備很大的參考價值，如果這個值很大，在執行查詢語句時，其效率必然很低，是以該值越小越好。

filtered

該字段表示查詢結果中滿足WHERE條件的行數占總行數的比例，其值範圍為0到1。

"filtered"字段是一個估算值，其準确性取決于MySQL對索引和資料的統計資訊的準确性，以及查詢語句的具體執行計劃。

extra

執行查詢語句時的一些其他資訊，這個資訊對索引調優而言比較重要，可以帶來不小的參考價值，但這個字段會出現的值有很多種，如下：

Using index：表示目前的查詢語句，使用了索引覆寫機制拿到了資料。
Using where：表示目前的查詢語句無法從索引中擷取資料，需要進一步做回表去拿表資料。
Using temporary：表示MySQL在執行查詢時，會建立一張臨時表來處理資料。
Using filesort：表示會以磁盤+記憶體完成排序工作，而完全加載資料到記憶體來完成排序。
Select tables optimized away：表示查詢過程中，對于索引字段使用了聚合函數。
Using where;Using index：表示要傳回的資料在索引中包含，但并不是索引的前導列，需要做回表擷取資料。
NULL：表示查詢的資料未被索引覆寫，但where條件中用到了主鍵，可以直接讀取表資料。
Using index condition：和Using where類似，要傳回的列未完全被索引覆寫，需要回表。
Using join buffer (Block Nested Loop)：連接配接查詢時驅動表不能有效的通過索引加快通路速度時，會使用join-buffer來加快通路速度，在記憶體中完成Loop比對。
Impossible WHERE：where後的條件永遠不可能成立時提示的資訊，如where 1!=1。
Impossible WHERE noticed after reading const tables：基于唯一索引查詢不存在的值時出現的提示。
const row not found：表中不存在資料時會傳回的提示。
distinct：去重查詢時，找到某個值的第一個值時，會将查找該值的工作從去重操作中移除。
Start temporary, End temporary：表示臨時表用于DuplicateWeedout半連接配接政策，也就是用來進行semi-join去重。
Using MRR：表示執行查詢時，使用了MRR機制讀取資料。
Using index for skip scan：表示執行查詢語句時，使用了索引跳躍掃描機制讀取資料。
Using index for group-by：表示執行分組或去重工作時，可以基于某個索引處理。
FirstMatch：表示對子查詢語句進行Semi-join優化政策。
No tables used：查詢語句中不存在from子句時提示的資訊，如desc table_name;。
......

具體的可參考《explain-Extra字段詳解》，其中介紹了Extra字段可能會出現的所有值，最後基于Extra字段做個性能排序：

Using index → NULL → Using index condition → Using where → Using where;Using index → Using join buffer → Using filesort → Using MRR → Using index for skip scan → Using temporary → Strart temporary,End temporary → FirstMatch

索引優化參考項

explain工具中的每個字段值，字段數量也比較多，但在做索引優化時，值得咱們參考的幾個字段為：

key：如果該值為空，則表示未使用索引查詢，此時需要調整SQL或建立索引。
type：這個字段決定了查詢的類型，如果為index、all就需要進行優化。
rows：這個字段代表着查詢時可能會掃描的資料行數，較大時也需要進行優化。
filtered：這個字段代表着查詢時，表中不會掃描的資料行占比，較小時需要進行優化。
Extra：這個字段代表着查詢時的具體情況，在某些情況下需要根據對應資訊進行優化。

在explain語句後面緊跟着show warings語句，可以得到優化後的查詢語句，進而看出優化器優化了什麼。

慢查詢優化案例

JOIN 相關配置

join_buffer_size：該參數控制連接配接操作中用于存儲臨時資料的緩沖區大小。如果JOIN操作涉及的資料量較大，可以适當增加該參數的值來提高性能。
sort_buffer_size：該參數控制排序操作中用于存儲臨時資料的緩沖區大小。如果JOIN操作涉及的資料量較大，可以适當增加該參數的值來提高性能。
max_join_size：該參數控制MySQL在執行JOIN操作時，允許連接配接的表的最大大小。如果超出該大小限制，MySQL将會傳回錯誤資訊。該參數的預設值為4GB。

臨時表設定

tmp_table_size：該參數控制MySQL在建立臨時表時，配置設定的記憶體大小。如果臨時表的資料量較大，可以适當增加該參數的值來提高性能。
default_tmp_storage_engine：指定建立臨時表時使用的預設存儲引擎。該參數的預設值為InnoDB。
internal_tmp_disk_storage_engine：該參數用于設定建立基于磁盤的臨時表時使用的存儲引擎。如果建立的臨時表需要存儲到磁盤上，則會使用該參數指定的存儲引擎。預設值為InnoDB。
tmp_table_size：該參數控制MySQL在建立臨時表時，配置設定的記憶體大小。如果臨時表的資料量較大，可以适當增加該參數的值來提高性能。
max_tmp_tables：是用于控制允許建立的最大臨時表數量的參數
tmpdir：是用于指定MySQL在建立臨時表時使用的臨時目錄的參數

MYSQL系列-SQL查詢