在面試中，SQL 調(diào)優(yōu)經(jīng)常是被問(wèn)及的問(wèn)題，它可以考察候選人對(duì)于 SQL 整體性能優(yōu)化的理解和掌握程度。一般來(lái)說(shuō)，SQL 調(diào)優(yōu)的步驟可以從以下幾個(gè)方面入手。

首先，需要準(zhǔn)確地定位問(wèn)題。在面試中，最好能結(jié)合具體的業(yè)務(wù)場(chǎng)景進(jìn)行說(shuō)明，例如某次線下報(bào)警引發(fā)的慢 SQL 問(wèn)題，或者性能分析顯示接口響應(yīng)時(shí)間過(guò)長(zhǎng)，根源是 SQL 查詢效率不佳。無(wú)論何種情況，都需要提供背景信息。

一旦問(wèn)題定位清楚，接下來(lái)就是對(duì)問(wèn)題進(jìn)行深入分析。

首先，需要通過(guò)各類監(jiān)控平臺(tái)或工具準(zhǔn)確定位到具體的 SQL 語(yǔ)句。一旦定位到了問(wèn)題 SQL 語(yǔ)句，我們就能夠確定是哪張表或哪個(gè) SQL 語(yǔ)句執(zhí)行速度較慢。

接下來(lái)，需要進(jìn)行詳細(xì)的分析。一般而言，一個(gè) SQL 語(yǔ)句執(zhí)行緩慢可能有以下幾種原因：

索引未被有效利用

多表連接

查詢字段過(guò)多

數(shù)據(jù)量過(guò)大的表

索引的區(qū)分度不高

數(shù)據(jù)庫(kù)連接數(shù)不足

數(shù)據(jù)庫(kù)表結(jié)構(gòu)不合理

數(shù)據(jù)庫(kù)的 IO 或 CPU 負(fù)載過(guò)高

數(shù)據(jù)庫(kù)參數(shù)設(shè)置不合理

長(zhǎng)時(shí)間事務(wù)

鎖競(jìng)爭(zhēng)引起的等待

因此，進(jìn)行一次全面的 SQL 調(diào)優(yōu)時(shí)，通常需要考慮上述幾個(gè)因素，往往會(huì)涉及其中一個(gè)或多個(gè)問(wèn)題。接下來(lái)，需要逐一進(jìn)行優(yōu)化。

首先，處理索引失效的問(wèn)題通常要通過(guò)執(zhí)行計(jì)劃分析是否正確使用了索引，以及使用的索引是否符合預(yù)期。如果索引設(shè)計(jì)不合理或者因索引失效導(dǎo)致問(wèn)題，可以考慮調(diào)整索引設(shè)計(jì)，修改 SQL 語(yǔ)句，或者強(qiáng)制使用特定的索引。

其次，多表連接（join）也是導(dǎo)致 SQL 執(zhí)行速度較慢的常見原因之一。

接下來(lái)，如果是索引區(qū)分度不高的話，這個(gè)其實(shí)也和索引不合理有關(guān)，但是其實(shí)到底快不快，用不用索引，并不是因?yàn)閰^(qū)分度高不高導(dǎo)致，其實(shí)還是索引掃描的行數(shù)的成本導(dǎo)致。所以，有的時(shí)候不能認(rèn)為區(qū)分度不高就一定會(huì)效率低，或者一定就不適合創(chuàng)建索引。

