* OPTIMIZE TABLE
: 물리적인 디스크 조각모음
- 테이블의 단편화 현상을 해결, 차지하는 용량을 줄임

OPTIMIZE TABLE후  innodb_optimize_fulltext_only=OFF 로 되돌려야 한다

show variables LIKE 'innodb_optimize_fulltext_only%';
set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE users;
set GLOBAL innodb_optimize_fulltext_only=OFF;
show variables LIKE 'innodb_optimize_fulltext_only%';


- DB의 모든 테이블에 대해 실행
mysqlcheck -u root -p --optimize --databases dbname


//----------------------
ANALYZE TABLE
:  인덱스를 재생성하여 성능을 최적화, 키를 재분배

ANALYZE TABLE table_name


- DB의 모든 테이블에 대해 실행
mysqlcheck -u root -p --analyze --databases dbname


//------------------
//참고
https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html
https://wwarchblog.wordpress.com/2016/04/29/mariadb-optimization-and-tuningtable/
http://blog.syszone.co.kr/3333

 

Posted by 코덴스

댓글을 달아 주세요