基本语句优化10个原则
原则1：尽量避免在列上进行运算，这样会导致索引失效。
如：
SELECT * FROM t WHERE d/2=100;
优化：
SELECT * FROM t WHERE d=100*2;
原则2：使用JOIN时，应该用小结果集驱动大结果集。同时把复杂的JOIN查询拆分成多个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.aid;
原则3：注意LIKE模糊查询的使用，避免%%。
如：
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;
优化：
先用PHP产生随机数，把这个字符串传给MySQL，MySQL里用in查询。
原则8：避免使用NULL。而且应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
如：
select id from t where num is null;
优化：在num上设置默认值0，确保表中num列没有null值。
select id from t where num=0;
原则9：不要使用count(id)，而应该是count(*)。
原则10：不要做无谓的排序操作。而应尽可能在索引中完成排序。
在没有建立索引的情况下，数据库查找某一条数据，必须进行全表扫描，对表中所有数据进行一次遍历，查找出符合条件的记录。而全表搜索是我们查询数据时应该尽力避免的。
下面来分析以下两条语句的执行效率是否一样。
因为如果简单地从条件语句的先后上看，这两个语句执行效率是不一样的，如果tID是一个聚合索引，那么第二句仅仅从表的 10000条以后的记录中查找就行了；而第一句则要先从全表中查找看有几个name='张三'的，而后再根据限制条件条件tID>
10000来提出查询结果。
select * from t1 where name='张三' and tID > 10000;
select * from table1 where tID > 10000 and name='张三';
事实上，这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。 虽然查询优化器可以根据where子句自动的进行查询优化，但有时查询优化器会不按照我们本意进行快速查询。
在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数（SARG），那么就称之为可优化的，并且可以利用索引快速获得所需数据。
SARG的定义：Searchable Arguments，用于限制搜索的一个操作，它通常是指一个特定的匹配，一个值的范围内的匹配或者两个以上条件的AND连接。形式如下：
列名 操作符 <常数 或 变量>
select * from table1 where tID > 10000 and name='张三';
事实上，这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。 虽然查询优化器可以根据where子句自动的进行查询优化，但有时查询优化器会不按照我们本意进行快速查询。
在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数（SARG），那么就称之为可优化的，并且可以利用索引快速获得所需数据。
SARG的定义：Searchable Arguments，用于限制搜索的一个操作，它通常是指一个特定的匹配，一个值的范围内的匹配或者两个以上条件的AND连接。形式如下：
列名 操作符 <常数 或 变量>
或
<常数 或 变量> 操作符 列名
列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：
列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：
name = ’张三’
price > 5000
5000 < price
name=’张三’ and 价格>5000
如果一个表达式不能满足SARG的形式，那它就无法限制搜索的范围了，也就是SQL SERVER必须对每一行都判断它是否满足where子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是不起作用的。
name=’张三’ or price>5000 不满足SARG，使用or会引起全表扫描。
所以，优化查询最重要的就是，尽量使语句符合查询优化器的规则避免全表扫描而使用索引查询。
优化建议
除了上面常用的十条优化原则，我们在使用SQL 查询时还应该注意：
1)不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、！=、<>、！<、！>、NOT EXISTS、NOT IN、NOT LIKE等，另外还有函数。
abs(price) < 5000
name like ‘%三’
2)应尽量避免在 where 子句中使用 or 来连接条件,注意：in 相当于 or
select * from table1 where tid in (2,3);
select * from table1 where tid in (2,3);
和
select * from table1 where tid=2 or tid=3;
上面两条语句是一样的，都会引起全表扫描。
select id from t where num=10 or num=20;
优化：
select id from t where num=10 union all select id from t where num=20;
优化：
select id from t where num=10 union all select id from t where num=20;
select id from t where num in(1,2,3);
优化：对于连续的数值，可以使用 between 来进行优化
select id from t where num between 1 and 3;
3)很多时候用 exists是一个好的选择：
select num from a where num in(select num from b);
优化：
select num from a where exists(select 1 from b where num=a.num);
select num from a where num in(select num from b);
优化：
select num from a where exists(select 1 from b where num=a.num);
4)尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
5)避免频繁创建和删除临时表，以减少系统表资源的消耗。
6)临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
7)在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
8)如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
9)尽量避免大事务操作，提高系统并发能力。
10)尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
11)避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:
select name from employee where salary > 60000
在这条语句中,如salary字段是float型的,则优化器很难对其进行优化,因为60000是个int型。应当在编程时将int型转化成为float型,不要等到运行时转化。
select name from employee where salary > 60000
在这条语句中,如salary字段是float型的,则优化器很难对其进行优化,因为60000是个int型。应当在编程时将int型转化成为float型,不要等到运行时转化。
12)能用distinct的就不用group by
select orderid from details where price > 10 group by orderid;
优化：
select distinct orderid from details where price > 10;
select orderid from details where price > 10 group by orderid;
优化：
select distinct orderid from details where price > 10;
13)能用 union all 就不要用 union，union all 不执行select distinct 函数，这样会造成很多不必要的资源浪费。
14)尽量不要用select into语句。select into 语句会导致表锁定，阻止其他用户访问该表。跟上面的 7）需要酌情考虑需不需要用。
参考资料：
《PHP 核心技术与最佳实践》 列旭松 陈文 著
SARG 百度百科：http://baike.baidu.com/link?url=HqND2q6NyeVyF3q6D-51BvRHCfvixoY2wZ2AlKAxHCxfpx0H5kRHjKIJVHIfWx7RFGQ9BTu7ZumXbBUJ29Sm6q
Mysql 大数据量高并发的数据库优化 http://lib.csdn.net/article/mysql/5057
扫一扫
2615
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
