這天我正在午休呢，公司DBA就把我喊醒了，說某庫出現(xiàn)大量慢SQL，很快啊，很快，我還沒反應(yīng)過來，庫就掛了，我心想現(xiàn)在的用戶不講武德啊，怎么在我睡覺的時(shí)候大量請(qǐng)求呢。

這是很常見的一個(gè)場景哈，因?yàn)楹芏鄻I(yè)務(wù)開始數(shù)據(jù)量級(jí)不大，所以寫sql的時(shí)候就沒注意性能，等量級(jí)上去，很多業(yè)務(wù)就需要做調(diào)優(yōu)了，在電商公司工作的這幾年我也總結(jié)了不少，下面就分享給大家吧。

在代碼開發(fā)過程中，我們都會(huì)遵循一些SQL開發(fā)規(guī)范去編寫高質(zhì)量SQL，來提高接口的Response Time(RT)，對(duì)一些核心接口要求RT在100ms以內(nèi)甚至更低。

由于業(yè)務(wù)前期數(shù)據(jù)量比較小，基本都能滿足這個(gè)要求，但隨著業(yè)務(wù)量的增長，數(shù)據(jù)量也隨之增加，對(duì)應(yīng)接口的SQL耗時(shí)也在變長，直接影響了用戶的體驗(yàn)，這時(shí)候就需要對(duì)SQL進(jìn)行優(yōu)化。

優(yōu)化點(diǎn)主要包括SQL規(guī)范性檢查，表結(jié)構(gòu)索引檢查，SQL優(yōu)化案例分析，下面從這三方面結(jié)合實(shí)際案例聊聊如何優(yōu)化SQL。

SQL規(guī)范性檢查

每個(gè)公司都有自己的MySQL開發(fā)規(guī)范，基本上大同小異，這里羅列一些比較重要的，我工作期間經(jīng)常接觸的給大家。

select檢查

UDF用戶自定義函數(shù)

SQL語句的select后面使用了自定義函數(shù)UDF，SQL返回多少行，那么UDF函數(shù)就會(huì)被調(diào)用多少次，這是非常影響性能的。

#getOrderNo是用戶自定義一個(gè)函數(shù)用戶來根據(jù)order_sn來獲取訂單編號(hào) 
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出現(xiàn)text類型的字段，就會(huì)消耗大量的網(wǎng)絡(luò)和IO帶寬，由于返回的內(nèi)容過大超過max_allowed_packet設(shè)置會(huì)導(dǎo)致程序報(bào)錯(cuò)，需要評(píng)估謹(jǐn)慎使用。

#表request_log的中content是text類型。 
select user_id, content, status, url, type from request_log where user_id = 32121; 

group_concat謹(jǐn)慎使用

gorup_concat是一個(gè)字符串聚合函數(shù)，會(huì)影響SQL的響應(yīng)時(shí)間，如果返回的值過大超過了max_allowed_packet設(shè)置會(huì)導(dǎo)致程序報(bào)錯(cuò)。

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'; 

內(nèi)聯(lián)子查詢

在select后面有子查詢的情況稱為內(nèi)聯(lián)子查詢，SQL返回多少行，子查詢就需要執(zhí)行過多少次，嚴(yán)重影響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過濾條件列索引，一些情況也不會(huì)走索引，導(dǎo)致大量的數(shù)據(jù)行被掃描，SQL性能變得很差，同時(shí)要清楚ON和Where的區(qū)別。

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的基于成本的優(yōu)化器CBO對(duì)子查詢的處理能力比較弱，不建議使用子查詢，可以改寫成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檢查

索引列被運(yùn)算

當(dāng)一個(gè)字段被索引，同時(shí)出現(xiàn)where條件后面，是不能進(jìn)行任何運(yùn)算，會(huì)導(dǎo)致索引失效。

