MySQL에서 테이블 및 데이터베이스 최적화

이 튜토리얼은 MySQL에서 테이블과 데이터베이스를 최적화하는 방법에 대한 철저한 가이드입니다. Windows와 Linux(Ubuntu 20.04)의 두 가지 운영 체제를 사용합니다.

또한 MySQL에서 최적화의 중요성에 대해서도 교육합니다.

MySQL에서 테이블을 최적화해야 하는 시기와 이유

애플리케이션이 데이터베이스에서 DELETE 및 UPDATE 작업을 많이 수행하는 경우 MySQL의 데이터 파일이 조각화될 가능성이 더 높습니다. 그 결과 성능에 영향을 줄 수 있는 사용되지 않는 공간이 생깁니다.

지속적으로 MySQL 테이블을 조각 모음하고 사용하지 않은 공간을 회수해야 합니다. 이것이 MySQL에서 테이블 최적화가 필요한 곳입니다. 이는 궁극적으로 데이터 입력 및 출력의 성능과 속도를 향상시키는 전용 스토리지 서버의 데이터 재정렬을 지원합니다.

이제 어떤 테이블을 최적화해야 하는지 어떻게 알 수 있습니까? 정보(데이터)가 지속적으로 업데이트되는 테이블을 최적화해야 합니다. 예를 들어, 트랜잭션 데이터베이스는 테이블 최적화를 위한 완벽한 후보입니다.

그러나 최적화 쿼리는 테이블 및 데이터베이스의 크기에 따라 더 많은 시간이 소요될 수 있습니다. 따라서 트랜잭션 시스템이 여러 시간 동안 테이블을 잠그는 것은 좋지 않습니다.

대신 INNODB 엔진 테이블에서 몇 가지 트릭을 시도할 수 있습니다. 일부 트릭은 다음과 같습니다.

1. 잘못된 값을 최적화하면 보조 인덱스가 조각화되는 경우가 있으므로 특정 값을 압축하여 더 많은 이점을 얻을 수 있는 방법을 분석하는 것이 중요합니다. 최적화를 위한 올바른 값을 식별하는 것이 매우 중요하다는 것을 의미합니다.
2. 또 다른 방법은 인덱스를 삭제하고 테이블을 최적화한 다음 인덱스를 다시 추가하는 것입니다. 이 방법은 테이블이 짧은 시간 동안 인덱스 없이 작동할 수 있는 경우에만 적용할 수 있습니다.

MySQL에서 테이블 최적화

먼저 최적화하려는 테이블을 분석해야 합니다. 다음 명령을 사용하여 데이터베이스에 연결해야 합니다.

예제 코드:

-- Syntax: Use your_database_name;
mysql> USE test;

원하는 데이터베이스에 연결되면 다음 쿼리를 사용하여 테이블의 상태를 가져옵니다.

예제 코드:

-- Syntax: SHOW TABLE STATUS LIKE "your_table_name" \G
mysql> SHOW TABLE STATUS LIKE "test_table" \G

출력:

이 테이블을 최적화해야 하는지 여부를 알 수 있는 두 가지 중요한 속성이 있습니다.

1. Data_length는 데이터베이스가 차지하는 공간을 나타냅니다.
2. Data_free는 데이터베이스 테이블 내에서 할당되었지만 사용되지 않은 바이트를 알려줍니다.

이 정보는 최적화가 필요한 테이블과 나중에 회수할 공간의 양을 식별하는 데 도움이 됩니다.

다음 쿼리를 사용하여 특정 데이터베이스의 모든 테이블에 대해 이 두 숫자(Data_length 및 Data_free)를 얻을 수 있습니다. 현재 test 데이터베이스에는 test_table이라는 테이블이 하나만 있습니다.

예제 코드:

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

출력:

위의 쿼리는 테이블 이름, 총 공간(바이트), 할당된 미사용 공간(바이트)을 출력합니다. 메가바이트 단위로 작업하는 것이 편하다면 다음 쿼리를 사용하여 메가바이트 단위로 출력을 얻을 수 있습니다.

예제 코드:

mysql> SELECT TABLE_NAME,
    -> round(data_length/1024/1024) AS Data_Length_in_MBs,
    -> round(data_free/1024/1024) AS Data_Free_in_MBs
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

주어진 예제 테이블이 심하게 조각화되지는 않았지만 OPTIMIZE TABLE 명령을 사용하여 공간을 회수할 수 있습니다.

MySQL 셸과 터미널을 사용하여 단일/다중 테이블 또는 데이터베이스를 최적화하는 방법을 배웁니다.

Windows/Ubuntu 20.04 OS에서 MySQL 셸을 사용하여 단일 또는 다중 테이블 최적화

예제 코드:

mysql> OPTIMIZE TABLE test_table;

출력:

여러 테이블을 최적화하려면 다음 쿼리를 사용하십시오.

예제 코드:

mysql> OPTIMIZE TABLE tableName1, tableName2;

이제 다음 명령을 사용하여 원하는 테이블이 최적화되었는지 확인합니다.

예제 코드:

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

출력:

동일한 쿼리가 Linux 운영 체제(Ubuntu 20.04)의 MySQL 셸에서 작동합니다.

Windows 터미널을 사용하여 테이블 최적화

예제 코드:

-- Syntax mysqlcheck -o <schema> <table> -u <username> -p <password>
mysqlcheck -o test test_table -u root -p

위에 주어진 명령을 작성하고 Enter 키를 누르면 MySQL root 암호를 입력하라는 메시지가 표시됩니다. 그냥 입력하세요.

쿼리를 실행하려면 bin 폴더에 있어야 합니다(다음 예 참조).

예제 코드:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlcheck -o test test_table -u root -p
Enter password: *****

Ubuntu 20.04 터미널을 사용하여 테이블 최적화

sudo su 명령을 사용하여 superuser로 로그인한 경우 아래와 같이 쿼리를 실행합니다. MySQL root 암호만 묻습니다.

예제 코드:

mysqlcheck -o test test_table -u root -p

수퍼유저로 로그인하지 않은 경우 mysqlcheck 명령을 실행합니다. 여기에서 시스템의 root 암호와 MySQL root 암호를 입력하라는 메시지가 표시됩니다.

예제 코드:

sudo mysqlcheck -o test test_table -u root -p

하나의 MySQL 데이터베이스에서 모든 테이블 최적화

Windows 명령줄을 사용하여 모든 테이블 최적화:

-- Syntax: mysqlcheck -o your_database_name -u username -pPassword
mysqlcheck -o test -u root -p

Ubuntu 터미널을 사용하여 모든 테이블 최적화:

-- if you are signed in as a superuser
mysqlcheck -o test -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o test -u root -p

MySQL의 모든 데이터베이스 최적화

Windows 명령줄을 사용하여 모든 데이터베이스 최적화:

-- Syntax: mysqlcheck -o --all-databases -u username -pPassword
mysqlcheck -o --all-databases -u root -p

Ubuntu 터미널을 사용하여 모든 데이터베이스 최적화:

-- if you are signed in as a superuser
mysqlcheck -o --all-databases -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o --all-databases -u root -p