짧은 대답 :

select  ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables  where  DATA_FREE > 0;

“알아야합니다”답변

먼저 행이 업데이트 될 때 MySQL 테이블이 조각화된다는 것을 이해해야하므로 정상적인 상황입니다. 데이터가있는 덤프를 사용하여 가져온 테이블을 만들면 모든 행이 여러 고정 크기 페이지에서 조각화없이 저장됩니다. 가변 길이 행을 업데이트 할 때이 행을 포함하는 페이지는 변경 사항을 저장하기 위해 둘 이상의 페이지로 나뉘어지고 새 두 개 이상의 페이지에는 사용되지 않은 공간을 채우는 빈 공간이 포함됩니다.

물론 조각화가 너무 커지지 않는 한 성능에 영향을 미치지 않습니다. 조각화가 너무 많으면 원하는 쿼리를 보도록하겠습니다.

  select  ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables  where  DATA_FREE > 0;

DATA_LENGTH 및 INDEX_LENGTH는 데이터 및 인덱스가 사용하는 공간이며 DATA_FREE는 모든 테이블 페이지에서 사용되지 않은 총 바이트 수입니다 (조각화).

실제 생산 테이블의 예는 다음과 같습니다.

| ENGINE | TABLE_NAME               | data_length | index_length | data_free |
| InnoDB | comments                 |         896 |          316 |         5 |

이 경우 (896 + 316) = 1212MB를 사용하는 테이블이 있고 데이터의 여유 공간이 5MB입니다. 이것은 다음과 같은 “단편화 비율”을 의미합니다.

5/1212 = 0.0041

… “조각화 비율”이 정말 낮습니다.

0.2에 가까운 비율 (빈 공간의 20 %를 의미)로 테이블을 작업하고 있으며 테이블을 최적화하더라도 성능이 동일하더라도 쿼리 속도가 느려지지 않습니다. 그러나 800MB 테이블에 최적화 테이블을 적용하는 데 많은 시간이 걸리고 몇 분 동안 테이블을 차단하므로 프로덕션에서는 불가능합니다.

따라서 성능에서이기는 것을 고려하고 테이블을 최적화하는 데 낭비되는 시간을 고려하지 않으면 최적화하지 않는 것이 좋습니다.

스토리지에 더 적합하다고 생각되면 비율을 확인하고 최적화 할 때 절약 할 수있는 공간을 확인하십시오. 일반적으로 너무 많지 않으므로 최적화하지 않는 것이 좋습니다.

그리고 최적화하면 다음 업데이트에서 페이지를 둘 이상으로 분할하여 빈 공간을 만듭니다. 그러나 테이블이 조각난 경우 행에 대한 업데이트가 반드시 페이지를 분할하지는 않기 때문에 조각난 테이블보다 조각화 된 테이블을 업데이트하는 것이 더 빠릅니다.

이것이 도움이되기를 바랍니다.

답변

select ENGINE,
  concat(TABLE_SCHEMA, '.', TABLE_NAME) as table_name,
  round(DATA_LENGTH/1024/1024, 2) as data_length,
  round(INDEX_LENGTH/1024/1024, 2) as index_length,
  round(DATA_FREE/1024/1024, 2) as data_free,
  (data_free/(index_length+data_length)) as frag_ratio
FROM information_schema.tables
WHERE DATA_FREE > 0
ORDER BY frag_ratio DESC;

$DB = new mysqli ('localhost', 'DbUser', 'DbPassword');
$results = $DB->query('show databases');
$allDbs = array();
while ($row = $results->fetch_array(MYSQLI_NUM))
{
    $allDbs[] = $row[0];
}
$results->close();
foreach ($allDbs as $dbName)
{
    if ($dbName != 'information_schema' && $dbName != 'mysql')
    {
        $DB->select_db($dbName);
        $results = $DB->query('SHOW TABLE STATUS WHERE Data_free > 0');
        if ($results->num_rows > 0)
        {
            while ($row = $results->fetch_assoc())
            {
                $DB->query('optimize table ' . $row['Name']);
            }
        }
        $results->close();
    }
}
$DB->close();

select  ENGINE, TABLE_SCHEMA, TABLE_NAME, Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables  where  DATA_FREE > 0 order by frag_ratio desc

ENGINE  | TABLE_SCHEMA  | TABLE_NAME    | data_length   | index_length  | data_free | frag_ratio

InnoDB  | db_name       | db_table      | 0             | 0             | 8         | 170.6667

ALTER TABLE `table_name` ENGINE = InnoDB;