判斷問(wèn)題SQL

判斷SQL是否有問(wèn)題時(shí)可以通過(guò)兩個(gè)表象進(jìn)行判斷：

系統(tǒng)級(jí)別表象

• CPU消耗嚴(yán)重
• IO等待嚴(yán)重
• 頁(yè)面響應(yīng)時(shí)間過(guò)長(zhǎng)
• 應(yīng)用的日志出現(xiàn)超時(shí)等錯(cuò)誤

可以使用sar命令，top命令查看當(dāng)前系統(tǒng)狀態(tài)。

也可以通過(guò)Prometheus、Grafana等監(jiān)控工具觀察系統(tǒng)狀態(tài)。

SQL語(yǔ)句表象

• 冗長(zhǎng)
• 執(zhí)行時(shí)間過(guò)長(zhǎng)
• 從全表掃描獲取數(shù)據(jù)
• 執(zhí)行計(jì)劃中的rows、cost很大

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

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

獲取問(wèn)題SQL

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

MySQL

• 慢查詢(xún)?nèi)罩?/li>
• 測(cè)試工具loadrunner
• Percona公司的ptquery等工具

Oracle

• AWR報(bào)告
• 測(cè)試工具loadrunner等
• 相關(guān)內(nèi)部視圖如v$、$session_wait等
• GRID CONTROL監(jiān)控工具

達(dá)夢(mèng)數(shù)據(jù)庫(kù)

• AWR報(bào)告
• 測(cè)試工具loadrunner等
• 達(dá)夢(mèng)性能監(jiān)控工具（dem）
• 相關(guān)內(nèi)部視圖如v$、$session_wait等

SQL編寫(xiě)技巧

SQL編寫(xiě)有以下幾個(gè)通用的技巧：

• 合理使用索引

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

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

• 使用UNION ALL替代UNION

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

• 避免select * 寫(xiě)法

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

• JOIN字段建議建立索引

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

• 避免復(fù)雜SQL語(yǔ)句

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

• 避免where 1=1寫(xiě)法

• 避免order by rand()類(lèi)似寫(xiě)法

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

SQL優(yōu)化

執(zhí)行計(jì)劃

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

接下來(lái)我們用一段實(shí)際優(yōu)化案例來(lái)說(shuō)明SQL優(yōu)化的過(guò)程及優(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)，查詢(xún)當(dāng)前用戶(hù)在當(dāng)前時(shí)間前后10個(gè)小時(shí)的訂單情況，并根據(jù)訂單創(chuàng)建時(shí)間升序排列，具體SQL如下

select a.seller_id,       a.seller_name,       b.user_name,       c.statefrom a,     b,     cwhere 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í)行時(shí)間

• 原執(zhí)行計(jì)劃

• 初步優(yōu)化思路

1. SQL中 where條件字段類(lèi)型要跟表結(jié)構(gòu)一致，表中user_id 為varchar(50)類(lèi)型，實(shí)際SQL用的int類(lèi)型，存在隱式轉(zhuǎn)換，也未添加索引。將b和c表user_id 字段改成int類(lèi)型。
2. 因存在b表和c表關(guān)聯(lián)，將b和c表user_id創(chuàng)建索引
3. 因存在a表和b表關(guān)聯(lián)，將a和b表seller_name字段創(chuàng)建索引
4. 利用復(fù)合索引消除臨時(shí)表和排序

初步優(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í)行時(shí)間

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

查看warnings信息

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

查看執(zhí)行時(shí)間

查看執(zhí)行計(jì)劃

總結(jié)

1. 查看執(zhí)行計(jì)劃 explain
2. 如果有告警信息，查看告警信息 show warnings;
3. 查看SQL涉及的表結(jié)構(gòu)和索引信息
4. 根據(jù)執(zhí)行計(jì)劃，思考可能的優(yōu)化點(diǎn)
5. 按照可能的優(yōu)化點(diǎn)執(zhí)行表結(jié)構(gòu)變更、增加索引、SQL改寫(xiě)等操作
6. 查看優(yōu)化后的執(zhí)行時(shí)間和執(zhí)行計(jì)劃
7. 如果優(yōu)化效果不明顯，重復(fù)第四步操作