您好,登錄后才能下訂單哦!
本篇內(nèi)容主要講解“怎么精通SQL優(yōu)化”,感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷,實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“怎么精通SQL優(yōu)化”吧!
Explain有哪些信息
先確認(rèn)一下試驗(yàn)的MySQL版本,這里使用的是5.7.31版本。
只需要在SQL語(yǔ)句前加上explain關(guān)鍵字就可以查看執(zhí)行計(jì)劃,執(zhí)行計(jì)劃包括以下信息:id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra,總共12個(gè)字段信息。
然后創(chuàng)建三個(gè)表:
CREATE TABLE `tb_student` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(36) NOT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='學(xué)生表'; CREATE TABLE `tb_class` ( `id` INT(10) primary key not null auto_increment, `name` VARCHAR(36) NOT NULL, `stu_id` INT(10) NOT NULL, `tea_id` INT(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班級(jí)表'; CREATE TABLE `tb_teacher` ( `id` INT(10) primary key not null auto_increment, `name` VARCHAR(36) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教師表';
Explain執(zhí)行計(jì)劃詳解
explain的使用很簡(jiǎn)單,只需要在SQL語(yǔ)句前加上關(guān)鍵字explain即可,關(guān)鍵是怎么看explain執(zhí)行后返回的字段信息,這才是重點(diǎn)。
一、id
SELECT識(shí)別符。這是SELECT的查詢序列號(hào)。SQL執(zhí)行的順序的標(biāo)識(shí),SQL從大到小的執(zhí)行。id列有以下幾個(gè)注意點(diǎn):
id相同時(shí),執(zhí)行順序由上至下。
id不同時(shí),如果是子查詢,id的序號(hào)會(huì)遞增,id值越大優(yōu)先級(jí)越高,越先被執(zhí)行。
EXPLAIN SELECT * FROM `tb_student` WHERE id IN (SELECT stu_id FROM tb_class WHERE tea_id IN(SELECT id FROM tb_teacher WHERE `name` = '馬老師'));
根據(jù)原則,當(dāng)id不同時(shí),SQL從大到小執(zhí)行,id相同則從上到下執(zhí)行。
二、select_type
表示select查詢的類型,用于區(qū)分各種復(fù)雜的查詢,例如普通查詢,聯(lián)合查詢,子查詢等等。
SIMPLE
表示最簡(jiǎn)單的查詢操作,也就是查詢SQL語(yǔ)句中沒有子查詢、union等操作。
PRIMARY
當(dāng)查詢語(yǔ)句中包含復(fù)雜查詢的子部分,表示復(fù)雜查詢中最外層的 select。
SUBQUERY
當(dāng) select 或 where 中包含有子查詢,該子查詢被標(biāo)記為SUBQUERY。
DERIVED
在SQL語(yǔ)句中包含在from子句中的子查詢。
UNION
表示在union中的第二個(gè)和隨后的select語(yǔ)句。
UNION RESULT
代表從union的臨時(shí)表中讀取數(shù)據(jù)。
EXPLAIN SELECT u.`name` FROM ((SELECT s.id,s.`name` FROM `tb_student` s) UNION (SELECT t.id,t.`name` FROM tb_teacher t)) AS u;
代表是id為2和3的select查詢的結(jié)果進(jìn)行union操作。
MATERIALIZED
MATERIALIZED表示物化子查詢,子查詢來自視圖。
三、table
表示輸出結(jié)果集的表的表名,并不一定是真實(shí)存在的表,也有可能是別名,臨時(shí)表等等。
四、partitions
表示SQL語(yǔ)句查詢時(shí)匹配到的分區(qū)信息,對(duì)于非分區(qū)表值為NULL,當(dāng)查詢的是分區(qū)表則會(huì)顯示分區(qū)表命中的分區(qū)情況。
五、type
需要重點(diǎn)關(guān)注的一個(gè)字段信息,表示查詢使用了哪種類型,在 SQL優(yōu)化中是一個(gè)非常重要的指標(biāo),依次從優(yōu)到差分別是:system > const > eq_ref > ref > range > index > ALL。
system和const
單表中最多有一條匹配行,查詢效率最高,所以這個(gè)匹配行的其他列的值可以被優(yōu)化器在當(dāng)前查詢中當(dāng)作常量來處理。通常出現(xiàn)在根據(jù)主鍵或者唯一索引進(jìn)行的查詢,system是const的特例,表里只有一條元組匹配時(shí)(系統(tǒng)表)為system。
eq_ref
primary key 或 unique key 索引的所有部分被連接使用 ,最多只會(huì)返回一條符合條件的記錄,所以這種類型常出現(xiàn)在多表的join查詢。
ref
相比eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前綴,可能會(huì)找到多個(gè)符合條件的行。
range
使用索引選擇行,僅檢索給定范圍內(nèi)的行。一般來說是針對(duì)一個(gè)有索引的字段,給定范圍檢索數(shù)據(jù),通常出現(xiàn)在where語(yǔ)句中使用 bettween...and、<、>、<=、in 等條件查詢 。
index
掃描全表索引,通常比ALL要快一些。
ALL
全表掃描,MySQL遍歷全表來找到匹配行,性能最差。
六、possible_keys
表示在查詢中可能使用到的索引來查找,而列出的索引并不一定是最終查詢數(shù)據(jù)所用到的索引。
七、key
跟possible_keys有所區(qū)別,key表示查詢中實(shí)際使用到的索引,若沒有使用到索引則顯示為NULL。
八、key_len
表示查詢用到的索引key的長(zhǎng)度(字節(jié)數(shù))。如果單列索引,那么就會(huì)把整個(gè)索引長(zhǎng)度計(jì)算進(jìn)去,如果是聯(lián)合索引,不是所有的列都用到,那么就只計(jì)算實(shí)際用到的列,因此可以根據(jù)key_len來判斷聯(lián)合索引是否生效。
九、ref
顯示了哪些列或常量被用于查找索引列上的值。常見的值有:const,func,null,字段名。
十、rows
mysql估算要找到我們所需的記錄,需要讀取的行數(shù)??梢酝ㄟ^這個(gè)數(shù)據(jù)很直觀的顯示 SQL 性能的好壞,一般情況下 rows 值越小越好。
十一、filtered
指返回結(jié)果的行占需要讀到的行(rows列的值)的百分比,一般來說越大越好。
十二、Extra
表示額外的信息。此字段能夠給出讓我們深入理解執(zhí)行計(jì)劃進(jìn)一步的細(xì)節(jié)信息。
Using index
說明在select查詢中使用了覆蓋索引。覆蓋索引的好處是一條SQL通過索引就可以返回我們需要的數(shù)據(jù)。
Using where
查詢時(shí)沒使用到索引,然后通過where條件過濾獲取到所需的數(shù)據(jù)。
Using temporary
表示在查詢時(shí),MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來保存結(jié)果。臨時(shí)表一般會(huì)比較影響性能,應(yīng)該盡量避免。
有時(shí)候使用DISTINCT去重時(shí)也會(huì)產(chǎn)生Using temporary。
Using filesort
我們知道索引除了查詢中能起作用外,排序也是能起到作用的,所以當(dāng)SQL中包含 ORDER BY 操作,而且無法利用索引完成排序操作的時(shí)候,MySQL不得不選擇相應(yīng)的排序算法來實(shí)現(xiàn),這時(shí)就會(huì)出現(xiàn)Using filesort,應(yīng)該盡量避免使用Using filesort。
總結(jié)
一般優(yōu)化SQL語(yǔ)句第一步是要知道這條SQL語(yǔ)句有哪些需要優(yōu)化的,explain執(zhí)行計(jì)劃就相當(dāng)于一面鏡子,能把詳細(xì)的執(zhí)行情況給開發(fā)者列出來。所以說善用explain執(zhí)行計(jì)劃,能解決80%的SQL優(yōu)化問題。
explain的信息中,一般我們要關(guān)心的是type,看是什么級(jí)別,如果是在互聯(lián)網(wǎng)公司一般需要在range以上的級(jí)別,接著關(guān)心的是Extra,有沒有出現(xiàn)filesort或者using template,一旦出現(xiàn)就要想辦法避免,接著再看key使用的是什么索引,還有看filtered篩選比是多少。
到此,相信大家對(duì)“怎么精通SQL優(yōu)化”有了更深的了解,不妨來實(shí)際操作一番吧!這里是億速云網(wǎng)站,更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。