SQL

深入了解 SQL JOINS - 實戰篇 [2]

A Comprehensive Guide to Inner, Outer, Self, and Cross Joins

Posted by Young on 2022-12-18
Estimated Reading Time 3 Minutes
Words 894 In Total
Viewed Times

實戰範例 INNER JOIN

目標:

  • 優化 SQL 查詢效能
  • 必須要有可以片段式(以空格隔開 Ex: C0 產品 1 備註 1 …)且跨欄位的查詢功能
  • 改善搜尋系統速度、使用者體驗。

範例一

原始 SQL 如下:

1
2
3
$sql="SELECT `bom_topic`.`bom_id`,`bom_topic`.`bom_num`
FROM `bom_topic`,`bom_Stc`,`stuff`
WHERE `bom_topic`.`bom_num`=`bom_Stc`.`bom_num` AND `bom_Stc`.`pdnum`=`stuff`.`ProductNo` AND (`bom_Stc`.`pdnum` LIKE '%$stc_query%' OR `stuff`.`ProductName` LIKE '%$stc_query%' OR `stuff`.`ProductName_e` LIKE '%$stc_query%')";

問題:

此 SQL Query 跨了三個 table 去撈資料,發生了以下問題:

  • 沒有縮排,還有很多不必要的 SELECT 欄位,也沒有使用索引
  • 跨三個 table 直接 FROM 多個 table,就相於跟多個 table 做 CROSS JOIN,效能會極差
  • 用過多 LIKE 且過多 OR,導致引擎放棄索引直接進行全表掃描。

第一次優化後的 SQL 如下:

1
2
3
4
5
$sql = "SELECT DISTINCT bt.bom_id, bt.bom_num
FROM `bom_topic` AS bt
INNER JOIN `bom_Stc` AS bs ON bt.bom_num = bs.bom_num
INNER JOIN `stuff` AS s ON bs.pdnum = s.ProductNo
WHERE CONCAT(s.ProductName, s.ProductName_e) OR CONCAT(s.ProductName_e, s.ProductName) LIKE '%$stc_query%'";

使用 CONCAT 函數和 LIKE 運算符儘管可以滿足跨欄位查詢的目的了,使用 LIKE 運算符搜索指定字符串之前,CONCAT 函數被用於將兩個列的值連接起來。但如果表中有許多行並且連接操作針對每行執行,則此操作可能會變慢,

最終優化後的 SQL 如下:

1
2
3
4
5
6
$sql="SELECT bt.bom_id, bt.bom_num
FROM `bom_topic` AS bt
INNER JOIN `bom_Stc` AS bs ON bt.bom_num = bs.bom_num
INNER JOIN `stuff` AS s ON bs.pdnum = s.ProductNo
WHERE MATCH(s.ProductName, s.ProductName_e) AGAINST ('$stc_query' IN BOOLEAN MODE)
GROUP BY bt.bom_id, bt.bom_num";

優化要點

  1. 把不需要 SELECT 的欄位拿掉:只留需要關聯用的欄位
  2. 使用索引:確保 table 中的相關欄位已經建立索引,以提高查詢效能。例如,bom_topic table 中的 bom_num 欄位和 bom_id 欄位,以及 bom_Stc table 中的 bom_num 欄位和 pdnum 欄位,都應該建立索引。
  3. 避免使用 LIKE,在 WHERE 子句中使用 LIKE 操作符,會導致全表掃描,嚴重影響效能,尤其是當%符號位於字串的開頭時。可以考慮使用全文檢索或其他更有效的方式,這邊用 MATCH AGAINST

MATCH AGAINST 用法

MATCH AGAINSTLIKE 都是用來進行模糊匹配的方法,但是它們的實現方式是不同的。

MATCH AGAINST 是一個 MySQL 內建的全文本檢索功能,它可以對指定的欄位進行全文本檢索,一般來說,使用 MATCH AGAINST 會比使用 LIKE 更有效率,因為 MATCH AGAINST 是針對全文本檢索進行優化的方法,可以利用全文本索引等技術,快速地找出符合條件的結果。

MATCH AGAINST 用法如下:

1
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_keyword');

table_name 是要查詢的表名,column_name 是要進行全文本檢索的欄位名,search_keyword 是要查詢的關鍵字。
可以使用 IN BOOLEAN MODEWITH QUERY EXPANSION 來擴展查詢,例如:

1
2
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_keyword' IN BOOLEAN MODE);
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_keyword' WITH QUERY EXPANSION);
  • IN BOOLEAN MODE 可以用來進行布爾搜索
  • WITH QUERY EXPANSION 可以用來進行相似度搜索
  • 需要注意的是,MATCH AGAINST 只能用於 MyISAMInnoDB 引擎的表中,而且需要在相應的欄位上建立全文本索引

建立全文本索引

1
ALTER TABLE table_name ADD FULLTEXT(`column_name`, `column_name`,...);
  • 建立索引會增加數據庫的存儲需求和索引維護成本,因此在建立索引之前需要仔細評估其對系統性能的影響。

若您覺得這篇文章對您有幫助,歡迎分享出去讓更多人看到⊂◉‿◉つ~


留言版