問(wèn)：慢 SQL 語(yǔ)句的幾種常見(jiàn)誘因?

在這個(gè)時(shí)候，就可以說(shuō)，面試官，您好，我覺(jué)得SQL慢出現(xiàn)的原因可以分為以下幾種，切記，不要回答自己模糊不清的，沒(méi)法自圓其說(shuō)的即使知道你也別回答，不然就是給自己挖坑。。。

無(wú)索引、索引失效導(dǎo)致慢查詢

如果表很大，而對(duì)where或者order by 后面字段沒(méi)有建立索引，那這種情況查起來(lái)肯定很費(fèi)力。但是有時(shí)候建立了索引，但在一些特定的場(chǎng)景下，索引還有可能會(huì)失效，所以索引失效也是導(dǎo)致慢查詢的主要原因之一。失效場(chǎng)景很多，比如模糊查詢白分號(hào)在前、查詢的字段使用了函數(shù)或者做了計(jì)算操作都有可能導(dǎo)致索引失效。

鎖等待

InnoDB 存儲(chǔ)引擎支持行鎖和表鎖，我們要注意行鎖升級(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í)為表鎖。除了鎖升級(jí)之外，行鎖相對(duì)表鎖來(lái)說(shuō)，雖然粒度更細(xì)，并發(fā)能力提升了，但也帶來(lái)了新的問(wèn)題，那就是死鎖。

不恰當(dāng)?shù)?SQL 語(yǔ)句

使用不恰當(dāng)?shù)?SQL 語(yǔ)句也是慢 SQL 最常見(jiàn)的誘因之一。例如，習(xí)慣使用 SQL 語(yǔ)句，在大數(shù)據(jù)表中使用 分頁(yè)查詢，以及對(duì)非索引字段進(jìn)行排序等等。對(duì)于一些慢的SQL，我該怎么分析?

上面我說(shuō)了一些關(guān)于SQL執(zhí)行慢的原因，下面就該看看怎么分析這些SQL了

找出慢SQL

開(kāi)啟慢查詢，慢查詢是分析SQL執(zhí)行慢的常見(jiàn)手段，主要有這幾個(gè)參數(shù)，slow_query_log：慢查詢開(kāi)啟狀態(tài)，slow_query_log_file：慢查詢?nèi)罩敬娣诺奈恢?這個(gè)目錄需要MySQL的運(yùn)行帳號(hào)的可寫(xiě)權(quán)限，一般設(shè)置為MySQL的數(shù)據(jù)存放目錄)，long_query_time：查詢超過(guò)多少秒才記錄。

通過(guò) EXPLAIN 分析 SQL 執(zhí)行計(jì)劃

通過(guò) Show Profile 分析 SQL 執(zhí)行性能

問(wèn)：平時(shí)寫(xiě)SQL時(shí)該注意什么?有什么經(jīng)驗(yàn)可談?

到這里，如果面試官還在聽(tīng)你回答，這時(shí)候，你可以適當(dāng)提高一點(diǎn)語(yǔ)氣，說(shuō)對(duì)于一些慢SQL，有很大一部分是由于我們開(kāi)發(fā)者寫(xiě)SQL時(shí)不注意SQL優(yōu)化導(dǎo)致的，那么我就說(shuō)一下我知道的一些注意事項(xiàng)。。。(提高聲音分貝，以防面試官睡著，并敲重點(diǎn)，告訴面試官，我準(zhǔn)備裝逼了，哈哈哈。。。)

統(tǒng)計(jì)時(shí)盡量使用count(*)，count(*)≈count(1)，大于count(主鍵)。

如果明確查找一條語(yǔ)句，請(qǐng)使用limit 1;，因?yàn)檎业揭粭l符合條件的記錄后就不會(huì)繼續(xù)查找了。

優(yōu)化分頁(yè)查詢

避免 Select *

