快捷導(dǎo)航

Mysql常見(jiàn)的慢查詢(xún)優(yōu)化方式總結(jié)

更新時(shí)間：2023年05月04日 09:22:01 作者：夏至&未至

優(yōu)化是一項(xiàng)復(fù)雜的任務(wù),因?yàn)樗罱K需要對(duì)整個(gè)系統(tǒng)的理解,下面這篇文章主要給大家總結(jié)介紹了關(guān)于Mysql常見(jiàn)的慢查詢(xún)優(yōu)化方式,文中介紹的非常詳細(xì),需要的朋友可以參考下

前言

這篇文章主要是就在公司實(shí)習(xí)的時(shí)候，對(duì)SQL優(yōu)化工作作出的一些整理。

在公司實(shí)習(xí)的時(shí)候，導(dǎo)師分配了SQL慢查詢(xún)優(yōu)化的任務(wù)，任務(wù)是這樣的：每周從平臺(tái)中導(dǎo)出生產(chǎn)數(shù)據(jù)庫(kù)的慢查詢(xún)文件進(jìn)行分析。進(jìn)行SQL優(yōu)化的手段也主要是修改SQL寫(xiě)法，或者新增索引。

現(xiàn)在從記錄項(xiàng)目中的一點(diǎn)點(diǎn)做起。

（1）數(shù)據(jù)庫(kù)中設(shè)置SQL慢查詢(xún)

一、第一步.開(kāi)啟mysql慢查詢(xún)

方式一：

修改配置文件在 my.ini 增加幾行: 主要是慢查詢(xún)的定義時(shí)間（超過(guò)2秒就是慢查詢(xún)），以及慢查詢(xún)log日志記錄（ slow_query_log）

方法二：通過(guò)MySQL數(shù)據(jù)庫(kù)開(kāi)啟慢查詢(xún):

（2）分析慢查詢(xún)?nèi)罩?nbsp;

直接分析mysql慢查詢(xún)?nèi)罩?,利用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢(xún)語(yǔ)句，來(lái)分析sql慢查詢(xún)語(yǔ)句

例如：執(zhí)行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000

得到如下結(jié)果：顯示結(jié)果分析：

table | type | possible_keys | key |key_len | ref | rows | Extra EXPLAIN列的解釋?zhuān)?nbsp;

table 顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
type 這是重要的列，顯示連接使用了何種類(lèi)型。從最好到最差的連接類(lèi)型為const、eq_reg、ref、range、indexhe和ALL
rows 顯示需要掃描行數(shù)
key 使用的索引

（3）常見(jiàn)的慢查詢(xún)優(yōu)化

（1）索引沒(méi)起作用的情況

1. 使用LIKE關(guān)鍵字的查詢(xún)語(yǔ)句

在使用LIKE關(guān)鍵字進(jìn)行查詢(xún)的查詢(xún)語(yǔ)句中，如果匹配字符串的第一個(gè)字符為“%”，索引不會(huì)起作用。只有“%”不在第一個(gè)位置索引才會(huì)起作用。

2. 使用多列索引的查詢(xún)語(yǔ)句

MySQL可以為多個(gè)字段創(chuàng)建索引。一個(gè)索引最多可以包括16個(gè)字段。對(duì)于多列索引，只有查詢(xún)條件使用了這些字段中的第一個(gè)字段時(shí)，索引才會(huì)被使用。

（2）優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)

合理的數(shù)據(jù)庫(kù)結(jié)構(gòu)不僅可以使數(shù)據(jù)庫(kù)占用更小的磁盤(pán)空間，而且能夠使查詢(xún)速度更快。數(shù)據(jù)庫(kù)結(jié)構(gòu)的設(shè)計(jì)，需要考慮數(shù)據(jù)冗余、查詢(xún)和更新的速度、字段的數(shù)據(jù)類(lèi)型是否合理等多方面的內(nèi)容。

1. 將字段很多的表分解成多個(gè)表

對(duì)于字段比較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來(lái)形成新表。因?yàn)楫?dāng)一個(gè)表的數(shù)據(jù)量很大時(shí)，會(huì)由于使用頻率低的字段的存在而變慢。

2. 增加中間表

