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

1. MySQL에서 테이블을 최적화해야 하는 시기와 이유
2. MySQL에서 테이블 최적화
3. Windows/Ubuntu 20.04 OS에서 MySQL 셸을 사용하여 단일 또는 다중 테이블 최적화
4. Windows 터미널을 사용하여 테이블 최적화
5. Ubuntu 20.04 터미널을 사용하여 테이블 최적화
6. 하나의 MySQL 데이터베이스에서 모든 테이블 최적화
7. 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