服务器数据库优化指南使用下面优化方式,可以大幅提高服务器相应能力,对低配置服务器最显著,对高性能服务器在巨量接入负载时也会有显著性能提升
修改表结构Muonline的joinlog表,修改如下servername修改为 char,长度20ip修改为 char,长度15然后建立或修改以下存储过程1:登陆连接用CREATE PROCEDURE WZ_CONNECT_MEMB @uid varchar(20),@Server varchar(10),@uip varchar(15)ASBeginDeclare @dtToday datetimeselect @dtToday={fn CURDATE()}select @dtTodayset nocount onif exists(select * from Uselog where id=@uid)begin update Uselog set State= 上线 where id=@uidINSERT INTO joinlog( ServerName,id,state,ip,sdatetime) values (@Server,@uid,上线,@uip,@dtToday)if(@@error <> 0)beginrollbackselect-1endelsebegincommitselect @uidendend elsebeginINSERT INTO Uselog ( ServerName,id,ip,State) values ( @Server , @uid, @uip,上线) INSERT INTO joinlog( ServerName,id,state,ip,sdatetime) values (@Server,@uid,上线,@uip,@dtToday)endendGO
2:注销登陆用CREATE PROCEDURE WZ_DISCONNECT_MEMB @uid varchar(10) ASBeginDECLARE @uidl varchar(10)Declare @dtToday datetimeDeclare @logindate datetimeDeclare @uip varchar(15)Declare @Server varchar(15)select @dtToday={fn CURDATE()}select @dtTodayset nocount onbegin transactionupdate Uselog set State= 下线 where id=@uidINSERT INTO joinlog(id,state,sdatetime) values (@uid,下线,@dtToday)if(@@error <> 0)beginrollback transactionselect-1endelsebegincommit transactionselect @uidendendGO
建立索引 1:仓库索引CREATE INDEX [warehouse_accountid_index] ON [dbo].[warehouse] ([AccountID])WITHDROP_EXISTINGON [PRIMARY]
2:角色选择画面索引CREATE INDEX [accountcharactet_id_index] ON [dbo].[AccountCharacter] ([Id])WITHDROP_EXISTINGON [PRIMARY]
3:角色索引CREATE UNIQUE INDEX [pk_character_index_wy] ON [dbo].[Character] ([AccountID] desc , [Name] desc , [Money])WITHDROP_EXISTINGON [PRIMARY]
4:角色登陆索引CREATE INDEX [pk_membpwd_index] ON [dbo].[MEMB_INFO] ([memb__pwd])WITHDROP_EXISTINGON [PRIMARY]以上代码分别在查询分析器里执行,执行后登陆看看,打开仓库看看,速度如何:)
注意下列命令是如果系统中存在则删除已存在的索引WITHDROP_EXISTINGON [PRIMARY]如果没有,删除自然出错并返回
服务器: 消息 7999,级别 16,状态 9,行 1未能找到任何名为 warehouse_accountid_index 的索引(属于表 warehouse)
删除建立索引命令内的WITHDROP_EXISTINGON [PRIMARY]这部分再运行就可以了。另外建立索引可以加快服务器数据库查询速度,应该可以在解决由于延迟导致的复制问题
自己看晕咯