EXPLAIN SELECT * FROM customers WHERE country = 'China';这将输出查询的执行计划,显示访问表的顺序、内存和执行时间估计、处理的行数、使用的任何索引等等!
SELECT customer_id, name, email FROM customers WHERE country = 'China';
INNER LEFT/RIGHT FULL OUTER还需要确保优化连接条件,尽量使用主键到外键关系,并尽可能避免多对多关系。
SELECT orders.order_id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
效率低下:
SELECT order_id, order_date FROM orders WHERE YEAR(order_date) = 2023;
SELECT order_id, order_date FROM orders WHERE order_date >= '2023-01-01' AND order_date <= '2022-13-31';5.使用适当的比较运算符
SELECT customer_id, name, email FROM customers WHERE email = 'jason@example.com';
SELECT * FROM orders WHERE ( SELECT COUNT(*) FROM order_items WHERE orders.order_id = order_items.order_id ) > 0;优化语句:
SELECT * FROM orders WHERE EXISTS ( SELECT * FROM order_items WHERE orders.order_id = order_items.order_id );7.使用 UNION ALL 代替 UNION
SELECT customer_id, name FROM customers WHERE country = 'China' UNION ALL SELECT customer_id, name FROM customers WHERE country = 'Russia';8.使用 LIMIT 或 TOP
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;9.谨慎使用 GROUP BY 和 HAVING
SELECT customer_id, COUNT(*) AS num_orders FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5;
CREATE PROCEDURE get_orders_by_customer (IN customer_id INT) BEGIN SELECT * FROM orders WHERE customer_id = customer_id; END;