两种分页方式 2022-07-27 15:38 ### 1、最常见的limit pageIndex, pageSize 最常见的分页方式就是使用数据库关键字limit的偏移量功能，每次查询n个；下次查询时向前或向后偏移x个，然后再查询出n个。 例如，如果页数是从1开始，每页展示10条数据。 - 第一页就是limit (1-1)*10, 10，即limit 0, 10 - 第二页就是limit (2-1)*10, 10，即limit 10, 10 - 第三页就是limit (3-1)*10, 10，即limit 20, 10 前面拼接上需要的查询条件即可。例如按照时间倒序展示： - 第一页：select * from user where is_active = 1 order by update_time desc limit 0, 10 - 第二页：select * from user where is_active = 1 order by update_time desc limit 10, 10 - 第三页：select * from user where is_active = 1 order by update_time desc limit 20, 10 #### 代码示例： model.model.go： ```go package model import ( "database/sql" _ "github.com/go-sql-driver/mysql" "github.com/go-xorm/xorm" "strings" ) var ( db *DB ) func NewDb(dsn, dbName string, showSql bool, beans ...interface{}) (*DB, error) { if err := CreateDatabase(dsn, dbName); err != nil { return nil, err } engine, err := xorm.NewEngine("mysql", dsn+dbName) if err != nil { return nil, err } db = &DB{engine, dbName} if err := db.initDB(beans, showSql); err != nil { db.CustomerClose(true) return nil, err } return db, nil } func CreateDatabase(dsn, dbName string) error { db, err := sql.Open("mysql", dsn) if err != nil { return err } defer db.Close() _, err = db.Exec("CREATE DATABASE " + dbName) if err != nil && strings.Contains(err.Error(), "database exists") { return nil } return err } type DB struct { Engine *xorm.Engine Name string } // isDrop 关闭时是否删除创建的库 func (db *DB) CustomerClose(isDrop bool) error { defer db.Engine.Close() if isDrop { _, err := db.Engine.DB().DB.Exec("DROP DATABASE " + db.Name) return err } return nil } func (db *DB) initDB(beans []interface{}, showSql bool) error { //if testing.Verbose() { db.Engine.ShowExecTime(showSql) db.Engine.ShowSQL(showSql) //} return db.Engine.Charset("utf8mb4").Sync2(beans...) } ``` model.company.go： ```go package model import ( "time" ) type Company struct { ID int64 `xorm:"'id' BIGINT(20) pk autoincr"` Abbreviation string `xorm:"'abbreviation' index(idx_abbreviation) VARCHAR(64) NOT NULL"` FullName string `xorm:"'full_name' unique VARCHAR(128) NOT NULL"` TaxNumber string `xorm:"'tax_number' VARCHAR(128) DEFAULT ''"` OrderNumber int64 `xorm:"'order_number' INT DEFAULT 0"` Memo string `xorm:"'memo' VARCHAR(256) DEFAULT ''"` Auth bool `xorm:"'auth' BOOL DEFAULT false"` AuthUpdatedAt time.Time `xorm:"'auth_update_at' created NOT NULL"` CreatedAt time.Time `xorm:"'created_at' created NOT NULL"` UpdatedAt time.Time `xorm:"'updated_at' updated NOT NULL"` } func InsertCompany(company *Company) (i int64, err error) { session := db.Engine.NewSession() defer session.Close() i, err = session.Insert(company) return } func ListCompany1(pageNum, pageSize int) (companies []*Company, err error) { session := db.Engine.NewSession() defer session.Close() if pageSize < 0 || pageSize >= 1000 { pageSize = 50 } if pageNum < 1 { pageNum = 1 } pageIndex := (pageNum - 1) * pageSize err = session.Select("id, full_name").Where("auth = false").Desc("updated_at").Limit(pageSize, pageIndex).Find(&companies) return } ``` Test.Test.go ```go package main import ( "TestGo/xorm/model" "encoding/json" "fmt" "time" ) func main() { showSql := true db, err := model.NewDb("root:hanxu@tcp(127.0.0.1)/", "test_company_auth", showSql, new(model.Company)) if err != nil { panic(err) } isDropDB := false defer db.CustomerClose(isDropDB) insertData() pageSize := 5 // 常规分页 pageOne(pageSize) } func pageOne(pageSize int) { for pageNum := 1; pageNum <= 3; pageNum++ { companies, err := model.ListCompany1(pageNum, pageSize) if err != nil { fmt.Printf("pageNum:%v, err:%v", pageNum, err.Error()) } else { print(companies) fmt.Println() } } } func insertData() { for i := 0; i < 12; i++ { company := &model.Company{ Abbreviation: fmt.Sprintf("abbr%v", i+1), FullName: fmt.Sprintf("fullName%v", i+1), Auth: false, } model.InsertCompany(company) //为了区分出时间，睡眠1s time.Sleep(1e9) } fmt.Println() } func print(companies []*model.Company) { for _, c := range companies { bytes, err := json.Marshal(c) if err != nil { fmt.Println("print Err:", err) } else { fmt.Println(string(bytes)) } } } ``` #### 执行结果： ``` [xorm] [info] 2022/07/27 11:46:06.356865 [SQL] SELECT id, full_name FROM `company` WHERE (auth = false) ORDER BY `updated_at` DESC LIMIT 5 - took: 303.282µs {"ID":12,"Abbreviation":"","FullName":"fullName12","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":11,"Abbreviation":"","FullName":"fullName11","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":10,"Abbreviation":"","FullName":"fullName10","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":9,"Abbreviation":"","FullName":"fullName9","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":8,"Abbreviation":"","FullName":"fullName8","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} [xorm] [info] 2022/07/27 11:46:06.357285 [SQL] SELECT id, full_name FROM `company` WHERE (auth = false) ORDER BY `updated_at` DESC LIMIT 5 OFFSET 5 - took: 209.519µs {"ID":7,"Abbreviation":"","FullName":"fullName7","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":6,"Abbreviation":"","FullName":"fullName6","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":5,"Abbreviation":"","FullName":"fullName5","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":4,"Abbreviation":"","FullName":"fullName4","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":3,"Abbreviation":"","FullName":"fullName3","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} [xorm] [info] 2022/07/27 11:46:06.357586 [SQL] SELECT id, full_name FROM `company` WHERE (auth = false) ORDER BY `updated_at` DESC LIMIT 5 OFFSET 10 - took: 206.874µs {"ID":2,"Abbreviation":"","FullName":"fullName2","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":1,"Abbreviation":"","FullName":"fullName1","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} ``` ### 2、使用limit配合id筛查范围 上面一种方式通常被认为在数据量很大时有性能问题。因为通常需要范围数据总量total（select count(*)）；进行随机跳转时，有可能出现limit 10000, 10这种情况，执行效率较低。 还有一种方式是使用id作为每次分页的“查询起点”，即总是在条件id > x，或者id < y的前提下，才进行limit n的查询。由于id总是作为主键，所以查询时这个“查询起点”特别方便定位，执行效率也很高。然后limit只取后续的n条数据，而不使用其偏移功能。 例如，总共22条数据的表： - 第一页是 select * from user where is_active = 1 order by id desc limit 10 （查出数据id范围：[22, 13]） - 下一页（第二页）是 select * from user where id_active = 1 and id < 13 order by id desc limit 10 （查出数据id范围：[12, 3]） - 下一页（第三页）是 select * from user where id_active = 1 and id < 3 order by id desc limit 10 （查出数据id范围：[2, 1]） - 上一页（第二页）是 select * from user where id_active = 1 and id > 2 order by id asc limit 10 （查出数据id范围：[12, 3]） #### 代码示例： model.company.go新增方法： ```go func ListCompany2(marker int64, pageSize int, isPrev bool) (prev, next int64, companies []*Company, err error) { session := db.Engine.NewSession() defer session.Close() if pageSize < 0 || pageSize >= 1000 { pageSize = 50 } if isPrev { session.Asc("id") if marker > 0 { session.Where("id > ?", marker) } } else { session.Desc("id") if marker > 0 { session.Where("id < ?", marker) } } err = session.Select("id, full_name").Where("auth = false").Limit(pageSize).Find(&companies) if err != nil { return } //如果是向前一页，这里查出来是正序的（order by id asc），需要颠倒一下 if isPrev { j := len(companies) - 1 for i := 0; i < j; i++ { companies[i], companies[j] = companies[j], companies[i] j-- } } len := len(companies) if len > 0 { prev = companies[0].ID next = companies[len-1].ID } return } ``` Test.Test.go新增代码： ```go func main() { showSql := true db, err := model.NewDb("root:hanxu@tcp(127.0.0.1)/", "test_company_auth", showSql, new(model.Company)) if err != nil { panic(err) } isDropDB := false defer db.CustomerClose(isDropDB) //insertData() pageSize := 5 // 常规分页 //pageOne(pageSize) // 另一种分页 pageTwo(err, pageSize) } func pageTwo(err error, pageSize int) { // 第一页 prev, next, companies, err := model.ListCompany2(0, pageSize, false) print(companies) fmt.Println() // 下一页（第二页） prev, next, companies, err = model.ListCompany2(next, pageSize, false) print(companies) fmt.Println() // 下一页（第三页） prev, next, companies, err = model.ListCompany2(next, pageSize, false) print(companies) fmt.Println() // 上一页（第二页） prev, next, companies, err = model.ListCompany2(prev, pageSize, true) print(companies) fmt.Println() } ``` #### 执行结果： ``` [xorm] [info] 2022/07/27 11:50:33.957107 [SQL] SELECT id, full_name FROM `company` WHERE (auth = false) ORDER BY `id` DESC LIMIT 5 - took: 351.163µs {"ID":12,"Abbreviation":"","FullName":"fullName12","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":11,"Abbreviation":"","FullName":"fullName11","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":10,"Abbreviation":"","FullName":"fullName10","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":9,"Abbreviation":"","FullName":"fullName9","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":8,"Abbreviation":"","FullName":"fullName8","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} [xorm] [info] 2022/07/27 11:50:33.957754 [SQL] SELECT id, full_name FROM `company` WHERE (id < ?) AND (auth = false) ORDER BY `id` DESC LIMIT 5 []interface {}{8} - took: 375.902µs {"ID":7,"Abbreviation":"","FullName":"fullName7","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":6,"Abbreviation":"","FullName":"fullName6","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":5,"Abbreviation":"","FullName":"fullName5","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":4,"Abbreviation":"","FullName":"fullName4","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":3,"Abbreviation":"","FullName":"fullName3","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} [xorm] [info] 2022/07/27 11:50:33.958221 [SQL] SELECT id, full_name FROM `company` WHERE (id < ?) AND (auth = false) ORDER BY `id` DESC LIMIT 5 []interface {}{3} - took: 297.925µs {"ID":2,"Abbreviation":"","FullName":"fullName2","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":1,"Abbreviation":"","FullName":"fullName1","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} [xorm] [info] 2022/07/27 11:50:33.958626 [SQL] SELECT id, full_name FROM `company` WHERE (id > ?) AND (auth = false) ORDER BY `id` ASC LIMIT 5 []interface {}{2} - took: 308.305µs {"ID":7,"Abbreviation":"","FullName":"fullName7","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":6,"Abbreviation":"","FullName":"fullName6","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":5,"Abbreviation":"","FullName":"fullName5","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":4,"Abbreviation":"","FullName":"fullName4","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} {"ID":3,"Abbreviation":"","FullName":"fullName3","TaxNumber":"","OrderNumber":0,"Memo":"","Auth":false,"AuthUpdatedAt":"0001-01-01T00:00:00Z","CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z"} ``` ### 效率对比 ```sql explain SELECT id, full_name FROM `company` WHERE (auth = false) ORDER BY `updated_at` DESC LIMIT 5 OFFSET 5 ```  ```sql explain SELECT id, full_name FROM `company` WHERE (id < 8) AND (auth = false) ORDER BY `id` DESC LIMIT 5 ```  第一种方式type是ALL，查询时会进行完整的表扫描（rows是12）。第二种方式type是range，会使用索引来检查记录，后面也显示了，使用的索引（key）是PRIMARY，扫描的行数（rows）是7。 ### 总结与思考 第一种分页方式是最常见的；第二种方式通常来说比第一种方式执行效率要高一点，因为使用了id筛查数据。但是第一种方式作用范围非常普遍，几乎任何需求都能实现；第二种方式有其自身的限制。 1、首先就是第一种方式可以显示页码：1、2、3、4、5、6...，可以直接跳转到第几页：从第一页直接点第六页。 而第二种方式只有「上一页」「下一页」两个操作，无法跳转某页。 2、其次是第二种方式大部分情况下只能按照id正/逆序排序，如果想使用其他字段的话，必须要保证该字段有序且不会重复，否则就会有问题。 例如，现在我想按照修改时间逆序排序（上面的例子是按照id逆序排序），但是修改时间这个字段是不能保证唯一的，也就是说有可能有两条记录，他们的修改时间相同，这时使用第二种方式，一定会出现展示错误：某条数据查不出来。 原因是相同的记录可能会被筛除掉，即使他还没有被查出。where update_time > ? order by update_time desc limit ?。 你可能会说我不用update_time比较不就好了，我用id，id可是唯一的，where id > ? order by update_time desc limit ?。其实这样也是会有问题的，我直接给你一个会出问题的示例好了： 12 2022-07-27 10:59:20 11 2022-07-27 10:59:19 1 2022-07-27 10:59:18 2 2022-07-27 10:59:17 如果一页有三条数据，查出第一页后，就没法查出第二页数据了。另外第二张方式本质上就只能在where 和order by 中使用同一个字段。 --END--
发表评论