Chinaunix首页 | 论坛 | 博客
  • 博客访问: 643311
  • 博文数量: 66
  • 博客积分: 15
  • 博客等级: 民兵
  • 技术积分: 2204
  • 用 户 组: 普通用户
  • 注册时间: 2010-10-26 21:43
个人简介

曾就职于阿里巴巴担任Oracle DBA,MySQL DBA,目前在新美大担任SRE。[是普罗米修斯还是一块石头,你自己选择!] 欢迎关注微信公众号 “自己的设计师”,不定期有原创运维文章推送。

文章分类

全部博文(66)

文章存档

2017年(2)

2016年(3)

2015年(7)

2014年(12)

2013年(42)

分类: Mysql/postgreSQL

2013-12-24 20:00:19

2013-12-24 06:25:0915: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存在,请分为多个批次来查询。

 

 

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

阅读(2504) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~