[SQL] 테이블 변경 쿼리에 대한 MySQL의 매우 느린

테이블 변경 쿼리에 대한 MySQL의 매우 느린

이유는 단순히 열을 추가하려면이 테이블을 업데이트 할 시간 이상 걸리는? 이 테이블은 15M 행이 있습니다. 그것은 2 개 인덱스와 단일 키 기본 키를 가지고있다. 은 ALTER TABLE 쿼리 15 분 현재 1 시간 동안 상태 "tmp를 테이블에 복사"에 있었다.

ALTER TABLE `frugg`.`item_catalog_map` 
ADD COLUMN `conversion_url` TEXT NULL DEFAULT NULL

표:

mysql> describe item_catalog_map;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| catalog_unique_item_id | varchar(255)  | NO   | PRI | NULL    |       |
| catalog_id             | int(11)       | YES  | MUL | NULL    |       |
| item_id                | int(11)       | YES  | MUL | NULL    |       |
| price                  | decimal(10,2) | YES  |     | 0.00    |       |
+------------------------+---------------+------+-----+---------+-------+

mysql> show index from item_catalog_map;
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name             | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| item_catalog_map |          0 | PRIMARY              |            1 | catalog_unique_item_id | A         |    15485115 |     NULL | NULL   |      | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184FCC3C66FC |            1 | catalog_id             | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184F126F525E |            1 | item_id                | A         |    15485115 |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+

해결법

1. ==============================

   1.MySQL의의 ALTER 표 성능이 매우 큰 테이블에 문제가 될 수 있습니다. MySQL의 수행 원하는 새로운 구조로 빈 테이블을 만드는 새로운 하나에 모두에게 이전 테이블에서 데이터를 삽입하고, 이전 테이블을 삭제하여 대부분의 변경. 이것은 당신이 메모리 부족있어 특히, 시간이 오래 걸릴 수 있으며 테이블이 크고 인덱스 많이 있습니다. 많은 사람들은 완료하는 데 몇 시간 또는 며칠을 촬영 한 ALTER 표 작업에 경험이 있습니다.

   MySQL의의 ALTER 표 성능이 매우 큰 테이블에 문제가 될 수 있습니다. MySQL의 수행 원하는 새로운 구조로 빈 테이블을 만드는 새로운 하나에 모두에게 이전 테이블에서 데이터를 삽입하고, 이전 테이블을 삭제하여 대부분의 변경. 이것은 당신이 메모리 부족있어 특히, 시간이 오래 걸릴 수 있으며 테이블이 크고 인덱스 많이 있습니다. 많은 사람들은 완료하는 데 몇 시간 또는 며칠을 촬영 한 ALTER 표 작업에 경험이 있습니다.

   당신이 테이블 변경을 진행해야 할 어쨌든 경우, 아마도 다음과 같은 리소스 당신을 도울 수 있습니다 :

2. ==============================

   2.당신이 다운 타임을 걱정하지 않는 경우, 내 제안이 사용하는 세 가지 ALTER 표 문을 분리. 첫 번째 문은 기존의 모든 보조 인덱스를 제거합니다. 두 번째 문은 모든 열 관련 변경 사항을 적용합니다. 마지막 문은 보조 인덱스는 다시 떨어졌다 및 기타 인덱스 변경 사항을 적용합니다 추가합니다.

   당신이 다운 타임을 걱정하지 않는 경우, 내 제안이 사용하는 세 가지 ALTER 표 문을 분리. 첫 번째 문은 기존의 모든 보조 인덱스를 제거합니다. 두 번째 문은 모든 열 관련 변경 사항을 적용합니다. 마지막 문은 보조 인덱스는 다시 떨어졌다 및 기타 인덱스 변경 사항을 적용합니다 추가합니다.

   또 다른 두 개의 팁 :

   다음 그림은 성능 차이를 보여줍니다. 접근법 1은 당신의 접근 방식과 접근 방식이 내 방법입니다. 접근법 2는 3.47 % 시간이 50m 테이블에 대한 접근 방식 1과 비교 정도 걸립니다. 이 솔루션은 MySQL의 (> = 5.5) 이노 엔진 작동합니다.

3. ==============================

   3.Percona 도구는 / 큰 테이블 w이 물건에 대한 생명의 은인입니다.

   Percona 도구는 / 큰 테이블 w이 물건에 대한 생명의 은인입니다.

   http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

   기본적으로 그들은 :

   영원히 간다,하지만 당신은 다운 타임없이 열을 변경할 수 있습니다이 의미하기 때문에 무슨 상관.

4. ==============================

   4.귀하의 표는 무엇인가 인 1500 만 개 행이 있습니다. 테이블 변경은 온통 테이블에서 데이터를 복사하고 인덱스를 다시 포함됩니다. 데이터 파일을 복사하는 첫 번째 측정 시도로 (item_catalog_map.MYD 그것의의 MyISAM 경우) 파일 시스템 및 그 소요 시간을 참조하십시오. 이것은 시간 ALTER TABLE 것 이상 걸릴 수 있습니다.

   귀하의 표는 무엇인가 인 1500 만 개 행이 있습니다. 테이블 변경은 온통 테이블에서 데이터를 복사하고 인덱스를 다시 포함됩니다. 데이터 파일을 복사하는 첫 번째 측정 시도로 (item_catalog_map.MYD 그것의의 MyISAM 경우) 파일 시스템 및 그 소요 시간을 참조하십시오. 이것은 시간 ALTER TABLE 것 이상 걸릴 수 있습니다.

5. ==============================

   5.내가 변경하고자하는 큰 테이블의 최대 잠금 최소화하기 위해, 나는 다음을 수행 :

   내가 변경하고자하는 큰 테이블의 최대 잠금 최소화하기 위해, 나는 다음을 수행 :

from https://stackoverflow.com/questions/12774709/mysql-very-slow-for-alter-table-query by cc-by-sa and MIT license