#device_no列上有索引，由于使用了ltrim函數(shù)導(dǎo)致索引失效 
select id, name , phone, address, device_no from users where ltrim(device_no) = 'Hfs1212121'; 
#balance列有索引,由于做了運(yùn)算導(dǎo)致索引失效 
select account_no, balance from accounts where balance + 100 = 10000 and status = 1; 

類型轉(zhuǎn)換

對(duì)于Int類型的字段，傳varchar類型的值是可以走索引，MySQL內(nèi)部自動(dòng)做了隱式類型轉(zhuǎn)換;相反對(duì)于varchar類型字段傳入Int值是無法走索引的，應(yīng)該做到對(duì)應(yīng)的字段類型傳對(duì)應(yīng)的值總是對(duì)的。

#user_id是bigint類型，傳入varchar值發(fā)生了隱式類型轉(zhuǎn)換，可以走索引。 
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開始建議所有對(duì)象字符集應(yīng)該使用用utf8mb4，包括MySQL實(shí)例字符集，數(shù)據(jù)庫字符集，表字符集，列字符集。避免在關(guān)聯(lián)查詢Join時(shí)字段字符集不匹配導(dǎo)致索引失效，同時(shí)目前只有utf8mb4支持emoji表情存儲(chǔ)。

character_set_server  =  utf8mb4    #數(shù)據(jù)庫實(shí)例字符集 
character_set_connection = utf8mb4  #連接字符集 
character_set_database = utf8mb4    #數(shù)據(jù)庫字符集 
character_set_results = utf8mb4     #結(jié)果集字符集 

group by檢查

前綴索引

group by后面的列有索引，索引可以消除排序帶來的CPU開銷，如果是前綴索引，是不能消除排序的。

#device_no字段類型varchar(200)，創(chuàng)建了前綴索引。 
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; 

函數(shù)運(yùn)算

假設(shè)需要統(tǒng)計(jì)某月每天的新增用戶量，參考如下SQL語句，雖然可以走create_time的索引，但是不能消除排序，可以考慮冗余一個(gè)字段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要慎重

對(duì)于limit m, n分頁查詢，越往后面翻頁即m越大的情況下SQL的耗時(shí)會(huì)越來越長，對(duì)于這種應(yīng)該先取出主鍵id，然后通過主鍵id跟原表進(jìn)行Join關(guān)聯(lián)查詢。

表結(jié)構(gòu)檢查

表&列名關(guān)鍵字

在數(shù)據(jù)庫設(shè)計(jì)建模階段，對(duì)表名及字段名設(shè)置要合理，不能使用MySQL的關(guān)鍵字，如desc, order, status, group等。同時(shí)建議設(shè)置lower_case_table_names = 1表名不區(qū)分大小寫。

表存儲(chǔ)引擎

對(duì)于OLTP業(yè)務(wù)系統(tǒng)，建議使用InnoDB引擎獲取更好的性能，可以通過參數(shù)default_storage_engine控制。

AUTO_INCREMENT屬性

建表的時(shí)候主鍵id帶有AUTO_INCREMENT屬性，而且AUTO_INCREMENT=1，在InnoDB內(nèi)部是通過一個(gè)系統(tǒng)全局變量dict_sys.row_id來計(jì)數(shù)，row_id是一個(gè)8字節(jié)的bigint unsigned，InnoDB在設(shè)計(jì)時(shí)只給row_id保留了6個(gè)字節(jié)的長度，這樣row_id取值范圍就是0到2^48 - 1，如果id的值達(dá)到了最大值，下一個(gè)值就從0開始繼續(xù)循環(huán)遞增，在代碼中禁止指定主鍵id值插入。

