环境CPU: Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
内存: 6G
硬盘: 250G
磁盘转速 7000-10000innodb缓存参数都调整为1G了,如下:
show variables like 'innodb_buffer_pool_size';
select 1073741824/1024/1024; -- 1024所有的业务表都是innodb存储引擎,但是查询一张5000条数据量的单表,select * 一把所有数据要5秒时间,这是为什么?而在旧的配置低许多的开发库上只需要0.1s而已。是不是还有别的参数或者别的原因呢?
内存: 6G
硬盘: 250G
磁盘转速 7000-10000innodb缓存参数都调整为1G了,如下:
show variables like 'innodb_buffer_pool_size';
select 1073741824/1024/1024; -- 1024所有的业务表都是innodb存储引擎,但是查询一张5000条数据量的单表,select * 一把所有数据要5秒时间,这是为什么?而在旧的配置低许多的开发库上只需要0.1s而已。是不是还有别的参数或者别的原因呢?
SHOW PROFILES;
'1', '0.00040600', 'SHOW SESSION VARIABLES LIKE ''sql_mode'''
'2', '0.00018500', 'SELECT @@profiling
LIMIT 0, 1000'
'3', '0.00035200', 'SHOW SESSION VARIABLES LIKE ''sql_mode'''
'4', '41.23405000', 'select * from fdp.company_lang limit 10000'
'5', '0.00042800', 'SHOW SESSION VARIABLES LIKE ''sql_mode'''那条sql语句查询记录总数为3200条,怎么要41秒时间啊?天啦!
'select * from fdp.company_lang limit 10000'就这个sql语句,没有啥优化的啊!
SELECT A.* FROM fdp.company_lang A INNER JOIN (
select ID from fdp.company_lang ORDER BY ID limit 10000) B ON A.ID=B.ID
有自增主键cpny_lang_id的。
EXPLAIN 一下上述SQL语句
SELECT A.* FROM fdp.company_lang A INNER JOIN (
select cpny_lang_id from fdp.company_lang ORDER BY cpny_lang_id limit 10000) B ON A.cpny_lang_id=B.cpny_lang_id;explain结果如下:'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '3264', ''
'1', 'PRIMARY', 'A', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'B.cpny_lang_id', '1', ''
'2', 'DERIVED', 'company_lang', 'index', NULL, 'PRIMARY', '4', NULL, '3782', 'Using index'
show profiles结果如下:'21', '33.93027800', 'SELECT A.* FROM fdp.company_lang A INNER JOIN (
select cpny_lang_id from fdp.company_lang ORDER BY cpny_lang_id limit 10000) B ON A.cpny_lang_id=B.cpny_lang_id
LIMIT 0, 1000'mysql workbench客户端工具显示如下:
Duration / Fetch: 0.516 sec / 33.953 sec后面的是啥意思呢?
在服务器linux的命令行下用mysql执行这天sql,不到1秒就执行完了
没有用,修改成了你的sql,在客户端执行查询,还是需要很长时间的。
在生产环境里面 执行 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'
'Waiting for query cache lock', '0.000009', '0.000000', '0.000000'
'Sending data', '0.000146', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'
'Sending data', '0.886554', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000007', '0.000000', '0.000000'
'Sending data', '0.000095', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'
'Sending data', '1.095699', '0.001000', '0.000000'
'Waiting for query cache lock', '0.000007', '0.000000', '0.000000'
'Sending data', '0.000106', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000003', '0.000000', '0.000000'
'Sending data', '0.904066', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000007', '0.000000', '0.000000'
'Sending data', '0.635002', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000006', '0.000000', '0.000000'
'Sending data', '0.000091', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000003', '0.000000', '0.000000'
'Sending data', '0.771809', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000007', '0.000000', '0.000000'
'Sending data', '0.000110', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'
'Sending data', '1.150615', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000008', '0.000000', '0.000000'
'Sending data', '0.000114', '0.001000', '0.000000'
'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'
'Sending data', '0.881805', '0.000000', '0.000000'
'Waiting for query cache lock', '0.000007', '0.000000', '0.000000'
'Sending data', '0.000154', '0.001000', '0.000000'
'Waiting for query cache lock', '0.000004', '0.000000', '0.000000'
'Sending data', '12.190506', '0.007999', '0.001999'
'end', '0.000015', '0.000000', '0.000000'
'query end', '0.000012', '0.000000', '0.000000'
'closing tables', '0.000016', '0.000000', '0.000000'
'freeing items', '0.000018', '0.000000', '0.000000'
'logging slow query', '0.000006', '0.000000', '0.000000'
'logging slow query', '0.000002', '0.000000', '0.000000'
'cleaning up', '0.000004', '0.000000', '0.000000'