Home >Database >Mysql Tutorial >sql千万级数据分页效率分析与方法

sql千万级数据分页效率分析与方法

WBOY
WBOYOriginal
2016-06-07 17:48:161073browse

sql千万级数据效率分析与方法

top 100 * from (select top 500 * from T1 order by id asc) a order by id desc

先asc取前500条 再倒着取(desc)这个结果的100条...

MSSQL在我这里测试通过

这个ID字段如果是默认的升序序列 也是主键列 那下面这句就能实现了
select * from t1 where id between 400 and 500

我就知道三种方法 having in   between and  和 >= and 测试过 between and 的效率应该是3者最高的


首先: id>400 and id id>=401 and id

最好的方法:用FIRST_ROWS(n)优化器提示:用它的目的是提高SQL语句的响应时间,快速的先返回 n 行

另外between and 的效率最高

select /* FIRST_ROWS (100) */
id, username from t1
where id between 401 and 500

最后总结

在查询点来看,用between...and比取“”号的效率要高
select a.*
from table1 a
where rownum between 10000001 and 10000101

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn