다른 테이블 쿼리에 대해 MySQL이 매우 느립니다.

다른 테이블 쿼리에 대해 MySQL이 매우 느립니다.

열을 추가하기 위해 이 테이블을 단순히 업데이트하는 데 1시간 이상 걸리는 이유는 무엇입니까?이 테이블에는 15M개의 행이 있습니다.2개의 인덱스와 1개의 키 프라이머리 키가 있습니다.ALTER TABLE 쿼리는 현재 1시간 15분 동안 "tmp table에 복사" 상태입니다.

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      |         |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+

MySQL의 ALTER TABLE 성능은 매우 큰 테이블에서 문제가 될 수 있습니다.MySQL은 원하는 새 구조의 빈 테이블을 만들고, 이전 테이블의 모든 데이터를 새 테이블에 삽입하고, 이전 테이블을 삭제하는 방식으로 대부분의 변경을 수행합니다.특히 메모리가 부족하고 테이블이 크고 인덱스가 많은 경우 이 작업은 매우 오랜 시간이 걸릴 수 있습니다.많은 사람들이 ALTER TABLE 작업을 완료하는 데 몇 시간 또는 며칠이 걸린 경험이 있습니다.

어쨌든 다른 테이블로 진행해야 하는 경우 다음 리소스를 사용하면 도움이 될 수 있습니다.

• https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html
• https://github.com/soundcloud/lhm
• https://githubengineering.com/gh-ost-github-s-online-migration-tool-for-mysql/

다운타임을 신경 쓰지 않는다면 3개의 분리된 시스템을 사용하는 것이 좋습니다.ALTER TABLE진술들.첫 번째 문은 기존의 모든 보조 인덱스를 삭제합니다.두 번째 문장은 모든 컬럼 관련 변경을 적용합니다.마지막 문은 삭제된 보조 인덱스를 다시 추가하고 다른 인덱스 변경을 적용합니다.

또 다른 두 가지 힌트:

1. 인덱스 변경을 적용하기 전에 다음 두 개의 문을 실행하고 인덱스 변경을 완료한 후 값을 다시 1로 변경하십시오.

   SET unique_checks=0;
   SET foreign_key_checks=0;
   

2. 여러 보조 인덱스를 생성할 때 하나의 인덱스에 저장ALTER TABLE여러 개의 구분된 문장이 아닌 문장ALTER TABLE진술들.

다음 그림은 성능 차이를 보여 줍니다.어프로치 1이 당신의 어프로치이고, 어프로치 2가 나의 어프로치입니다.50m 테이블의 접근법1에 비해 접근법2에는 약 3.47%의 시간이 소요됩니다.이 솔루션은 MySQL(>=5.5) InnoDB 엔진에서만 작동합니다.

변경할 큰 테이블의 잠금을 최소화하기 위해 다음 작업을 수행합니다.

• 기존 테이블을 기반으로 새 빈 테이블을 만들고 이 새 빈 테이블을 변경합니다.
• 큰 테이블의 mysqldump를 실행하여 큰 테이블의 레코드당 완전한 삽입문을 1개 만듭니다(스위치 -c 및 --skip-extended-insert).
• 이 mysqldump를 large_table 이름이 비어 있는 다른(빈) 데이터베이스로 가져옵니다.
• 다른 데이터베이스에서 이 새 이름 변경 테이블의 mysqldump를 가져와 원래 데이터베이스로 가져옵니다.

• 원래 데이터베이스에서 large_table 및 large_table_new의 이름을 변경합니다.

  mysql> create table DATABASE_NAME.LARGE_TABLE_NEW like DATABASE_NAME.LARGE_TABLE;
  mysql> alter table DATABASE_NAME.LARGE_TABLE_NEW add column NEW_COLUMN_NAME COL_DATA_TYPE(SIZE) default null;
  
  $ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p DATABASE_NAME LARGE_TABLE > LARGE_TABLE.sql
  
  mysql> create table test.LARGE_TABLE like DATABASE_NAME.LARGE_TABLE;
  
  $ mysql -u root -p -D test < LARGE_TABLE.sql
  
  mysql> rename table test.LARGE_TABLE to test.LARGE_TABLE_NEW;
  
  $ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p test LARGE_TABLE_NEW > LARGE_TABLE_NEW.sql
  
  $ mysql -u root -p -D DATABASE_NAME < LARGE_TABLE_NEW.sql
  
  mysql> rename table DATABASE_NAME.LARGE_TABLE to DATABASE_NAME.LARGE_TABLE_OLD, DATABASE_NAME.LARGE_TABLE_NEW to DATABASE_NAME.LARGE_TABLE;
  

Percona 툴은 큰 테이블이 있는 이 물건의 구세주입니다.

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

기본적으로 다음과 같습니다.

1. 중복 테이블 생성
2. 테이블을 동기화하는 트리거 생성
3. 대량 복사 데이터
4. 확인하다
5. 테이블을 교환하다

시간이 오래 걸리지만 다운타임 없이 열을 변경할 수 있기 때문에 상관없습니다.

당신 테이블에는 1500만 개의 줄이 있어요, 뭔가 있어요.ALTER TABLE은 테이블의 모든 데이터를 복사하고 인덱스를 다시 만듭니다.첫 번째 측정으로 데이터 파일(item_catalog_map)을 복사해 보십시오.파일 시스템에 MyISAM이 있는 경우 MYD를 사용하여 시간이 얼마나 걸리는지 확인합니다.이 시간은 ALTER TABLE이 적어도 걸리는 시간입니다.

같은 문제가 발생하여 다음 방법으로 MySQL 서비스를 다시 시작했습니다.

sudo service mysql restart

이 Ubuntu에서, 이 에, Ubuntu에서.ALTER TABLE이치노

도 , ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★」ALTER TABLEMariaDB Docker 컨테이너 내에서 1,100만 행이 가동되는 테이블치고는 엄청나게 느렸습니다. 같 theALTER TABLE1100년 RDS에 의한 것입니다.

Mac OS 12.4를 실행하고 있으며 Docker 컨테이너의 데이터는 파일 시스템 볼륨에 있습니다.병목현상은 다른 답변에서 지적한 바와 같이 임시 테이블의 작성이었습니다.

도커 VirtioFS의 VirtioFS입니다.ALTER TABLE8시간이 걸릴 것 같은 시간이 아닌 10분 정도 걸립니다.

언급URL : https://stackoverflow.com/questions/12774709/mysql-very-slow-for-alter-table-query