程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql分頁存儲過程

mysql分頁存儲過程

編輯:MySQL綜合教程

mysql分頁存儲過程


網上關於mysql分頁存儲過程的資料很多,但內容大同小異。作為初學者,引用mysql存儲過程如下:

<p> </p><p>DELIMITER $$</p><p>USE `database1`$$ -- 數據庫名稱</p><p>DROP PROCEDURE IF EXISTS `Query_Pagination`$$  -- 分頁存儲過程名稱,存在則刪除</p><p>CREATE <a target=_blank href="mailto:DEFINER=`root`@`%">DEFINER=`root`@`%</a>` PROCEDURE `Query_Pagination`(  -- 創建新的分頁存儲過程
  IN _fields VARCHAR (2000), -- 顯示的字段
  IN _tables TEXT, -- 表名
  IN _where VARCHAR (2000), --  where條件,可為空
  IN _orderby VARCHAR (200), -- 排序條件,可為空
  IN _pageindex INT, -- 開始頁
  IN _pagesize INT, -- 每頁大小
  OUT _totalcount INT, -- 總共行數
  OUT _pagecount INT --  總共頁數
)
BEGIN
  SET @startrow = _pagesize * (_pageindex - 1) ;
  SET @pagesize = _pagesize ;
  SET @rowindex = 0 ;
  SET @strsql = CONCAT(
    ' select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,', -- 顯示每條的行號
    _fields,
    ' from ',
    _tables,
    CASE
      IFNULL(_where, '') 
      WHEN '' 
      THEN '' 
      ELSE CONCAT(' where ', _where) 
    END,
      CASE
      IFNULL(_orderby, '') 
      WHEN '' 
      THEN '' 
      ELSE CONCAT(' order by ', _orderby) 
    END,  
    ' limit ',
    @startRow,
    ',',
    @pageSize
  ) ;
  PREPARE strsql FROM @strsql ;
  EXECUTE strsql ;
  SET _totalcount = FOUND_ROWS() ;
  IF (_totalcount <= _pagesize) 
  THEN SET _pagecount = 1 ;
  ELSE IF (_totalcount % _pagesize > 0) 
  THEN SET _pagecount = _totalcount / _pageSize + 1 ;
  ELSE SET _pagecount = _totalcount / _pageSize ;
  END IF ;
  END IF ;
END$$</p><p>DELIMITER ;</p><p> </p>

上述分頁存儲過程的確很好用,但是當數據量達到百萬級時,發現速度就會下降,用explain執行以下語句:

EXPLAIN SELECT SQL_CALC_FOUND_ROWS @rowindex:=@rowindex+1 AS rownumber FROM view_visitregisterinfo WHERE CardType='1' ORDER BY tableid DESC LIMIT 0,1000;

執行結果如下:

\
可以看到,雖然用了limit 但是依然檢索了整個表,導致查詢緩慢。執行時間為16秒。

去掉SQL_CALC_FOUND_ROWS @rowindex:=@rowindex+1 AS rownumber ,查詢語句改為EXPLAIN SELECT * FROM view_visitregisterinfo WHERE 1=1 AND CardType='1' ORDER BY tableid DESC LIMIT 0,1000; 執行結果如下:\

可以看到,執行的行數為1000,執行時間為0.038s,時間加快了很多。

所以,修改存儲過程為:

DELIMITER $$

USE `speednew`$$ -- 數據庫名稱

DROP PROCEDURE IF EXISTS `Query_Pagination`$$  -- 分頁存儲過程名稱,存在則刪除

CREATE DEFINER=`root`@`%` PROCEDURE `Query_Pagination`(  -- 創建新的分頁存儲過程
  IN _fields VARCHAR (2000), -- 顯示的字段
  IN _tables TEXT, -- 表名
  IN _where VARCHAR (2000), --  where條件,可為空
  IN _orderby VARCHAR (200), -- 排序條件,可為空
  IN _pageindex INT, -- 開始頁
  IN _pagesize INT, -- 每頁大小
  OUT _totalcount INT, -- 總共行數
  OUT _pagecount INT --  總共頁數
)
BEGIN
  SET @startrow = _pagesize * (_pageindex - 1) ;
  SET @pagesize = _pagesize ;
  SET @rowindex = 0 ;
  SET @strsql = CONCAT(
    ' select ', 
    _fields,
    ' from ',
    _tables,
    CASE
      IFNULL(_where, '') 
      WHEN '' 
      THEN '' 
      ELSE CONCAT(' where ', _where) 
    END,
      CASE
      IFNULL(_orderby, '') 
      WHEN '' 
      THEN '' 
      ELSE CONCAT(' order by ', _orderby) 
    END,  
    ' limit ',
    @startRow,
    ',',
    @pageSize
  ) ;
  PREPARE strsql FROM @strsql ;
  EXECUTE strsql ;
  SET _totalcount = FOUND_ROWS() ;
  IF (_totalcount <= _pagesize) 
  THEN SET _pagecount = 1 ;
  ELSE IF (_totalcount % _pagesize > 0) 
  THEN SET _pagecount = _totalcount / _pageSize + 1 ;
  ELSE SET _pagecount = _totalcount / _pageSize ;
  END IF ;
  END IF ;
END$$

DELIMITER ;


總結經驗:引用現有的東西時,一定要根據個人的情況進行二次修改,改為適合自己的,同時要明白別人原先那樣寫的原因。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved