有情懷，有干貨，微信搜索【三太子敖丙】關注這個不一樣的程式員，

本文 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，