mysql limit 分页优化及mysql分页方法

查询从第1000000之后的30条记录：

SQL代码1：平均用时6.6秒 SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

SQL代码2：平均用时0.6秒 SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM 
`cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30

 mysql 分页方法 

        /// <summary>
        /// 获取数据集分页信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="orderBy"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public IList<T> QueryPaged<T>(string sql, object param = null, int pageSize = 10, int pageIndex = 1, string orderBy = "Id", SortOrder order = SortOrder.Unspecified)
        {
            if (pageSize <= 0)
            {
                throw new ArgumentOutOfRangeException(nameof(pageSize));
            }
            if (pageIndex < 0)
            {
                throw new ArgumentOutOfRangeException(nameof(pageIndex));
            }

            int offset = (pageIndex > 0) ? (pageIndex - 1) * pageSize : 0;
            var sortOrder = order == SortOrder.Descending ? "desc" : "asc";
            var pageDataSql = $"select * from (select @rownum:=@rownum+1 as rn,t.* from (select @rownum:=0) r, ({sql}) t order by {orderBy} {sortOrder}) AS b where b.rn> {offset} limit {pageSize}";
            using (IDbConnection dbConnection = Connection)
            {
                return dbConnection.Query<T>(pageDataSql, param).ToList();
            }
        }

 

--语法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

--举例：

select * from table limit 5; --返回前5行

select * from table limit 0,5; --同上，返回前5行

select * from table limit 5,10; --返回6-15行

 

 

 

i源文 ：https://www.cnblogs.com/fanyong/p/3351350.html