项目实际用到的一些mysql优化（记录）

    最近几天一直在做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

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