優(yōu)化查詢對(duì)于提高執(zhí)行速度和數(shù)據(jù)庫性能至關(guān)重要。以下是優(yōu)化查詢的 10 種方法以及示例代碼。

1.使用 EXPLAIN 進(jìn)行測(cè)試

優(yōu)化 SQL 查詢的第一步是找出它們未優(yōu)化的位置和方式。

EXPLAIN 命令提供有關(guān)數(shù)據(jù)庫如何執(zhí)行查詢的信息。 EXPLAIN 使用示例：

EXPLAIN SELECT *
FROM customers
WHERE country = 'China';

這將輸出查詢的執(zhí)行計(jì)劃，顯示訪問表的順序、內(nèi)存和執(zhí)行時(shí)間估計(jì)、處理的行數(shù)、使用的任何索引等等！

2.避免使用 SELECT *

不要從表中選擇所有列，而只需選擇實(shí)際需要的列。這可以顯著減少需要處理的數(shù)據(jù)量。

SELECT customer_id, name, email
FROM customers
WHERE country = 'China';

3.明智地使用 JOIN

根據(jù)使用的數(shù)據(jù)使用最有效的 JOIN 類型。

常見連接從最快到最慢列出：

• INNER
• LEFT/RIGHT
• FULL OUTER

還需要確保優(yōu)化連接條件，盡量使用主鍵到外鍵關(guān)系，并盡可能避免多對(duì)多關(guān)系。

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers 
  ON orders.customer_id = customers.customer_id;

4.有效過濾數(shù)據(jù)：

盡早使用 WHERE 子句過濾數(shù)據(jù)。避免在 WHERE 子句中使用函數(shù)和計(jì)算，因?yàn)樗鼤?huì)減慢查詢速度。

效率低下：

SELECT order_id, order_date
FROM orders
WHERE YEAR(order_date) = 2023;

優(yōu)化語句：

SELECT order_id, order_date
FROM orders
WHERE order_date >= '2023-01-01' 
  AND order_date <= '2022-13-31';

5.使用適當(dāng)?shù)谋容^運(yùn)算符

比較精確匹配時(shí)使用“=”而不是“LIKE”，因?yàn)樗梢约涌觳樵兊膱?zhí)行時(shí)間。

SELECT customer_id, name, email
FROM customers
WHERE email = 'jason@example.com';

6.使用 EXISTS 而不是 COUNT：

使用 EXISTS 而不是 COUNT 來檢查記錄是否存在，因?yàn)樗梢约涌觳樵兊膱?zhí)行時(shí)間。

效率低下：

SELECT *
FROM orders
WHERE (
    SELECT COUNT(*) FROM order_items 
    WHERE orders.order_id = order_items.order_id
) > 0;

優(yōu)化語句：

SELECT *
FROM orders
WHERE EXISTS (
    SELECT * FROM order_items 
    WHERE orders.order_id = order_items.order_id
);

7.使用 UNION ALL 代替 UNION

組合多個(gè)結(jié)果集時(shí)，使用 UNION ALL 而不是 UNION，因?yàn)樗粫?huì)刪除重復(fù)記錄并且可以提高查詢的性能。

SELECT customer_id, name
FROM customers
WHERE country = 'China'
UNION ALL
SELECT customer_id, name
FROM customers
WHERE country = 'Russia';

8.使用 LIMIT 或 TOP

如果只需要檢索少量記錄，請(qǐng)使用 LIMIT 或 TOP 限制返回的記錄數(shù)。

SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10;

9.謹(jǐn)慎使用 GROUP BY 和 HAVING

GROUP BY 和 HAVING 對(duì)于匯總數(shù)據(jù)非常有用，但它們也可能非常慢。嘗試僅在必要時(shí)使用它們并盡可能優(yōu)化它們，例如按基數(shù)較低的列進(jìn)行分組。

SELECT customer_id, COUNT(*) AS num_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5;

10.使用存儲(chǔ)過程

使用存儲(chǔ)過程預(yù)編譯和優(yōu)化常用查詢，因?yàn)樗鼈兛梢燥@著提高性能。

CREATE PROCEDURE get_orders_by_customer (IN customer_id INT)
BEGIN
    SELECT *
    FROM orders
    WHERE customer_id = customer_id;
END;

勿噴，實(shí)在沒有辦法的時(shí)候可以試試存儲(chǔ)過程。