项目实际用到的一些mysql优化（记录）-凯发app官方网站

    最近几天一直在做mysql的优化工作，其原因是游戏中的角色数据存盘时间过长,而且出现内存不断的增长，类似内存泄露，其后者与之前做的代码优化有关。
    原因：
    4000 的机器人，平均每个25个物品，存盘时间超过200ms,将近占总时长的一半，根据现有的设计，进行存盘的时候，遍历每个物品，并将角色下所有的物品设置invlid,执行update/insert操作（如果存在部分关键字段相同，则update,否则insert新的物品）;
    方案一：
    先从数据库查询结果，然后比较内存的数据，只存储变更的物品，减少插入的次数；因使用了查询缓存，query_cache_size设置的不够大，默认为2m，导致了在运行过程中内存不断增加，调整参数后能得到一定的缓解；
    方案二：
    使用replace ...into 替代 insert,物品表使用了联合索引（charguid,packettype,packetpos）,在使用了索引或者主键的情况下，如果存在相同的值，则会先删除，后插入；并使用批量插入，这样也达到减少操作次数的目的；物品的个数是不固定的，所以进行分页存储，每次存储固定个数，剩余的最后一次存储；
    结论：
    方案一没有带来实质性的改善（我想应该是在代码的某处存在问题），存盘时间还是比较长，甚至比优化前更长；
    方案二改善比较大，时间为原来的1/10左右，也就是说提升了90%；进行优化时，总会考虑批量的执行insert,但没想到效果是这么明显；
    其他：
    运行时执行tunning-primer.sh，可得到一些mysql优化的建议；

其他：

1， mysql 中text类型的字段最大长度为2^16=65535，即16k

2， innodb存储引擎的默认格式是compact（redundant为兼容以前的版本），对于blob,text,varchar这样的大字段（8092），innodb只会存放768个字节在数据页中，而剩余的数据则会存储在溢出段中（发生溢出情况的时候适用）；

3 ，innodb的块大小默认为16k,由于innodb的存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少有两行记录，也就是说innodb引擎每行数据不能超过8k；

4， mysql 在操作数据的时候，以page为单位，不管是更新，插入，还是删除，都需要将那行数据所在的page读到内存中，然后再进行操作，这样就存在一个命中率的问题，如果一个page中能够相对的存放足够多的行，那么命中率就会相对高一些，性能就会有提升；

已做过的优化：

（1）分表

如下表：

点击(此处)折叠或打开

create table `t_char` (
`aid` bigint(20) not null auto_increment,
`accname` varchar(50) character set utf8 collate utf8_bin not null,
`charguid` int(11) not null,
`charname` varchar(50) character set utf8 collate utf8_bin not null,
`title` varchar(50) not null,
`pw` varchar(15) not null,
`sex` smallint(6) not null,
`level` int(11) not null,
`enegry` int(11) not null,
`outlook` int(11) not null,
`scene` int(11) not null,
`xpos` int(11) not null,
`zpos` int(11) not null,
`menpai` smallint(6) not null,
`hp` int(11) not null,
`mp` int(11) not null,
`strikepoint` smallint(6) not null,
`camp` varchar(50) character set utf8 collate utf8_bin not null,
`str` int(11) not null,
`con` int(11) not null,
`dex` int(11) not null,
`spr` int(11) not null,
`ipr` int(11) not null,
`points` int(11) not null,
`logouttime` int(11) not null,
`logintime` int(11) not null,
`createtime` int(11) not null,
`dbversion` int(11) not null default '0',
`haircolor` int(11) not null,
`hairmodel` int(11) not null,
`facecolor` int(11) not null,
`facemodel` int(11) not null,
`vmoney` int(11) not null,
`isvalid` smallint(6) not null,
`exp` int(11) not null,
`pres` text not null,
`mdata` text,
`mflag` text,
`relflag` text,
`settings` text not null,
`shopinfo` text,
`carrypet` varchar(20) not null,
`guldid` int(11) not null,
`teamid` int(11) not null,
`headid` int(11) not null,
`erecover` int(11) not null,
`rmb` int(11) not null default '0',
`vigor` int(11) not null,
`maxvigor` int(11) not null,
`bankrmb` int(11) not null default '0',
`vrecover` int(11) not null,
`energymax` int(11) not null,
`pwdeltime` int(11) not null,
`pinfo` text,
`bkscene` int(11) default null,
`bkxpos` int(11) default null,
`bkzpos` int(11) default null,
`titleinfo` text,
`dietime` int(11) not null,
`bankmoney` int(11) not null,
`bankend` int(11) not null,
`cooldown` text,
`rage` int(11) default '0',
primary key (`aid`,`charname`),
unique key `index_char_charguid` (`charguid`),
unique key `index_char_charname` (`charname`),
key `index_char_accname` (`accname`)
) engine=innodb default charset=utf8;

将表中的非text字段分为一个表，text字段分为另外一个表，根据知识点2，表的平均行长度最好不要超过8k；也可以多分为几个表，字段长度差不多的分在一个表中；

实践表明，100g左右的数据，经过分表之后，大小约为20g,而且备份数据的速度大大提升；

（2）加索引

根据查询explain,分析表的必须访问的行数，根据其加表索引；基本上是根据select语句，为检索的字段加索引；

（3）优化insert语句

将多次insert改为一次插入多条记录；

（4）代码优化

优化数据库逻辑相关代码，将数据库的循环操作，特别是物品类，每个物品一条记录，每个玩家身上最大可能有270个左右的数据，也就是说在存盘的时候，需要循环270次，根据日志记录，在此消耗时间比较大；其方案是标记玩家更改的数据，存盘时只存变更的数据；

（5）配置相关优化

降低事务的隔离级别；
关闭二进制日志；

减少磁盘io,降低日志刷新频率；
加大查询缓存设置；

其他常规配置；

（6）其他

根据vmstat,iostat工具监测cpu,磁盘io的性能，根据参数确认服务器瓶颈

1， mysql 中text类型的字段最大长度为2^16=65535，即16k

阅读(2248) | 评论(0) | 转发(0) |

上一篇：high-performance server architecture

下一篇：linux下使用读写锁

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们