56,687
社区成员
发帖
与我相关
我的任务
分享关于SQL性能优化，求助！！！
关于SQL的性能优化，
具体从哪里开始着手？
根据我现在的sql文
1
select count(*) 改成select count(字段1)
2
select tableA.*之类的，改成select tableA.字段1，字段2。。。。。。
3
from tableB,tableC 迪卡尔积的地方，根据表间关系，改成inner join
我想问问，具体通常还有那些需要注意的地方。
select from
tableA
left join tableB on
left join tableC on
......
我想问一下，对于所要结果，tableB中的字段我并不是都想要，
select from
tableA
left join (select 所需字段 from tableB) tableB on
......
这样的写法，那个性能好一些。
具体从哪里开始着手？
根据我现在的sql文
1
select count(*) 改成select count(字段1)
2
select tableA.*之类的，改成select tableA.字段1，字段2。。。。。。
3
from tableB,tableC 迪卡尔积的地方，根据表间关系，改成inner join
我想问问，具体通常还有那些需要注意的地方。
select from
tableA
left join tableB on
left join tableC on
......
我想问一下，对于所要结果，tableB中的字段我并不是都想要，
select from
tableA
left join (select 所需字段 from tableB) tableB on
......
这样的写法，那个性能好一些。
...全文
请发表友善的回复…
发表回复
askyin 2006-09-25
- 打赏
- 举报
2。SELECT优化方法
讨论关键字
SELECT这个关键字没什么好讨论的，主要就是与其组合的关键字下面我们来一一讨论：
FROM
该关键字后面跟表名，当然可以跟多个，但只要条件设置的好就没有问题。如：SELECT AID,BID FROM A,B WHERE AID=BID 这样设置的话就没有问题，数据库会先通过条件建立AB表的逻辑表然后进行查找需要的数据。但是值得注意的是，如果这两表没有建立索引那么就有点麻烦了，因为数据库会进行全表扫描，在数据量比较大的情况下这是我们所不希望看到的，那么有没有办法解决呢？当然，设置聚集索引，就像在看一本书，如果我们没有目录，我们只能从第一页行查找，这和数据库是一个道理。
索引
总的来说索引可以分为聚集索引和非聚集索引。一个表中只能有一个聚集索引。对一个表建立一个聚集索引后，数据库会调整记录的顺序，使表按照索引的顺序重新排列。而非聚集索引不会改变表的顺序。
建立索引的几种情况有：
1．主键列、经常有范围查找和ORDER BY或GROUP BY的列，可以考虑建立聚集索引；
2．在使用频繁的列且经常需要使用函数的时候，可以考虑使用函数索引。
3．外键列、频繁修改更新的列，可以考虑使用非聚集索引。
索引虽有助于提高性能，但并不是索引越多越好，恰好相反过多的索引会导致系统低效。因为用户在没一个表中每增加一个索引，维护索引集合就要做相应的更新工作，同时每次有数据改变的时候就需要维护索引。
WHERE
该条件语句虽然简单，但不同的使用方法结果却截然不同。比如：
select * from table1 where name= ’zhangsan’ and TID > 10000
与 select * from table1 where TID > 10000 and name= ’zhangsan’
一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是一样的，但如果TID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID>10000来提出查询结果。如果在记录集较多的情况下区别是非常大的。
但有时你会发现着两句的效率其实差不多。这是什么原因呢？因为绝大多数数据库软件中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。但是也必须付出代价，就是数据库软件会消耗一部分开销去优化低效的SQL语句。如果你的SQL语句写的比较复杂的话，那么软件在比对与分析SQL语句上的开销是很大的。
尽量减少格式转换
举个简单的例子：某表有一时间字段，现在需要与‘20050101’和‘20051231’进行比较。我们比较下面两个语句：（PGSQL）
SELECT * FROM TABLE1 WHERE TO_CHAR(WORKDATE,’YYYMMDD’) BETWEEN ‘20050101’ AND ‘20051231’ 将WORKDATE转换成字符串进行比较。
与
SELECT * FROM TABLE1 WHERE
WORKDATE>=TO_DATE(‘2005-01-01’,’YYYY-MM-DD’) AND
WORKDATE<=TO_DATE(‘2005-12-31’,’YYYY-MM-DD’) 将字符串转换成日期型进行比较。
第二句SQL的性能明显优于第一句。这是因为第一句数据库必须将没一行记录的日期都转换一次，而第二句只需要对常量做转换。（以上结论成立的前提是日期字段不能使用索引）
谨慎使用IS NULL 和 IS NOT NULL
不能用NULL做索引，任何包含NULL值的列都将不会被包含在索引中。即使在列上建立索引，只要这些列中含有NULL，该列就会从索引中删除。也就是说，如果某列存在空值，即使对该列建索引也不会提高性能。同时，任何在WHERE子句中使用IS NULL或者IS NOT NULL优化器是不允许使用索引的。
IN和OR
SQLSERVER会自动将IN转换成OR，因此对SQLSERVER来说下面两句是一样的。SELECT ID FROM USERS WHERE ID = ‘1’OR ID = ‘2’OR ID = ‘3’这句查找出ID是1或2或3的人。
与
SELECT ID FEOM USERS WHERE ID IN(1,2,3)的效率是一样的。
而对于其他数据库来说，大多数参考书都说IN的效率比OR高。
ORDER BY
对于ORDER BY是非常重要的关键子，我们不希望数据是杂乱无章的，那样对我们没有价值，而该关键子使我们可以得到想要的结果。但是也必须付出代价，消耗一定的资源。当然如果在需要经常排序的列上建立索引的话是有助于提高查询性能的。（注意：过多的索引反而影响系统速度）
GROUP BY
在使用统计函数时要用到它，同样的也回消耗资源，但是为了得到结果也是必须的。和排序一样，如果在某列建立了索引的是话，那么将会有助于提高查询效率。（注意：过多的索引反而影响系统速度）
带通配符（%）的LIKE 语句
要求在表中查询出包含‘A’的人。可以采用如下的查询语句：SELECT * FROM TABLEA WHERE NAME LIKE ‘%A%’；这里由于通配符%在搜寻首出现，所以即使NAME建立了索引，有些数据库也将不使用NAME的索引。在很多情况下可能是无法避免的，但是要做到心中有低，通配符如此使用会降低查询速度。
当然通配符出现在字符串其他位置时，优化器可以利用索引。如：SELECT * FROM TABLEA WHERE NAME LIKE ‘A%’；
查询字段需要多少，查询多少
我们每少提取一个字段，查询速度就会有相应的上升。所以要避免SELECT *这样的查询出现，需要哪些字段就查哪些字段。
关键字DISTINCT
取消重复值也许会在很多SQL语句中出现，但是笔者的感觉是尽量不要多用。在实际应用中发现有时数据库出现了多笔重复的问题，但被该关键字给掩盖了。或者有时候查询出很多重复的数据，有些人也许会用该关键字来解决着一问题。但是，我觉得重复数据的出现肯定是有原因的，如果盲目使用这种方式去解决将遗留下很多问题。所以我的观点是：该关键字要用，是在数据重复原因明确的情况下使用。这样才能保证问题不会被掩盖，保证数据库运行一段时间以后不会出现难以弥补的问题。
一个小技巧，去除重复值还有另外一个方法。GROUP BY依据指定的分组列对所有行进行分组，而且每一组返回一个行。这与DISTINCT得到的结果相同。那么哪一种方法更好呢？其实DISTINCT是一个更容易理解的语句，而使用GROUP BY则可以使数据库系统能更快地解决问题。
关于嵌套子查询
有时候普通查询对我们来说是脆弱的，多表或者更多表的时候往往需要其他的一些办法，先不讨论外连接。和绝大多数本本一样，我建议不要轻易使用嵌套子查询。原因很简单，消耗效率。用SQLSERVER2000测试后发现两表之间用其他的查询方式，其效率是差不多的。但是如果使用了字查询，那么他的成本消耗将会成倍的增长。如：SELECT AID,BID,BNAME FROM A,B WHERE A.AID=B.BID；原来两表的效率是A+B。
如果使用了字查询没增加一个字段乘数就会加1，如：SELECT AID,(SELECT BID FROM B WHERE A.AID=B.BID),(SELECT BNAME FROM B WHERE A.AID=B.BID) FROM A；那么他的效率就是A+2*B。完成同样的目的却有截然不同的结果，所以请慎用嵌套子查询。
上面一个例子也许有些牵强，因为没有人会用第二种方法，但是我想突出的是如果是多表查询的话，也要注意能不用尽量不用。（多表可以考虑使用外连接）
外连接内连接
根据性能测试发现内连接的性能高于外连接。如下两句语句：select * from abm1008,abm1009,tbl_headmaterial where abm1008.abmfldid=abm1009.abmfldid and fld_abm8097=material_no（内连接）①
与
select * from abm1008
left join (select * from abm1009 ) as abm1009 on abm1008.abmfldid=abm1009.abmfldid
left join (select * from tbl_headmaterial) as tbl_headmaterial on abm1009.fld_abm8097=tbl_headmaterial.material_no（外连接）②
完成相同的任务只有一点差异，但是内连接的效率明显要于外连接。
配置P42.4G 512M内存测试结果：
300次运行平均数据 ① ②
客户端处理累计时间 4.6087ms 42.5085ms
服务器应答累计等待时间 465.85ms 629.678ms
CPU消耗 24 78
I/O消耗 112 122
但是有时候不得已还是会使用外连接，所以要看情况，能使用内连接不使用外连接，如果一定要，就选择表比较小的而且最好有建立索引的进行连接。
3．小结
本章主要讲述查询的优化，无论您的数据库软件优化器是否强大，这都是需要的。在您的数据库运行速度不理想但有无法优化的情况下，这个是最节约成本的方法之一。
学习任何东西要去教条的学习，绝不能生搬硬套。要使用索引，但要考虑是否值得？过多的索引只会是数据库负担更大。用多少，什么时候用，关键看各位在实际工作中的经验累计。根据现实状况来选择查询语句的优化程度才是正确的方法。
讨论关键字
SELECT这个关键字没什么好讨论的，主要就是与其组合的关键字下面我们来一一讨论：
FROM
该关键字后面跟表名，当然可以跟多个，但只要条件设置的好就没有问题。如：SELECT AID,BID FROM A,B WHERE AID=BID 这样设置的话就没有问题，数据库会先通过条件建立AB表的逻辑表然后进行查找需要的数据。但是值得注意的是，如果这两表没有建立索引那么就有点麻烦了，因为数据库会进行全表扫描，在数据量比较大的情况下这是我们所不希望看到的，那么有没有办法解决呢？当然，设置聚集索引，就像在看一本书，如果我们没有目录，我们只能从第一页行查找，这和数据库是一个道理。
索引
总的来说索引可以分为聚集索引和非聚集索引。一个表中只能有一个聚集索引。对一个表建立一个聚集索引后，数据库会调整记录的顺序，使表按照索引的顺序重新排列。而非聚集索引不会改变表的顺序。
建立索引的几种情况有：
1．主键列、经常有范围查找和ORDER BY或GROUP BY的列，可以考虑建立聚集索引；
2．在使用频繁的列且经常需要使用函数的时候，可以考虑使用函数索引。
3．外键列、频繁修改更新的列，可以考虑使用非聚集索引。
索引虽有助于提高性能，但并不是索引越多越好，恰好相反过多的索引会导致系统低效。因为用户在没一个表中每增加一个索引，维护索引集合就要做相应的更新工作，同时每次有数据改变的时候就需要维护索引。
WHERE
该条件语句虽然简单，但不同的使用方法结果却截然不同。比如：
select * from table1 where name= ’zhangsan’ and TID > 10000
与 select * from table1 where TID > 10000 and name= ’zhangsan’
一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是一样的，但如果TID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID>10000来提出查询结果。如果在记录集较多的情况下区别是非常大的。
但有时你会发现着两句的效率其实差不多。这是什么原因呢？因为绝大多数数据库软件中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。但是也必须付出代价，就是数据库软件会消耗一部分开销去优化低效的SQL语句。如果你的SQL语句写的比较复杂的话，那么软件在比对与分析SQL语句上的开销是很大的。
尽量减少格式转换
举个简单的例子：某表有一时间字段，现在需要与‘20050101’和‘20051231’进行比较。我们比较下面两个语句：（PGSQL）
SELECT * FROM TABLE1 WHERE TO_CHAR(WORKDATE,’YYYMMDD’) BETWEEN ‘20050101’ AND ‘20051231’ 将WORKDATE转换成字符串进行比较。
与
SELECT * FROM TABLE1 WHERE
WORKDATE>=TO_DATE(‘2005-01-01’,’YYYY-MM-DD’) AND
WORKDATE<=TO_DATE(‘2005-12-31’,’YYYY-MM-DD’) 将字符串转换成日期型进行比较。
第二句SQL的性能明显优于第一句。这是因为第一句数据库必须将没一行记录的日期都转换一次，而第二句只需要对常量做转换。（以上结论成立的前提是日期字段不能使用索引）
谨慎使用IS NULL 和 IS NOT NULL
不能用NULL做索引，任何包含NULL值的列都将不会被包含在索引中。即使在列上建立索引，只要这些列中含有NULL，该列就会从索引中删除。也就是说，如果某列存在空值，即使对该列建索引也不会提高性能。同时，任何在WHERE子句中使用IS NULL或者IS NOT NULL优化器是不允许使用索引的。
IN和OR
SQLSERVER会自动将IN转换成OR，因此对SQLSERVER来说下面两句是一样的。SELECT ID FROM USERS WHERE ID = ‘1’OR ID = ‘2’OR ID = ‘3’这句查找出ID是1或2或3的人。
与
SELECT ID FEOM USERS WHERE ID IN(1,2,3)的效率是一样的。
而对于其他数据库来说，大多数参考书都说IN的效率比OR高。
ORDER BY
对于ORDER BY是非常重要的关键子，我们不希望数据是杂乱无章的，那样对我们没有价值，而该关键子使我们可以得到想要的结果。但是也必须付出代价，消耗一定的资源。当然如果在需要经常排序的列上建立索引的话是有助于提高查询性能的。（注意：过多的索引反而影响系统速度）
GROUP BY
在使用统计函数时要用到它，同样的也回消耗资源，但是为了得到结果也是必须的。和排序一样，如果在某列建立了索引的是话，那么将会有助于提高查询效率。（注意：过多的索引反而影响系统速度）
带通配符（%）的LIKE 语句
要求在表中查询出包含‘A’的人。可以采用如下的查询语句：SELECT * FROM TABLEA WHERE NAME LIKE ‘%A%’；这里由于通配符%在搜寻首出现，所以即使NAME建立了索引，有些数据库也将不使用NAME的索引。在很多情况下可能是无法避免的，但是要做到心中有低，通配符如此使用会降低查询速度。
当然通配符出现在字符串其他位置时，优化器可以利用索引。如：SELECT * FROM TABLEA WHERE NAME LIKE ‘A%’；
查询字段需要多少，查询多少
我们每少提取一个字段，查询速度就会有相应的上升。所以要避免SELECT *这样的查询出现，需要哪些字段就查哪些字段。
关键字DISTINCT
取消重复值也许会在很多SQL语句中出现，但是笔者的感觉是尽量不要多用。在实际应用中发现有时数据库出现了多笔重复的问题，但被该关键字给掩盖了。或者有时候查询出很多重复的数据，有些人也许会用该关键字来解决着一问题。但是，我觉得重复数据的出现肯定是有原因的，如果盲目使用这种方式去解决将遗留下很多问题。所以我的观点是：该关键字要用，是在数据重复原因明确的情况下使用。这样才能保证问题不会被掩盖，保证数据库运行一段时间以后不会出现难以弥补的问题。
一个小技巧，去除重复值还有另外一个方法。GROUP BY依据指定的分组列对所有行进行分组，而且每一组返回一个行。这与DISTINCT得到的结果相同。那么哪一种方法更好呢？其实DISTINCT是一个更容易理解的语句，而使用GROUP BY则可以使数据库系统能更快地解决问题。
关于嵌套子查询
有时候普通查询对我们来说是脆弱的，多表或者更多表的时候往往需要其他的一些办法，先不讨论外连接。和绝大多数本本一样，我建议不要轻易使用嵌套子查询。原因很简单，消耗效率。用SQLSERVER2000测试后发现两表之间用其他的查询方式，其效率是差不多的。但是如果使用了字查询，那么他的成本消耗将会成倍的增长。如：SELECT AID,BID,BNAME FROM A,B WHERE A.AID=B.BID；原来两表的效率是A+B。
如果使用了字查询没增加一个字段乘数就会加1，如：SELECT AID,(SELECT BID FROM B WHERE A.AID=B.BID),(SELECT BNAME FROM B WHERE A.AID=B.BID) FROM A；那么他的效率就是A+2*B。完成同样的目的却有截然不同的结果，所以请慎用嵌套子查询。
上面一个例子也许有些牵强，因为没有人会用第二种方法，但是我想突出的是如果是多表查询的话，也要注意能不用尽量不用。（多表可以考虑使用外连接）
外连接内连接
根据性能测试发现内连接的性能高于外连接。如下两句语句：select * from abm1008,abm1009,tbl_headmaterial where abm1008.abmfldid=abm1009.abmfldid and fld_abm8097=material_no（内连接）①
与
select * from abm1008
left join (select * from abm1009 ) as abm1009 on abm1008.abmfldid=abm1009.abmfldid
left join (select * from tbl_headmaterial) as tbl_headmaterial on abm1009.fld_abm8097=tbl_headmaterial.material_no（外连接）②
完成相同的任务只有一点差异，但是内连接的效率明显要于外连接。
配置P42.4G 512M内存测试结果：
300次运行平均数据 ① ②
客户端处理累计时间 4.6087ms 42.5085ms
服务器应答累计等待时间 465.85ms 629.678ms
CPU消耗 24 78
I/O消耗 112 122
但是有时候不得已还是会使用外连接，所以要看情况，能使用内连接不使用外连接，如果一定要，就选择表比较小的而且最好有建立索引的进行连接。
3．小结
本章主要讲述查询的优化，无论您的数据库软件优化器是否强大，这都是需要的。在您的数据库运行速度不理想但有无法优化的情况下，这个是最节约成本的方法之一。
学习任何东西要去教条的学习，绝不能生搬硬套。要使用索引，但要考虑是否值得？过多的索引只会是数据库负担更大。用多少，什么时候用，关键看各位在实际工作中的经验累计。根据现实状况来选择查询语句的优化程度才是正确的方法。
askyin 2006-09-25
- 打赏
- 举报
范围太广了 我之前写过一个东西，是给公司新人教育训练用的 不知道对你有没有帮助。
我知道写的不怎么样，（我可没出来献）各位大侠千万不要骂我啊！！！ 只是给楼主一点资料
SELECT语句优化设计
1．数据库查询语句优化的重要性概述
优化概述
其实我们发现很多人只是需要从一个数据库中检索出信息。这些人的数量比从事数据库编程和应用的人要多的多。而且数据查询的效率直接影响软件的效率和企业的运营。SQL语句是在程序开发阶段就已经决定了的由于低效率的SQL语句给数据库性能带来的问题，往往在数据库运行一段时间后才凸现出来，但发现后就变的难以改变，成为不可突破的性能瓶颈。
因为几乎所有数据库都不可避免的运行一些效率低下的SQL语句。对数据库性能的调整，往往是从SQL语句优化开始。
SQL语句的优化可以使数据库减少负荷，提高使用效率 ，相对于硬件升级是比较经济的一种方法。
优化规则
绝大多数情况下，我们并不需要去关心SQL语句是怎么执行的，这是因为当前流行的数据库软件中都无一例外的采用了高性能的优化器，而这些优化器在绝大多数的情况下都能将用户某些不合理的SQL语句结构转换成更合理的形式，从而有效的提高SQL的执行效率。
优化器的优化原则有两种：
1．成本的优化：对一条SQL语句，优化器会生成所有可能的执行方式，估计这些执行方式将使用的硬件成本，相互比较之后从中选择成本最低的执行计划。缺点是必须收集大量统计数据，给服务器造成额外的负担。
2．基于规则的优化：相对于上面的优化原则，规则优化显得死板的多。它只是根据预先设计好的规则进行运算，我们知道在现实生活中，预先设计好的规则有时候都是很没效率。
我知道写的不怎么样，（我可没出来献）各位大侠千万不要骂我啊！！！ 只是给楼主一点资料
SELECT语句优化设计
1．数据库查询语句优化的重要性概述
优化概述
其实我们发现很多人只是需要从一个数据库中检索出信息。这些人的数量比从事数据库编程和应用的人要多的多。而且数据查询的效率直接影响软件的效率和企业的运营。SQL语句是在程序开发阶段就已经决定了的由于低效率的SQL语句给数据库性能带来的问题，往往在数据库运行一段时间后才凸现出来，但发现后就变的难以改变，成为不可突破的性能瓶颈。
因为几乎所有数据库都不可避免的运行一些效率低下的SQL语句。对数据库性能的调整，往往是从SQL语句优化开始。
SQL语句的优化可以使数据库减少负荷，提高使用效率 ，相对于硬件升级是比较经济的一种方法。
优化规则
绝大多数情况下，我们并不需要去关心SQL语句是怎么执行的，这是因为当前流行的数据库软件中都无一例外的采用了高性能的优化器，而这些优化器在绝大多数的情况下都能将用户某些不合理的SQL语句结构转换成更合理的形式，从而有效的提高SQL的执行效率。
优化器的优化原则有两种：
1．成本的优化：对一条SQL语句，优化器会生成所有可能的执行方式，估计这些执行方式将使用的硬件成本，相互比较之后从中选择成本最低的执行计划。缺点是必须收集大量统计数据，给服务器造成额外的负担。
2．基于规则的优化：相对于上面的优化原则，规则优化显得死板的多。它只是根据预先设计好的规则进行运算，我们知道在现实生活中，预先设计好的规则有时候都是很没效率。
