int bigint int
id nr time这是一个百万的数据表,mysql 100%
语句:
SELECT id,cx_time FROM k_cx WHERE nr=13400043001 ORDER BY id DESC LIMIT 20;//所用时间1.058s左右,请问我该怎么优化 经测试如下语句:
SELECT id,cx_time FROM k_cx ORDER BY id DESC LIMIT 20;
//0.0s
SELECT id,cx_time FROM k_cx WHERE nr=13400043001 ORDER BY id DESC;
//0.187s
这条现在凑活用着,服务器正常了.但是还是想要只取前20条.该怎么优化呢,请问,各位高手.
id nr time这是一个百万的数据表,mysql 100%
语句:
SELECT id,cx_time FROM k_cx WHERE nr=13400043001 ORDER BY id DESC LIMIT 20;//所用时间1.058s左右,请问我该怎么优化 经测试如下语句:
SELECT id,cx_time FROM k_cx ORDER BY id DESC LIMIT 20;
//0.0s
SELECT id,cx_time FROM k_cx WHERE nr=13400043001 ORDER BY id DESC;
//0.187s
这条现在凑活用着,服务器正常了.但是还是想要只取前20条.该怎么优化呢,请问,各位高手.
SELECT id,cx_time FROM k_cx WHERE nr=13400043001 ORDER BY id DESC LIMIT 20;//所用时间1.058s左右
SELECT id,cx_time FROM k_cx WHERE nr=13400043001 ORDER BY id DESC;
//0.187s
怎么可能加上limit后反而效率低了这么多,贴出你的explain
参考:http://stackoverflow.com/questions/20148/myisam-versus-innodb其次建立索引
ALTER TABLE `k_cx` ADD INDEX `nr` (`nr`)然后,增加sort_buffer_size 和read_rnd_buffer_size在你的my.cnf或者my.ini里
参考:http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html