對(duì)于需要經(jīng)常聯(lián)合查詢(xún)的表，可以建立中間表以提高查詢(xún)效率。通過(guò)建立中間表，把需要經(jīng)常聯(lián)合查詢(xún)的數(shù)據(jù)插入到中間表中，然后將原來(lái)的聯(lián)合查詢(xún)改為對(duì)中間表的查詢(xún)，以此來(lái)提高查詢(xún)效率。

（3）分解關(guān)聯(lián)查詢(xún)

將一個(gè)大的查詢(xún)分解為多個(gè)小查詢(xún)是很有必要的。

很多高性能的應(yīng)用都會(huì)對(duì)關(guān)聯(lián)查詢(xún)進(jìn)行分解，就是可以對(duì)每一個(gè)表進(jìn)行一次單表查詢(xún)，然后將查詢(xún)結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)，很多場(chǎng)景下這樣會(huì)更高效，例如：

 SELECT * FROM tag 
        JOIN tag_post ON tag_id = tag.id
        JOIN post ON tag_post.post_id = post.id
        WHERE tag.tag = 'mysql';
        分解為：
        SELECT * FROM tag WHERE tag = 'mysql';
        SELECT * FROM tag_post WHERE tag_id = 1234;
        SELECT * FROM post WHERE post.id in (123,456,567);

（4）優(yōu)化LIMIT分頁(yè)

在系統(tǒng)中需要分頁(yè)的操作通常會(huì)使用limit加上偏移量的方法實(shí)現(xiàn)，同時(shí)加上合適的order by 子句。如果有對(duì)應(yīng)的索引，通常效率會(huì)不錯(cuò)，否則MySQL需要做大量的文件排序操作。

一個(gè)非常令人頭疼問(wèn)題就是當(dāng)偏移量非常大的時(shí)候，例如可能是limit 10000,20這樣的查詢(xún)，這是mysql需要查詢(xún)10020條然后只返回最后20條，前面的10000條記錄都將被舍棄，這樣的代價(jià)很高。

優(yōu)化此類(lèi)查詢(xún)的一個(gè)最簡(jiǎn)單的方法是盡可能的使用索引覆蓋掃描，而不是查詢(xún)所有的列。然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列。對(duì)于偏移量很大的時(shí)候這樣做的效率會(huì)得到很大提升。

對(duì)于下面的查詢(xún)：

select id,title from collect limit 90000,10;

該語(yǔ)句存在的最大問(wèn)題在于limit M,N中偏移量M太大（我們暫不考慮篩選字段上要不要添加索引的影響），導(dǎo)致每次查詢(xún)都要先從整個(gè)表中找到滿(mǎn)足條件的前M條記錄，之后舍棄這M條記錄并從第M+1條記錄開(kāi)始再依次找到N條滿(mǎn)足條件的記錄。如果表非常大，且篩選字段沒(méi)有合適的索引，且M特別大那么這樣的代價(jià)是非常高的。試想，如我們下一次的查詢(xún)能從前一次查詢(xún)結(jié)束后標(biāo)記的位置開(kāi)始查找，找到滿(mǎn)足條件的100條記錄，并記下下一次查詢(xún)應(yīng)該開(kāi)始的位置，以便于下一次查詢(xún)能直接從該位置開(kāi)始，這樣就不必每次查詢(xún)都先從整個(gè)表中先找到滿(mǎn)足條件的前M條記錄，舍棄，在從M+1開(kāi)始再找到100條滿(mǎn)足條件的記錄了。

方法一：慮篩選字段（title）上加索引

title字段加索引（此效率如何未加驗(yàn)證）

方法二：先查詢(xún)出主鍵id值

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查詢(xún)出90000條數(shù)據(jù)對(duì)應(yīng)的主鍵id的值，然后直接通過(guò)該id的值直接查詢(xún)?cè)搃d后面的數(shù)據(jù)。

方法三：“關(guān)延遲聯(lián)”

如果這個(gè)表非常大，那么這個(gè)查詢(xún)可以改寫(xiě)成如下的方式：

Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

這里的“關(guān)延遲聯(lián)”將大大提升查詢(xún)的效率，它讓MySQL掃描盡可能少的頁(yè)面，獲取需要的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢(xún)需要的所有列。這個(gè)技術(shù)也可以用在優(yōu)化關(guān)聯(lián)查詢(xún)中的limit。

方法四：建立復(fù)合索引 acct_id和create_time

select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

注意sql查詢(xún)慢的原因都是:引起filesort

