查看日志中的ERROR和WARNING D:\software\mysql\MySQL Server 5.6\bin\mysqld: Sort aborted: Query execution was interrupted 可能出现的原因如下 •Insufficient disk space in tmpdir prevented tmpfile from being created. 配置的临时文件存储空间不足 •Insufficient memory for sort_buffer_size to be allocated. 设置的sort_buffer_size已经没有足够的空间去产生 •Somebody ran KILL in the middle of a filesort. 在排序的时候被强行关闭了进程 •The server was shutdown while some queries were sorting. 服务器的异常关闭 •A transaction got rolled back or aborted due to lock wait timeout or deadlock. WARNING Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator. 这个比较简单,innodb_additional_mem_pool_size这个变量已经废弃,与innodb_use_sys_malloc变量合并
谢谢,以解决,能帮我分析下slow.log吗? # Time: 140415 10:46:04 # User@Host: root[root] @ localhost [127.0.0.1] Id: 91 # Query_time: 26.671875 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529964; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 95 # Query_time: 26.484375 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529964; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10; # Time: 140415 10:46:06 # User@Host: root[root] @ localhost [127.0.0.1] Id: 90 # Query_time: 28.375000 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529966; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 81 # Query_time: 25.500000 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529966; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # Time: 140415 10:46:07 # User@Host: root[root] @ localhost [127.0.0.1] Id: 77 # Query_time: 25.718750 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529967; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # Time: 140415 10:46:08 # User@Host: root[root] @ localhost [127.0.0.1] Id: 71 # Query_time: 25.562500 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529968; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 94 # Query_time: 29.109375 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529968; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 73 # Query_time: 27.062500 Lock_time: 0.015625 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529968; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10; # Time: 140415 10:46:09 # User@Host: root[root] @ localhost [127.0.0.1] Id: 82 # Query_time: 26.046875 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529969; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 84 # Query_time: 25.953125 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529969; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # Time: 140415 10:46:10 # User@Host: root[root] @ localhost [127.0.0.1] Id: 79 # Query_time: 24.796875 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529970; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 85 # Query_time: 25.265625 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529970; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 75 # Query_time: 25.828125 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529970; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10; # Time: 140415 10:46:13 # User@Host: root[root] @ localhost [127.0.0.1] Id: 98 # Query_time: 20.796875 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 2848 SET timestamp=1397529973; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=1) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 70 # Query_time: 25.765625 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529973; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 89 # Query_time: 23.250000 Lock_time: 0.015625 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529973; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10; # Time: 140415 10:46:15 # User@Host: root[root] @ localhost [127.0.0.1] Id: 69 # Query_time: 24.812500 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529975; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 78 # Query_time: 25.140625 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529975; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 83 # Query_time: 26.015625 Lock_time: 0.015625 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529975; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # Time: 140415 10:46:17 # User@Host: root[root] @ localhost [127.0.0.1] Id: 86 # Query_time: 26.421875 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529977; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # Time: 140415 10:46:18 # User@Host: root[root] @ localhost [127.0.0.1] Id: 72 # Query_time: 27.796875 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529978; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 78 # Query_time: 26.000000 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529949; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10; # Time: 140415 10:45:50 # User@Host: root[root] @ localhost [127.0.0.1] Id: 72 # Query_time: 22.375000 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529950; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 89 # Query_time: 21.875000 Lock_time: 0.062500 Rows_sent: 10 Rows_examined: 2848 SET timestamp=1397529950; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=1) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 69 # Query_time: 24.968750 Lock_time: 0.015625 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529950; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # Time: 140415 10:45:51 # User@Host: root[root] @ localhost [127.0.0.1] Id: 86 # Query_time: 24.015625 Lock_time: 0.015625 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529951; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10; # User@Host: root[root] @ localhost [127.0.0.1] Id: 68 # Query_time: 24.218750 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835 SET timestamp=1397529951; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10; # Time: 140415 10:45:55 # User@Host: root[root] @ localhost [127.0.0.1] Id: 74 # Query_time: 26.281250 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529955; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; # Time: 140415 10:45:56 # User@Host: root[root] @ localhost [127.0.0.1] Id: 76 # Query_time: 26.250000 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847 SET timestamp=1397529956; select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10; 多谢了
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10;改语句如下select id,brandname from brand where id in( select pinpai_id from product where oname_id=2 ) order by px desc limit 10;然后视情况,创建索引如下。create index xxx on brand (id,px); create index yyy on product (oname_id,pinpai_id);
D:\software\mysql\MySQL Server 5.6\bin\mysqld: Sort aborted: Query execution was interrupted
可能出现的原因如下
•Insufficient disk space in tmpdir prevented tmpfile from being created.
配置的临时文件存储空间不足
•Insufficient memory for sort_buffer_size to be allocated.
设置的sort_buffer_size已经没有足够的空间去产生
•Somebody ran KILL in the middle of a filesort.
在排序的时候被强行关闭了进程
•The server was shutdown while some queries were sorting.
服务器的异常关闭
•A transaction got rolled back or aborted due to lock wait timeout or deadlock. WARNING
Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
这个比较简单,innodb_additional_mem_pool_size这个变量已经废弃,与innodb_use_sys_malloc变量合并
谢谢,以解决,能帮我分析下slow.log吗?
# Time: 140415 10:46:04
# User@Host: root[root] @ localhost [127.0.0.1] Id: 91
# Query_time: 26.671875 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529964;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 95
# Query_time: 26.484375 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529964;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10;
# Time: 140415 10:46:06
# User@Host: root[root] @ localhost [127.0.0.1] Id: 90
# Query_time: 28.375000 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529966;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 81
# Query_time: 25.500000 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529966;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# Time: 140415 10:46:07
# User@Host: root[root] @ localhost [127.0.0.1] Id: 77
# Query_time: 25.718750 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529967;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# Time: 140415 10:46:08
# User@Host: root[root] @ localhost [127.0.0.1] Id: 71
# Query_time: 25.562500 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529968;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 94
# Query_time: 29.109375 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529968;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 73
# Query_time: 27.062500 Lock_time: 0.015625 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529968;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10;
# Time: 140415 10:46:09
# User@Host: root[root] @ localhost [127.0.0.1] Id: 82
# Query_time: 26.046875 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529969;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 84
# Query_time: 25.953125 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529969;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# Time: 140415 10:46:10
# User@Host: root[root] @ localhost [127.0.0.1] Id: 79
# Query_time: 24.796875 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529970;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 85
# Query_time: 25.265625 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529970;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 75
# Query_time: 25.828125 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529970;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10;
# Time: 140415 10:46:13
# User@Host: root[root] @ localhost [127.0.0.1] Id: 98
# Query_time: 20.796875 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 2848
SET timestamp=1397529973;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=1) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 70
# Query_time: 25.765625 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529973;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 89
# Query_time: 23.250000 Lock_time: 0.015625 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529973;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10;
# Time: 140415 10:46:15
# User@Host: root[root] @ localhost [127.0.0.1] Id: 69
# Query_time: 24.812500 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529975;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 78
# Query_time: 25.140625 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529975;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 83
# Query_time: 26.015625 Lock_time: 0.015625 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529975;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# Time: 140415 10:46:17
# User@Host: root[root] @ localhost [127.0.0.1] Id: 86
# Query_time: 26.421875 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529977;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# Time: 140415 10:46:18
# User@Host: root[root] @ localhost [127.0.0.1] Id: 72
# Query_time: 27.796875 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529978;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 78
# Query_time: 26.000000 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529949;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10;
# Time: 140415 10:45:50
# User@Host: root[root] @ localhost [127.0.0.1] Id: 72
# Query_time: 22.375000 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529950;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 89
# Query_time: 21.875000 Lock_time: 0.062500 Rows_sent: 10 Rows_examined: 2848
SET timestamp=1397529950;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=1) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 69
# Query_time: 24.968750 Lock_time: 0.015625 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529950;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# Time: 140415 10:45:51
# User@Host: root[root] @ localhost [127.0.0.1] Id: 86
# Query_time: 24.015625 Lock_time: 0.015625 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529951;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=2) as dispid) order by px desc limit 10;
# User@Host: root[root] @ localhost [127.0.0.1] Id: 68
# Query_time: 24.218750 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 2835
SET timestamp=1397529951;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=3) as dispid) order by px desc limit 10;
# Time: 140415 10:45:55
# User@Host: root[root] @ localhost [127.0.0.1] Id: 74
# Query_time: 26.281250 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529955;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
# Time: 140415 10:45:56
# User@Host: root[root] @ localhost [127.0.0.1] Id: 76
# Query_time: 26.250000 Lock_time: 0.000000 Rows_sent: 9 Rows_examined: 2847
SET timestamp=1397529956;
select id,brandname from brand where id in( select pinpai_id from (select distinct pinpai_id from product where oname_id=5) as dispid) order by px desc limit 10;
多谢了
谢谢
product:oname_id、pinpai_id复合索引
where id in(
select pinpai_id from product where oname_id=2
)
order by px desc limit 10;然后视情况,创建索引如下。create index xxx on brand (id,px);
create index yyy on product (oname_id,pinpai_id);