27,582
社区成员
发帖
与我相关
我的任务
分享【讨论】数据库优化（升星散分）
就数据库的性能优化请各位高手发表高见，
可以是纯理论方面：比如索引的设计、宽表与窄表的比较、页面存储的基本原理等。
也可以是应用实践：假设性能应用场景，提出几种不同设计，分析优缺点，也可以是分析语句的性能，提出高性能语句的基本原则。
为表示感谢，300分奉上。
ps：纯顶无分。
ps: 纯恭喜贴本人表示感谢，但一人一贴足已。
可以是纯理论方面：比如索引的设计、宽表与窄表的比较、页面存储的基本原理等。
也可以是应用实践：假设性能应用场景，提出几种不同设计，分析优缺点，也可以是分析语句的性能，提出高性能语句的基本原则。
为表示感谢，300分奉上。
ps：纯顶无分。
ps: 纯恭喜贴本人表示感谢，但一人一贴足已。
...全文
请发表友善的回复…
发表回复
zhnzzy 2008-03-31
- 打赏
- 举报
学习研究
云飞扬77 2008-03-27
- 打赏
- 举报
如果有应用场景就好了
xiaomeixiang 2008-03-27
- 打赏
- 举报
宽表与窄表的比较
-------------------------------------
理论上说是窄表好，可是在实际应用时往往都是宽表用的多，如果数据冗余过多还是分出表比较好。呵呵。。。
具体情况具体分析吧，不过逻辑关系一定要清晰。。。
-------------------------------------
理论上说是窄表好，可是在实际应用时往往都是宽表用的多，如果数据冗余过多还是分出表比较好。呵呵。。。
具体情况具体分析吧，不过逻辑关系一定要清晰。。。
xiaoku 2008-03-27
- 打赏
- 举报
[Quote=引用 13 楼 zhuyx808 的回复:]
tempdb.mdf疯狂增长怎么搞定？1G/min 的速度
出了什么问题？如何解决？
[/Quote]
这个似乎有很多种情况.
在存储过程中的事务会使用，另外就是 存储过程中的临时表等...
tempdb.mdf疯狂增长怎么搞定？1G/min 的速度
出了什么问题？如何解决？
[/Quote]
这个似乎有很多种情况.
在存储过程中的事务会使用，另外就是 存储过程中的临时表等...
zjcxc 2008-03-27
- 打赏
- 举报
1.少用游标.由其是游标结果集很大的时候
-----------
这个不准确, 如果更新 100 万条记录, 我宁愿用游标, 或者一次更新几千条
一次更新的事务开销太大, 弄不好服务器就被你跑死了
-----------
这个不准确, 如果更新 100 万条记录, 我宁愿用游标, 或者一次更新几千条
一次更新的事务开销太大, 弄不好服务器就被你跑死了
nextflying 2008-03-27
- 打赏
- 举报
顶顶
zhuyx808 2008-03-27
- 打赏
- 举报
小梁的头像一天一变啊
viva369 2008-03-27
- 打赏
- 举报
支持11楼~
zhuyx808 2008-03-27
- 打赏
- 举报
问完问题在来接个分
zhuyx808 2008-03-27
- 打赏
- 举报
tempdb.mdf疯狂增长怎么搞定？1G/min 的速度
出了什么问题？如何解决？
出了什么问题？如何解决？
liangCK 2008-03-27
- 打赏
- 举报
[Quote=引用 3 楼 dobear_0922 的回复:]
恭喜生猩
[/Quote]
恭喜生猩
[/Quote]
pt1314917 2008-03-27
- 打赏
- 举报
前几天自己整理的一个，平时开发过程中也尽量按照这些来做的。。有什么不妥的地方望各位指正。。
1.1. 操作符
1.1.1 慎用NOT IN， NOT IN会多次扫描表，使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.
1.1.2 使用in时，在in后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数。
1.1.3 如果使用了IN或者OR等时发现查询没有走索引，使用显式申明指定索引，如：Select * From FA01(INDEX=IX_SEX) Where AA0107 IN(‘01’,‘02’)。
1.1.4 用OR的字句可以分解成多个查询，并且通过UNION连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
1.1.5 Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。
select * from chineseresume where title in ('男','女')
Select * from chineseresume where between '男' and '女'
是一样的。由于in会在比较多次，所以有时会慢些。
1.2. 查询
1.1.1 注意UNion和UNion all的区别。UNION比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
1.1.2 查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量使用联接查询代替子查询。。如外联接、内联接等等。。
1.1.3 没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNION ALL一样的道理。
1.1.4 一般在GROUP BY和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：
select的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By和Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快。
1.1.5 在大数据量的查询输出SELECT语句中尽量不要使用自定义函数，调用自定义函数的函数时系统调用是一个迭代过程，很影响查询输出性能的。在查询字段时尽可能使用小字段量输出，并在WHERE子句或者使用SELECT TOP 10/1 PERCENT来限制返回的记录数，使用SET ROWCOUNT来限制操作的记录数，避免整表扫描。返回不必要的数据，不但浪费了服务器的I/O资源，加重了网络的负担，如果表很大的话，在表扫描期间将表锁住，禁止其他的联接访问，后过很严重的。
1.1.6 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
1.1.7 注意WHERE子句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序一致，范围从大到小。尽量不要在WHERE子句中的“=”左边进行函数、算术或其他表达式运算，否则系统可能无法正确使用索引。尽量使用“>=”,不使用“>”。
1.1.8 尽量使用EXISTS代替SELECT COUNT（1）来判断是否存在记录，COUNT函数只有在统计表中所有行数时使用，而且COUNT（1）比COUNT（*）效率更高。
1.3. 临时表
1.1.1 慎用临时表，临时表存储于tempdb库中，操作临时表时，会引起跨库操作。尽量用结果集和表变量来代替它。
1.1.2 当用SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示声明语句，在另一个连接中SELECT * from sysobjects可以看到 SELECT INTO 会锁住系统表， Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事务内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。
1.1.3 在新建临时表时，如果一次性插入数据量很大，那么可以使用SELECT INTO代替CREATE TABLE避免LOG，提高速度；数据量不大时为了缓和系统表的资源，建议先CREATE TABLE然后INSERT。在使用了临时表后务必将所有的临时表显式删除，先TRUNCATE TABLE然后DROP TABLE，这样可以避免系统表的较长时间锁定。
1.4. 存储过程、视图、函数
1.1.1 在存储过程、函数中定义参数时，将参数的大小设置为合适即可，勿设置太大。这样开销很大。另外切忌用游标，效率太低。
1.1.2 不要在一段SQL或者存储过程中多次使用相同的函数或相同的查询语句，这样比较浪费资源,建议将结果放在变量里再调用。这样更快。
1.1.3 尽量少用视图和函数，用存储过程代替。存储过程是编译好、优化过，并且被组织到一个执行规划里、且存储在数据库中的 SQL语句，是控制流语言的集合，速度当然快。
1.5. 索引
1.1.1 创建合理的索引，对于插入或者修改比较频繁的表，尽量慎用索引。因为如果表中存在索引，插入和修改时也会引起全表扫描。索引一般使用于where后经常用作条件的字段上。
1.1.2 根据查询条件建立优化的索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建里索引（参照索引的创建），不要对有限的几个值的字段建立单一索引（如性别字段）。
1.1.3 如果使用LIKE进行查询的话，简单的使用INDEX是不行的，全文索引又太耗费空间。LIKE ‘N%’使用索引，LIKE ‘%N’不使用索引。用LIKE‘%N%’查询时，查询耗时和字段值总长度成正比，所以不能用CHAR类型而采用VARCHAR。对于字段的值很长的字段建立全文索引。
1.1.4 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引；尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过INDEX INDEX_NAMEl来强制指定索引。避免对大表查询时进行 TABLE SCAN，必要时考虑新建索引。
1.1.5 在使用索引字段作为条件时，如果该索引是联合索引，则必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。
1.1. 操作符
1.1.1 慎用NOT IN， NOT IN会多次扫描表，使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.
1.1.2 使用in时，在in后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数。
1.1.3 如果使用了IN或者OR等时发现查询没有走索引，使用显式申明指定索引，如：Select * From FA01(INDEX=IX_SEX) Where AA0107 IN(‘01’,‘02’)。
1.1.4 用OR的字句可以分解成多个查询，并且通过UNION连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
1.1.5 Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。
select * from chineseresume where title in ('男','女')
Select * from chineseresume where between '男' and '女'
是一样的。由于in会在比较多次，所以有时会慢些。
1.2. 查询
1.1.1 注意UNion和UNion all的区别。UNION比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
1.1.2 查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量使用联接查询代替子查询。。如外联接、内联接等等。。
1.1.3 没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNION ALL一样的道理。
1.1.4 一般在GROUP BY和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：
select的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By和Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快。
1.1.5 在大数据量的查询输出SELECT语句中尽量不要使用自定义函数，调用自定义函数的函数时系统调用是一个迭代过程，很影响查询输出性能的。在查询字段时尽可能使用小字段量输出，并在WHERE子句或者使用SELECT TOP 10/1 PERCENT来限制返回的记录数，使用SET ROWCOUNT来限制操作的记录数，避免整表扫描。返回不必要的数据，不但浪费了服务器的I/O资源，加重了网络的负担，如果表很大的话，在表扫描期间将表锁住，禁止其他的联接访问，后过很严重的。
1.1.6 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
1.1.7 注意WHERE子句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序一致，范围从大到小。尽量不要在WHERE子句中的“=”左边进行函数、算术或其他表达式运算，否则系统可能无法正确使用索引。尽量使用“>=”,不使用“>”。
1.1.8 尽量使用EXISTS代替SELECT COUNT（1）来判断是否存在记录，COUNT函数只有在统计表中所有行数时使用，而且COUNT（1）比COUNT（*）效率更高。
1.3. 临时表
1.1.1 慎用临时表，临时表存储于tempdb库中，操作临时表时，会引起跨库操作。尽量用结果集和表变量来代替它。
1.1.2 当用SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示声明语句，在另一个连接中SELECT * from sysobjects可以看到 SELECT INTO 会锁住系统表， Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事务内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。
1.1.3 在新建临时表时，如果一次性插入数据量很大，那么可以使用SELECT INTO代替CREATE TABLE避免LOG，提高速度；数据量不大时为了缓和系统表的资源，建议先CREATE TABLE然后INSERT。在使用了临时表后务必将所有的临时表显式删除，先TRUNCATE TABLE然后DROP TABLE，这样可以避免系统表的较长时间锁定。
1.4. 存储过程、视图、函数
1.1.1 在存储过程、函数中定义参数时，将参数的大小设置为合适即可，勿设置太大。这样开销很大。另外切忌用游标，效率太低。
1.1.2 不要在一段SQL或者存储过程中多次使用相同的函数或相同的查询语句，这样比较浪费资源,建议将结果放在变量里再调用。这样更快。
1.1.3 尽量少用视图和函数，用存储过程代替。存储过程是编译好、优化过，并且被组织到一个执行规划里、且存储在数据库中的 SQL语句，是控制流语言的集合，速度当然快。
1.5. 索引
1.1.1 创建合理的索引，对于插入或者修改比较频繁的表，尽量慎用索引。因为如果表中存在索引，插入和修改时也会引起全表扫描。索引一般使用于where后经常用作条件的字段上。
1.1.2 根据查询条件建立优化的索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建里索引（参照索引的创建），不要对有限的几个值的字段建立单一索引（如性别字段）。
1.1.3 如果使用LIKE进行查询的话，简单的使用INDEX是不行的，全文索引又太耗费空间。LIKE ‘N%’使用索引，LIKE ‘%N’不使用索引。用LIKE‘%N%’查询时，查询耗时和字段值总长度成正比，所以不能用CHAR类型而采用VARCHAR。对于字段的值很长的字段建立全文索引。
1.1.4 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引；尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过INDEX INDEX_NAMEl来强制指定索引。避免对大表查询时进行 TABLE SCAN，必要时考虑新建索引。
1.1.5 在使用索引字段作为条件时，如果该索引是联合索引，则必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。
pgy8288 2008-03-27
- 打赏
- 举报
看大虾的观点
pxpsoft 2008-03-27
- 打赏
- 举报
超大型数据库的大小常常达到数百GB，有时甚至要用TB来计算。而单表的数据量往往会达到上亿的记录，并且记录数会随着时间而增长。这不但影响着数据库的运行效率，也增大数据库的维护难度。除了表的数据量外，对表不同的访问模式也可能会影响性能和可用性。这些问题都可以通过对大表进行合理分区得到很大的改善。当表和索引变得非常大时，分区可以将数据分为更小、更容易管理的部分来提高系统的运行效率。如果系统有多个CPU或是多个磁盘子系统，可以通过并行操作获得更好的性能。所以对大表进行分区是处理海量数据的一种十分高效的方法。
来自http://www.cnblogs.com/13590/archive/2007/07/09/810770.html
Leo1734 2008-03-27
- 打赏
- 举报
[Quote=引用 7 楼 kelph 的回复:]
lz,散分贴和讨论贴分开吧，也各归各的版块
散分就是散分，大家乐乐
讨论就是讨论，严谨认真
[/Quote]
同意！
lz,散分贴和讨论贴分开吧，也各归各的版块
散分就是散分，大家乐乐
讨论就是讨论，严谨认真
[/Quote]
同意！
wawa_200 2008-03-27
- 打赏
- 举报
谢谢楼上的。。学习不少
-狙击手- 2008-03-27
- 打赏
- 举报
1.1.6 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
---
并发、更新，一致性如何 保证
---
并发、更新，一致性如何 保证
-狙击手- 2008-03-27
- 打赏
- 举报
[Quote=引用 18 楼 zjcxc 的回复:]
1.少用游标.由其是游标结果集很大的时候
-----------
这个不准确, 如果更新 100 万条记录, 我宁愿用游标, 或者一次更新几千条
一次更新的事务开销太大, 弄不好服务器就被你跑死了
[/Quote]
1.少用游标.由其是游标结果集很大的时候
-----------
这个不准确, 如果更新 100 万条记录, 我宁愿用游标, 或者一次更新几千条
一次更新的事务开销太大, 弄不好服务器就被你跑死了
[/Quote]
昵称被占用了 2008-03-27
- 打赏
- 举报
恭喜
问题太大
问题太大
csshan 2008-03-27
- 打赏
- 举报
数据文件存储，数据文件和日志文件放到不同的disk上，数据初始化文件大小，增长文件大小及比例，也要注意哦
加载更多回复（7）
