Mysql优化的几点小建议

2013-12-24 06:25:09到15:31:24的数据库慢日志跟踪来看，目前该库所存在的压力比较高的sql主要有如下几个：

# Time range: 2013-12-24 06:25:09 to 15:31:24# Profile

# Rank Query ID           Response time          Calls R/Call     V/M   It

# ==== ================== ====================== ===== ========== ===== ==

#    1 0x002441D5F6684630 4294969085.0000 100.0% 93620 45876.6192 39... UPDATE train_boss

#    2 0x3150166F1154318E        1895.0000  0.0% 11636     0.1629  0.82 SELECT player_mail

#    3 0xF3C9FE3E4EFFE06D        5270.0000  0.0% 10313     0.5110  0.48 SELECT player

#    4 0x739167CD063486F3          68.0000  0.0%  1002     0.0679  0.92 SELECT account

#    5 0x766B1AAD71294112         138.0000  0.0%   798     0.1729  0.81 UPDATE player_mail

#    6 0x0FEAB37EA89D1B0B          92.0000  0.0%   523     0.1759  0.81 DELETE player_mail

#    7 0xBEEB2F54E133F1E6           1.0000  0.0%   222     0.0045  0.98 DELETE lottery

#    8 0x67A347A2812914DF         278.0000  0.0%   198     1.4040  4.83 SELECT player

#    9 0xFD4ECE8C98B90784          68.0000  0.0%   124     0.5484  0.44 SELECT player

#   10 0x27CC9C64FBD225A7          15.0000  0.0%    53     0.2830  0.71 SELECT player

#   11 0xF12B9E7B9B9815EB          24.0000  0.0%    38     0.6316  0.36 SELECT player

 

我分别整理了一下，并在这里给出优化建议：

 

No.1 sql书写导致没有使用到index

UPDATE  `train_boss` SET  …….  WHERE `player_id`=305664769394061\G;

这个看似没问题的update为什么为需要这么长的时间返回?

我们来看看这个表的结构先:

Database changed

mysql> show create table train_boss\G

*************************** 1. row ***************************

       Table: train_boss

Create Table: CREATE TABLE `train_boss` (

  `player_id` char(64) NOT NULL,

  `role_boss_info` blob,

  `friend_info` blob,

  PRIMARY KEY  (`player_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

我们看到表里面定义的player_id为字符型，我们在update后面使用的是整形，所以导致没有使用上index。

修改前该sql的执行计划：

mysql> explain select * from `train_boss` where  `player_id`=305664769394061\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: train_boss

         type: ALL

possible_keys: PRIMARY

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 5740

        Extra: Using where

 

修改之后的执行计划：

mysql> explain select * from `train_boss` where  `player_id`='305664769394061'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: train_boss

         type: const

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 192

          ref: const

         rows: 1

        Extra:

1 row in set (0.00 sec)

第二次使用到了主键作为索引，所以我们在使用字段作为查询条件的时候，一定要使用相应的类型。这里我们应该把

UPDATE  `train_boss` SET  …….  WHERE `player_id`=305664769394061;

改为(第二次加了单引号，表示该串为字符串而不是数字)

UPDATE  `train_boss` SET  …….  WHERE `player_id`=’305664769394061’\G;

 

No.2 没有使用主键，也没有使用index

Query_TEXT: SELECT `id`,`content` FROM `player_mail` WHERE `player_id`=283674536846227

该表的结构如下：

mysql> show create table player_mail\G

*************************** 1. row ***************************

       Table: player_mail

Create Table: CREATE TABLE `player_mail` (

  `id` bigint(20) NOT NULL,

  `player_id` bigint(20) NOT NULL,

  `content` blob,

  `recv_time` int(11) default '1387350433',

  `end_time` int(11) default '1387955233'

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

对于该表，我们很明显的看到没有主键，所以mysql会我们建立一个隐含的6 byte的主键，而这个对应用而言是透明的，因此又有什么用呢？mysql innodb 都是采用b+tree的存储结构，所以采用一个自增的数字类型作为主键是必要的。

现在的建议：

[1]建立一个自增的主键

[2]在player_id上建立index

 

No.3 相关字段上没有index

3.1:

Query_TEXT:SELECT id,authed FROM player WHERE account = 283674536846430\G

建议：
在account字段上建立index

3.2:

Query_TEXT: SELECT 'mobile_account', id, account, password, pf_account, pf_username, gm, authed, last_login, last_logout FROM `account` WHERE `mobile_account` = 'fe3953b75dfcc25aa8da5f5063612f73'

建议：

在mobile_account字段上建立index

 

No.4 全表扫面

SELECT /*!40001 SQL_NO_CACHE */ * FROM `player`\G

不知道为什么会存在全表扫面，如果实在需要这种sql存在，请分为多个批次来查询。

 

 

其余目前还看不出来有很大的影响，有兴趣的朋友可以看看。对应的结果文档请参考这里：