[搜片神器]BT管理程序数据库速度调试优化问题

DHT抓取程序开源地址：https://github.com/h31h31/H31DHTDEMO

数据处理程序开源地址:https://github.com/h31h31/H31DHTMgr

服务器在抓取和处理同时进行，所以访问速度慢是有些的，特别是搜索速度通过SQL的like来查询慢，正在通过分词改进中。。

 ---------------------------------------------------------------------------------------------------

当数据库里面很多表的数据都有300万左右,总文件大小有8G大小的时候,发现新增加的数据速度比原来慢很多的问题,一些性能和速度优化的问题就必须进行考虑了.

因为服务器还需要跑网站,查询速度与程序插入速度会导致人多会越来越慢.

 

1.目前数据操作流程是通过HASH值查询数据库里面是否,如果没有,则直接插入,如果有,则直接更新此条纪录的计数.

2.由于纪录表采用ID与文件列表进行关联.设置ID为主键值,只对hashkey进行了唯一键的约束索引设计,设计的表结构如图所示:

3.没有处理经验之前,只有增加日志输出信息.

[2013-9-5 23:55:02]: 2:2:线程[4]>>>>67F8DAC16B2ACB5CC79BDD02F7478457E99C5966更新到数据库1010成功1TIME:0-78-0-140
[2013-9-5 23:55:02]: 2:2:线程[1]>>>>57D55712F097DFDA3F3204C3E35B59461CCFE851更新到数据库1011成功1TIME:140-109-0-219
[2013-9-5 23:55:03]: 2:2:线程[3]>>>>720A898E7C76CD5AD0B7B379D3CD97329EC9BDFE更新到数据库4011成功4TIME:0-422-0-16
[2013-9-5 23:55:03]: 2:2:线程[4]>>>>7B534EAFF508F861B8B1E5A5D79D9C11F1655B43更新到数据库1011成功1TIME:0-78-16-31
[2013-9-5 23:55:03]: 2:2:线程[5]>>>>9AAF76DE08F2ACA7DEDD11B139EE76798591D30F更新到数据库1011成功1TIME:0-485-0-31
[2013-9-5 23:55:03]: 2:2:线程[1]>>>>6D8C1ACA280773A40958A4D3B4FF5DA447651C14更新到数据库1011成功1TIME:140-485-0-15
[2013-9-5 23:55:03]: 2:2:线程[2]>>>>6A511827CD07A9FB725AA9DF317DE180B342A4A4更新到数据库1011成功1TIME:407-94-15-78
[2013-9-5 23:55:03]: 2:2:线程[4]>>>>95A6DAF234532E10012169372448096544D58D68更新到数据库1011成功1TIME:0-94-0-109
[2013-9-5 23:55:03]: 2:2:线程[3]>>>>C0A3C675DE7848F19E1BACE2DFE729D825580290更新到数据库3011成功3TIME:0-62-0-47
[2013-9-5 23:55:04]: 2:2:线程[5]>>>>DC82DDB68F6F5F0CB310726CD9A3A382A8549802更新到数据库2010成功2TIME:0-31-0-16
[2013-9-5 23:55:04]: 2:2:线程[1]>>>>3CBB82952AA59A020388415B299AA79B46CCF7DF更新到数据库1011成功1TIME:140-62-0-32
[2013-9-5 23:55:04]: 2:2:线程[4]>>>>BF59A4902E6424A84418239F7BE670CBBF84ED03更新到数据库1010成功1TIME:0-47-0-110
[2013-9-5 23:55:04]: 2:2:线程[1]>>>>3A804B13102E6C5B427F7E1F0F472A88F64A225C更新到数据库1011成功1TIME:140-78-0-16
[2013-9-5 23:55:04]: 2:2:线程[5]>>>>EBE9FD044F8A07FF8E157B98BF8BE10F6977A570更新到数据库1010成功1TIME:0-94-0-93
[2013-9-5 23:55:04]: 2:2:线程[5]>>>>70CC8C9B5EDE1F5673B7A4B684219F39E75DB660更新到数据库1011成功1TIME:0-31-0-32
[2013-9-5 23:55:05]: 2:2:线程[2]>>>>CAEAF85ED0D3A9DEA7DA2EC75445565368F51E63更新到数据库6011成功6TIME:407-328-0-47

