mysql workbench导入6g_mysql 新生产环境select超级慢，何解？

原帖由 jinguanding 于 2011-8-4 12:58 发表

若是5.1系列，使用profiling看下各个步骤花费多长时间，就知道问题出现在那，毕竟你的SQL简单，可能是从磁盘上读数据，没有缓存到内存中

当然也看你的是myisam还是innodb引擎，自己去分析下

在生产环境里面 执行SHOW PROFILE CPU FOR QUERY 4;发现结果如下：

status -- duration -- cpu_user -- cpu_system

'starting', '0.000015', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'checking query cache for query', '0.000051', '0.000000', '0.000000'

'checking permissions', '0.000012', '0.000000', '0.000000'

'Opening tables', '0.000069', '0.000000', '0.000000'

'System lock', '0.000010', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000050', '0.000000', '0.000000'

'init', '0.000024', '0.000000', '0.000000'

'optimizing', '0.000004', '0.000000', '0.000000'

'statistics', '0.000009', '0.000000', '0.000000'

'preparing', '0.000008', '0.000000', '0.000000'

'executing', '0.000003', '0.000000', '0.000000'

'Sending data', '0.000235', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '0.000155', '0.001000', '0.000000'

'Waiting for query cache lock', '0.000005', '0.000000', '0.000000'

'Sending data', '0.000132', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '0.741126', '0.000000', '0.000000''Waiting for query cache lock', '0.000007', '0.000000', '0.000000'

'Sending data', '0.938495', '0.000000', '0.000000''Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.000166', '0.001000', '0.000000'

'Waiting for query cache lock', '0.000003', '0.000000', '0.000000'

'Sending data', '1.012100', '0.000000', '0.000000''Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.000162', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '0.704330', '0.000000', '0.000000''Waiting for query cache lock', '0.000021', '0.000000', '0.000000'

'Sending data', '0.000130', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '0.866775', '0.000000', '0.000000''Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.635191', '0.000000', '0.000000''Waiting for query cache lock', '0.000005', '0.000000', '0.000000'

'Sending data', '0.000229', '0.001000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '1.014845', '0.000000', '0.000000''Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.000174', '0.001000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '1.194790', '0.000000', '0.000000''Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.000151', '0.001000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '0.773689', '0.000000', '0.000000''Waiting for query cache lock', '0.000007', '0.000000', '0.000000'

'Sending data', '0.000152', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '0.754462', '0.000000', '0.000000''Waiting for query cache lock', '0.000007', '0.000000', '0.000000'

'Sending data', '0.000198', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000003', '0.000000', '0.000000'

'Sending data', '1.159284', '0.000000', '0.000000''Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.632931', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.000178', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '1.013887', '0.000000', '0.000000''Waiting for query cache lock', '0.000007', '0.000000', '0.000000'

'Sending data', '0.000162', '0.001000', '0.000000'

'Waiting for query cache lock', '0.000003', '0.000000', '0.000000'

'Sending data', '1.098200', '0.000000', '0.000000''Waiting for query cache lock', '0.000009', '0.000000', '0.000000'

'Sending data', '0.000078', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000003', '0.000000', '0.000000'

'Sending data', '0.973570', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.000151', '0.000999', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '1.448393', '0.000000', '0.000000''Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.000144', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '0.891945', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '1.009651', '0.000000', '0.000000''Waiting for query cache lock', '0.000009', '0.000000', '0.000000'

'Sending data', '0.000163', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '1.275830', '0.000000', '0.000000''Waiting for query cache lock', '0.000007', '0.000000', '0.000000'

'Sending data', '0.000166', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '1.106479', '0.000000', '0.000000''Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.700365', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000022', '0.000000', '0.000000'

'Sending data', '0.000101', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000003', '0.000000', '0.000000'

'Sending data', '0.718246', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000036', '0.000000', '0.000000'

'Sending data', '0.000136', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000005', '0.000000', '0.000000'

'Sending data', '0.713753', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000007', '0.000000', '0.000000'

'Sending data', '1.016063', '0.000000', '0.000000''Waiting for query cache lock', '0.000008', '0.000000', '0.000000'

'Sending data', '0.000113', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '0.905941', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000007', '0.000000', '0.000000'

'Sending data', '0.000150', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000003', '0.000000', '0.000000'

'Sending data', '0.511361', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000005', '0.000000', '0.000000'

'Sending data', '0.000116', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'

'Sending data', '1.488879', '0.000000', '0.000000'

'Waiting for query cache lock', '0.000026', '0.000000', '0.000000'

'Sending data', '0.917371', '0.000000', '0.000000'

.......(回复内容不允许1000字，所以用省略号代替了)

为什么这show profiles cpu for query 4命令下，sending data 都需要1秒左右，搞得我客户端mysql workbench 查询3000多条数据出来都要差不多一分钟了！Why ?