大数据量查询优化问题

独步古今

表: t1的记录数有8000万，已经建了索引。利用索引进行查询时，用时40秒左右，用show processlist查看状态一直为Sending data，请教各位大侠有没有什么高招？

sql语句如下： select device_ip, create_time, source_ip from t1 where device_ip = 3232241510 limit 10000;
(注：IP是数值型的，用explain分析也是有用上索引的。)


据个人看法：因为sql语句很简单，而且用上了索引。不至于那么慢的，现在是不是考虑服务器参数调优方面， 同样的语句，参数调整OK的话，查询速度也就上去了，各位大侠，你们觉得呢？

kider

首先有两点你可以提高的：

1、如果需要返回值很大，客户端与服务器端又沟通很多，考虑用存储过程。

2、limit 10000 可以修改成翻页，返回值尽量的小些，如limit 1,100，速度会快

然后才是服务器调整参数，是一个好些参数的综合结果，请先看看官方参考手册中关于参数一章....

独步古今

kider说得好，
用limit100 用时3秒,limit10000用时40秒。所以，采取分页是可行的。谢谢kider

但主要还是因为表数据量大。如果表数据只有几万条记录的话，limit200和limit10000没有什么区别。
所以，请问kider像这种大表查询时，limit的不同值差别很大时，真的要优化服务器参数的话，主要和哪些服务器参数有关呢？我们该调整哪些服务器参数呢？

独步古今

我们情况比较特殊，limit分页好像不可行。
       因为我们要求页面上最多可以展示的是10000条记录(分页显示)，然后这10000条记录有可能是从多张表查询出来进行组合的。
       假如有4张表，t1,t2,t3,t4，且每张表都是8000万记录，然后从t1可以查到10条,t2可以查到20条，t3可以查到70条，t4可以查到9900条。假设前台分页时，每页显示200条，这样，分页时，就跨过了好几张后台数据库表。
       所以分页时，也不是很好做的。如果能够直接把limit 10000的性能提上去了，这样我们后台查询时，只要加上limit 10000就OK了。也就是我们把查询列表直接返回给前台就OK了，分页由前台去做，而不用在数据库后台来做了。因此我们重点还得放在优化服务器参数上面了，sql语句比较简单，如果不考虑limit值调小外，似乎没有什么优化的余地了。

kider

也跟存储引擎有关。

当调节MySQL服务器时，要配置的两个最重要的变量是key_buffer_size和table_cache。在试图更改其它变量前你应先确信已经适当地配置了这些变量。

max_allowed_packet              

详细请参考：
http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#server-system-variables

mysqlkumao