導(dǎo)致sql執(zhí)行速度慢的幾種情況盤點(diǎn)(生產(chǎn)環(huán)境踩過(guò)的坑)
前言
當(dāng)我們遇到慢sql,第一反應(yīng)可能就是去優(yōu)化我們的sql語(yǔ)句。一些比較復(fù)雜的語(yǔ)句如果執(zhí)行慢可能還能理解,但是有時(shí)一些特別簡(jiǎn)單的查詢也會(huì)變得卡頓,“查一行”,也會(huì)執(zhí)行得特別慢。今天,我們盤點(diǎn)一下,都有哪些情況會(huì)導(dǎo)致sql執(zhí)行速度慢。
1,數(shù)據(jù)庫(kù)本身壓力較大
如果數(shù)據(jù)庫(kù)本身的性能壓力就比較大,資源比較緊張,CPU占用率或者IO利用率很高,這時(shí)會(huì)導(dǎo)致所有的語(yǔ)句執(zhí)行起來(lái)都比較慢。這種情況下首先要做的應(yīng)該是提升服務(wù)器的配置,然后觀察服務(wù)器的性能指標(biāo)是否平穩(wěn)。
2,表鎖沖突
如果遇到一個(gè)簡(jiǎn)單的查詢長(zhǎng)時(shí)間未返回結(jié)果,那么大概率是表被鎖住了。一般遇到這種情況,都是通過(guò)show processlist命令,查看sql語(yǔ)句的狀態(tài)。
如圖所示,id為8的語(yǔ)句正在等待一個(gè)MDL鎖,我們可以使用kill命令殺掉這個(gè)阻塞線程。
另外還可以通過(guò)表sys.schema_table_lock_waits查詢阻塞的線程id。
3,行鎖沖突
mysql> select * from temp where id =3 for update;
當(dāng)我們?cè)L問(wèn)id=3這條記錄時(shí),使用了for update,表示要對(duì)這條語(yǔ)句加鎖。但是如果此時(shí)已經(jīng)有另一個(gè)事務(wù)對(duì)這條記錄加了鎖,并且一直持有不釋放鎖,那么當(dāng)前語(yǔ)句就會(huì)一直阻塞。
通過(guò)上圖可以看出,第一個(gè)語(yǔ)句不提交事務(wù),第二個(gè)語(yǔ)句就一直處于等待阻塞狀態(tài)。
我們執(zhí)行show processlist,
可以看出確實(shí)有一個(gè)線程處于阻塞狀態(tài)。
鎖沖突會(huì)導(dǎo)致執(zhí)行效率降低,進(jìn)而影響到業(yè)務(wù),需要我們重點(diǎn)關(guān)注。
4,索引未命中
mysql> select * from t where c=50000 limit 1;
如果字段c上面沒(méi)有索引,那么就只能走主鍵id順序掃描,一直掃描到第50000行才能停下來(lái)。
給表數(shù)據(jù)加索引可以快速提升查詢性能,一般來(lái)說(shuō),因?yàn)樗饕?dǎo)致的慢sql可能是我們平常開發(fā)過(guò)程中經(jīng)常遇到的。
5,多表join查詢
有了索引,并不代表萬(wàn)事大吉。如果一個(gè)比較復(fù)雜的sql,需要關(guān)聯(lián)很多表進(jìn)行查詢,即使每張表的索引都可以起到作用,但是由于數(shù)據(jù)量過(guò)多,即使都命中索引,掃描的行數(shù)仍然是巨大的。這樣sql的執(zhí)行速度仍然會(huì)受到很大影響。
一般來(lái)說(shuō),超過(guò)3個(gè)表的join就應(yīng)該盡量避免,將其拆分為多個(gè)查詢,使用空間換時(shí)間也不失為一個(gè)好辦法。
補(bǔ)充知識(shí):慢 SQL 語(yǔ)句的幾種常見誘因
1. 無(wú)索引、索引失效導(dǎo)致
慢查詢?nèi)绻谝粡垘浊f(wàn)數(shù)據(jù)的表中以一個(gè)沒(méi)有索引的列作為查詢條件,大部分情況下查詢會(huì)非常耗時(shí),這種查詢毫無(wú)疑問(wèn)是一個(gè)慢 SQL 查詢。
所以對(duì)于大數(shù)據(jù)量的查詢,需要建立適合的索引來(lái)優(yōu)化查詢。雖然我們很多時(shí)候建立了索引,但在一些特定的場(chǎng)景下,索引還有可能會(huì)失效,所以索引失效也是導(dǎo)致慢查詢的主要原因之一。
2. 鎖等待
常用的存儲(chǔ)引擎有 InnoDB 和 MyISAM,前者支持行鎖和表鎖,后者只支持表鎖。
如果數(shù)據(jù)庫(kù)操作是基于表鎖實(shí)現(xiàn)的,試想下,如果一張數(shù)據(jù)表在更新時(shí),需要鎖住整張表,那么其它大量數(shù)據(jù)庫(kù)操作(包括查詢)都將處于等待狀態(tài),這將嚴(yán)重影響到系統(tǒng)的并發(fā)性能。這時(shí),InnoDB 存儲(chǔ)引擎支持的行鎖更適合高并發(fā)場(chǎng)景。
行鎖升級(jí)為表鎖的可能:
在批量更新操作時(shí),行鎖就很可能會(huì)升級(jí)為表鎖。
MySQL 認(rèn)為如果對(duì)一張表使用大量行鎖,會(huì)導(dǎo)致事務(wù)執(zhí)行效率下降,從而可能造成其它事務(wù)長(zhǎng)時(shí)間鎖等待和更多的鎖沖突問(wèn)題發(fā)生,致使性能嚴(yán)重下降,所以 MySQL 會(huì)將行鎖升級(jí)為表鎖。還有,行鎖是基于索引加的鎖,如果我們?cè)诟虏僮鲿r(shí),條件索引失效,那么行鎖也會(huì)升級(jí)為表鎖。
因此,基于表鎖的數(shù)據(jù)庫(kù)操作,會(huì)導(dǎo)致 SQL 阻塞等待,影響執(zhí)行速度。
行鎖相對(duì)表鎖來(lái)說(shuō),雖然粒度更細(xì),并發(fā)能力提升了,但也帶來(lái)了死鎖的問(wèn)題。因此,在使用行鎖時(shí),我們要注意避免死鎖。
3. 不恰當(dāng)?shù)?SQL 語(yǔ)句
使用不恰當(dāng)?shù)?SQL 語(yǔ)句也是慢 SQL 最常見的誘因之一 :
在大數(shù)據(jù)表中使用 分頁(yè)查詢,以及對(duì)非索引字段進(jìn)行排序等等。
總結(jié)
總的來(lái)說(shuō),sql執(zhí)行速度慢會(huì)受到很多種情況的影響,比如表鎖,行鎖。在實(shí)際的業(yè)務(wù)場(chǎng)景中也許會(huì)更復(fù)雜,但是處理起來(lái)的步驟基本大同小異,只要找到問(wèn)題的根源,知道如何解決,處理起來(lái)就可以做到心中有數(shù),游刃有余。
到此這篇關(guān)于導(dǎo)致sql執(zhí)行速度慢的幾種情況盤點(diǎn)的文章就介紹到這了,更多相關(guān)sql執(zhí)行速度慢的情況內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySql分表、分庫(kù)、分片和分區(qū)知識(shí)深入詳解
這篇文章主要介紹了MySql分表、分庫(kù)、分片和分區(qū)知識(shí)深入詳解,如果有并發(fā)場(chǎng)景和數(shù)據(jù)量較大的場(chǎng)景的可以看一下文章,對(duì)你會(huì)有或多或少的幫助2021-03-03RR與RC隔離級(jí)別下索引和鎖的測(cè)試腳本示例代碼
這篇文章主要給大家介紹了關(guān)于RR與RC隔離級(jí)別下索引和鎖的測(cè)試腳本的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2018-12-12MySQL中VARCHAR與CHAR格式數(shù)據(jù)的區(qū)別
char是一種固定長(zhǎng)度的類型,varchar則是一種可變長(zhǎng)度的類型,那么他們具體使用過(guò)程中有什么區(qū)別嗎2015-09-09mysql中影響數(shù)據(jù)庫(kù)性能的因素講解
在本篇文章中我們給大家講述了mysql中影響性能的因素以及相關(guān)知識(shí)點(diǎn)內(nèi)容,有興趣的朋友參考下。2018-09-09為mysql數(shù)據(jù)庫(kù)添加添加事務(wù)處理的方法
開始首先說(shuō)明一下,mysql數(shù)據(jù)庫(kù)默認(rèn)的數(shù)據(jù)庫(kù)引擎是MyISAM,是不支持事務(wù)的,單數(shù)如果你添加了數(shù)據(jù)執(zhí)行語(yǔ)句是不會(huì)出錯(cuò)的,單數(shù)不管用,即便是回滾事務(wù),記錄也是插入進(jìn)去了,所有首先我們要做的第一步是更改數(shù)據(jù)庫(kù)引擎2011-07-07