Mysql Optimization（6）
【问题现象】
使用sphinx支持倒排索引，但sphinx从mysql查询源数据的时候，查询的记录数才几万条，但查询的速度非常慢，大概要4~5分钟左右
【处理过程】
1）explain
首先怀疑索引没有建好，于是使用explain查看查询计划，结果如下：
从explain的结果来看，整个语句的索引设计是没有问题的，除了第一个表因为业务需要进行整表扫描外，其它的表都是通过索引访问
explain看不出问题，那到底慢在哪里呢？
于是想到了使用 show processlist查看sql语句执行状态，查询结果如下：
发现很长一段时间，查询都处在 “Sending data”状态
查询一下“Sending data”状态的含义，原来这个状态的名称很具有误导性，所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”。
这里的关键是为什么要收集数据，原因在于：mysql使用“索引”完成查询结束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到“数据行”上将需要返回的数据读取出来返回个客户端。
3）show profile
为了进一步验证查询的时间分布，于是使用了show profile命令来查看详细的时间分布
首先打开配置：set profiling=
执行完查询后，使用show profiles查看
使用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倍
【原理研究】
至此问题已经明确，但原理上我们还需要继续探究。
这篇淘宝的文章很好的解释了相关原理：
这里的关键信息是：当Innodb的存储格式是&ROW_FORMAT=COMPACT&(or&ROW_FORMAT=REDUNDANT)的时候，Innodb只会存储前768字节的长度，剩余的数据存放到“溢出页”中。
我们使用show table status来查看表的相关信息：
可以看到，平均一行大约1.5K，也就说大约1/10行会使用“溢出存储”，一旦采用了这种方式存储，返回数据的时候本来是顺序读取的数据，就变成了随机读取了，所以导致性能急剧下降。
另外，在测试过程中还发现，无论这条语句执行多少次，甚至将整个表select *几次，语句的执行速度都没有明显变化。这个表的数据和索引加起来才150M左右，而整个Innodb buffer pool有5G，缓存整张表绰绰有余，如果缓存了溢出页，性能应该大幅提高才对。
但实测结果却并没有提高，因此从这个测试可以推论Innodb并没有将溢出页（overflow
page）缓存到内存里面。
这样的设计也是符合逻辑的，因为overflow page本来就是存放大数据的，如果也放在缓存里面，就会出现一次大数据列（blob、text、varchar）查询，可能就将所有的缓存都更新了，这样会导致其它普通的查询性能急剧下降。
【解决方法】
找到了问题的根本原因，解决方法也就不难了。有几种方法：
1）查询时去掉description的查询，但这受限于业务的实现，可能需要业务做较大调整
2）表结构优化，将descripion拆分到另外的表，这个改动较大，需要已有业务配合修改，且如果业务还是要继续查询这个description的信息，则优化后的性能也不会有很大提升。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：35529次
排名：千里之外
原创：39篇
转载：32篇
(5)(7)(1)(3)(1)(6)(8)(2)(5)(2)(2)(6)(4)(5)(6)(3)(2)9244人阅读
select * from searchzh where modified_date & ' 14:45:22';
一条mysql查询语句的性能：sending data 耗时10分钟。。。。
+--------------------------------+-----------+| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration& |+--------------------------------+-----------+| (initialization)&&&&&&&&&&&&&& | 0.000006& | | checking query cache for query | 0.000025& | | checking permissions&&&&&&&&&& | 0.000008& | | Opening tables&&&&&&&&&&&&&&&& | 0.000148& | | System lock&&&&&&&&&&&&&&&&&&& | 0.000008& | | Table lock&&&&&&&&&&&&&&&&&&&& | 0.000008& | | init&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000007& | | checking permissions&&&&&&&&&& | 0.000045& | | optimizing&&&&&&&&&&&&&&&&&&&& | 0.000016& | | statistics&&&&&&&&&&&&&&&&&&&& | 0.00027&& | | preparing&&&&&&&&&&&&&&&&&&&&& | 0.000034& | | executing&&&&&&&&&&&&&&&&&&&&& | 0.000005& | | Sending data&&&&&&&&&&&&&&&&&& | 614.25387 | | end&&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000046& | | query end&&&&&&&&&&&&&&&&&&&&& | 0.000012& | | freeing items&&&&&&&&&&&&&&&&& | 0.000017& | | closing tables&&&&&&&&&&&&&&&& | 0.000008& | | logging slow query&&&&&&&&&&&& | 0.000054& | +--------------------------------+-----------+18 rows in set (0.01 sec)
产生的原因：
searchzh视图相关的表，update，insert，delete操作频繁。在mysql的表锁定机制中，select语句的优先级比update低。所以此select语句一直在锁队列中等待，后来的update操作会插队到select语句前面。导致| Sending data&&&&&&&&&&&&&&&&&& | 614.25387 |
解决方法有多种，最简单的：
&select HIGH_PRIORITY * from searchzh where modified_date & ' 14:45:22';
指定该select语句具有高优先级。
=======================================
mysql15:21:30& SHOW STATUS LIKE 'Table%';+-----------------------+-----------+| Variable_name&&&&&&&& | Value&&&& |+-----------------------+-----------+| Table_locks_immediate |
| | Table_locks_waited&&& | 577&&&&&& | +-----------------------+-----------+
表锁争用现象比较严重。
在频繁更新和查询的情景下，使用inodb也许更好。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：85507次
积分：1072
积分：1072
排名：千里之外
原创：17篇
转载：14篇
(1)(11)(1)(2)(12)(4)MySQL解决sending data的方法_数据库技术_Linux公社-Linux系统门户网站
你好，游客
MySQL解决sending data的方法
来源：Linux社区&
作者：wangcong83
今天在用MySQL的语句查看的时候，发现有好几个sending data出现，都是查询语句引起的。
网上查了一下，说是有可能由于key_buffer设置引起的，结果就修改了这个设置，重启MySQL服务之后发现无效。
之后发现sending data的语句都是select语句，于是怀疑和select有关，检查了一下发现是查询的字段没有建立索引，于是就建立了个索引，解决了这个问题。
相关资讯 & & &
& (08/15/:23)
& (02/08/:35)
& (03/22/:49)
& (05/24/:58)
& (02/05/:47)
& (01/29/:55)
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款
你大爷 发表于 神马玩意儿啊2009年9月 其他数据库开发大版内专家分月排行榜第三
2009年9月 其他数据库开发大版内专家分月排行榜第三
2010年 总版技术专家分年内排行榜第二
2009年 总版技术专家分年内排行榜第三
2010年 总版技术专家分年内排行榜第二
2009年 总版技术专家分年内排行榜第三
本帖子已过去太久远了，不再提供回复功能。实战：MySQL Sending data导致查询很慢的问题详细分析
时间： 13:00:05
这两天帮忙定位一个mysql查询很慢的问题，定位过程综合各种方法、理论、工具，很有代表性，分享给大家作为新年礼物：）
【问题现象】
使用sphinx支持倒排索引，但sphinx从mysql查询源数据的时候，查询的记录数才几万条，但查询的速度非常慢，大概要4~5分钟左右
【处理过程】
1）explain
首先怀疑索引没有建好，于是使用explain查看查询计划，结果如下：
从explain的结果来看，整个语句的索引设计是没有问题的，除了第一个表因为业务需要进行整表扫描外，其它的表都是通过索引访问
explain看不出问题，那到底慢在哪里呢？
于是想到了使用 show processlist查看sql语句执行状态，查询结果如下：
发现很长一段时间，查询都处在 “Sending data”状态
查询一下“Sending data”状态的含义，原来这个状态的名称很具有误导性，所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”。
这里的关键是为什么要收集数据，原因在于：mysql使用“索引”完成查询结束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到“数据行”上将需要返回的数据读取出来返回个客户端。
3）show profile
为了进一步验证查询的时间分布，于是使用了show profile命令来查看详细的时间分布
首先打开配置：set profiling=
执行完查询后，使用show profiles查看
使用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倍
【原理研究】
至此问题已经明确，但原理上我们还需要继续探究。
这篇淘宝的文章很好的解释了相关原理：innodb使用大字段text，blob的一些优化建议
具体对应到本文的这个实例，我们使用show table status来查看表的相关信息：
可以看到，平均一行大约1.5K，Innodb每页16K，除去一些数据结构空间和保留空间，大概1/10的行需要采用溢出存储（即将数据存放在另外的页中），一旦采用了这种方式存储，返回数据的时候本来是顺序读取的数据，就变成了随机读取了，所以导致性能急剧下降。
【解决方法】
找到了问题的根本原因，解决方法也就不难了。有两种方法：
1）查询时去掉description的查询，但这受限于业务的实现，可能需要业务做较大调整
2）增大Innodb buffer pool，但由于Innodb buffer pool会根据查询进行自动调整，因此如果gm_platform_info不是热门表，作用也不是很明显
3）表结构优化，将descripion拆分到另外的表，这个改动较大，需要已有业务配合修改
$T.total > 0 && $T.page <= $T.pageNum}
{#foreach $T.data as r}
{$T.r.formt_tm}{#if $T.r.nickname}{#else}匿名{#/if}
{$T.r.content}
{#if $T.page > 1 && $T.pageNum > 1)
$T.s_num > 2}
{#for index = $T.s_num to $T.e_num}
$T.pageNum > $T.pageNavSize+ 2 && $T.s_num != $T.pageNum - $T.pageNavSize}
{#if $T.pageNum > 1}
{#if $T.pageNum != $T.page && $T.pageNum > 1}
<a href="javascript:void(0);" page="{$T.page 下一页
您的回应...
也许你感兴趣
(window.slotbydup=window.slotbydup || []).push({
id: '2803163',
container: s,
size: '120,270',
display: 'float'
(C)2012 本站提供的内容来源于广大网络用户，我们不保证内容的正确性。如果转载了您的内容，希望删除的请联系我们！}