如何提高MySQL数据分页效率

WBOY
Release: 2016-06-07 16:21:13
Original
864 people have browsed it

本节的数据编辑插教程给我们简单介绍如何提高MySQL数据分页效率,下面我们将会给出的这段代码是大数据量时提高分页的效率的测试代码。 如果你也需要设置MySQL数据提高分页效率,那么,就一起来看看代码段吧! --提高分页效率:实现分页时只读取显示数据,需要先

  本节的数据编辑插教程给我们简单介绍如何提高MySQL数据分页效率,,下面我们将会给出的这段代码是大数据量时提高分页的效率的测试代码。

  如果你也需要设置MySQL数据提高分页效率,那么,就一起来看看代码段吧!

  --提高分页效率:实现分页时只读取显示数据,需要先在数据库创建数据库“TestForPaging”

  use TestForPaging

  go

  --创建表SomeData

  create table SomeData

  (

  id int primary key,

  name varchar(30) null,

  description text

  )

  go

  --插入数据

  insert into SomeData values(1,'num1','第1条')

  go

  insert into SomeData values(2,'num2','第2条')

  go

  insert into SomeData values(3,'num3','第3条')

  go

  insert into SomeData values(4,'num4','第4条')

  go

  insert into SomeData values(5,'num5','第5条')

  go

  --数据条目总数

  select count(*) from SomeData

  go

  --给每条记录添加一个数据级别

  select name,description,ROW_NUMBER() over(order by id desc)as dataLevel from SomeData

  go

  --查看指定的数据级别间的数据条目

  select dataLevel,name,description from

  (select name,description,row_number() over(order by id desc)as dataLevel from SomeData)

  as datawithleverl where dataLevel between 2 and 4

  go

  --实现查看指定的数据级别间的数据条目的存储过程

  create procedure GetDataPaged

  (

  @startRowIndex int,

  @maximumRows int,

  @sort varchar

  )

  AS

  --确保指定sort

  if len(@sort)=0

  set @sort='id'

  --带参数的查询

  select dataLevel,name,description from

  (select name,description,row_number() over(order by @sort desc)as dataLevel from SomeData) AS datawithleverl

  WHERE dataLevel > (@startRowIndex*10) AND dataLevel

  go

source:php.cn
Statement of this Website
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
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!