（5）分析具體的SQL語(yǔ)句 1、兩個(gè)表選哪個(gè)為驅(qū)動(dòng)表，表面是可以以數(shù)據(jù)量的大小作為依據(jù)，但是實(shí)際經(jīng)驗(yàn)最好交給mysql查詢(xún)優(yōu)化器自己去判斷。例如： select * from a where id in (select id from b );

對(duì)于這條sql語(yǔ)句它的執(zhí)行計(jì)劃其實(shí)并不是先查詢(xún)出b表的所有id,然后再與a表的id進(jìn)行比較。
mysql會(huì)把in子查詢(xún)轉(zhuǎn)換成exists相關(guān)子查詢(xún)，所以它實(shí)際等同于這條sql語(yǔ)句：select * from a where exists(select * from b where b.id=a.id );

而exists相關(guān)子查詢(xún)的執(zhí)行原理是: 循環(huán)取出a表的每一條記錄與b表進(jìn)行比較，比較的條件是a.id=b.id . 看a表的每條記錄的id是否在b表存在，如果存在就行返回a表的這條記錄。

exists查詢(xún)有什么弊端？

由exists執(zhí)行原理可知，a表(外表)使用不了索引，必須全表掃描，因?yàn)槭悄胊表的數(shù)據(jù)到b表查。而且必須得使用a表的數(shù)據(jù)到b表中查（外表到里表中），順序是固定死的。

如何優(yōu)化？

建索引。但是由上面分析可知，要建索引只能在b表的id字段建，不能在a表的id上，mysql利用不上。

這樣優(yōu)化夠了嗎？還差一些。

由于exists查詢(xún)它的執(zhí)行計(jì)劃只能拿著a表的數(shù)據(jù)到b表查（外表到里表中），雖然可以在b表的id字段建索引來(lái)提高查詢(xún)效率。
但是并不能反過(guò)來(lái)拿著b表的數(shù)據(jù)到a表查，exists子查詢(xún)的查詢(xún)順序是固定死的。

為什么要反過(guò)來(lái)？

因?yàn)槭紫瓤梢钥隙ǖ氖欠催^(guò)來(lái)的結(jié)果也是一樣的。這樣就又引出了一個(gè)更細(xì)致的疑問(wèn)：在雙方兩個(gè)表的id字段上都建有索引時(shí)，到底是a表查b表的效率高，還是b表查a表的效率高？

該如何進(jìn)一步優(yōu)化？

把查詢(xún)修改成inner join連接查詢(xún)：select * from a inner join b on a.id=b.id; （但是僅此還不夠，接著往下看）

為什么不用left join 和 right join？

這時(shí)候表之間的連接的順序就被固定住了，比如左連接就是必須先查左表全表掃描，然后一條一條的到另外表去查詢(xún)，右連接同理。仍然不是最好的選擇。

為什么使用inner join就可以？

inner join中的兩張表，如： a inner join b，但實(shí)際執(zhí)行的順序是跟寫(xiě)法的順序沒(méi)有半毛錢(qián)關(guān)系的，最終執(zhí)行也可能會(huì)是b連接a，順序不是固定死的。如果on條件字段有索引的情況下，同樣可以使用上索引。

那我們又怎么能知道a和b什么樣的執(zhí)行順序效率更高？

你不知道，我也不知道。誰(shuí)知道？mysql自己知道。讓mysql自己去判斷（查詢(xún)優(yōu)化器）。具體表的連接順序和使用索引情況，mysql查詢(xún)優(yōu)化器會(huì)對(duì)每種情況做出成本評(píng)估，最終選擇最優(yōu)的那個(gè)做為執(zhí)行計(jì)劃。

在inner join的連接中,mysql會(huì)自己評(píng)估使用a表查b表的效率高還是b表查a表高，如果兩個(gè)表都建有索引的情況下，mysql同樣會(huì)評(píng)估使用a表?xiàng)l件字段上的索引效率高還是b表的。

利用explain字段查看執(zhí)行時(shí)運(yùn)用到的key（索引）

而我們要做的就是：把兩個(gè)表的連接條件的兩個(gè)字段都各自建立上索引，然后explain 一下，查看執(zhí)行計(jì)劃，看mysql到底利用了哪個(gè)索引，最后再把沒(méi)有使用索引的表的字段索引給去掉就行了。