這篇文章將為大家詳細(xì)講解有關(guān)MySQL中的SQL語(yǔ)句怎么優(yōu)化，文章內(nèi)容質(zhì)量較高，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

創(chuàng)新互聯(lián)公司是一家專業(yè)提供曲江企業(yè)網(wǎng)站建設(shè),專注與成都做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)、HTML5建站、小程序制作等業(yè)務(wù)。10年已為曲江眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站設(shè)計(jì)公司優(yōu)惠進(jìn)行中。

結(jié)構(gòu)化查詢語(yǔ)言(Structured Query Language)簡(jiǎn)稱SQL，結(jié)構(gòu)化查詢語(yǔ)言是一種數(shù)據(jù)庫(kù)查詢和程序設(shè)計(jì)語(yǔ)言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫(kù)系統(tǒng);sql 語(yǔ)句就是對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作的一種語(yǔ)言。

判斷問(wèn)題SQL

新手優(yōu)化sql語(yǔ)句，可以直接調(diào)試，最直接明顯的辦法就是看執(zhí)行時(shí)間，哪一段最慢，就來(lái)進(jìn)行優(yōu)化

判斷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ì)劃告訴我們本次查詢走了全表掃描Type=ALL，rows很大(9950400)基本可以判斷這是一段"有味道"的SQL。

獲取問(wèn)題SQL

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

• MySQL
  • 慢查詢?nèi)罩?/li>
  • 測(cè)試工具loadrunner
  • Percona公司的ptquery等工具
• Oracle
  • AWR報(bào)告
  • 測(cè)試工具loadrunner等
  • 相關(guān)內(nèi)部視圖如v$sql、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$sql、v$session_wait等

SQL編寫技巧

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

? 合理使用索引

索引少了查詢慢；索引多了占用空間大，執(zhí)行增刪改語(yǔ)句的時(shí)候需要?jiǎng)討B(tài)維護(hù)索引，影響性能
選擇率高（重復(fù)值少）且被where頻繁引用需要建立B樹(shù)索引；一般join列需要建立索引；復(fù)雜文檔類型查詢采用全文索引效率更好；索引的建立要在查詢和DML性能之間取得平衡；復(fù)合索引創(chuàng)建時(shí)要注意基于非前導(dǎo)列查詢的情況

? 使用UNION ALL替代UNION

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

? 避免select * 寫法

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

? JOIN字段建議建立索引

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

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

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

? 避免where 1=1寫法

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

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ì)劃都不一樣，需要自行了解）

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

接下來(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)，查詢當(dāng)前用戶在當(dāng)前時(shí)間前后10個(gè)小時(shí)的訂單情況，并根據(jù)訂單創(chuàng)建時(shí)間升序排列，具體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í)行時(shí)間

  

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

  

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

• SQL中 where條件字段類型要跟表結(jié)構(gòu)一致，表中user_id 為varchar(50)類型，實(shí)際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ù)合索引消除臨時(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ì)劃

優(yōu)化總結(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改寫等操作
6. 查看優(yōu)化后的執(zhí)行時(shí)間和執(zhí)行計(jì)劃

如果優(yōu)化效果不明顯，重復(fù)第四步操作

以上就是MySQL中的SQL語(yǔ)句優(yōu)化方法，看完之后是否有所收獲呢？如果想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊，感謝各位的閱讀。

當(dāng)前文章：MySQL中的SQL語(yǔ)句怎么優(yōu)化
文章位置：http://vcdvsql.cn/article18/podsgp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供軟件開(kāi)發(fā)、響應(yīng)式網(wǎng)站、營(yíng)銷型網(wǎng)站建設(shè)、品牌網(wǎng)站制作、企業(yè)網(wǎng)站制作、域名注冊(cè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)