分类： Mysql/postgreSQL
2016-06-30 11:41:06
原文地址：Mysql优化的几点小建议 作者：zxszcaijin
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存在，请分为多个批次来查询。
其余目前还看不出来有很大的影响，有兴趣的朋友可以看看。对应的结果文档请参考这里：
