数据库优化系列（转）

数据库的优化措施 索引优化
索引的作用就类似于书的目录，书的目录会按照章节的顺序排列，会指想某一张的位置。这样如果在一本数百页的书里面查找某个章节位置的时候，我们就可以只扫描书的目录，扫描的范围缩小了n倍，查询的效率自然就提高了。另外在sql server内存够用的情况下索引会被放到内存中，在内存中查找自然又会提高效率；所以我们必须得合理利用索引。

1）对什么列建索引
数据库默认情况下会对主键建聚集索引，除了这个索引之外还需要在哪些列上建索引呢？这个问题只能具体情况具体分析，要看需要优化的sql语句（通常是查询次数多，查询相应想要高的语句），根据什么列的条件进行查询。

例如：在论坛的数据库中有一张表是帖子回复表，在论坛的应用中用到最多的就是对指定帖子的某一页的回复进行查询，查询回复表的条件是主贴的id；这时候在主贴字段上建索引就势在必然。

2）一定要在主键上建聚集索引吗
通常情况下sql server会自动给主键加上聚集索引，但也有一些例外的情况我们需要把聚集索引建在其他列上，例如我们用到了表分区，而分区的字段不是主键，这时候就需要将聚集索引建在分区的列上。另外如果查询时根据主键查询较少，而根据其他列的查询较频繁，则也可以考虑将聚集索引建在非主键上。单需要注意的是聚集索引的列必须是不易变的列，如果聚集索引变了一会引起聚集索引内的记录的搬迁，造成页page的分离与碎片；二会引起每一个非聚 集索引被修改，以便于所有相关的非聚集索引的行的索引键的值被纠正。这既浪费时间和空间，也导致需要整理的碎片，增加了不必要的开销(每个列重组聚集键)。

3）复合索引（索引有两个以上的列）要注意列顺序
索引在数据库中是以B树的形式存储的。包含A,B两个列的索引会首先根据A列建B树，A列的叶节点上才会开始根据B列建B树。所以包含两个列的索引就需要根据查询条件所在列来决定两个列在索引中的顺序。

可以用下面的sql做实验：

show sourceview sourceprint?01 USE [Test] 

02 GO 

03 /****** 对象:  Table [dbo].[testIndexOrder]    脚本日期: 05/27/2010 09:11:26 ******/ 

04 SET ANSI_NULLS ON

05 GO 

06 SET QUOTED_IDENTIFIER ON

07 GO 