查詢字段過(guò)多有時(shí)是因?yàn)檎`用了?SELECT *，通常情況下，查詢少于 100 個(gè)字段并不是大問(wèn)題，除非字段數(shù)目極多。解決方法有兩種：一是只查詢必要的字段，避免檢索不需要的數(shù)據(jù)；二是進(jìn)行垂直分表，將數(shù)據(jù)分散存儲(chǔ)到多張表中。然而，這種分散存儲(chǔ)也可能帶來(lái)需要多表連接的問(wèn)題，因此在進(jìn)行分表時(shí)需要考慮數(shù)據(jù)冗余的問(wèn)題。對(duì)于表中數(shù)據(jù)量過(guò)大的情況，一般而言，超過(guò) 1000 萬(wàn)條數(shù)據(jù)會(huì)顯著降低查詢效率，即使使用了索引也可能不夠快。因此，解決方法包括：

數(shù)據(jù)歸檔，將歷史數(shù)據(jù)移出，只保留近期數(shù)據(jù)，例如保留最近半年數(shù)據(jù)，將半年前的數(shù)據(jù)歸檔。

分庫(kù)分表或分區(qū)。通過(guò)拆分?jǐn)?shù)據(jù)來(lái)分散存儲(chǔ)，以減輕單表的壓力。具體的分庫(kù)分表和分區(qū)策略可以參考詳細(xì)文檔，這里不展開說(shuō)明。

考慮使用支持大數(shù)據(jù)量查詢的第三方數(shù)據(jù)庫(kù)，如 OceanBase、TiDB，或者搜索引擎如 Elasticsearch 等。

數(shù)據(jù)庫(kù)連接數(shù)不足也需要具體分析原因。可能原因包括：業(yè)務(wù)量過(guò)大，單個(gè)數(shù)據(jù)庫(kù)無(wú)法處理；存在慢 SQL 或長(zhǎng)事務(wù)導(dǎo)致連接阻塞，進(jìn)而影響其他查詢速度。

數(shù)據(jù)庫(kù)表結(jié)構(gòu)不合理通常是一個(gè)關(guān)鍵原因。例如，某些字段可能存儲(chǔ)了過(guò)長(zhǎng)的內(nèi)容，或者沒(méi)有進(jìn)行合理的數(shù)據(jù)冗余，導(dǎo)致需要頻繁進(jìn)行多表關(guān)聯(lián)查詢等情況。解決方法通常是進(jìn)行數(shù)據(jù)庫(kù)結(jié)構(gòu)重構(gòu)或者進(jìn)行表的分解。

數(shù)據(jù)庫(kù)的 IO 或 CPU 負(fù)載較高也是常見問(wèn)題。當(dāng)數(shù)據(jù)庫(kù)整體的 IO 或 CPU 負(fù)載升高時(shí)，查詢速度可能會(huì)受到影響。因此，需要深入分析其背后的原因，并采取相應(yīng)的解決策略。

存在長(zhǎng)事務(wù)和慢 SQL 類似，都會(huì)占用數(shù)據(jù)庫(kù)連接，從而導(dǎo)致其他請(qǐng)求需要等待。

鎖競(jìng)爭(zhēng)導(dǎo)致的等待則是在高并發(fā)情況下，多個(gè)請(qǐng)求競(jìng)爭(zhēng)共享資源，導(dǎo)致鎖定等待時(shí)間增長(zhǎng)，進(jìn)而使得 SQL 執(zhí)行變慢。這一過(guò)程也可以參考上述導(dǎo)致 CPU 負(fù)載過(guò)高的問(wèn)題。

數(shù)據(jù)庫(kù)參數(shù)設(shè)置不合理也是常見問(wèn)題，針對(duì)具體的業(yè)務(wù)場(chǎng)景進(jìn)行適當(dāng)?shù)膮?shù)調(diào)整，有時(shí)能顯著提升 SQL 的效率。例如調(diào)整內(nèi)存大小、緩存大小以及線程池大小等。

擴(kuò)展知識(shí)

參數(shù)優(yōu)化

假設(shè)我們管理的數(shù)據(jù)庫(kù)名為 mydb，其中包含一個(gè)名為 mytable 的 InnoDB 表。該表具有自增主鍵 id，一個(gè)整數(shù)類型的 age 字段和一個(gè)字符串類型的 name 字段。我們希望對(duì)這個(gè)表進(jìn)行優(yōu)化。

首先，可以通過(guò)執(zhí)行?SHOW VARIABLES LIKE 'innodb%';?命令來(lái)查看當(dāng)前 InnoDB 參數(shù)的設(shè)置情況。這些參數(shù)涵蓋了緩沖池大小、刷新間隔、日志大小等核心設(shè)置。

接下來(lái)，我們可以嘗試調(diào)整幾個(gè)關(guān)鍵參數(shù)來(lái)優(yōu)化數(shù)據(jù)庫(kù)的性能：

innodb_buffer_pool_size：緩沖池大小是 InnoDB 存儲(chǔ)引擎的關(guān)鍵參數(shù)之一，它決定了 InnoDB 存儲(chǔ)引擎在內(nèi)存中使用的大小。通常建議將該參數(shù)設(shè)置為系統(tǒng)可用內(nèi)存的 70% 到 80%。例如，如果系統(tǒng)總內(nèi)存為 8GB，我們可以將 innodb_buffer_pool_size 設(shè)置為 6GB。在 MySQL 中，可以使用以下命令進(jìn)行設(shè)置：

復(fù)制SET GLOBAL innodb_buffer_pool_size=6G;

1.

**innodb_read_io_threads?和?innodb_write_io_threads **這兩個(gè)參數(shù)控制著 InnoDB 存儲(chǔ)引擎的 I/O 線程數(shù)量。一般建議將它們?cè)O(shè)置為可用 CPU 核心數(shù)的一半。在 MySQL 中，您可以使用以下命令進(jìn)行設(shè)置：

復(fù)制SET GLOBAL innodb_read_io_threads=4; SET GLOBAL innodb_write_io_threads=4;

1.

2.

innodb_log_file_size?參數(shù)控制著事務(wù)日志文件的大小。默認(rèn)情況下，其大小為 5M，這通常是不足夠的。在 MySQL 中，您可以使用以下命令進(jìn)行設(shè)置：

復(fù)制SET GLOBAL innodb_log_file_size=1G;

1.

一般來(lái)說(shuō)，在設(shè)置這個(gè)參數(shù)之前，需要先進(jìn)行數(shù)據(jù)采樣?？梢杂^察業(yè)務(wù)高峰期約 2 小時(shí)內(nèi)寫入的日志量，然后將這個(gè)量作為設(shè)定事務(wù)日志文件大小的參考。通常建議設(shè)置為約 1G 左右，或者系統(tǒng)內(nèi)存的 1/4。

區(qū)分度不高的字段建索引一定沒(méi)用嗎

關(guān)于剛剛上面提到的區(qū)分度不高的字段。做一下解釋，這個(gè)區(qū)分度不高的字段建立索引到底有沒(méi)有用呢。

答案是：不一定。

在某些情況下，索引的有效性并不完全取決于字段的區(qū)分度。例如，如果一個(gè)表中包含性別字段，僅有兩個(gè)可能的取值：男和女，那么通常情況下這個(gè)字段的區(qū)分度較低，使用該字段進(jìn)行查詢可能無(wú)法有效地過(guò)濾大量數(shù)據(jù)，從而無(wú)法充分發(fā)揮索引的優(yōu)勢(shì)。

然而，也存在特殊情況。比如，如果性別的分布比例是 95%男性和 5%女性，那么當(dāng)以"女"作為性別查詢條件時(shí)，依然可以通過(guò)索引進(jìn)行高效查詢，因?yàn)樗軌蚩焖龠^(guò)濾掉大部分?jǐn)?shù)據(jù)，從而提升性能。這種情況下，索引仍然能夠顯著提升效率。

類似的情況在任務(wù)表中也很常見。例如，任務(wù)表中可能有一個(gè)狀態(tài)字段，大多數(shù)任務(wù)處于成功狀態(tài)（SUCCESS），只有少數(shù)任務(wù)處于初始化狀態(tài)（INIT）。在這種情況下，為狀態(tài)字段添加索引可以顯著提升查詢效率。這樣在掃描任務(wù)表并執(zhí)行任務(wù)時(shí)，可以更快地定位到需要處理的任務(wù)。

因此，雖然字段的區(qū)分度影響索引的效果，但在特定的數(shù)據(jù)分布情況下，即使區(qū)分度不高的字段仍然可以通過(guò)索引來(lái)優(yōu)化查詢性能。