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

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

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

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

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

select * 的問題及影響

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

當(dāng)查詢的表中包含大量字段的時(shí)候，這種寫法既浪費(fèi)帶寬和 I/O 資源，還會(huì)造成緩存和 CPU 的過度使用，嚴(yán)重影響 SQL 的查詢性能。

如何正確使用 select 語句

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

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

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

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

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

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

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

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

例如：

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

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

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

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

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

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

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

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

如何減少 join 的使用呢

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

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

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

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

舉個(gè)例子：

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

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

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

在 SQL 查詢中，左模糊和全模糊查詢都是非常耗費(fèi)資源的查詢方式，會(huì)造成嚴(yán)重的性能問題。

這是因?yàn)椋涸诓樵冞^程中，需要對(duì)每一條數(shù)據(jù)進(jìn)行全文搜索，這樣就會(huì)導(dǎo)致查詢速度非常慢，甚至導(dǎo)致數(shù)據(jù)庫的性能嚴(yán)重下降。

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

使用模糊查詢時(shí)，需要注意以下幾點(diǎn)：

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

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

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

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

示例：

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

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

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

我們可以使用下面這個(gè)語句：

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

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

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

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

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

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

如何正確使用索引

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

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

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

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

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

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

如下，索引訪問類型不到 range 級(jí)別：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

create INDEX idx_username_email ON users(username, email);

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

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

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

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

create INDEX idx_email_username ON users(email, username);

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

總結(jié)

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

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