sql优化10个原则

在数据库的应用中,程序员们通过不断地实践总结了很多经验,这些经验是一些普遍适用 的规则。每一个程序员都应该了解并记住它们,在构造SQL时,养成良好的习惯。以下列举10个比较重要的原则供大家参考。


原则1 : 尽量避免在列上进行运算,这样会导致索引失效。

例如原句为: 

SELECT * FROM t WHERE YEAR(d) >= 2011;

优化为:

SELECT * FROM t WHERE d >= '2011-01-01';


原则2 : 使用JO IN时,应该用小结果集驱动大结果集。同时把复杂的JO IN查询拆分成多 个Query。因为JOIN多个表时,可能导致更多的锁定和堵塞。

例如: 

SELECT * FROM a JOIN b ON a.id=b.id

LEFT JOIN c ON c.time=a.date

LEFT JOIN d ON c.pid=b.aid

LEFT JOIN e ON e.cid=a.did 


原则3 : 注意L IK E模糊查询的使用,避免% %。

例如原句为: 

SELECT * FROM t WHERE name LIKE '%de%'

优化为:

SELECT * FROM t WHERE name>='de' AND name<'df'


原则4 : 仅列出需要查询的字段,这对速度不会有明显影响,主要考虑节省内存。

例如原句为: 

SELECT * FROM Member;

优化为:

SELECT id,name,pwd FROM Member;


原则5 : 使用批量插入语句节省交互。

例如原句为: 

INSERT INTO t (id,name) VALUES (1,'a');

INSERT INTO t (id,name) VALUES (2,'b');

INSERT INTO t (id,name) VALUES (3,'c');

优化为: 

INSERT INTO t (id,name) VALUES (1,'a'),(2,'b'),(3,'c'); 


原则6: limit的基数比较大时使用between。

例如原句为: 

select * from article as article order by id limit 1000000,10

优化为:

select * from article as article where id between 1000000 and 1000010 order by id

between限定比limit快,所以在海量数据访问时,建议用between或是where替换掉limit。 

但是between也有缺陷,如果id中间有断行或是中间部分id不读取的情况,总读取的数量会少 

于预计数量! 

在取比较后面的数据时,通过desc方式把数据反向查找,以减少对前段数据的扫描,让 

limit的基数越小越好! 


原则7 : 不要使用rand函数获取多条随机记录。例如: 

select * from table order by rand() limit 20;

使用下面的语句代替:

SELECT * FROM 'table' AS tl JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM 'table')-(SELECT MIN(id) FROM 'table'))+(SELECT MIN(id) FROM 'table')) AS id ) AS t2 WHERE tl.id>=t2.id ORDER BY tl.id LIMIT 1;

这是获取一条随机记录,这样即使执行2 0次,也比原来的语句髙效。或者先用P H P产生 

随机数,把这个字符串传给MySQL, MySQL里用in查询。 


原则8 : 避免使用NULL。 


原则9 : 不要使用count(id), 而应该是count( * )。 


原则1 0 :不要做无谓的排序操作,而应尽可能在索引中完成排序。 

文章来源:https://www.cnblogs.com/xiaozong/p/5788759.html

推荐阅读更多精彩内容