怎么優(yōu)化SQL

189次閱讀

沒有評論

共計 3304 個字符，預(yù)計需要花費 9 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下怎么優(yōu)化 SQL，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

判斷問題 SQL

判斷 SQL 是否有問題時可以通過兩個表象進行判斷：

系統(tǒng)級別表象

CPU 消耗嚴重

IO 等待嚴重

頁面響應(yīng)時間過長

應(yīng)用的日志出現(xiàn)超時等錯誤

可以使用 sar 命令，top 命令查看當前系統(tǒng)狀態(tài)。也可以通過 Prometheus、Grafana 等監(jiān)控工具觀察系統(tǒng)狀態(tài)。

SQL 語句表象

冗長

執(zhí)行時間過長

從全表掃描獲取數(shù)據(jù)

執(zhí)行計劃中的 rows、cost 很大

冗長的 SQL 都好理解，一段 SQL 太長閱讀性肯定會差，而且出現(xiàn)問題的頻率肯定會更高。更進一步判斷 SQL 問題就得從執(zhí)行計劃入手，如下所示：

執(zhí)行計劃告訴我們本次查詢走了全表掃描 Type=ALL，rows 很大 (9950400) 基本可以判斷這是一段有味道的 SQL。

獲取問題 SQL

不同數(shù)據(jù)庫有不同的獲取方法，以下為目前主流數(shù)據(jù)庫的慢查詢 SQL 獲取工具

MySQL

慢查詢?nèi)罩?/p>

測試工具 loadrunner

Percona 公司的 ptquery 等工具

Oracle

AWR 報告

測試工具 loadrunner 等

相關(guān)內(nèi)部視圖如 v$、$session_wait 等

GRID CONTROL 監(jiān)控工具

達夢數(shù)據(jù)庫

AWR 報告

測試工具 loadrunner 等

達夢性能監(jiān)控工具（dem）

相關(guān)內(nèi)部視圖如 v$、$session_wait 等

SQL 編寫技巧

SQL 編寫有以下幾個通用的技巧：

? 合理使用索引

索引少了查詢慢；索引多了占用空間大，執(zhí)行增刪改語句的時候需要動態(tài)維護索引，影響性能選擇率高（重復(fù)值少）且被 where 頻繁引用需要建立 B 樹索引；

一般 join 列需要建立索引；復(fù)雜文檔類型查詢采用全文索引效率更好；索引的建立要在查詢和 DML 性能之間取得平衡；復(fù)合索引創(chuàng)建時要注意基于非前導列查詢的情況

? 使用 UNION ALL 替代 UNION

UNION ALL 的執(zhí)行效率比 UNION 高，UNION 執(zhí)行時需要排重；UNION 需要對數(shù)據(jù)進行排序

? 避免 select * 寫法

執(zhí)行 SQL 時優(yōu)化器需要將 * 轉(zhuǎn)成具體的列；每次查詢都要回表，不能走覆蓋索引。

? JOIN 字段建議建立索引

一般 JOIN 字段都提前加上索引

? 避免復(fù)雜 SQL 語句

提升可閱讀性；避免慢查詢的概率；可以轉(zhuǎn)換成多個短查詢，用業(yè)務(wù)端處理

? 避免 where 1= 1 寫法

? 避免 order by rand()類似寫法

RAND()導致數(shù)據(jù)列被多次掃描

SQL 優(yōu)化執(zhí)行計劃

完成 SQL 優(yōu)化一定要先讀執(zhí)行計劃，執(zhí)行計劃會告訴你哪些地方效率低，哪里可以需要優(yōu)化。我們以 MYSQL 為例，看看執(zhí)行計劃是什么。（每個數(shù)據(jù)庫的執(zhí)行計劃都不一樣，需要自行了解）

字段解釋 id 每個被獨立執(zhí)行的操作標識，標識對象被操作的順序，id 值越大，先被執(zhí)行，如果相同，執(zhí)行順序從上到下 select_type 查詢中每個 select 字句的類型 table 被操作的對象名稱，通常是表名，但有其他格式 partitions 匹配的分區(qū)信息(對于非分區(qū)表值為 NULL)type 連接操作的類型 possible_keys 可能用到的索引 key 優(yōu)化器實際使用的索引(最重要的列) 從最好到最差的連接類型為 const、eq_reg、ref、range、index 和 ALL。當出現(xiàn) ALL 時表示當前 SQL 出現(xiàn)了“壞味道”key_len 被優(yōu)化器選定的索引鍵長度，單位是字節(jié) ref 表示本行被操作對象的參照對象，無參照對象為 NULLrows 查詢執(zhí)行所掃描的元組個數(shù)（對于 innodb，此值為估計值）filtered 條件表上數(shù)據(jù)被過濾的元組個數(shù)百分比 extra 執(zhí)行計劃的重要補充信息，當此列出現(xiàn) Using filesort , Using temporary 字樣時就要小心了，很可能 SQL 語句需要優(yōu)化

接下來我們用一段實際優(yōu)化案例來說明 SQL 優(yōu)化的過程及優(yōu)化技巧。

優(yōu)化案例

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

CREATE TABLE `a`
 `id` int(11) NOT NULLAUTO_INCREMENT,
 `seller_id` bigint(20) DEFAULT NULL,
 `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `gmt_create` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`id`)
CREATE TABLE `b`
 `id` int(11) NOT NULLAUTO_INCREMENT,
 `seller_name` varchar(100) DEFAULT NULL,
 `user_id` varchar(50) DEFAULT NULL,
 `user_name` varchar(100) DEFAULT NULL,
 `sales` bigint(20) DEFAULT NULL,
 `gmt_create` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`id`)
CREATE TABLE `c`
 `id` int(11) NOT NULLAUTO_INCREMENT,
 `user_id` varchar(50) DEFAULT NULL,
 `order_id` varchar(100) DEFAULT NULL,
 `state` bigint(20) DEFAULT NULL,
 `gmt_create` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

三張表關(guān)聯(lián)，查詢當前用戶在當前時間前后 10 個小時的訂單情況，并根據(jù)訂單創(chuàng)建時間升序排列，具體 SQL 如下

select a.seller_id,
 a.seller_name,
 b.user_name,
 c.state
from a,
 b,
 c
where a.seller_name = b.seller_name
 and b.user_id = c.user_id
 and c.user_id = 17
 and a.gmt_create
 BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
 AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create；

查看數(shù)據(jù)量

原執(zhí)行時間

原執(zhí)行計劃

初步優(yōu)化思路

SQL 中 where 條件字段類型要跟表結(jié)構(gòu)一致，表中 user_id 為 varchar(50)類型，實際 SQL 用的 int 類型，存在隱式轉(zhuǎn)換，也未添加索引。將 b 和 c 表 user_id 字段改成 int 類型。

因存在 b 表和 c 表關(guān)聯(lián)，將 b 和 c 表 user_id 創(chuàng)建索引

因存在 a 表和 b 表關(guān)聯(lián)，將 a 和 b 表 seller_name 字段創(chuàng)建索引

利用復(fù)合索引消除臨時表和排序

初步優(yōu)化 SQL

alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

查看優(yōu)化后執(zhí)行時間

查看優(yōu)化后執(zhí)行計劃

查看 warnings 信息

繼續(xù)優(yōu)化 alter table a modify gmt_create datetime DEFAULT NULL;

查看執(zhí)行時間

查看執(zhí)行計劃