用到什么字段就具體寫(xiě)什么字段，原因除了 select * 查詢所有字段會(huì)多出網(wǎng)絡(luò)傳輸開(kāi)銷之外，還有更重要的一點(diǎn)是，select * 無(wú)法使用覆蓋索引。

盡量使用 MySQL 5.6以后的版本

對(duì)于使用索引方面對(duì)索引字段做函數(shù)操作或者做運(yùn)算操作，都不能使用上索引。

所以針對(duì)這一點(diǎn)，除了我們索引的字段不要加函數(shù)之外。還要注意一些隱式轉(zhuǎn)換，比如，交易日志表(tradelog)，tradeid 的字段類型是 varchar(32)，字段有索引，但是當(dāng)你執(zhí)行 select * from tradelog where tradeid=110717;語(yǔ)句，你發(fā)現(xiàn)走的還是全索引掃描。這是因?yàn)樗鋵?shí)做了類型轉(zhuǎn)換，相當(dāng)于這么執(zhí)行mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;。隱式字符編碼轉(zhuǎn)換

如果兩張表使用的編碼集不一致，比如一個(gè)是utf8mb4，一個(gè)是utf8，那么這兩個(gè)字段連接后，相當(dāng)于做了一次CONVERT(traideid USING utf8mb4),這樣也用不了索引。其實(shí)字符集不同只是條件之一，連接過(guò)程中要求在被驅(qū)動(dòng)表的索引字段上加函數(shù)操作，是直接導(dǎo)致對(duì)被驅(qū)動(dòng)表做全表掃描的原因。

問(wèn)：1. 什么是SQL查詢優(yōu)化?

答: 查詢優(yōu)化是一種以某種方式編寫(xiě)查詢以便可以快速執(zhí)行的過(guò)程。對(duì)于任何標(biāo)準(zhǔn)應(yīng)用而言，這都是重要的一步。

問(wèn)：有哪些技巧可以提高SQL查詢的性能?

答: 優(yōu)化SQL查詢可以對(duì)性?能帶來(lái)實(shí)質(zhì)性的積極影響。它還取決于您所擁有的RDBMS知識(shí)水平。現(xiàn)在讓我們看一下調(diào)整SQL查詢的一些技巧。

1.盡量不要編寫(xiě)冗長(zhǎng)的查詢，使用視圖和存儲(chǔ)過(guò)程，它有助于最大程度地減少網(wǎng)絡(luò)負(fù)載。

2.最好引入約束而不是觸發(fā)器。它們比觸發(fā)器更有效，并且可以提高性能。

3.使用表級(jí)變量而不是臨時(shí)表。

4. UNION ALL子句的響應(yīng)速度比UNION快。它不會(huì)查找重復(fù)的行，而UNION語(yǔ)句會(huì)查找重復(fù)的行，無(wú)論它們是否存在。

5.防止使用DISTINCT和HAVING子句。

6.避免過(guò)多使用SQL游標(biāo)。

7.構(gòu)建存儲(chǔ)過(guò)程時(shí)，請(qǐng)使用SET NOCOUNT ON子句。它表示受T-SQL語(yǔ)句影響的行。這將導(dǎo)致網(wǎng)絡(luò)流量減少。

8.優(yōu)良作法是返回所需的列而不是表的所有列。

9.最好不要使用復(fù)雜的Join，并避免不當(dāng)?shù)厥褂糜|發(fā)器。

10.創(chuàng)建表索引并遵守標(biāo)準(zhǔn)

問(wèn)：有哪些影響數(shù)據(jù)庫(kù)性能的瓶頸?

答: 在Web應(yīng)用程序中，數(shù)據(jù)庫(kù)層可能被證明是實(shí)現(xiàn)最后一英里可擴(kuò)展性的關(guān)鍵瓶頸。如果數(shù)據(jù)庫(kù)存在性能泄漏，則可能成為瓶頸，并可能導(dǎo)致問(wèn)題。一些常見(jiàn)的性能問(wèn)題如下。

