任鸟飞网站设计博客

网站（动态）的数据一般情况下都会存储在数据库中（例如MS SQL、My SQL等），当用户访问网站时，会根据一些缺省的或用户提交的参数（比如网址后面可能会带有”index.php?cat=design” ）来查询或更新数据库，虽然数据库的运行速度很快，但在一些不恰当的数据库操作的情况下，会增加数据库运行占用的系统资源，严重时可能会拖垮整个数据库的运行。

下面汇总了10条常见的SQL语句问题点及优化建议

1. 尽量减少 * 的使用，只查询你需要的字段内容。

优化前：

SELECT * FROM wp_posts;

优化后：

SELECT title, excerpt, author FROM wp_posts;

2. 记得使用 LIMIT ， 限制查询的数量。

优化后：

SELECT title, excerpt, author FROM wp_posts LIMIT 10;

LIMIT 的使用方法：
LIMIT OFFSET,NUMBER ； offset代表偏移量，number代表从offset开始查询的数量。
例如：
LIMIT 10 ； 代表偏移量为0，查询条数为10条。
LIMIT 10,12 ; 代表偏移量为10，查询条数为12条。

3. 避免在程序语言的循环语句（例如 PHP 的foreach、while 等）中使用SQL语句

优化前:

foreach ($display_order as $id => $ordinal) {
$sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";}

优化后：

UPDATE categories
SET display_order =   CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)

具体请参考：如何在数据库中一次更新多行多字段

4. 使用join代替子查询（subqueries）语句

优化前：

SELECT a.id,
(SELECT MAX(created)
FROM posts
WHERE author_id = a.id)
AS latest_post FROM authors a

优化后：

SELECT a.id, MAX(p.created) AS latest_post
FROM authors a
INNER JOIN posts p
ON (a.id = p.author_id)
GROUP BY a.id 

5. 尽量减少通配符的使用，特别是前缀通配符（通配符放在前面）

#Full wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';
#Postfix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE  'hello%';
#Prefix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE  '%hello';

6. 使用union代替or查询语句

优化前：

SELECT * FROM a, b WHERE a.p = b.q or a.x = b.y;

优化后

SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x = b.y

7. 记得使用索引（index）

CREATE INDEX idxModel ON Product (Model);

8. 用>=替代>

优化前：

SELECT * FROM EMP WHERE DEPTNO >3

优化后：

SELECT * FROM EMP WHERE DEPTNO >=4