数据10多万条, 下面这个语句执行时间9秒, 不知道为什么# Query_time: 9 Lock_time: 2 Rows_sent: 12 Rows_examined: 64841 select id,title from so_subject where oneid=11 order by day_num desc limit 0,12; # User@Host: ggssw[ggssw] @ localhost [127.0.0.1] # Query_time: 9 Lock_time: 2 Rows_sent: 1 Rows_examined: 64830 select id,title from so_subject where oneid=11 and id < 133842 order by id desc limit 0,1; # User@Host: ggssw[ggssw] @ localhost [127.0.0.1] # Query_time: 9 Lock_time: 2 Rows_sent: 1 Rows_examined: 64830 select id,title from so_subject where oneid=11 and id < 85427 order by id desc limit 0,1; # User@Host: ggssw[ggssw] @ localhost [127.0.0.1] # Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 64830 select id,title from so_subject where oneid=11 and id > 158043 order by id desc limit 0,1;
EXPLAIN select id,title from so_subject where oneid=11 order by day_num desc limit 0,12\G看下其执行计划.....是不是无索引可走,还是索引执行计划有问题...
select id,title from so_subject where oneid=11 order by day_num desc limit 0,12;
# User@Host: ggssw[ggssw] @ localhost [127.0.0.1]
# Query_time: 9 Lock_time: 2 Rows_sent: 1 Rows_examined: 64830
select id,title from so_subject where oneid=11 and id < 133842 order by id desc limit 0,1;
# User@Host: ggssw[ggssw] @ localhost [127.0.0.1]
# Query_time: 9 Lock_time: 2 Rows_sent: 1 Rows_examined: 64830
select id,title from so_subject where oneid=11 and id < 85427 order by id desc limit 0,1;
# User@Host: ggssw[ggssw] @ localhost [127.0.0.1]
# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 64830
select id,title from so_subject where oneid=11 and id > 158043 order by id desc limit 0,1;
EXPLAIN select id,title from so_subject where oneid=11 order by day_num desc limit 0,12\G看下其执行计划.....是不是无索引可走,还是索引执行计划有问题...
2 oneid看下是int的还是varchar的,如果是varchar改成int之类的类型,或者用oneid='11'
检查的步骤看一下binlog日志具体的操作。
另外查看一下具体的进程show processlist