怎么進行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 &hellip;.. and &hellip;.)就停止掃描，索引正確的索引順序應該是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調優”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！