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行      

此随筆或為自己所寫、或為轉載于網絡。僅用于個人收集及備忘。