mysql优化_mysql sql优化_mysql优化面试题(5)

pageSize=20;

pageNo = 5;

1.分页技术1（直接利用sql语句进行分页，效率最高和最推荐的）

mysql:sql = 'select * from articles limit ' + (pageNo-1)*pageSize +',' + pageSize;

oracle: sql = 'select * from ' +

'(select rownum r,* from ' +

'(select * from articles order by postime desc)' +

'where rownum<= ' + pageNo*pageSize +') tmp ' +

'where r>' + (pageNo-1)*pageSize;

注释：第7行保证rownum的顺序是确定的，因为oracle的索引会造成rownum返回不同的值

简洋提示：没有order by时，rownum按顺序输出，一旦有了orderby，rownum不按顺序输出了，这说明rownum是排序前的编号。如果对orderby从句中的字段建立了索引，那么，rownum也是按顺序输出的，因为这时候生成原始的查询结果集时会参照索引表的顺序来构建。

sqlserver:sql = 'select top 10 * from id not id(select top ' +(pageNo-1)*pageSize + 'id from articles)'

DataSource ds = new InitialContext().lookup(jndiurl);

Connection cn = ds.getConnection();

//'select * from user where id=?' --->binary directive

PreparedStatement pstmt = cn.prepareSatement(sql);

ResultSet rs = pstmt.executeQuery()

while(rs.next())

{

out.println(rs.getString(1));

}

2.不可滚动的游标

pageSize=20;

pageNo = 5;

cn = null

stmt = null;

rs = null;

try

{

sqlserver:sql = 'select * from articles';

DataSource ds = new InitialContext().lookup(jndiurl);

Connection cn = ds.getConnection();

//'select * from user where id=?' --->binary directive

PreparedStatement pstmt = cn.prepareSatement(sql);

ResultSet rs = pstmt.executeQuery()

for(int j=0;j<(pageNo-1)*pageSize;j++)

{

rs.next();

}

int i=0;

while(rs.next() && i<10)

{

i++;

out.println(rs.getString(1));

}

}

cacth(){}

finnaly

{

if(rs!=null)try{rs.close();}catch(Exception e){}

if(stm.........

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-34607-5.html