大数据量排序查询，并分页显示查询结果的优化

alaix_xu

我是在实现大数据量排序查询，并分页显示查询结果时碰到这个问题，我的查询语句如下：
select * from session where ... order by appname asc limit 0,50; 这是显示第一页的SQL，
当点击下一页时，执行：select * from session where ... order by appname asc limit 51,100;
页数由select count(*) from session  where ...得到的数量除以50得到。

当数据量较大时，比如有10万条记录的时候，每次翻页执行SQL语句都很慢，要3秒以上；
这是EXPLAIN的结果：
+----+-------------+---------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | session | ALL  | NULL          | NULL | NULL    | NULL | 33539 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+-------+----------------+
这是表结构：
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | varchar(20) | NO   | PRI |         |       |
| appname   | mediumtext  | YES  |     | NULL    |       |
| username  | mediumtext  | YES  |     | NULL    |       |
| starttime | int(11)     | YES  |     | NULL    |       |
| endtime   | int(11)     | YES  |     | NULL    |       |
| clientIP  | mediumtext  | YES  |     | NULL    |       |
| serverIP  | mediumtext  | YES  |     | NULL    |       |
| filepath  | mediumtext  | YES  |     | NULL    |       |
| apptype   | mediumtext  | YES  |     | NULL    |       |
| size      | int(11)     | YES  |     | NULL    |       |
| hostuser  | mediumtext  | YES  |     | NULL    |       |
| extend1   | mediumtext  | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

各位大侠，有什么办法能提高每次翻页执行SQL的速度？

我尝试过的几个方法：
1. 用MySQL的内部临时表机制（Uses Internal Temporary Tables，mysql文档里提到的）
Temporary tables can be created under conditions such as these:
    * If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
    * If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table.
    * DISTINCT combined with ORDER BY may require a temporary table.
但发现这只是针对相同的SQL语句，当第一次执行时产生临时表，第二次执行相同的语句时才能直接从临时表中取数据,
而我执行翻页语句后面都有limit，取不同的范围，所以每次翻页执行的语句不一样，这种机制也使用不上。

2. 使用Mysql cache机制（MySQL Query Cache）
这个机制跟上面类似，不同的是cache肯定是在内存中，但是上面的内部临时表可能在内存也可能在硬盘中。
这种机制也只能对执行相同的SQL语句才能起到优化效果，哪怕SQL语句有一个字符大小写差别，都不认为是同一条语句。
The query cache stores the text of a SELECT  statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

3. 将每次排序好的所有结果缓存到客户端，也就是先将select * from session where ... order by appname asc的结果存到本地数据库session_cache中，然后翻页时直接select * from session_cache limit 51,100;就相当快了。 这样做有个问题，就是缓存到本地的数据可能会相当大，比如10万条记录，从服务器下到客户端，超过15秒才能完成，速度很慢；100万条就更不可想象了。

4. 将每次排序好的所有结果缓存在mysql服务器同一个数据库中，也就是将select * from session where ... order by appname asc的结果存到同一个数据库另外一张临时表session_cache中，可以使用如下语句insert into session_cache (select * from session where ... order by appname asc),这样数据也不用经网络传输，速度还可以，以后翻页时执行select * from session_cache limit 51,100即可。但这样做仍然有问题：当多个客户端进行并发查询操作时，就会出现大家都要将查询的结果放在session_cache中，每次放之前都会做清空动作，这会导致客户端获取的数据错误。客户端只有select权限，无法动态创建表。所以这种方法也无法解决问题。


各位大侠，不知道大家怎么处理这个问题，还望不吝赐教！

[ 本帖最后由 alaix_xu 于 2008-10-20 19:27 编辑 ]

gogo407

33539 行的数据还是比较少了
我抛砖引玉一下吧：）
order by appname　非要这样吗，你用char和varchar不行吗
id 需要varchar吗你用int不行吗
select * from table where key=a order by b asc limit 0,50
建立ab的联合索引
当需要查找的数大于count(*)/2的时候可以使用倒序取
--------------------------------------------------------
还有很多优化的方法，包括做临时表（由id,where条件，order by的列做）
你真个表设计得。。。还是有很多欠缺的地方啊：）
需要详细指导，可以站内短，因为需要更加详细点的信息

wildlily980

你的where条件是什么?

yueliangdao0608

用ID来分页吧。

alaix_xu

在客户端，用户是可以选择任何一个字段进行全排序，然后分页进行查看呀，这是很平常的需求，用appname进行order by，只是举个例子

alaix_xu

where条件有很多，根据用户在GUI上的配置生成，可能是任何几个字段的条件组合，比如clientIP=‘172.22.0.1’，starttime=1245789566等等

alaix_xu

原帖由 gogo407 于 2008-10-20 22:10 发表
33539 行的数据还是比较少了
我抛砖引玉一下吧：）
order by appname　非要这样吗，你用char和varchar不行吗
id 需要varchar吗你用int不行吗
select * from table where key=a order by b asc limit 0,50
...

谢谢你。
我解释一下：
1. 33539 行的数据只是其中一台服务器的数据，很多大数据量的服务器没有拿出来；
2. order by appname只是举个例子，可能是其中任何一个字段，根据用户在GUI上的选择；
3. varchar、text、mediumtext都可以，当初表设计成这样，我就没有把mediumtext改成text或者varchar了，这对效率会有很大影响吗？
4. 这个表的id有特殊意义，只能用字符串的形式；
5. where子句中的条件可能是任何几个字段的组合条件，这是根据用户在GUI上的选择生成的。

wildlily980

有没有考虑过内存表?

7islands

可以每次取500行在客户端，够分10页了。
谁这么无聊会翻这么多页阿！ 不够了再取。
google也不是把所以的查出来，等你点下100页的吧   

别的优化另说。

sunnyfun

像 clientIP 这种字段没必要 mediumtext  吧，和 varchar 相比在索引的支持度上是不一样的。
极端的查询优化就是对每个可能要排序的字段都单独建索引，负载高的话就读写分离吧。