mysql 分頁預存程序,mysql預存程序

來源:互聯網
上載者:User

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 ;


 

 

 

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

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.