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

xiaoxue85

最近几天一直在做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的性能，根据参数确认服务器瓶颈