长时间处于sending data状态是否跟结果集过大有关？

Question

我发现，处于sending data状态的sql语句大多其结果集很大！不知sending data是不是处于发送结果到php程序的状态中？但是我在mysql终端运行的时候，一般来讲很快就把结果列出来了！
迷茫中 ...

Answer 1

估计是你sending data 时候传输效率 你是不是SSL了

Answer 2

这是我一个分析结果
mysql> show profile cpu, block io for query 2;
+--------------------------------+-----------+-----------+------------+--------------+---------------+
| Status                         | Duration  | CPU_user  | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+-----------+-----------+------------+--------------+---------------+
| starting                       |  0.000017 |  0.000000 |   0.000000 |            0 |             0 | 
| checking query cache for query |  0.000086 |  0.000000 |   0.000000 |            0 |             0 | 
| Opening tables                 |  0.000017 |  0.000000 |   0.000000 |            0 |             0 | 
| System lock                    |  0.000013 |  0.000000 |   0.000000 |            0 |             0 | 
| Table lock                     |  0.000012 |  0.000000 |   0.000000 |            0 |             0 | 
| init                           |  0.000030 |  0.000000 |   0.000000 |            0 |             0 | 
| optimizing                     |  0.000031 |  0.000000 |   0.000000 |            0 |             0 | 
| statistics                     |  0.000289 |  0.000000 |   0.000000 |            0 |             0 | 
| preparing                      |  0.000023 |  0.000000 |   0.000000 |            0 |             0 | 
| executing                      |  0.000004 |  0.000000 |   0.000000 |            0 |             0 | 
| Sorting result                 |  0.000004 |  0.000000 |   0.000000 |            0 |             0 | 
| Sending data                   | 12.169379 | 18.651165 |   4.117374 |            0 |             0 | 
| end                            |  0.000010 |  0.000000 |   0.000000 |            0 |             0 | 
| query end                      |  0.000004 |  0.000000 |   0.000000 |            0 |             0 | 
| freeing items                  |  0.000605 |  0.001000 |   0.000000 |            0 |             0 | 
| logging slow query             |  0.000004 |  0.000000 |   0.000000 |            0 |             0 | 
| logging slow query             |  0.000039 |  0.000000 |   0.000000 |            0 |             0 | 
| cleaning up                    |  0.000004 |  0.000000 |   0.000000 |            0 |             0 | 
+--------------------------------+-----------+-----------+------------+--------------+---------------+

Answer 3

从MYSQL的源程序来看（我没看过，只是在MYSQL的官方论坛上听说）Sending data 显示的这个时间 = Time(Sending data) - Time (Sorting result), 这样其实应该是排序所用的时间MySQL 的文档中没有关于这方面的介绍。估计也只能去看源代码来理解这个 profile 的结果了。

Answer 4

具体代码你可以阅读
sql_select.cc

Answer 5

版主mysql5.1的源程序在哪下载 
提供下

Answer 6

http://dev.mysql.com/downloads/mysql/5.1.html
然后在最下面的。Source downloads
Note that in the more recent MySQL 5.0 and 5.1 releases, Windows binaries are built from the same source as the Unix/Linux source TAR.
你可以选择一个，或者就下载最后一个 Source (zip)

Answer 7

哦，如果是版主遇上了在Sending data 状态上花了十几秒时间，会从何处着手呢？ 也就是大概能猜出哪个环节出了问题呢？

Answer 8

mysql> show variables like "%SSL%";
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)看have_ssl 是不是disabled

Answer 9