TIME:[读取文本时间与此无关]-[查询HASH存在否]-[更新纪录计数值]-[写入IP等信息到IP表]

平均的查询hashkey的速度在100ms左右,还是比较费时的.而且SQLSERVER进程CPU占用经常在30%左右,到了必须需要考虑的问题了.

程序查询语句:

string tempstr1 = string.Format("select top 1 ID,Detail from H31_DHT_TYPE_{0}_{1} where hashKey='{2}'", hashtype, isHanzhi, hashname);

目前发现查询HASH是否存储的时间比较大,如何进行优化呢?

从网上搜索数据库优化经验:

数据库优化目标
1.避免磁盘I/O瓶颈、
2.减少CPU利用率
3.减少资源竞争。
角度：
基本表设计、扩展设计和数据库表对象放置
方法：
1.数据库设计
2.索引
3.避免长事务
4.存储过程
5.sql优化。

1.开始分析的思路是否是在多表之间进行跳转更新查询,从而影响查询速度?  

2.数据库索引是否设置正确?如果将主索引移动到hashkey上是否可以?由于之前考虑自增的ID上进行主索引会方便很多,毕竟hashkey是40字节,索引起来会更慢的问题.

抱着试一下的态度,对hashkey设置主键,

设置后服务器上程序查询速度增加了很快,统计信息如下:

[2013-9-6 0:50:43]: 2:2:线程[3]>>>>1EE9C3804EF32A5A6EA9ACA18948B426C44BC9CA更新到数据库1011成功1TIME:0-15-0-16
[2013-9-6 0:50:43]: 2:2:线程[2]>>>>46E527F7CE92F958688F40CD30F4D068ECCF0BDC更新到数据库1011成功1TIME:171-0-0-15
[2013-9-6 0:50:43]: 2:2:线程[1]>>>>F865CBCEA3D97F4D8706D8EEC5D1D2C3E5B93CDF更新到数据库4010成功4TIME:0-47-0-31
[2013-9-6 0:50:43]: 2:2:线程[3]>>>>46F08974CB1861B5A18BDA403F12DF3BF72BD990更新到数据库1011成功1TIME:0-0-0-16
[2013-9-6 0:50:43]: 2:2:线程[5]>>>>2E0CB6E4A3CAF3DF42FE063DAA4C0B2DED0E393A更新到数据库3011成功3TIME:0-31-16-31
[2013-9-6 0:50:43]: 2:2:线程[4]>>>>2889CAB940CD696A645868FACC3893774A1C6252更新到数据库3010成功3TIME:15-47-0-31
[2013-9-6 0:50:43]: 2:2:线程[1]>>>>9DC1DE71F39C237B264EE8FDE618642F20F0610C更新到数据库1011成功1TIME:0-0-0-32
[2013-9-6 0:50:43]: 2:2:线程[2]>>>>23710902E6EB72D8099ECF39CEFD6B17DF942AFC更新到数据库1011成功1TIME:171-15-0-32
[2013-9-6 0:50:43]: 2:2:线程[3]>>>>4E8D7DA699BD577C2AA06B71D173049657067DB7更新到数据库1011成功1TIME:0-0-0-16
[2013-9-6 0:50:44]: 2:2:线程[1]>>>>FD410EE7433B4796D611D2BF7F942029DFEEC0A9更新到数据库1011成功1TIME:0-0-0-31
[2013-9-6 0:50:44]: 2:2:线程[5]>>>>B62136A52A711275BE375592B377DC8F28AB35A5更新到数据库2011成功2TIME:0-62-16-16
[2013-9-6 0:50:44]: 2:2:线程[2]>>>>8DF4D76D34A8A352118F6D34F8017AF16EF30A80更新到数据库2011成功2TIME:171-32-31-62
[2013-9-6 0:50:44]: 2:2:线程[4]>>>>353B7E166C071433DDDFB82928625D1D8E762204更新到数据库1011成功1TIME:15-0-0-15
[2013-9-6 0:50:44]: 2:2:线程[3]>>>>511C7C32A4B0CA0F77853944FB9BD03DAC389579更新到数据库1011成功1TIME:0-0-0-94
[2013-9-6 0:50:44]: 2:2:线程[1]>>>>562E5E0CDADD9ED787765C83DD6EA1508986E97C更新到数据库1011成功1TIME:0-0-0-31
[2013-9-6 0:50:44]: 2:2:线程[2]>>>>3748B7734A277347F8206898B827BDE6DB4FD131更新到数据库3011成功3TIME:171-31-0-31
[2013-9-6 0:50:44]: 2:2:线程[4]>>>>CFDB99C97C4EB6AF3B698DC3690FA9F8E35DD4B1更新到数据库1011成功1TIME:15-0-0-94
[2013-9-6 0:50:44]: 2:2:线程[3]>>>>7D4CF91647D6268580CC7D9A525786B5F3F2D22E更新到数据库1011成功1TIME:0-0-0-78

