有情懷，有乾貨，微信搜尋【三太子敖丙】關注這個不一樣的程式設計師。
本文 GitHub https://github.com/JavaFamily 已收錄，有一線大廠面試完整考點、資料以及我的系列文章。
前言
這天我正在午休呢，公司DBA就把我喊醒了，說某庫出現大量慢SQL，很快啊，很快，我還沒反應過來，庫就掛了，我心想現在的使用者不講武德啊，怎麼在我睡覺的時候大量請求呢。
這是很常見的一個場景哈，因為很多業務開始資料量級不大，所以寫sql的時候就沒注意效能，等量級上去，很多業務就需要做調優了，在電商公司工作的這幾年我也總結了不少，下面就分享給大家吧。
在程式碼開發過程中，我們都會遵循一些SQL開發規範去編寫高質量SQL，來提高介面的Response Time(RT)，對一些核心介面要求RT在100ms以內甚至更低。
由於業務前期資料量比較小，基本都能滿足這個要求，但隨著業務量的增長，資料量也隨之增加，對應介面的SQL耗時也在變長，直接影響了使用者的體驗，這時候就需要對SQL進行優化。
優化點主要包括SQL規範性檢查，表結構索引檢查，SQL優化案例分析，下面從這三方面結合實際案例聊聊如何優化SQL。
SQL規範性檢查
每個公司都有自己的MySQL開發規範，基本上大同小異，這裡羅列一些比較重要的，我工作期間經常接觸的給大家。
select檢查
UDF使用者自定義函式
SQL語句的select後面使用了自定義函式UDF，SQL返回多少行，那麼UDF函式就會被呼叫多少次，這是非常影響效能的。
#getOrderNo是使用者自定義一個函式使用者來根據order_sn來獲取訂單編號
select id, payment_id, order_sn, getOrderNo(order_sn) from payment_transaction where status = 1 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';
text型別檢查
如果select出現text型別的欄位，就會消耗大量的網路和IO頻寬，由於返回的內容過大超過max_allowed_packet設定會導致程式報錯，需要評估謹慎使用。
#表request_log的中content是text型別。
select user_id, content, status, url, type from request_log where user_id = 32121;
group_concat謹慎使用
gorup_concat是一個字串聚合函式，會影響SQL的響應時間，如果返回的值過大超過了max_allowed_packet設定會導致程式報錯。
select batch_id, group_concat(name) from buffer_batch where status = 0 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';
內聯子查詢
在select後面有子查詢的情況稱為內聯子查詢，SQL返回多少行，子查詢就需要執行過多少次，嚴重影響SQL效能。
select id,(select rule_name from member_rule limit 1) as rule_name, member_id, member_type, member_name, status from member_info m where status = 1 and create_time between '2020-09-02 10:00:00' and '2020-10-01 10:00:00';
from檢查
表的連結方式
在MySQL中不建議使用Left Join，即使ON過濾條件列索引，一些情況也不會走索引，導致大量的資料行被掃描，SQL效能變得很差，同時要清楚ON和Where的區別。
SELECT a.member_id,a.create_time,b.active_time FROM operation_log a LEFT JOIN member_info b ON a.member_id = b.member_id where b.`status` = 1
and a.create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' limit 100, 0;
子查詢
由於MySQL的基於成本的優化器CBO對子查詢的處理能力比較弱，不建議使用子查詢，可以改寫成Inner Join。
select b.member_id,b.member_type, a.create_time,a.device_model from member_operation_log a inner join (select member_id,member_type from member_base_info where `status` = 1
and create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00') as b on a.member_id = b.member_id;
where檢查
索引列被運算
當一個欄位被索引，同時出現where條件後面，是不能進行任何運算，會導致索引失效。
#device_no列上有索引，由於使用了ltrim函式導致索引失效
select id, name , phone, address, device_no from users where ltrim(device_no) = 'Hfs1212121';
#balance列有索引,由於做了運算導致索引失效
select account_no, balance from accounts where balance + 100 = 10000 and status = 1;
型別轉換
對於Int型別的欄位，傳varchar型別的值是可以走索引，MySQL內部自動做了隱式型別轉換；相反對於varchar型別欄位傳入Int值是無法走索引的，應該做到對應的欄位型別傳對應的值總是對的。
#user_id是bigint型別，傳入varchar值發生了隱式型別轉換，可以走索引。
select id, name , phone, address, device_no from users where user_id = '23126';
#card_no是varchar(20)，傳入int值是無法走索引
select id, name , phone, address, device_no from users where card_no = 2312612121;
列字符集
從MySQL 5.6開始建議所有物件字符集應該使用用utf8mb4，包括MySQL例項字符集，資料庫字符集，表字符集，列字符集。避免在關聯查詢Join時欄位字符集不匹配導致索引失效，同時目前只有utf8mb4支援emoji表情儲存。
character_set_server = utf8mb4 #資料庫例項字符集
character_set_connection = utf8mb4 #連線字符集
character_set_database = utf8mb4 #資料庫字符集
character_set_results = utf8mb4 #結果集字符集
group by檢查
字首索引
group by後面的列有索引，索引可以消除排序帶來的CPU開銷，如果是字首索引，是不能消除排序的。
#device_no欄位型別varchar(200)，建立了字首索引。 mysql> alter table users add index idx_device_no(device_no(64));
mysql> select device_no, count(*) from users where create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' group by device_no;
函式運算
假設需要統計某月每天的新增使用者量，參考如下SQL語句，雖然可以走create_time的索引，但是不能消除排序，可以考慮冗餘一個欄位stats_date date型別來解決這種問題。
select DATE_FORMAT(create_time, '%Y-%m-%d'), count(*) from users where create_time between '2020-09-01 00:00:00' and '2020-09-30 23:59:59' group by DATE_FORMAT(create_time, '%Y-%m-%d');
order by檢查
字首索引
order by後面的列有索引，索引可以消除排序帶來的CPU開銷，如果是字首索引，是不能消除排序的。
欄位順序
排序欄位順序，asc/desc升降要跟索引保持一致，充分利用索引的有序性來消除排序帶來的CPU開銷。
limit檢查
limit m,n要慎重
對於limit m, n分頁查詢，越往後面翻頁即m越大的情況下SQL的耗時會越來越長，對於這種應該先取出主鍵id，然後通過主鍵id跟原表進行Join關聯查詢。
表結構檢查
表&列名關鍵字
在資料庫設計建模階段，對錶名及欄位名設定要合理，不能使用MySQL的關鍵字，如desc, order, status, group等。同時建議設定lower_case_table_names = 1表名不區分大小寫。
表儲存引擎
對於OLTP業務系統，建議使用InnoDB引擎獲取更好的效能，可以通過引數default_storage_engine控制。
AUTO_INCREMENT屬性
建表的時候主鍵id帶有AUTO_INCREMENT屬性，而且AUTO_INCREMENT=1，在InnoDB內部是通過一個系統全域性變數dict_sys.row_id來計數，row_id是一個8位元組的bigint unsigned，InnoDB在設計時只給row_id保留了6個位元組的長度，這樣row_id取值範圍就是0到2^48 - 1，如果id的值達到了最大值，下一個值就從0開始繼續迴圈遞增，在程式碼中禁止指定主鍵id值插入。
#新插入的id值會從10001開始，這是不對的，應該從1開始。 create table booking( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',......) engine = InnoDB auto_increment = 10000;指定了id值插入，後續自增就會從該值開始+1，索引禁止指定id值插入。
insert into booking(id, book_sn) values(1234551121, 'N12121');
NOT NULL屬性
根據業務含義，儘量將欄位都新增上NOT NULL DEFAULT VALUE屬性，如果列值儲存了大量的NULL，會影響索引的穩定性。
DEFAULT屬性
在建立表的時候，建議每個欄位儘量都有預設值，禁止DEFAULT NULL，而是對欄位型別填充響應的預設值。
COMMENT屬性
欄位的備註要能明確該欄位的作用，尤其是某些表示狀態的欄位，要顯式的寫出該欄位所有可能的狀態數值以及該數值的含義。
TEXT型別
不建議使用Text資料型別，一方面由於傳輸大量的資料包可能會超過max_allowed_packet設定導致程式報錯，另一方面表上的DML操作都會變的很慢，建議採用es或者物件儲存OSS來儲存和檢索。
索引檢查
索引屬性
索引基數指的是被索引的列唯一值的個數，唯一值越多接近表的count(*)說明索引的選擇率越高，通過索引掃描的行數就越少，效能就越高，例如主鍵id的選擇率是100%，在MySQL中儘量所有的update都使用主鍵id去更新，因為id是聚集索引儲存著整行資料，不需要回表，效能是最高的。
mysql> select count(*) from member_info; +----------+ | count(*) | +----------+ | 148416 | +----------+ 1 row in set (0.35 sec)mysql> show index from member_base_info;
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| member_info | 0 | PRIMARY | 1 | id | A | 131088 | NULL | NULL | | BTREE | | |
| member_info | 0 | uk_member_id | 1 | member_id | A | 131824 | NULL | NULL | | BTREE | | |
| member_info | 1 | idx_create_time | 1 | create_time | A | 6770 | NULL | NULL | | BTREE | | |
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+Table： 表名
Non_unique ：是否為unique index，0-是，1-否。
Key_name：索引名稱
Seq_in_index：索引中的順序號，單列索引-都是1；複合索引-根據索引列的順序從1開始遞增。
Column_name：索引的列名
Collation：排序順序，如果沒有指定asc/desc，預設都是升序ASC。
Cardinality：索引基數-索引列唯一值的個數。
sub_part：字首索引的長度；例如index (member_name(10)，長度就是10。
Packed：索引的組織方式，預設是NULL。
Null：YES:索引列包含Null值；'':索引不包含Null值。
Index_type：預設是BTREE，其他的值FULLTEXT，HASH，RTREE。
Comment：在索引列中沒有被描述的資訊，例如索引被禁用。
Index_comment：建立索引時的備註。
字首索引
對於變長字串型別varchar(m)，為了減少key_len，可以考慮建立字首索引，但是字首索引不能消除group by， order by帶來排序開銷。如果欄位的實際最大值比m小很多，建議縮小欄位長度。
alter table member_info add index idx_member_name_part(member_name(10));
複合索引順序
有很多人喜歡在建立複合索引的時候，總以為前導列一定是唯一值多的列，例如索引index idx_create_time_status(create_time, status)，這個索引往往是無法命中，因為掃描的IO次數太多，總體的cost的比全表掃描還大，CBO最終的選擇是走full table scan。
MySQL遵循的是索引最左匹配原則，對於複合索引，從左到右依次掃描索引列，到遇到第一個範圍查詢（>=, >,<, <=, between ….. and ….）就停止掃描，索引正確的索引順序應該是index idx_status_create_time(status, create_time)。
select account_no, balance from accounts where status = 1 and create_time between '2020-09-01 00:00:00' and '2020-09-30 23:59:59';
時間列索引
對於預設欄位created_at(create_time)、updated_at(update_time)這種預設就應該建立索引，這一般來說是預設的規則。
SQL優化案例
通過對慢查詢的監控告警，經常發現一些SQL語句where過濾欄位都有索引，但是由於SQL寫法的問題導致索引失效，下面二個案例告訴大家如何通過SQL改寫來查詢。可以通過以下SQL來撈取最近5分鐘的慢查詢進行告警。
select CONCAT( '# Time: ', DATE_FORMAT(start_time, '%y%m%d %H%i%s'), '\n', '# User@Host: ', user_host, '\n', '# Query_time: ', TIME_TO_SEC(query_time), ' Lock_time: ', TIME_TO_SEC(lock_time), ' Rows_sent: ', rows_sent, ' Rows_examined: ', rows_examined, '\n', sql_text, ';' ) FROM mysql.slow_log where start_time between current_timestamp and date_add(CURRENT_TIMESTAMP,INTERVAL -5 MINUTE);
慢查詢SQL
| 2020-10-02 19:17:23 | w_mini_user[w_mini_user] @ [10.200.20.11] | 00:00:02 | 00:00:00 | 9 | 443117 | mini_user | 0 | 0 | 168387936 | select id,club_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or applicant_id=12395) order by created_time desc limit 0,10; | 1219921665 |
從慢查詢slow_log可以看到，執行時間2s，掃描了443117行，只返回了9行，這是不合理的。
SQL分析
#原始SQL，頻繁訪問的介面，目前執行時間2s。 select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;執行計劃
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | t_user_msg | index | invite_id,app_id,team_id | created_time | 5 | NULL | 10 | Using where |
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)
從執行計劃可以看到，表上有單列索引invite_id,app_id,team_id,created_time，走的是create_time的索引，而且type=index索引全掃描，因為create_time沒有出現在where條件後，只出現在order by後面，只能是type=index，這也預示著表資料量越大該SQL越慢，我們期望是走三個單列索引invite_id，app_id，team_id，然後type=index_merge操作。
按照常規思路，對於OR條件拆分兩部分，分別進行分析。
select id, ……. from t_user_msg where 1 and **(team_id in (3212) and app_id is not null)** order by created_time desc limit 0,10;
從執行計劃看走的是team_id的索引，沒有問題。
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+----------------------+---------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | t_user_msg | ref | app_id,team_id | team_id | 8 | const | 30 | Using where; Using filesort |
再看另外一個sql語句：
select id, ……. from t_user_msg where 1 and **(invite_id=12395 or app_id=12395)** order by created_time desc limit 0,10;
從執行計劃上看，分別走的是invite_id,app_id的單列索引，同時做了index_merge合併操作，也沒有問題。
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------------------+
| 1 | SIMPLE | t_user_msg | index_merge | invite_id,app_id | invite_id,app_id | 9,9 | NULL | 2 | Using union(invite_id,app_id); Using where; Using filesort |
通過上面的分析，第一部分SQL走的執行計劃走team_id索引沒問題，第二部分SQL分別走invite_id,app_id索引並且index_merge也沒問題，為什麼兩部分SQL進行OR關聯之後走create_time的單列索引呢，不應該是三個單列索引的index_merge嗎？
index_merge預設是在優化器選項是開啟的，主要是將多個範圍掃描的結果集合併成一個，可以通過變數檢視。
mysql >select @@optimizer_switch;
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,
其他三個欄位都傳入的是具體的值，而且都走了相應的索引，只能懷疑app_id is not null這個條件影響了CBO對最終執行計劃的選擇，去掉這個條件來看執行計劃，竟然走了三個單列索引且type=index_merge，那下面只要搞定app_id is not null這個條件就OK了吧。
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------+---------------------------------+---------------------------------+---------+------+------+---------------------------------------------------------------------------+
| 1 | SIMPLE | t_user_msg | index_merge | invite_id,app_id,teadm_id | team_id,invite_id,app_id | 8,9,9 | NULL | 32 | Using union(team_id,invite_id,app_id); Using where; Using filesort |
SQL改寫
通過上面分析得知，條件app_id is not null影響了CBO的選擇，下面進行改造。
改寫優化1
根據SQL開發規範改寫，將OR改寫成Union All方式即可，最終的SQL如下：
select id, ……. from (
select id, ……. from t_user_msg where **1 and (club_id in (5821) and applicant_id is not null)**
**union all** select id, ……. from t_user_msg where **1 and invitee_id='146737'**
**union all** select id, ……. from t_user_msg where **1 and app_id='146737'**
) as a order by created_time desc limit 0,10;
一般情況下，Java程式碼和SQL是分開的，SQL是配置在xml檔案中，根據業務需求，除了team_id是必填，其他兩個都是可選的，所以這種改寫雖然能提高SQL執行效率，但不適合這種業務場景。
改寫優化2
app_id is not null 改寫為IFNULL(app_id, 0) >0)，最終的SQL為：
select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **IFNULL(app_id, 0) >0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;
改寫優化3
將欄位app_id bigint(20) DEFAULT NULL，變更為app_id bigint(20) NOT NULL DEFAULT 0，同時更新將app_id is null的時候全部更新成0，就可以將條件app_id is not null 轉換為app_id > 0，最終的SQL為：
select id,team_id,reason,status,type,created_at,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **app_id > 0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;
從執行計劃看，兩種改寫優化方式都走三個單列索引，執行時間從2s降低至10ms，線上採用的是優化1的方式，如果一開始能遵循MySQL開發規範就就會避免問題的發生。
總結
上面介紹了SQL規範性檢查，表結構檢查，索引檢查以及通過SQL改寫來優化查詢，在編寫程式碼的過程，如果能提前做這些規範性檢查，評估出自己認為理想的執行計劃，然後通過explain解析出MySQL CBO的執行計劃，兩者做對比分析差異，弄清楚自己的選擇和CBO的不同，不但能夠編寫高質量的SQL，同時也能清楚CBO的工作原理。
文章持續更新，可以微信搜一搜「 三太子敖丙 」第一時間閱讀，回覆【資料】有我準備的一線大廠面試資料和簡歷模板，本文 GitHub https://github.com/JavaFamily 已經收錄，有大廠面試完整考點，歡迎Star。