共計(jì) 12409 個(gè)字符，預(yù)計(jì)需要花費(fèi) 32 分鐘才能閱讀完成。
這篇文章主要介紹“怎么進(jìn)行 SQL 調(diào)優(yōu)”，在日常操作中，相信很多人在怎么進(jìn)行 SQL 調(diào)優(yōu)問(wèn)題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”怎么進(jìn)行 SQL 調(diào)優(yōu)”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著丸趣 TV 小編一起來(lái)學(xué)習(xí)吧！
SQL 規(guī)范性檢查
每個(gè)公司都有自己的 MySQL 開(kāi)發(fā)規(guī)范，基本上大同小異，這里羅列一些比較重要的，我工作期間經(jīng)常接觸的給大家。
select 檢查
UDF 用戶自定義函數(shù)
SQL 語(yǔ)句的 select 后面使用了自定義函數(shù) UDF，SQL 返回多少行，那么 UDF 函數(shù)就會(huì)被調(diào)用多少次，這是非常影響性能的。
#getOrderNo 是用戶自定義一個(gè)函數(shù)用戶來(lái)根據(jù) order_sn 來(lái)獲取訂單編號(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:00text 類型檢查
如果 select 出現(xiàn) text 類型的字段，就會(huì)消耗大量的網(wǎng)絡(luò)和 IO 帶寬，由于返回的內(nèi)容過(guò)大超過(guò) 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í)間，如果返回的值過(guò)大超過(guò)了 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í)行過(guò)多少次，嚴(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:00from 檢查
表的鏈接方式
在 MySQL 中不建議使用 Left Join，即使 ON 過(guò)濾條件列索引，一些情況也不會(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ì)子查詢的處理能力比較弱，不建議使用子查詢，可以改寫(xiě)成 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 值是無(wú)法走索引的，應(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 值是無(wú)法走索引 select id, name , phone, address, device_no from users where card_no = 2312612121;列字符集
從 MySQL 5.6 開(kāi)始建議所有對(duì)象字符集應(yīng)該使用用 utf8mb4，包括 MySQL 實(shí)例字符集，數(shù)據(jù)庫(kù)字符集，表字符集，列字符集。避免在關(guān)聯(lián)查詢 Join 時(shí)字段字符集不匹配導(dǎo)致索引失效，同時(shí)目前只有 utf8mb4 支持 emoji 表情存儲(chǔ)。
character_set_server = utf8mb4 #數(shù)據(jù)庫(kù)實(shí)例字符集 character_set_connection = utf8mb4 #連接字符集 character_set_database = utf8mb4 #數(shù)據(jù)庫(kù)字符集 character_set_results = utf8mb4 # 結(jié)果集字符集group by 檢查
前綴索引
group by 后面的列有索引，索引可以消除排序帶來(lái)的 CPU 開(kāi)銷，如果是前綴索引，是不能消除排序的。
#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 語(yǔ)句，雖然可以走 create_time 的索引，但是不能消除排序，可以考慮冗余一個(gè)字段 stats_date date 類型來(lái)解決這種問(wèn)題。
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-%dorder by 檢查
前綴索引
order by 后面的列有索引，索引可以消除排序帶來(lái)的 CPU 開(kāi)銷，如果是前綴索引，是不能消除排序的。
字段順序
排序字段順序，asc/desc 升降要跟索引保持一致，充分利用索引的有序性來(lái)消除排序帶來(lái)的 CPU 開(kāi)銷。
limit 檢查
limit m,n 要慎重
對(duì)于 limit m, n 分頁(yè)查詢，越往后面翻頁(yè)即 m 越大的情況下 SQL 的耗時(shí)會(huì)越來(lái)越長(zhǎng)，對(duì)于這種應(yīng)該先取出主鍵 id，然后通過(guò)主鍵 id 跟原表進(jìn)行 Join 關(guān)聯(lián)查詢。
表結(jié)構(gòu)檢查
表 列名關(guān)鍵字
在數(shù)據(jù)庫(kù)設(shè)計(jì)建模階段，對(duì)表名及字段名設(shè)置要合理，不能使用 MySQL 的關(guān)鍵字，如 desc, order, status, group 等。同時(shí)建議設(shè)置 lower_case_table_names = 1 表名不區(qū)分大小寫(xiě)。
表存儲(chǔ)引擎
對(duì)于 OLTP 業(yè)務(wù)系統(tǒng)，建議使用 InnoDB 引擎獲取更好的性能，可以通過(guò)參數(shù) default_storage_engine 控制。
AUTO_INCREMENT 屬性
建表的時(shí)候主鍵 id 帶有 AUTO_INCREMENT 屬性，而且 AUTO_INCREMENT=1，在 InnoDB 內(nèi)部是通過(guò)一個(gè)系統(tǒng)全局變量 dict_sys.row_id 來(lái)計(jì)數(shù)，row_id 是一個(gè) 8 字節(jié)的 bigint unsigned，InnoDB 在設(shè)計(jì)時(shí)只給 row_id 保留了 6 個(gè)字節(jié)的長(zhǎng)度，這樣 row_id 取值范圍就是 0 到 2^48 – 1，如果 id 的值達(dá)到了最大值，下一個(gè)值就從 0 開(kāi)始繼續(xù)循環(huán)遞增，在代碼中禁止指定主鍵 id 值插入。
# 新插入的 id 值會(huì)從 10001 開(kāi)始，這是不對(duì)的，應(yīng)該從 1 開(kāi)始。 create table booking( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 主鍵 id ,......) engine = InnoDB auto_increment = 10000; # 指定了 id 值插入，后續(xù)自增就會(huì)從該值開(kāi)始 +1，索引禁止指定 id 值插入。 insert into booking(id, book_sn) values(1234551121, N12121NOT 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)的字段，要顯式的寫(xiě)出該字段所有可能的狀態(tài)數(shù)值以及該數(shù)值的含義。
TEXT 類型
不建議使用 Text 數(shù)據(jù)類型，一方面由于傳輸大量的數(shù)據(jù)包可能會(huì)超過(guò) max_allowed_packet 設(shè)置導(dǎo)致程序報(bào)錯(cuò)，另一方面表上的 DML 操作都會(huì)變的很慢，建議采用 es 或者對(duì)象存儲(chǔ) OSS 來(lái)存儲(chǔ)和檢索。
索引檢查
索引屬性
索引基數(shù)指的是被索引的列唯一值的個(gè)數(shù)，唯一值越多接近表的 count(*)說(shuō)明索引的選擇率越高，通過(guò)索引掃描的行數(shù)就越少，性能就越高，例如主鍵 id 的選擇率是 100%，在 MySQL 中盡量所有的 update 都使用主鍵 id 去更新，因?yàn)?id 是聚集索引存儲(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 開(kāi)始遞增。 #Column_name：索引的列名 #Collation：排序順序，如果沒(méi)有指定 asc/desc，默認(rèn)都是升序 ASC。 #Cardinality：索引基數(shù) - 索引列唯一值的個(gè)數(shù)。 #sub_part：前綴索引的長(zhǎng)度；例如 index (member_name(10)，長(zhǎng)度就是 10。 #Packed：索引的組織方式，默認(rèn)是 NULL。 #Null：YES: 索引列包含 Null 值；: 索引不包含 Null 值。 #Index_type：默認(rèn)是 BTREE，其他的值 FULLTEXT，HASH，RTREE。 #Comment：在索引列中沒(méi)有被描述的信息，例如索引被禁用。 #Index_comment：創(chuàng)建索引時(shí)的備注。前綴索引
對(duì)于變長(zhǎng)字符串類型 varchar(m)，為了減少 key_len，可以考慮創(chuàng)建前綴索引，但是前綴索引不能消除 group by，order by 帶來(lái)排序開(kāi)銷。如果字段的實(shí)際最大值比 m 小很多，建議縮小字段長(zhǎng)度。
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è)索引往往是無(wú)法命中，因?yàn)閽呙璧?IO 次數(shù)太多，總體的 cost 的比全表掃描還大，CBO 最終的選擇是走 full table scan。
MySQL 遵循的是索引最左匹配原則，對(duì)于復(fù)合索引，從左到右依次掃描索引列，到遇到第一個(gè)范圍查詢 (=, , , =, between hellip;.. and hellip;.) 就停止掃描，索引正確的索引順序應(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)建索引，這一般來(lái)說(shuō)是默認(rèn)的規(guī)則。
SQL 優(yōu)化案例
通過(guò)對(duì)慢查詢的監(jiān)控告警，經(jīng)常發(fā)現(xiàn)一些 SQL 語(yǔ)句 where 過(guò)濾字段都有索引，但是由于 SQL 寫(xiě)法的問(wèn)題導(dǎo)致索引失效，下面二個(gè)案例告訴大家如何通過(guò) SQL 改寫(xiě)來(lái)查詢。可以通過(guò)以下 SQL 來(lái)?yè)迫∽罱?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，頻繁訪問(wèn)的接口，目前執(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)?create_time 沒(méi)有出現(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, hellip; hellip;. 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 的索引，沒(méi)有問(wèn)題。
| 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 語(yǔ)句：
select id, hellip; hellip;. 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 合并操作，也沒(méi)有問(wèn)題。
| 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 |通過(guò)上面的分析，第一部分 SQL 走的執(zhí)行計(jì)劃走 team_id 索引沒(méi)問(wèn)題，第二部分 SQL 分別走 invite_id,app_id 索引并且 index_merge 也沒(méi)問(wèn)題，為什么兩部分 SQL 進(jìn)行 OR 關(guān)聯(lián)之后走 create_time 的單列索引呢，不應(yīng)該是三個(gè)單列索引的 index_merge 嗎?
index_merge 默認(rèn)是在優(yōu)化器選項(xiàng)是開(kāi)啟的，主要是將多個(gè)范圍掃描的結(jié)果集合并成一個(gè)，可以通過(guò)變量查看。
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è)條件來(lái)看執(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 改寫(xiě)
通過(guò)上面分析得知，條件 app_id is not null 影響了 CBO 的選擇，下面進(jìn)行改造。
改寫(xiě)優(yōu)化 1
根據(jù) SQL 開(kāi)發(fā)規(guī)范改寫(xiě)，將 OR 改寫(xiě)成 Union All 方式即可，最終的 SQL 如下：
select id, hellip; hellip;. from ( select id, hellip; hellip;. from t_user_msg where **1 and (club_id in (5821) and applicant_id is not null)** **union all** select id, hellip; hellip;. from t_user_msg where **1 and invitee_id= 146737 ** **union all** select id, hellip; hellip;. from t_user_msg where **1 and app_id= 146737 ** ) as a order by created_time desc limit 0,10;一般情況下，Java 代碼和 SQL 是分開(kāi)的，SQL 是配置在 xml 文件中，根據(jù)業(yè)務(wù)需求，除了 team_id 是必填，其他兩個(gè)都是可選的，所以這種改寫(xiě)雖然能提高 SQL 執(zhí)行效率，但不適合這種業(yè)務(wù)場(chǎng)景。
改寫(xiě)優(yōu)化 2
app_id is not null 改寫(xiě)為 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;改寫(xiě)優(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ì)劃看，兩種改寫(xiě)優(yōu)化方式都走三個(gè)單列索引，執(zhí)行時(shí)間從 2s 降低至 10ms，線上采用的是優(yōu)化 1 的方式，如果一開(kāi)始能遵循 MySQL 開(kāi)發(fā)規(guī)范就就會(huì)避免問(wèn)題的發(fā)生。
到此，關(guān)于“怎么進(jìn)行 SQL 調(diào)優(yōu)”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！