#新插入的id值會(huì)從10001開始，這是不對(duì)的，應(yīng)該從1開始。 
create table booking( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',......) engine = InnoDB auto_increment = 10000; 
 
#指定了id值插入，后續(xù)自增就會(huì)從該值開始+1，索引禁止指定id值插入。 
insert into booking(id, book_sn) values(1234551121, 'N12121'); 

NOT NULL屬性

根據(jù)業(yè)務(wù)含義，盡量將字段都添加上NOT NULL DEFAULT VALUE屬性，如果列值存儲(chǔ)了大量的NULL，會(huì)影響索引的穩(wěn)定性。

DEFAULT屬性

在創(chuàng)建表的時(shí)候，建議每個(gè)字段盡量都有默認(rèn)值，禁止DEFAULT NULL，而是對(duì)字段類型填充響應(yīng)的默認(rèn)值。

COMMENT屬性

字段的備注要能明確該字段的作用，尤其是某些表示狀態(tài)的字段，要顯式的寫出該字段所有可能的狀態(tài)數(shù)值以及該數(shù)值的含義。

TEXT類型

不建議使用Text數(shù)據(jù)類型，一方面由于傳輸大量的數(shù)據(jù)包可能會(huì)超過max_allowed_packet設(shè)置導(dǎo)致程序報(bào)錯(cuò)，另一方面表上的DML操作都會(huì)變的很慢，建議采用es或者對(duì)象存儲(chǔ)OSS來存儲(chǔ)和檢索。

索引檢查

索引屬性

索引基數(shù)指的是被索引的列唯一值的個(gè)數(shù)，唯一值越多接近表的count(*)說明索引的選擇率越高，通過索引掃描的行數(shù)就越少，性能就越高，例如主鍵id的選擇率是100%，在MySQL中盡量所有的update都使用主鍵id去更新，因?yàn)閕d是聚集索引存儲(chǔ)著整行數(shù)據(jù)，不需要回表，性能是最高的。

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：索引中的順序號(hào)，單列索引-都是1；復(fù)合索引-根據(jù)索引列的順序從1開始遞增。 
#Column_name：索引的列名 
#Collation：排序順序，如果沒有指定asc/desc，默認(rèn)都是升序ASC。 
#Cardinality：索引基數(shù)-索引列唯一值的個(gè)數(shù)。 
#sub_part：前綴索引的長度；例如index (member_name(10)，長度就是10。 
#Packed：索引的組織方式，默認(rèn)是NULL。 
#Null：YES:索引列包含Null值；'':索引不包含Null值。 
#Index_type：默認(rèn)是BTREE，其他的值FULLTEXT，HASH，RTREE。 
#Comment：在索引列中沒有被描述的信息，例如索引被禁用。 
#Index_comment：創(chuàng)建索引時(shí)的備注。 

前綴索引

對(duì)于變長字符串類型varchar(m)，為了減少key_len，可以考慮創(chuàng)建前綴索引，但是前綴索引不能消除group by， order by帶來排序開銷。如果字段的實(shí)際最大值比m小很多，建議縮小字段長度。

alter table member_info add index idx_member_name_part(member_name(10)); 

復(fù)合索引順序

有很多人喜歡在創(chuàng)建復(fù)合索引的時(shí)候，總以為前導(dǎo)列一定是唯一值多的列，例如索引index idx_create_time_status(create_time, status)，這個(gè)索引往往是無法命中，因?yàn)閽呙璧腎O次數(shù)太多，總體的cost的比全表掃描還大，CBO最終的選擇是走full table scan。

MySQL遵循的是索引最左匹配原則，對(duì)于復(fù)合索引，從左到右依次掃描索引列，到遇到第一個(gè)范圍查詢(>=, >,<, <=, between ….. and ….)就停止掃描，索引正確的索引順序應(yīng)該是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'; 

時(shí)間列索引

對(duì)于默認(rèn)字段created_at(create_time)、updated_at(update_time)這種默認(rèn)就應(yīng)該創(chuàng)建索引，這一般來說是默認(rèn)的規(guī)則。

SQL優(yōu)化案例

通過對(duì)慢查詢的監(jiān)控告警，經(jīng)常發(fā)現(xiàn)一些SQL語句where過濾字段都有索引，但是由于SQL寫法的問題導(dǎo)致索引失效，下面二個(gè)案例告訴大家如何通過SQL改寫來查詢。可以通過以下SQL來撈取最近5分鐘的慢查詢進(jìn)行告警。

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可以看到，執(zhí)行時(shí)間2s，掃描了443117行，只返回了9行，這是不合理的。

