SQL 查詢性能的優(yōu)化，是面試中的高頻知識點，也是必知必會的技能。

SQL 查詢性能的好壞，直接影響程序性能和用戶體驗。特別是一些數(shù)據(jù)量大、復(fù)雜多樣的應(yīng)用場景中，對 SQL 查詢性能優(yōu)化就更加剛需。

本文主要介紹六個常用的 SQL 查詢性能優(yōu)化方法。

《阿里巴巴開發(fā)手冊》中指出：

【強制】在表查詢中，一律不要使用 * 作為查詢的字段列表，需要哪些字段必須明確寫明說明。

select * 的問題及影響

在 SQL 語句中，select * 是最常見的寫法之一，表示返回所有的字段。

當查詢的表中包含大量字段的時候，這種寫法既浪費帶寬和 I/O 資源，還會造成緩存和 CPU 的過度使用，嚴重影響 SQL 的查詢性能。

如何正確使用 select 語句

正確使用 select 語句的方法是僅選擇需要的字段，既能提升查詢效率，還能讓結(jié)果集更易于理解和處理。

例如：對于一張包含 100 個字段的表，如果只需要其中的 10 個字段，那么就應(yīng)該這樣寫：

select field1, field2, field3, field4, field5, field6, field7, field8, field9, field10 from table_name;

只返回需要的 10 個字段，極大地減少查詢的時間和資源消耗。

在 SQL 查詢中，一個表可能與其他多個表進行關(guān)聯(lián)，關(guān)聯(lián)的表之間可能存在大小的差異。

在關(guān)聯(lián)時，讓小表驅(qū)動大表就能提高查詢性能。這是因為小表的數(shù)據(jù)量較小，更容易被緩存，而大表的數(shù)據(jù)量很大，會占用大量的 I/O 資源，導(dǎo)致查詢變慢。

小表驅(qū)動大表的示例

具體來說，就是我們可以通過將大表作為外部表（right join/left join），從而實現(xiàn)小表驅(qū)動大表。這樣在查詢時就可以優(yōu)先使用小表的緩存，減少 I/O 開銷。

例如：

我們有兩張表 A 和 B，其中 A 表只有 100 行，而 B 表有 10000 行。

如果我們使用 A 表作為驅(qū)動表，那么我們只需要對 A 表進行 100 次查詢，就可以完成整個查詢過程，這比使用 B 表作為驅(qū)動表要快得多。

下面是一個使用小表驅(qū)動大表的示例：

select * from small_tableLEFT JOIN big_tableON small_table.id = big_table.id;

這個查詢語句中，small_table 是小表，big_table 是大表，通過 left join 關(guān)鍵字，我們讓 small_table 作為驅(qū)動表。

這樣，在查詢時，系統(tǒng)會優(yōu)先使用 small_table 的緩存，減少 I/O 開銷，提高查詢效率。

在 SQL 查詢中，join 語句是常用的語句之一，但過多的 join 關(guān)聯(lián)表會極大地影響 SQL 查詢的性能。

這是因為：每個 join 語句都需要執(zhí)行一次關(guān)聯(lián)操作，從而導(dǎo)致了 SQL 查詢的復(fù)雜度成倍增加，同時也降低了數(shù)據(jù)庫的響應(yīng)速度。

如何減少 join 的使用呢

• 可以使用子查詢來代替 join 語句，尤其是在需要關(guān)聯(lián)的數(shù)據(jù)量不大的情況下。

• 對于一些特定的需求，例如需要取出某個表的前 N 條數(shù)據(jù)，可以直接使用 limit 語句進行操作，而不必使用 join 語句。

• 可以使用數(shù)據(jù)庫中的視圖（View）來簡化 SQL 查詢中的 join 操作，通過將一些常用的 join 操作定義為視圖，從而可以方便地在其他查詢中使用。

join 關(guān)聯(lián)表過多的解決方法

舉個例子：

有 A 和 B 兩張表 ，需要通過 join 語句將它們關(guān)聯(lián)在一起。

但是，B 表中的數(shù)據(jù)過于龐大，如果直接使用 join 語句進行關(guān)聯(lián)，就會導(dǎo)致 SQL 查詢的性能極差。

這種情況下，我們就可以考慮使用其他方式來減少關(guān)聯(lián)表的數(shù)量。譬如，先將 B 表中的數(shù)據(jù)按照某種條件進行篩選，再將其與 A 表進行關(guān)聯(lián)。這樣就能將關(guān)聯(lián)的數(shù)據(jù)量減少到一個可控范圍內(nèi)，提高了 SQL 查詢的性能。

在 SQL 查詢中，左模糊和全模糊查詢都是非常耗費資源的查詢方式，會造成嚴重的性能問題。

這是因為：在查詢過程中，需要對每一條數(shù)據(jù)進行全文搜索，這樣就會導(dǎo)致查詢速度非常慢，甚至導(dǎo)致數(shù)據(jù)庫的性能嚴重下降。

正確使用模糊查詢，才能提高查詢性能，減少查詢開銷。

使用模糊查詢時，需要注意以下幾點：

• 盡量避免在模糊查詢中使用“%”通配符，尤其是在左側(cè)使用，在可能的情況下，使用具體的字符范圍來代替它。

• 盡量避免使用左模糊或者全模糊查詢，可以使用右模糊查詢（like 'value%'），又或者其他查詢方式來代替模糊查詢。

• 如果必須使用模糊查詢，可以考慮使用全文索引（full-text index）來優(yōu)化查詢性能。

