SQL tuning技巧

SQL tuning 技巧

--1 如果你用实际的列名代替* Begin---
SELECT id, first_name, last_name, age, subject FROM student_details;

代替

SELECT * FROM student_details;

--1 如果你用实际的列名代替* End---

--2 having语句用来在所有row被选择后过滤row的，也就是用在跟group by配合的。不要用于其它目的 Begin---
SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;

代替:

 SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

--2  End---

--3 可能在你的主查询中有超过一个的子查询，请尽量最小化子查询block的个数 Begin---
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';

把两个子查询替换为一个子查询:

SELECT name
FROM employee
 WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';

--3  End---

--4 EXISTS, IN, TableJoin要使用恰当 Begin---
1 IN 的性能最低
2 当大多数filter criteria在子查询中时，IN是有效率的
3 当大多数filter criteria在Main查询中，Exist是有效率的

Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)

而不是：
Select * from product p
where IN (select * from order_items o
where o.product_id = p.product_id)
--4  End---

--5 使用EXISTS代替DISTINCT当Join包括的Tables有one to many relations Begin---
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

代替:
 
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

--5  End---

--6 使用Unique All代替UNION Begin---
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;

而不是:
 
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

--6  End---

--7 在Where clause中请小心使用condition Begin---
SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:
 
SELECT id, first_name, age FROM student_details WHERE age != 10;
---
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';
 

Instead of:
 
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';

---
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:
 
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)
---
不要使用non-column expression在查询的一边，因为它会被提早的处理。
SELECT id, name, salary
FROM employee
WHERE salary < 25000;

Instead of:
 
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;
---
SELECT id, first_name, age
FROM student_details
WHERE age > 10;

Instead of:
 
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

--7  End---