1. CPU使用率異常是最明顯的性能瓶頸。但是，您可以通過(guò)擴(kuò)容CPU或替換為高級(jí)CPU來(lái)修復(fù)它。它可能看起來(lái)像是一個(gè)簡(jiǎn)單的問(wèn)題，但異常的CPU使用率可能導(dǎo)致其他問(wèn)題。

2.內(nèi)存不足是下一個(gè)最常見(jiàn)的瓶頸。如果服務(wù)器無(wú)法管理峰值負(fù)載，那么它將對(duì)性能造成很大的問(wèn)號(hào)。對(duì)于任何應(yīng)用程序來(lái)說(shuō)，執(zhí)行內(nèi)存都是至關(guān)重要的，因?yàn)樗瘸志眯詢?nèi)存要快。同樣，當(dāng)RAM下降到特定閾值時(shí)，操作系統(tǒng)將轉(zhuǎn)向使用交換內(nèi)存。但這會(huì)使應(yīng)用程序運(yùn)行非常緩慢。

您可以通過(guò)擴(kuò)容RAM來(lái)解決它，但是如果有內(nèi)存泄漏，它將無(wú)法解決。在這種情況下，您需要分析應(yīng)用程序，以識(shí)別其代碼中的潛在泄漏。

3.對(duì)外部存儲(chǔ)設(shè)備(如SATA磁盤(pán))的過(guò)度依賴也可能成為瓶頸。在將大量數(shù)據(jù)寫(xiě)入磁盤(pán)時(shí)，其影響顯而易見(jiàn)。如果輸出操作非常慢，則很明顯表明問(wèn)題已成為瓶頸。

在這種情況下，您需要進(jìn)行擴(kuò)容，用更快的驅(qū)動(dòng)器替換現(xiàn)有驅(qū)動(dòng)器。嘗試升級(jí)到SSD硬盤(pán)驅(qū)動(dòng)器或類似產(chǎn)品。

問(wèn)：改善SQL性能涉及哪些步驟?

答：

發(fā)現(xiàn)–首先，找出需要改進(jìn)的地方。探索諸如探查器，查詢執(zhí)行計(jì)劃，SQL調(diào)優(yōu)顧問(wèn)，動(dòng)態(tài)視圖和自定義存儲(chǔ)過(guò)程之類的工具。

審核–集中討論可用數(shù)據(jù)以隔離主要問(wèn)題。

提議–這是一種可以提高性能的標(biāo)準(zhǔn)方法。但是，您可以對(duì)其進(jìn)行進(jìn)一步自定義以最大程度地受益。

1.識(shí)別字段并創(chuàng)建索引。

2.修改大型查詢以使用創(chuàng)建的索引。

3.刷新表和視圖并更新統(tǒng)計(jì)信息。

4.重置現(xiàn)有索引并刪除未使用的索引。

5.查找并清除死塊。

驗(yàn)證–測(cè)試SQL性能調(diào)整方法。定期監(jiān)視進(jìn)度。另外，跟蹤是否對(duì)應(yīng)用程序的其他部分有不利影響。

發(fā)布–現(xiàn)在，是時(shí)候與團(tuán)隊(duì)中的每個(gè)人共享工作解決方案了。讓他們知道所有最佳做法，以便他們可以輕松使用它。

問(wèn)：什么是執(zhí)行計(jì)劃(explain plan)?

答: 這是SQL中使用的術(shù)語(yǔ)，它顯示其優(yōu)化程序計(jì)劃用于執(zhí)行SELECT / UPDATE / INSERT / DELETE語(yǔ)句的執(zhí)行計(jì)劃。

以上就是“2023年精選出來(lái)的幾道sql語(yǔ)句優(yōu)化面試題”，你能回答上來(lái)嗎?如果想要了解更多的Java面試題相關(guān)內(nèi)容，可以關(guān)注動(dòng)力節(jié)點(diǎn)Java官網(wǎng)。