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

實戰範例 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 AGAINST 和 LIKE 都是用來進行模糊匹配的方法，但是它們的實現方式是不同的。

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 MODE 或 WITH 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 只能用於 MyISAM 或 InnoDB 引擎的表中，而且需要在相應的欄位上建立全文本索引

建立全文本索引

1

ALTER TABLE table_name ADD FULLTEXT(`column_name`, `column_name`,...);

• 建立索引會增加數據庫的存儲需求和索引維護成本，因此在建立索引之前需要仔細評估其對系統性能的影響。

• ← Previous Post
• Next Post →