• 優(yōu)先考慮使用前綴索引來加速查詢。

示例：

假設(shè)我們要查詢某個表中以 “abc” 開頭的用戶名：

select * from user where name LIKE '%abc';

使用上面的語句，即便表中只有少量以 “abc” 開頭的用戶名，這樣的查詢也會導(dǎo)致全表掃描，造成不必要的開銷，極大地影響查詢性能。

我們可以使用下面這個語句：

select * from user where name LIKE 'abc%';

利用前綴索引來加速查詢，避免了全表掃描，極大提高了查詢性能。

索引訪問類型是指 MySQL 在查詢數(shù)據(jù)時使用的索引方式，通常分為全值匹配、范圍查詢、索引掃描和全表掃描等多種方式。

其中，索引訪問類型不到 range 級別的查詢方式，就會對查詢性能造成較大的影響。

索引訪問類型的問題及影響

當索引訪問類型不到 range 級別時，MySQL 在查詢數(shù)據(jù)時需要對索引進行全表掃描或索引掃描，導(dǎo)致查詢效率低下，查詢速度變慢，嚴重影響系統(tǒng)的性能。

如何正確使用索引

以下是一些使用索引的建議：

• 在經(jīng)常查詢的列上創(chuàng)建索引。

• 在經(jīng)常用于排序、分組和聯(lián)合查詢的列上創(chuàng)建索引。

• 限制索引列的數(shù)量，避免創(chuàng)建過多的索引。

• 避免在索引列上使用函數(shù)或表達式。

索引訪問類型不到 range 級別的解決方法

如下，索引訪問類型不到 range 級別：

select * from orders where order_date > '2022-01-01';

在這個查詢語句中，如果 orders 表的 order_date 字段沒有創(chuàng)建索引，MySQL 就會對該字段進行全表掃描，從而導(dǎo)致查詢效率低下。

為了避免這種情況，我們可以在 order_date 字段上創(chuàng)建索引，將查詢類型從全表掃描變?yōu)榉秶樵?，從而提高查詢效率?/span>

聯(lián)合索引是由多個列組成的索引，可以在多個列上進行查詢，它同時包含了多個列的索引，多個列組合成一個鍵來進行索引。

相較于單列索引，聯(lián)合索引可以提高查詢效率和優(yōu)化數(shù)據(jù)庫性能。

聯(lián)合索引的好處

聯(lián)合索引可以減少磁盤 I/O 操作，提高查詢效率，減少系統(tǒng)負載。

• 提高查詢效率：聯(lián)合索引可以根據(jù)多個列組合進行查詢，能夠快速定位所需要的記錄，減少掃描數(shù)據(jù)表的時間，提高查詢效率。

• 優(yōu)化數(shù)據(jù)庫性能：聯(lián)合索引可以避免使用多個單列索引，從而減少索引的數(shù)量和空間，縮短查詢的響應(yīng)時間，優(yōu)化數(shù)據(jù)庫性能。

• 能夠滿足多個查詢需求：由于聯(lián)合索引包含多個列，因此能夠滿足多個查詢需求，避免創(chuàng)建過多的索引。

創(chuàng)建和使用聯(lián)合索引時，需要特別注意以下幾點：

• 列的順序非常重要，應(yīng)該將最經(jīng)常被過濾的列放在索引的前面。

• 不要創(chuàng)建過多的聯(lián)合索引，只創(chuàng)建必要的索引。

• 要注意索引的大小和內(nèi)存使用情況，避免出現(xiàn)性能問題。

• 定期監(jiān)控索引的使用情況，及時調(diào)整索引以適應(yīng)不同的查詢需求。

• 在經(jīng)常用于排序、分組和聯(lián)合查詢的列上創(chuàng)建聯(lián)合索引。

• 在經(jīng)常同時查詢多個列的情況下，可以使用聯(lián)合索引替代單個列索引。

如果聯(lián)合索引使用不當，將導(dǎo)致查詢效率降低、占用過多的磁盤空間、更新數(shù)據(jù)時效率低下等問題。

聯(lián)合索引的使用示例

我們假設(shè)有一個用戶表，包含用戶 ID、用戶名和電子郵件地址三個列，我們想要按照用戶名和電子郵件地址進行查詢，可以創(chuàng)建以下聯(lián)合索引：

create INDEX idx_username_email ON users(username, email);

接著，再執(zhí)行以下查詢：

select * from users where email = 'test@example.com';

這個查詢雖然可以使用到 idx_username_email 索引，但是它并不會很快，因為 email 列排在了索引的第二個位置，查詢時需要先按照用戶名進行排序，然后再根據(jù)電子郵件地址進行過濾，而這個過程可能需要耗費大量的時間。

正確的做法是將 email 列放在第一個位置：

create INDEX idx_email_username ON users(email, username);

這樣，查詢時就可以直接使用索引來過濾電子郵件地址了，而不需要再按照用戶名進行排序，極大地提高了查詢性能。

總結(jié)

本文總結(jié)了 SQL 查詢性能優(yōu)化的一些經(jīng)驗和注意事項，包括禁止使用 select * 、用小表驅(qū)動大表、join 關(guān)聯(lián)表不宜過多、禁止使用左模糊或者全模糊查詢、索引訪問類型至少達到 range 級別、更優(yōu)雅的使用聯(lián)合索引等。

在實際應(yīng)用過程中，要結(jié)合具體情況靈活運用，以滿足不同的業(yè)務(wù)需求和應(yīng)用場景。