发布日期:2015-10-21 16:51 来源: 标签: MySQL数据库 MySQL教程 MySQL查询 Sending data
本章节我们将学习MySQL Sending data使查询慢的原因是什么,这种问题现象该如何处理,下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。

【问题现象】

使用sphinx支持倒排索引,但sphinx从mysql查询源数据的时候,查询的记录数才几万条,但查询的速度非常慢,大概要4~5分钟左右

【处理过程】

1)explain

首先怀疑索引没有建好,于是使用explain查看查询计划,结果如下:

从explain的结果来看,整个语句的索引设计是没有问题的,除了第一个表因为业务需要进行整表扫描外,其它的表都是通过索引访问

2)show processlist;

explain看不出问题,那到底慢在哪里呢?

于是想到了使用 show processlist查看sql语句执行状态,查询结果如下:

发现很长一段时间,查询都处在 “ Sending data ”状态

查询一下“Sending data”状态的含义,原来这个状态的名称很具有误导性,所谓的“Sending data”并不是单纯的发送数据,而是包括“收集 + 发送 数据”。

这里的关键是为什么要收集数据,原因在于: mysql使用“索引”完成查询结束后,mysql得到了一堆的行id,如果有的列并不在索引中,mysql需要重新到“数据行”上将需要返回的数据读取出来返回个客户端。

3)show profile

为了进一步验证查询的时间分布,于是使用了show profile命令来查看详细的时间分布

首先打开配置:set profiling=on;
执行完查询后,使用show profiles查看query id;
使用show profile for query query_id查看详细信息;

结果如下:

从结果可以看出,Sending data的状态执行了216s

4)排查对比

经过以上步骤,已经确定查询慢是因为大量的时间耗费在了Sending data状态上,结合Sending data的定义,将目标聚焦在查询语句的 返回列 上面

经过一 一排查,最后定为到一个description的列上,这个列的设计为: `description` varchar(8000) DEFAULT NULL COMMENT '游戏描述',

于是采取了对比的方法,看看“ 不返回 description的结果”如何。show profile的结果如下:

可以看出,不返回description的时候,查询时间只需要15s,返回的时候,需要216s, 两者相差15倍

【原理研究】

具体对应到本文的这个实例,我们使用show table status来查看表的相关信息:

可以看到,平均一行大约1.5K,Innodb每页16K,除去一些数据结构空间和保留空间,大概1/10的行需要采用溢出存储(即将数据存放在另外的页中),一旦采用了这种方式存储,返回数据的时候 本来是顺序读取的数据,就变成了随机读取 了,所以导致性能急剧下降。

【解决方法】

找到了问题的根本原因,解决方法也就不难了。有几种方法:

1) 查询时去掉description的查询 ,但这受限于业务的实现,可能需要业务做较大调整

2) 增大Innodb buffer pool ,但由于Innodb buffer pool会根据查询进行自动调整,因此如果gm_platform_info不是热门表,作用也不是很明显

3) 表结构优化 ,将descripion拆分到另外的表,这个改动较大,需要已有业务配合修改       

相关评论

专题信息
    MySQL 是一种关联数据库管理系统,MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。本教程主要讲解了MySQL查询语句、MySQL查询方法、MySQL查询命令等,希望对大家有所帮助。