- 论坛徽章:
- 0
|
5可用积分
我是在实现大数据量排序查询,并分页显示查询结果时碰到这个问题,我的查询语句如下:
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 编辑 ] |
最佳答案
查看完整内容
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的列做)你真个表设计得。。。还是有 ...
|