✅✅프로젝트 개선점) 쿼리튜닝

데이터 5000만건, 페이징처리를 위해 특정데이터를 반환할 때 쿼리 실행 시 14초 이상 걸림 - 개선할 것

use springLegacyProject;

insert into tbl_board (bno, title, content, writer)(Select nextval('seq_board'),title, content, writer from tbl_board) limit 1000;
explain select * from tbl_board;

//deprecated
select @profiling;

set profiling = 0;

explain select * from tbl_board order by bno +1 desc;

explain select * from tbl_board order by bno desc;

explain tbl_board;
 select /*+ index_Desc(tbl_board pk_board)*/ * from tbl_board where bno >0;

show index from tbl_board;

explain select * from tbl_board where bno = 34;

select * from tbl_board order by bno desc;


show processlist;

SHOW STATUS LIKE '%innodb_buffer_pool%';

select * from tbl_board where bno > 1000;

SELECT COUNT(*) FROM tbl_board;

select /* + INDEX_ASC  (tbl_board pk_board) */ * from tbl_board where bno > 0 ;

SELECT * FROM TBL_BOARD  WHERE BNO >0 ORDER BY BNO DESC;


SELECT @ROWNUM := @ROWNUM + 1 AS RN FROM tbl_board WHERE (@ROWNUM:=0) = 0;

SELECT A.RN
    FROM (
           SELECT @ROWNUM := @ROWNUM + 1 AS RN
             FROM tbl_board A, (SELECT @ROWNUM:=0) RN_TB
         ) A
ORDER BY RN DESC;

 SELECT @ROWNUM:=@ROWNUM+1 AS ROWNUM
     , t.*
  FROM ( SELECT @ROWNUM := 0) R
     , tbl_board t 
 where @rownum <20 ORDER BY bno DESC ;
 
 select rownum, bno, title, content from (
 
  SELECT @ROWNUM:=@ROWNUM+1 AS ROWNUM
     , t.*
  FROM ( SELECT @ROWNUM := 0) R
     , tbl_board t 
 where @rownum <20 ORDER BY bno DESC) AS I where @rownum > 10;