查询hashkey是否存在的速度只需要10ms左右.这时候SQLSERVER的进程占用CPU速度在17%左右,基本上降了一半,但查询速度快了10倍.

第一次数据库优化成功,但插入IP信息列表的时间还占用比较大,需要进一步考虑如何优化的问题.特此记录一下.

 

总结:

1.初期数据库设计上比较充分的考虑到数据库每张表的大小问题,所以表切分的问题目前不存在,需要考虑其它因素.

2.网上大部分介绍需要建立索引,如何建立对的索引很重要,以查询的字段最好设置为主键值,因为涉汲到大量的查询;

3.有时候问题并不是都是程序的问题,需要考虑的问题很多,比如数据库,服务器内存,CPU等都需要考虑.

4.如果对很多字段进行比对的字段进行优化呢?比如需要比较时间和其它字段信息? 希望大家指导下此表是否有优化的余地,在此表示感谢

表结构:

CREATE TABLE [dbo].[H31_DHT_LOG_201307](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [hashID] [int] NOT NULL,
    [hashKey] [nvarchar](40) COLLATE Chinese_PRC_CI_AS NULL,
    [recvTime] [datetime] NULL,
    [RecvIP] [nvarchar](16) COLLATE Chinese_PRC_CI_AS NULL,
    [recvPort] [int] NULL,
    [keyType] [int] NULL,
    [recvTimes] [int] NULL,
    [areaID] [int] NULL,
    [areaID2] [int] NULL,
 CONSTRAINT [PK_H31_DHT_LOG_201307] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

查询到这一天的IP信息一条纪录则更新计数:

StringBuilder strSql = new StringBuilder();
DateTime starttime = Convert.ToDateTime(thehash.updateTime.ToString("yyyy-MM-dd"));
DateTime endtime = starttime.AddDays(1);
int daytable = Convert.ToInt32((thehash.updateTime.Day - 1) / 5) + 1;

string tempstr1 = string.Format("select top 1 ID from H31_DHT_LOG_{0}_{1:D2} where hashKey='{2}' and RecvIP='{3}' and recvTime>='{4}' order by id desc", thehash.updateTime.ToString("yyyyMM"), daytable, thehash.hashKey, thehash.recvIp, starttime.ToString("yyyy-MM-dd"));
object countid = dbsql.ExecuteScalar(CommandType.Text, tempstr1.ToString(), null);
if (countid != null && dbsql.GetInt(countid) > 0)
{
    string tempstr = string.Format("update H31_DHT_LOG_{0}_{1:D2}  set recvTimes=recvTimes+1 where id={2}", thehash.updateTime.ToString("yyyyMM"), daytable,dbsql.GetInt(countid));
    strSql.Append(tempstr);
    return dbsql.ExecuteNonQuery(CommandType.Text, strSql.ToString(), null);
}

希望大家多多指导和推荐求助..