08 CREATE TABLE [dbo].[testIndexOrder]( 

09  [ID] [int] IDENTITY(1,1) NOT NULL, 

10  [FirstName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL, 

11  [LastName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL, 

12  [Desc] [nvarchar](400) COLLATE Chinese_PRC_CI_AS NULL, 

13  CONSTRAINT [PK_testIndexOrder] PRIMARY KEY CLUSTERED  

14 ( 

15  [ID] ASC

16 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] 

17 ) ON [PRIMARY] 

18 GO 

19 /****** 对象:  Index [IX_testIndexOrder]    脚本日期: 05/27/2010 09:11:51 ******/ 

20 CREATE NONCLUSTERED INDEX [IX_testIndexOrder] ON [dbo].[testIndexOrder]  

21 ( 

22  [FirstName] ASC, 

23  [LastName] ASC

24 )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] 

25 GO 

26 declare @i INT; 

27 DECLARE @random varchar(36); 

28 set @i = 0; 

29 while @i < 100000 

30  begin

31  set @random = newid(); 

32   

33  INSERT INTO [testIndexOrder] 

34  (FirstName,LastName,[Desc]) 

35  VALUES( 

36   substring(@random,1,8),substring(@random,12,8),@random 

37  ); 

38  set @i = @i + 1 

39 end

40   

41   

42 set statistics time on

43 select * from [testIndexOrder] where lastname = '6F-4ECA-'

44 select * from [testIndexOrder] where firstname = 'CAABE009'

45 set statistics time off

4）索引的个数问题
索引提高查询效率是以降低更新、插入、删除的速度为代价的。每当索引列发生变化时都需要对索引数据进行相应的调整。所以一个表上不可以建太多的索引，除非你完全不在乎修改数据的效率。另外sql server本身会对索引的数量和索引的数据长度有限制，具体请参考

5）在必要时重建索引
Sql server运行一段时间之后就会形成一些索引碎片，这时候就需要重建索引了，有时候重建索引可以起到意想不到的效果。

查看索引碎片，重建索引，可以通过sql server管理器来重建；也可以通过下面的sql语句来实现：

view sourceprint?1 --显示表testIndexOrder的索引碎片情况 

2 DBCC SHOWCONTIG(testIndexOrder) 

3   

4 --重建表的索引 

5 --第一个参数，可以是表名，也可以是表ID。 

6 --第二个参数，如果是''，表示影响该表的所有索引。 

7 --第三个参数，填充因子，即索引页的数据填充程度。如果是，表示每一个索引页都全部填满，此时select效率最高，但以后要插入索引时，就得移动后面的所有页，效率很低。如果是，表示使用先前的填充因子值。 

8 DBCC DBREINDEX(testIndexOrder，''，)

数据库优化是一门复杂的学问，需要不断的学习实践，积累经验。

--------------------------------------------------------
数据库优化 分字诀上 分库

三国讲：话说天下大势，分久必合，合久必分；我们的数据库优化也需要这个“分”字。

当我们的数据量很小的时候，我们会把用户表，博客表，论坛表，闪存表等等都砸在一个库里，我们的业务增长的很好，在不久之后我们尽力的优化了查询，但是效果依然不佳，这时候用分字诀的时机到了。

如果你有先见之明的话，会给表名，存储过程的名字加上前缀，例如论坛表命名为BBS_xxx，博客表命名为BLOG_xxx；这样的话在分表处理时会比较容易一些。说到这儿也许你会想到外键约束怎么办，我的博客表，论坛帖子表都有用了User表的主键做外键呀。这个很容易处理，我们需要当机立断的删掉外键，这个当机立断可能会带来一些麻烦，我们来分析下可能会遇到一些什么问题：

1. 分成多个库，没了外键了以前的inner join操作要跨库吗？
假定场景：博客表有对用户表的外键引用，我们需要在首页显示博客列表，博客列表需要显示用户名和用户id的信息
之前用户表，博客表在一个库里面的时候我们可以通过外键inner join来取得用户的关联信息，现在用户库和博客库被拆成了两个库，我想对跨库做inner join说no；为什么呢，因为这不适合扩展，假如有一天我们的业务量又增长了我们就需要把用户库挪到另外一台机器上，这要导致inner join跨服务器了，这显然不是一个好办法，那该怎么办呢？ 我有两种方案，大家评判好坏：
1）做违反范式的设计，将用户的不变信息用户名和用户id一起存在博客表中，让用户名冗余吧，这样做可以保证取博客数据连带用户名时是非常高效率的
2）我们不再从数据库中取用户名的信息，改从缓存中取，我们可以在缓存中形成一个最近活跃的用户数据池，当我们需要用户名时从这个缓存区中去取。

目前在我的应用中用的是第一种方案，第二种更有伸缩性，第一种存冗余数据只能存用户名，有时候只存用户名就够了，有时候可能会出现不够的问题。

2. 如果用到了根据外键做的级联删除，那这是一个噩梦
对付这个问题，我的方案是修改程序，如果需要级联删除，在程序逻辑中完成，不要在数据库做级联删除了，级联删除是一种隐含在数据库中的逻辑，是一种不好的设计方案。

3. 触发器也可能带来和外键做级联删除同样的麻烦，同样的也是修改程序逻辑，代替这种数据库级别的隐含逻辑。

也许你会说分库之后一定会带来性能的提高吗？这个问题得具体分析，这要看你的服务器性能如何，如果分库之后数据库的cpu，io，内存的压力依然很大；那么您可以将分库之后的其中某一个库迁移到另外一台服务器上，让两台服务器分摊数据访问的压力，肯定会提升性能的。

最后说下，分库分与不分是由数据量、性能要求决定的。下篇分表敬请期待！

---------------------------------------------------------
数据库优化 分字诀 分表（纵向拆分，横向分区）
1. 纵向分表
纵向分表是指将一个有20列的表根据列拆分成两个表一个表10列一个表11列，这样单个表的容量就会减少很多，可以提高查询的性能，并在一定程度上减少锁行，锁表带来的性能损耗。

纵向分表的原则是什么呢，应该怎样拆分呢？答案是根据业务逻辑的需要来拆分，对于一张表如果业务上分两次访问某一张表其中一部分数据，那么就可以根据每次访问列的不同来做拆分; 另外还可以根据列更新的频率来拆分，例如某些列每天要更新3次，有些列从创建开始基本上很少更新。

举例：
假定场景，我有一张用户表，这张表包含列：
ID, UserName, Password, RealName, Gender, Email, IsEmailValid, Birthday, Country, City, Address, Mobile, Phone, ZipCode, Hometown, OfficePhone, Company, Position, Industry, LatestLoginTime, LatestLoginIP, LoginTimes,OnlineMinutes

假定现在我们的登录出现了性能问题，用户登录经常出现数据库超时的现象。我们打算用拆表的方法解决这个问题。先看下涉及到登录的字段有：UserName,Password,LatestLoginTime,LatestLoginIP,LoginTimes；那么我们就可以以此为依据将原表拆分为：UserLogin和UserBase 两个表，后者包含除了登录信息的其他列信息；两张表都要包含主键ID。

2. 横向分区
横向分区是将表从行的角度拆分，例如将创建时间在05年之前的数据放在一个分区上，将05年到08年之间的数据放到另一个分区上，以此类推。横向分区所根据的列必须在聚集索引上，通常会根据时间，主键id等进行划分。

横向分区将数据划分为不同的区，在根据分区列条件进行查询时可以缩小查询的范围，从而提高查询的性能；另外如果数据库服务器有多个cpu，则可以通过并行操作获得更好的性能。

到底要根据那个列进行横向的分区和查询有关系，我们在建表的时候需要分析，会根据那个列进行查询。

举例：
1. 订单是一个实效性很强的实体，我们很少查询几年前的订单数据，我们就可以在订单的创建时间列上创建分区函数来做分区。
2. 比如帖子通常情况下只有在首页推荐的最新的帖子被访问次数很多，而几年前的帖子被访问的几率较小，这时候我们可以根据帖子的主键id来做分区，id小于300w的在一个分区上，id在300到600w之间的在一个分区上。
有关分区的更多信息，请参考高性能网站建设之 MS Sql Server数据库分区
有关如何进行分区，请参考Sql Server 分区演练

------------------------------------------------------------
数据库优化措施 使用主从库（上）
http://www.cnblogs.com/yukaizhao/archive/2010/06/02/sql-server-master-slave-mode.html

数据库优化措施 使用主从库（下）
在上一篇中我们配置好了主从库，现在我们尝试在程序中使用主从库。

主从库之间是一种发布订阅的关系，发布者和订阅者之间并非实时同步的，通常会有几分钟的延时，更有甚者会有几个小时的延时。所以我们需要通过合理的使用来避开有延时这个问题。

我们希望主库尽可能的少参与查询，来提高写的及时性；同时要让从库在不影响读出数据的准确及时的前提下尽可能的分担主库的压力。

主从两个库需要在配置文件中配置两个连接字符串，CONN_Master和CONN_Slave。我们需要设定一些规则决定当前的查询应该从主库查还是需要从从库查。这个规则没有定式，只能根据业务需要来确定。下面我举几个例子来说明：

1. 以豆瓣读书书的详细页为假定场景，你可以点击这里看下页面的结构（我不是豆瓣的技术，在这里只是拿这个页面举例）
我们来分析呈现这个页面需要的数据和这些数据的实效性要求
1） 书的详细信息 时效性要求：要求及时
2） 豆瓣成员的常用标签 实效性：不需要很及时
3） 喜欢读这本书的人也喜欢读的书 属于分析数据，不需要很及时
4） 最新书评 要求及时
5） 读这本书的几个用户 及时性不高
6） 喜欢这本书的人常去的小组 属于分析数据不需要很及时
从上面的分析可以看出只有1），4）两项数据需要从主库读，而2），3），5），6）为非及时数据从从库读取即可。当然我们可以对这些实效性不高的数据做缓存处理。

2. 以论坛帖子列表页面为假定场景，玩论坛的人都喜欢顶贴，把自己的帖子顶到第一页让更多的人关注，而对于50页之后的帖子则反读的人很少；我们可以根据这个业务逻辑特征来决定在用户访问前50页帖子列表数据时从主库读，而当用户访问超过50页之后的数据时则从从库进行查询。

3. 以订单为例，通常超过三个月的订单就不会再有变化了，假定我们把订单号设计为日期格式时，根据订单号去查询订单时就可以根据订单号来决定该访问主库还是从库。

举了几个适用的场景，我们以第三个场景为例，写一段简单的示意代码看下

view sourceprint?01 //orderNo 的格式为 20100528120105000001 即yyyyMMddHHmmss + 序号 

02 public OrderInfo GetOrder(string orderNo) { 

03     string connString = ConnStringGetter.GetForOrder(orderNo); 

04     using (SqlConnection conn = new SqlConnection(connString)) 

05     { 

06         ... 

07     } 

08 } 

09   

10 public class ConnStringGetter 

11 { 

12     public static string GetForOrder(string orderNo) {  

13         int year = int.Parse(orderNo.Substring(0,4)); 

14         int money = int.Parse(orderNo.Substring(4,2)); 

15         int date = int.Parse(orderNo.Substring(6,2)); 

16         DateTime orderTime = new DateTime(year, money, date); 

17   

18         TimeSpan ts = DateTime.Now - orderTime; 

19 //根据订单的时间决定使用主库还是从库 

20         if (ts.TotalDays > 30) return ConfigurationManager.ConnectionStrings["CONN_Slave"].ConnectionString; 

21         return ConfigurationManager.ConnectionStrings["CONN_Master"].ConnectionString; 

22     } 

23 }

正确的使用主从库，可以很好的提升系统的性能。使用主库还是从库的选择权决定在业务逻辑的手里。

---------------------------------------------------------------------------
缓字决 （上） 缓冲写数据
通常情况下Asp.Net 网站的底层数据存储都是关系数据库，关系数据库资源比较昂贵，而且也很容易造成瓶颈。缓字决文章就是为大家介绍如何有效使用缓存，异步写缓冲数据库的压力，从而保证网站的性能。

大家已经看到很多关于Asp.Net缓存的文章了。所以我写的时候要改变一下思路，从缓冲写数据开始说起。缓冲写数据的意思是在数据需要更新时不马上把数据存到数据库，而是先缓冲一下，然后在适当的时机再写入到数据库中。

缓冲写数据可以避免在网站并发访问多的时候，数据库瞬间承受过大压力，而造成死锁或响应不及时的情况。

那么什么时候适合缓冲写呢？是不是所有情况都适用呢？缓冲写会导致数据在内存中或者web server硬盘或者第三方存储中驻留一段时间，在这段时间内如果从数据库中查询最新数据的话，会有遗漏。大多数事物都有两面性，我们需要学会趋利避害；换句话说在保证缓冲写不会导致用户感觉数据缺少的情况下，或者在使用适当措施不让用户感觉数据缺失的情况下就可以使用缓冲写。我有两个具体的实例来介绍如何使用缓冲写：

1. Pv(页面浏览量)统计，大多数网站都有这个功能，有些网站还专门做这个服务
　　网站每有一个页面被浏览时，就会需要给对应页面的Pv+1；这种情况下，如果直接更新到数据库中，访问量稍微大一些就会造成数据库压力过大的问题。

　　所以我们需要对Pv计数做缓冲，在单web server的情况下我们可以在内存中维护一个hashtable，然后用一个异步的线程去定时扫描这个hashtable，当点击数达到“一定数字”时更新到数据库。听上去很简单，不过也需要一个小技巧，上一句话中说的“一定数字”四个字不能是随便的一个数字，如果它是4，试想一下会出现什么情况，我们的所有Pv数都会是4的倍数，用户会怀疑我们是不是在Pv上造假了；我们没有造假却留下了造假的迹象！这个“一定数字”必须是一个素数，我们可以取7，也可以用13，如果我们的访问量很大也可以取23或31；这样就不会出现“造假的迹象”了。

2. 发送站内短消息
　　站内短消息也是一个比较通用的模块，他可以说是一种离线消息，并非im即时消息，所以我们可以利用这个业务特性，　　来对发消息做下缓冲。当有短消息发送时我们可以先将这个短消息放到硬盘文件中，然后很快的响应用户，在ui上告诉用户，你的消息已经发出去了，然后我们可以用另一个线程（或者做一个windows服务）去监视缓冲短消息的目录顺序的将短消息存储到数据库中。

以上两个场景都是比较经典的利用缓冲写的例子，在现实中需要我们去具体分析业务和某个业务是否会造成数据库压力来决定是否缓冲写，如果业务本身对数据库的压力就很小，那当然就没必要考虑了，反之如果业务压力较大我们就需要做一些工作避免缓冲写的问题并利用缓冲写。