也没什么好办法。其实做个实现。select count(*) from t1 where id<10;
select count(*) from t1 where id<1000;
select count(*) from t1 where id<10000;就可以看到 send data 的时间随着记录总数的增长而增长。并不是象其文档中所说 sending data 送数据到 client 端。
send data 中肯定包含了执行和运算的时间。
下表是测试的一个结果。
+--------------------++--------------------------------++--------------------------------+
|                    || select * from t1               || select count(*) from t1        |
+--------------------++----------+----------+----------++----------+----------+----------+
| Status             || id<11    | id<100   | id<10000 || id<11    | id<100   | id<10000 |
+--------------------++----------+----------+----------++----------+----------+----------+
| starting           || 0.000126 | 0.000128 | 0.000117 || 0.000123 | 0.000047 | 0.000065 |
| Opening tables     || 0.000015 | 0.000015 | 0.000014 || 0.000015 | 0.000007 | 0.000009 |
| System lock        || 0.000005 | 0.000005 | 0.000005 || 0.000005 | 0.000003 | 0.000004 |
| Table lock         || 0.000010 | 0.000010 | 0.000010 || 0.000009 | 0.000004 | 0.000005 |
| init               || 0.000031 | 0.000029 | 0.000027 || 0.000028 | 0.000011 | 0.000014 |
| optimizing         || 0.000010 | 0.000010 | 0.000010 || 0.000012 | 0.000006 | 0.000008 |
| statistics         || 0.000055 | 0.000053 | 0.000052 || 0.000047 | 0.000024 | 0.000037 |
| preparing          || 0.000012 | 0.000013 | 0.000011 || 0.000012 | 0.000005 | 0.000007 |
| executing          || 0.000002 | 0.000002 | 0.000003 || 0.000004 | 0.000003 | 0.000003 |
| Sending data       || 0.000075 | 0.000371 | 0.034405 || 0.000034 | 0.000066 | 0.005287 |
| end                || 0.000003 | 0.000003 | 0.000006 || 0.000003 | 0.000002 | 0.000003 |
| query end          || 0.000002 | 0.000002 | 0.000002 || 0.000002 | 0.000002 | 0.000002 |
| freeing items      || 0.000020 | 0.000021 | 0.000032 || 0.000042 | 0.000012 | 0.000041 |
| logging slow query || 0.000001 | 0.000002 | 0.000001 || 0.000002 | 0.000001 | 0.000002 |
| cleaning up        || 0.000002 | 0.000002 | 0.000004 || 0.000002 | 0.000001 | 0.000001 |
+--------------------++----------+----------+----------++----------+----------+----------+
当输出记录多的时候 select * 显然 sending data 要花长时间，这一点也没什么可优化的。
当符合条件的记录多时候，同样 sending data  也会增加。　但executing 时间却基本上一样。

Answer 10

嗯，按照版主的试验确实是这样，
也就是说，sending data所耗费的时间跟select查询所涉及的数据量成正比了？

Answer 11

select * from t1 order by id limit 10;
show profile;select * from t1 order by id limit 20,10;
show profile;select * from t1 order by id limit 5000,10;
show profile;做了这么个测试。
明显，虽然这几个语句输出都是10 行记录。但时间却大不相同。+--------------------+----------+----------+----------+----------+
|                    | lime 10  | 10,10    | 20,10    | 5000,10 |
| Status             | Duration | Duration | Duration | Duration |
+--------------------+----------+----------+----------+----------+
| starting           | 0.000109 | 0.000043 | 0.000106 | 0.000301 |
| Opening tables     | 0.000014 | 0.000007 | 0.000014 | 0.000013 |
| System lock        | 0.000005 | 0.000002 | 0.000004 | 0.000005 |
| Table lock         | 0.000009 | 0.000004 | 0.000009 | 0.000008 |
| init               | 0.000017 | 0.000008 | 0.000017 | 0.000017 |
| optimizing         | 0.000003 | 0.000002 | 0.000003 | 0.000004 |
| statistics         | 0.000012 | 0.000004 | 0.000011 | 0.000011 |
| preparing          | 0.000009 | 0.000004 | 0.000010 | 0.000009 |
| executing          | 0.000002 | 0.000002 | 0.000002 | 0.000002 |
| Sorting result     | 0.000005 | 0.000003 | 0.000005 | 0.017256 |
| Sending data       | 0.000069 | 0.000080 | 0.000139 | 0.000468 |
| end                | 0.000003 | 0.000002 | 0.000003 | 0.000005 |
| query end          | 0.000002 | 0.000001 | 0.000002 | 0.000003 |
| freeing items      | 0.000018 | 0.000011 | 0.000021 | 0.000039 |
| logging slow query | 0.000001 | 0.000002 | 0.000002 | 0.000003 |
| cleaning up        | 0.000002 | 0.000002 | 0.000002 | 0.000003 |
+--------------------+----------+----------+----------+----------+

Answer 12

| have_openssl  | NO    | 
| have_ssl      | NO    |压根就没有！

Answer 13

这个我觉得应该说法是没错的
因为数据不是一次性都往客户端送的，而是采用一定的机制进行批量发送，先写进net_buffer_length，然后把net_buffer_length发送清空再写...