SQL分析

#原始SQL，頻繁訪問的接口，目前執(zhí)行時(shí)間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; 
 
#執(zhí)行計(jì)劃 
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+ 
| 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) 

從執(zhí)行計(jì)劃可以看到，表上有單列索引invite_id,app_id,team_id,created_time，走的是create_time的索引，而且type=index索引全掃描，因?yàn)閏reate_time沒有出現(xiàn)在where條件后，只出現(xiàn)在order by后面，只能是type=index，這也預(yù)示著表數(shù)據(jù)量越大該SQL越慢，我們期望是走三個(gè)單列索引invite_id，app_id，team_id，然后type=index_merge操作。

按照常規(guī)思路，對(duì)于OR條件拆分兩部分，分別進(jìn)行分析。

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; 

從執(zhí)行計(jì)劃看走的是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 | 

再看另外一個(gè)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; 

從執(zhí)行計(jì)劃上看，分別走的是invite_id,app_id的單列索引，同時(shí)做了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走的執(zhí)行計(jì)劃走team_id索引沒問題，第二部分SQL分別走invite_id,app_id索引并且index_merge也沒問題，為什么兩部分SQL進(jìn)行OR關(guān)聯(lián)之后走create_time的單列索引呢，不應(yīng)該是三個(gè)單列索引的index_merge嗎?

index_merge默認(rèn)是在優(yōu)化器選項(xiàng)是開啟的，主要是將多個(gè)范圍掃描的結(jié)果集合并成一個(gè)，可以通過變量查看。

mysql >select @@optimizer_switch; 
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on, 

其他三個(gè)字段都傳入的是具體的值，而且都走了相應(yīng)的索引，只能懷疑app_id is not null這個(gè)條件影響了CBO對(duì)最終執(zhí)行計(jì)劃的選擇，去掉這個(gè)條件來看執(zhí)行計(jì)劃，竟然走了三個(gè)單列索引且type=index_merge，那下面只要搞定app_id is not null這個(gè)條件就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的選擇，下面進(jìn)行改造。

改寫優(yōu)化1

根據(jù)SQL開發(fā)規(guī)范改寫，將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文件中，根據(jù)業(yè)務(wù)需求，除了team_id是必填，其他兩個(gè)都是可選的，所以這種改寫雖然能提高SQL執(zhí)行效率，但不適合這種業(yè)務(wù)場景。

改寫優(yōu)化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; 

改寫優(yōu)化3

將字段app_id bigint(20) DEFAULT NULL，變更為app_id bigint(20) NOT NULL DEFAULT 0，同時(shí)更新將app_id is null的時(shí)候全部更新成0，就可以將條件app_id is not null 轉(zhuǎn)換為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; 

從執(zhí)行計(jì)劃看，兩種改寫優(yōu)化方式都走三個(gè)單列索引，執(zhí)行時(shí)間從2s降低至10ms，線上采用的是優(yōu)化1的方式，如果一開始能遵循MySQL開發(fā)規(guī)范就就會(huì)避免問題的發(fā)生。

總結(jié)

上面介紹了SQL規(guī)范性檢查，表結(jié)構(gòu)檢查，索引檢查以及通過SQL改寫來優(yōu)化查詢，在編寫代碼的過程，如果能提前做這些規(guī)范性檢查，評(píng)估出自己認(rèn)為理想的執(zhí)行計(jì)劃，然后通過explain解析出MySQL CBO的執(zhí)行計(jì)劃，兩者做對(duì)比分析差異，弄清楚自己的選擇和CBO的不同，不但能夠編寫高質(zhì)量的SQL，同時(shí)也能清楚CBO的工作原理。

好啦，以上就是本期的全部內(nèi)容了，我是敖丙，你知道的越多，你不知道的越多，我們下期見。