新手园地& & & 硬件问题Linux系统管理Linux网络问题Linux环境编程Linux桌面系统国产LinuxBSD& & & BSD文档中心AIX& & & 新手入门& & & AIX文档中心& & & 资源下载& & & Power高级应用& & & IBM存储AS400Solaris& & & Solaris文档中心HP-UX& & & HP文档中心SCO UNIX& & & SCO文档中心互操作专区IRIXTru64 UNIXMac OS X门户网站运维集群和高可用服务器应用监控和防护虚拟化技术架构设计行业应用和管理服务器及硬件技术& & & 服务器资源下载云计算& & & 云计算文档中心& & & 云计算业界& & & 云计算资源下载存储备份& & & 存储文档中心& & & 存储业界& & & 存储资源下载& & & Symantec技术交流区安全技术网络技术& & & 网络技术文档中心C/C++& & & GUI编程& & & Functional编程内核源码& & & 内核问题移动开发& & & 移动开发技术资料ShellPerlJava& & & Java文档中心PHP& & & php文档中心Python& & & Python文档中心RubyCPU与编译器嵌入式开发驱动开发Web开发VoIP开发技术MySQL& & & MySQL文档中心SybaseOraclePostgreSQLDB2Informix数据仓库与数据挖掘NoSQL技术IT业界新闻与评论IT职业生涯& & & 猎头招聘IT图书与评论& & & CU技术图书大系& & & Linux书友会二手交易下载共享Linux文档专区IT培训与认证& & & 培训交流& & & 认证培训清茶斋投资理财运动地带快乐数码摄影& & & 摄影器材& & & 摄影比赛专区IT爱车族旅游天下站务交流版主会议室博客SNS站务交流区CU活动专区& & & Power活动专区& & & 拍卖交流区频道交流区
UID空间积分0 积分556阅读权限20帖子精华可用积分556 信誉积分355 专家积分0 在线时间261 小时注册时间最后登录
丰衣足食, 积分 556, 距离下一级还需 444 积分
帖子主题精华可用积分556 信誉积分355 专家积分0 在线时间261 小时注册时间最后登录
论坛徽章:0
附件内容是游戏项目中用于生成物品数值的查询, 执行整个文件大约需要2小时, 但把查询一段一段复制到navicat上前后几分钟就执行完了.
查询主要卡连表update上, 表都建了索引, 因此执行单挑查询很快, 不解的是执行整个查询文件却很慢.
请各位大神不吝赐教, 谢谢.
查询很多是在excel中生成的, 比较恶心, 请见谅.
ps: 谢谢唐成老师在oc上的指教.
附件不能传sql文件, 压缩成rar了.
(3.67 KB, 下载次数: 9)
15:21 上传
下载次数: 9
&&nbsp|&&nbsp&&nbsp|&&nbsp&&nbsp|&&nbsp&&nbsp|&&nbsp
UID空间积分0 积分376阅读权限20帖子精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
稍有积蓄, 积分 376, 距离下一级还需 124 积分
帖子主题精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
论坛徽章:2
你是用psql -f sqlfile.sql这样执行的吗？
UID空间积分0 积分556阅读权限20帖子精华可用积分556 信誉积分355 专家积分0 在线时间261 小时注册时间最后登录
丰衣足食, 积分 556, 距离下一级还需 444 积分
帖子主题精华可用积分556 信誉积分355 专家积分0 在线时间261 小时注册时间最后登录
论坛徽章:0
是的, 但发现psql -f xx.sql花费的时间与把xx.sql全部内容复制到navicat执行的时间差不多.
UID空间积分0 积分376阅读权限20帖子精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
稍有积蓄, 积分 376, 距离下一级还需 124 积分
帖子主题精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
论坛徽章:2
看了你的脚本也看不出什么问题。而你的这个脚本只能在你的环境中运行（因为一些其它的表不在这个脚本中），所以也无法帮助调试。你可以自行调试，如你可以设置参数log_min_duration_statement = 10000，然后再运行你的脚本，然后运行时间超过10000毫秒(10秒)的SQL都会打印到日志中，然后你再分析一下这些SQL为什么慢。
UID空间积分0 积分556阅读权限20帖子精华可用积分556 信誉积分355 专家积分0 在线时间261 小时注册时间最后登录
丰衣足食, 积分 556, 距离下一级还需 444 积分
帖子主题精华可用积分556 信誉积分355 专家积分0 在线时间261 小时注册时间最后登录
论坛徽章:0
改log_min_duration_statement后把整个文件都打出来了, 感觉是pgsql把整个文件的东西当成一个超大事务来跑, 导致花费大量时间, 一行一行其实用不了多少时间的.
我把全部sql跟pgsql配置都打了个包, 按readme里面的步骤可以执行下去, 唐老师有空帮再看下, 不胜感激.
(194.58 KB, 下载次数: 9)
14:10 上传
下载次数: 9
UID空间积分0 积分376阅读权限20帖子精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
稍有积蓄, 积分 376, 距离下一级还需 124 积分
帖子主题精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
论坛徽章:2
我在我的服务器上试了一下，可以在5分16秒运行完：
$time psql -Uu01 -f 升星.sql & my.log
psql:升星.sql:5: NOTICE:&&table &star_config_part_attrib_quota& does not exist, skipping
psql:升星.sql:39: NOTICE:&&table &star_config_attack_increace_rate& does not exist, skipping
psql:升星.sql:85: NOTICE:&&table &star_config_defence_increace_rate& does not exist, skipping
psql:升星.sql:131: NOTICE:&&table &player_equip_star_value& does not exist, skipping
psql:升星.sql:328: NOTICE:&&table &sum_up_player_lv_reincarnation_equip_star& does not exist, skipping
psql:升星.sql:376: NOTICE:&&table &star_all_item_star_numeric& does not exist, skipping
real& & & & 5m16.772s
user& & & & 0m0.065s
sys& & & & 0m0.009s
UID空间积分0 积分376阅读权限20帖子精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
稍有积蓄, 积分 376, 距离下一级还需 124 积分
帖子主题精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
论坛徽章:2
在我的笔记本电脑上也只运行了16分钟：
osdba-mac:pgtest osdba$ time psql u01 -f 升星.sql & my2.log
psql:升星.sql:5: NOTICE:&&table &star_config_part_attrib_quota& does not exist, skipping
psql:升星.sql:39: NOTICE:&&table &star_config_attack_increace_rate& does not exist, skipping
psql:升星.sql:85: NOTICE:&&table &star_config_defence_increace_rate& does not exist, skipping
psql:升星.sql:131: NOTICE:&&table &player_equip_star_value& does not exist, skipping
psql:升星.sql:328: NOTICE:&&table &sum_up_player_lv_reincarnation_equip_star& does not exist, skipping
psql:升星.sql:376: NOTICE:&&table &star_all_item_star_numeric& does not exist, skipping
real& & & & 16m41.132s
user& & & & 0m0.207s
sys& & & & 0m0.012s
UID空间积分0 积分376阅读权限20帖子精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
稍有积蓄, 积分 376, 距离下一级还需 124 积分
帖子主题精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
论坛徽章:2
update player.sum_up_player_lv_reincarnation_equip_star x
health& && && &= a.health& && && &+ b.health,
magic& && && & = a.magic& && && & + b.magic,
physic_attack&&= a.physic_attack&&+ b.physic_attack,
magic_attack& &= a.magic_attack& &+ b.magic_attack,
daoshu_attack&&= a.daoshu_attack&&+ b.daoshu_attack,
physic_defence = a.physic_defence + b.physic_defence,
magic_defence&&= a.magic_attack& &+ b.magic_defence
player.sum_up_player_basic a,
equipment.player_equip_star_value b
where x.career = a.career
and a.career = b.career
and x.lv = a.lv
and a.lv = b.lv
and x.star = b.star
and a.god_type = b.god_type
可以加两个索引，加快更新的速度：
create index on player.sum_up_player_lv_reincarnation_equip_star(career, lv, star);
create index on equipment.player_equip_star_value(career, lv, star);
UID空间积分0 积分376阅读权限20帖子精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
稍有积蓄, 积分 376, 距离下一级还需 124 积分
帖子主题精华可用积分376 信誉积分147 专家积分0 在线时间319 小时注册时间最后登录
论坛徽章:2
在每个表的大规模更新后，加上analyze &tablename&;语句。因为每次更新后马上执行查询，统计信息还没有收集上来，会导致差的执行计划，所以要手工执行analyze &tablename&收集统计信息，保证后续的SQL的执行计划正确。而你分开执行快的原因是你等了一会儿，数据库自动帮你收集了统计信息的原因。我加上analyze &tablename&语句后，在我的笔记本上1分54秒就可以运行出来了：
osdba-mac:pgtest osdba$ time psql u01 -f 升星.sql & my2.log
real& & & & 1m54.936s
user& & & & 0m0.235s
sys& & & & 0m0.015s
加analyze的示例如下：
insert into equipment.player_equip_star_value(career, lv, god_type, star)
select a.career, a.lv, a.god_type, b.star
player.sum_up_player_basic a,
config.basic_star_open b
order by a.career, a.lv, a.god_type, b.star
create index on equipment.player_equip_star_value(career);
create index on equipment.player_equip_star_value(lv);
create index on equipment.player_equip_star_value(god_type);
create index on equipment.player_equip_star_value(star);
create index on equipment.player_equip_star_value(career, lv, star);
analyze equipment.player_equip_star_
UID空间积分0 积分556阅读权限20帖子精华可用积分556 信誉积分355 专家积分0 在线时间261 小时注册时间最后登录
丰衣足食, 积分 556, 距离下一级还需 444 积分
帖子主题精华可用积分556 信誉积分355 专家积分0 在线时间261 小时注册时间最后登录
论坛徽章:0
多谢唐老师, 问题解决了, 就是analyze的问题, 现在56秒跑完
谢谢谢谢...
北京皓辰网域网络信息技术有限公司. 版权所有 京ICP证:060528号 北京市公安局海淀分局网监中心备案编号：
广播电视节目制作经营许可证(京) 字第1234号
中国互联网协会会员&&联系我们：
感谢所有关心和支持过ChinaUnix的朋友们
转载本站内容请注明原作者名及出处转载：/blog/612414
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或&&操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
若要提高效率，可以考虑全文检索。
7.如果在 where
子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然
而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
select id from t where num=100*2
9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
select id from t where datediff(day,createdate,'')=0--&&生成的id
select id from t where name like 'abc%'
select id from t where createdate&='' and createdate&''
10.不要在 where 子句中的&=&左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
13.很多时候用 exists 代替 in 是一个好的选择：
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)
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为
insert 或 update
时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有
16.应尽可能的避免更新 clustered 索引数据列，因为 clustered
索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新
clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用 select * from t ，用具体的字段列表代替&*&，不要返回用不到的任何字段。
20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD
游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括&合计&的例程通常要比使用游标执行的速度快。如果开发时
间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
29.尽量避免大事务操作，提高系统并发能力。
30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
阅读(...) 评论()