일치하는 행을 삭제하는 더 빠른 방법?
나는 데이터베이스에 관해서는 상대적으로 초보자입니다. 우리는 MySQL을 사용하고 있으며 현재 실행하는 데 시간이 걸리는 SQL 문 속도를 높이기 위해 노력하고 있습니다. 나는 비슷한 질문에 대해 주위를 둘러 보았지만 하나를 찾지 못했습니다.
목표는 테이블 B에서 일치하는 ID가있는 테이블 A의 모든 행을 제거하는 것입니다.
나는 현재 다음을하고 있습니다.
DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
테이블 a에는 약 100K 행이 있고 테이블 b에는 약 22K 행이 있습니다. 'id'열은 두 테이블의 PK입니다.
이 명령문은 내 테스트 박스에서 실행하는 데 약 3 분이 걸립니다 (Pentium D, XP SP3, 2GB ram, MySQL 5.0.67). 이것은 나에게 느린 것 같습니다. 그렇지 않을 수도 있지만 속도를 높이고 싶었습니다. 이를 수행하는 더 나은 / 빠른 방법이 있습니까?
편집하다:
도움이 될 수있는 몇 가지 추가 정보입니다. 테이블 A와 B는 테이블 B를 만들기 위해 다음을 수행 한 것과 동일한 구조를 가지고 있습니다.
CREATE TABLE b LIKE a;
테이블 a (및 따라서 테이블 b)에는 이에 대한 쿼리 속도를 높이는 데 도움이되는 몇 가지 인덱스가 있습니다. 다시 말하지만, 저는 DB 작업에서 상대적으로 초보자이며 여전히 배우고 있습니다. 나는 이것이 사물에 얼마나 많은 영향을 미치는지 모르겠습니다. 인덱스도 정리해야하므로 효과가 있다고 생각합니다. 속도에 영향을 줄 수있는 다른 DB 설정이 있는지도 궁금합니다.
또한 INNO DB를 사용하고 있습니다.
다음은 도움이 될 수있는 몇 가지 추가 정보입니다.
테이블 A는 다음과 유사한 구조를 가지고 있습니다 (이것을 조금 삭제했습니다).
DROP TABLE IF EXISTS `frobozz`.`a`;
CREATE TABLE `frobozz`.`a` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`fk_g` varchar(30) NOT NULL,
`h` int(10) unsigned default NULL,
`i` longtext,
`j` bigint(20) NOT NULL,
`k` bigint(20) default NULL,
`l` varchar(45) NOT NULL,
`m` int(10) unsigned default NULL,
`n` varchar(20) default NULL,
`o` bigint(20) NOT NULL,
`p` tinyint(1) NOT NULL,
PRIMARY KEY USING BTREE (`id`),
KEY `idx_l` (`l`),
KEY `idx_h` USING BTREE (`h`),
KEY `idx_m` USING BTREE (`m`),
KEY `idx_fk_g` USING BTREE (`fk_g`),
KEY `fk_g_frobozz` (`id`,`fk_g`),
CONSTRAINT `fk_g_frobozz` FOREIGN KEY (`fk_g`) REFERENCES `frotz` (`g`)
) ENGINE=InnoDB AUTO_INCREMENT=179369 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
이 테이블에 대한 여러 인덱스가 문제의 일부라고 생각합니다. 테이블 B에는 id및 열만 포함되어 있지만 테이블 B와 비슷해 보입니다 h.
또한 프로파일 링 결과는 다음과 같습니다.
starting 0.000018
checking query cache for query 0.000044
checking permissions 0.000005
Opening tables 0.000009
init 0.000019
optimizing 0.000004
executing 0.000043
end 0.000005
end 0.000002
query end 0.000003
freeing items 0.000007
logging slow query 0.000002
cleaning up 0.000002
해결
모든 응답과 의견에 감사드립니다. 그들은 확실히 문제에 대해 생각하게했습니다. 명예의 정보는 다음의 제품에 dotjoe 간단한 질문을 물어 문제에서 멀리 단계로 저를 얻기 위해 "다른 테이블 참조 a.id를 수행을?"
문제는 테이블 A에 저장된 프로 시저를 호출하여 두 개의 다른 테이블 C와 D를 업데이트하는 DELETE TRIGGER가 있다는 것입니다. 테이블 C는 저장 프로 시저에서 해당 ID와 관련된 작업을 수행 한 후 FK를 a.id로 되돌 렸습니다. , 그것은 진술을 가지고,
DELETE FROM c WHERE c.id = theId;
나는 EXPLAIN 문을 살펴보고 이것을 다음과 같이 다시 작성했습니다.
EXPLAIN SELECT * FROM c WHERE c.other_id = 12345;
그래서 이것이 무엇을하는지 볼 수 있었고 다음 정보를 얻었습니다.
id 1
select_type SIMPLE
table c
type ALL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows 2633
Extra using where
이것은 만드는 것이 고통스럽고 22500 번 호출 될 것이기 때문에 (삭제되는 주어진 데이터 세트에 대해) 그것이 문제라고 말했습니다. other_id 열에 INDEX를 만들고 EXPLAIN을 다시 실행하면 다음을 얻었습니다.
id 1
select_type SIMPLE
table c
type ref
possible_keys Index_1
key Index_1
key_len 8
ref const
rows 1
Extra
훨씬 낫습니다. 사실 정말 좋습니다.
Index_1 및 삭제 시간이 mattkemp 가보고 한 시간과 일치한다고 추가했습니다 . 이것은 마지막 순간에 몇 가지 추가 기능에 대한 구두 경적 때문에 내 부분에서 정말 미묘한 오류였습니다. Daniel이 말했듯 이 제안 된 대체 DELETE / SELECT 문의 대부분은 본질적으로 동일한 시간이 걸리고 soulmerge가 언급 했듯이이 문은 무엇을 기반으로 구성 할 수 있을지 거의 최고였습니다. 나는해야했다. 이 다른 테이블 C에 대한 인덱스를 제공하면 DELETE가 빠릅니다.
사후 분석 :
이 연습에서 두 가지 교훈을 얻었습니다. 첫째, SQL 쿼리의 영향을 더 잘 이해하기 위해 EXPLAIN 문의 기능을 활용하지 않았 음이 분명합니다. 그것은 신인 실수이기 때문에 나는 그것에 대해 나 자신을 때리지 않을 것입니다. 나는 그 실수로부터 배울 것이다. 둘째, 문제가되는 코드는 '빠른 처리'정신의 결과였으며 부적절한 설계 / 테스트로 인해이 문제가 더 빨리 나타나지 않았습니다. 이 새로운 기능에 대한 테스트 입력으로 사용하기 위해 몇 가지 상당한 테스트 데이터 세트를 생성 했더라면 저도 시간을 낭비하지 않았을 것입니다. DB 측에 대한 내 테스트는 내 애플리케이션 측의 깊이가 부족했습니다. 이제이를 개선 할 기회가 있습니다.
InnoDB에서 데이터를 삭제하는 것은 요청할 수있는 가장 비용이 많이 드는 작업입니다. 이미 쿼리 자체가 문제가 아니라는 것을 발견 했으므로 대부분은 어쨌든 동일한 실행 계획에 최적화됩니다.
모든 경우의 DELETE가 가장 느린 이유를 이해하기 어려울 수 있지만 다소 간단한 설명이 있습니다. InnoDB는 트랜잭션 스토리지 엔진입니다. 즉, 쿼리가 중간에 중단 된 경우 아무 일도 일어나지 않은 것처럼 모든 레코드가 그대로 유지됩니다. 완료되면 모든 것이 같은 순간에 사라집니다. DELETE 동안 서버에 연결하는 다른 클라이언트는 DELETE가 완료 될 때까지 레코드를 볼 수 있습니다.
이를 위해 InnoDB는 MVCC (Multi Version Concurrency Control)라는 기술을 사용합니다. 기본적으로하는 것은 트랜잭션의 첫 번째 명령문이 시작되었을 때와 마찬가지로 각 연결에 전체 데이터베이스의 스냅 샷보기를 제공하는 것입니다. 이를 위해 InnoDB의 모든 레코드는 내부적으로 각 스냅 샷에 대해 하나씩 여러 값을 가질 수 있습니다. 이것이 InnoDB에서 COUNTing하는 데 시간이 걸리는 이유이기도합니다. 그 시점에서 보는 스냅 샷 상태에 따라 다릅니다.
DELETE 트랜잭션의 경우 쿼리 조건에 따라 식별되는 모든 레코드가 삭제 표시됩니다. 다른 클라이언트가 동시에 데이터에 액세스 할 수 있으므로 삭제의 원 자성을 보장하기 위해 각각의 스냅 샷을 확인해야하므로 테이블에서 즉시 데이터를 제거 할 수 없습니다.
모든 레코드가 삭제 표시되면 트랜잭션이 성공적으로 커밋됩니다. 그리고 DELETE 트랜잭션 이전에 스냅 샷 값으로 작업 한 다른 모든 트랜잭션도 종료되기 전에 실제 데이터 페이지에서 즉시 제거 할 수 없습니다.
따라서 실제로 트랜잭션 안전 방식으로 제거 할 준비를하기 위해 모든 레코드를 수정해야한다는 사실을 고려할 때 3 분은 그렇게 느리지 않습니다. 아마도 명령문이 실행되는 동안 하드 디스크가 작동하는 것을 "듣게"될 것입니다. 이는 모든 행에 액세스하여 발생합니다. 성능을 향상시키기 위해 서버의 InnoDB 버퍼 풀 크기를 늘리고 DELETE하는 동안 데이터베이스에 대한 다른 액세스를 제한하여 InnoDB가 레코드 당 유지해야하는 기록 버전 수를 줄일 수 있습니다. 추가 메모리를 사용하면 InnoDB는 테이블 (대부분)을 메모리로 읽고 디스크 검색 시간을 피할 수 있습니다.
당신의 3 분 시간은 정말 느린 것 같습니다. 내 생각 엔 id 열이 제대로 인덱싱되지 않고 있다는 것입니다. 사용중인 정확한 테이블 정의를 제공 할 수 있다면 도움이 될 것입니다.
간단한 파이썬 스크립트를 만들어 테스트 데이터를 생성하고 동일한 데이터 세트에 대해 여러 버전의 삭제 쿼리를 실행했습니다. 내 테이블 정의는 다음과 같습니다.
drop table if exists a;
create table a
(id bigint unsigned not null primary key,
data varchar(255) not null) engine=InnoDB;
drop table if exists b;
create table b like a;
그런 다음 a에 100k 행을 삽입하고 b에 25k 행을 삽입했습니다 (이 중 22.5k도 a에 있음). 다음은 다양한 삭제 명령의 결과입니다. 그런데 실행 사이에 테이블을 삭제하고 다시 채웠습니다.
mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (1.14 sec)
mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (0.81 sec)
mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (0.97 sec)
mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (0.81 sec)
모든 테스트는 Ubuntu 8.10 및 MySQL 5.0이 설치된 Intel Core2 쿼드 코어 2.5GHz, 2GB RAM에서 실행되었습니다. 하나의 sql 문 실행은 여전히 단일 스레드입니다.
최신 정보:
itsmatt의 스키마를 사용하도록 테스트를 업데이트했습니다. 자동 증가를 제거하고 (합성 데이터를 생성하고 있음) 문자 집합 인코딩 (작동하지 않음-조사하지 않음)을 약간 수정했습니다.
내 새 테이블 정의는 다음과 같습니다.
drop table if exists a;
drop table if exists b;
drop table if exists c;
create table c (id varchar(30) not null primary key) engine=InnoDB;
create table a (
id bigint(20) unsigned not null primary key,
c_id varchar(30) not null,
h int(10) unsigned default null,
i longtext,
j bigint(20) not null,
k bigint(20) default null,
l varchar(45) not null,
m int(10) unsigned default null,
n varchar(20) default null,
o bigint(20) not null,
p tinyint(1) not null,
key l_idx (l),
key h_idx (h),
key m_idx (m),
key c_id_idx (id, c_id),
key c_id_fk (c_id),
constraint c_id_fk foreign key (c_id) references c(id)
) engine=InnoDB row_format=dynamic;
create table b like a;
그런 다음 a에서 100k 행과 b에서 25k 행으로 동일한 테스트를 다시 실행했습니다 (실행 사이에 다시 채우기).
mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (11.90 sec)
mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (11.48 sec)
mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (12.21 sec)
mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (12.33 sec)
보시다시피 이것은 여러 인덱스로 인해 이전보다 상당히 느립니다. 그러나 3 분 표시 근처에는 없습니다.
당신이보고 싶은 다른 것은 긴 텍스트 필드를 스키마의 끝으로 옮기는 것입니다. 모든 크기 제한 필드가 먼저이고 텍스트, 블롭 등이 끝에 있으면 mySQL이 더 잘 수행된다는 것을 기억하는 것 같습니다.
이 시도:
DELETE a
FROM a
INNER JOIN b
on a.id = b.id
하위 쿼리를 사용하면 외부 쿼리의 각 레코드에 대해 실행되기 때문에 조인보다 느린 경향이 있습니다.
초대형 데이터로 작업해야 할 때 항상 수행하는 작업입니다 (여기 : 150000 행이있는 샘플 테스트 테이블).
drop table if exists employees_bak;
create table employees_bak like employees;
insert into employees_bak
select * from employees
where emp_no > 100000;
rename table employees to employees_todelete;
rename table employees_bak to employees;
이 경우 SQL은 50000 개의 행을 백업 테이블로 필터링합니다. 쿼리 캐스케이드는 5 초 안에 느린 시스템에서 수행됩니다. 자신의 필터 쿼리로 select에 삽입을 바꿀 수 있습니다.
이것이 큰 데이터베이스에서 대량 삭제를 수행하는 트릭입니다!; =)
'a'의 모든 행에 대해 'b'에 대한 하위 쿼리를 수행하고 있습니다.
시험:
DELETE FROM a USING a LEFT JOIN b ON a.id = b.id WHERE b.id IS NOT NULL;
이것을 시도하십시오 :
DELETE QUICK A.* FROM A,B WHERE A.ID=B.ID
일반 쿼리보다 훨씬 빠릅니다.
구문 참조 : http://dev.mysql.com/doc/refman/5.0/en/delete.html
나는이 질문이 OP의 색인 작성 누락으로 인해 거의 해결되었음을 알고 있지만이 문제의 더 일반적인 경우에 유효한 추가 조언을 제공하고 싶습니다.
I have personally dealt with having to delete many rows from one table that exist in another and in my experience it's best to do the following, especially if you expect lots of rows to be deleted. This technique most importantly will improve replication slave lag, as the longer each single mutator query runs, the worse the lag would be (replication is single threaded).
So, here it is: do a SELECT first, as a separate query, remembering the IDs returned in your script/application, then continue on deleting in batches (say, 50,000 rows at a time). This will achieve the following:
- 각각의 delete 문은 테이블을 너무 오랫동안 잠그지 않으므로 복제 지연이 제어를 벗어나지 않도록합니다 . 비교적 최신 데이터를 제공하기 위해 복제에 의존하는 경우 특히 중요합니다. 배치 사용의 이점은 각 DELETE 쿼리가 여전히 너무 오래 걸리는 경우 DB 구조를 건드리지 않고 더 작게 조정할 수 있다는 것입니다.
- another benefit of using a separate SELECT is that the SELECT itself might take a long time to run, especially if it can't for whatever reason use the best DB indexes. If the SELECT is inner to a DELETE, when the whole statement migrates to the slaves, it will have to do the SELECT all over again, potentially lagging the slaves because it has to do the long select all over again. Slave lag, again, suffers badly. If you use a separate SELECT query, this problem goes away, as all you're passing is a list of IDs.
Let me know if there's a fault in my logic somewhere.
For more discussion on replication lag and ways to fight it, similar to this one, see MySQL Slave Lag (Delay) Explained And 7 Ways To Battle It
P.S. One thing to be careful about is, of course, potential edits to the table between the times the SELECT finishes and DELETEs start. I will let you handle such details by using transactions and/or logic pertinent to your application.
DELETE FROM a WHERE id IN (SELECT id FROM b)
Maybe you should rebuild the indicies before running such a hugh query. Well, you should rebuild them periodically.
REPAIR TABLE a QUICK;
REPAIR TABLE b QUICK;
and then run any of the above queries (i.e.)
DELETE FROM a WHERE id IN (SELECT id FROM b)
The query itself is already in an optimal form, updating the indexes causes the whole operation to take that long. You could disable the keys on that table before the operation, that should speed things up. You can turn them back on at a later time, if you don't need them immediately.
Another approach would be adding a deleted flag-column to your table and adjusting other queries so they take that value into account. The fastest boolean type in mysql is CHAR(0) NULL (true = '', false = NULL). That would be a fast operation, you can delete the values afterwards.
The same thoughts expressed in sql statements:
ALTER TABLE a ADD COLUMN deleted CHAR(0) NULL DEFAULT NULL;
-- The following query should be faster than the delete statement:
UPDATE a INNER JOIN b SET a.deleted = '';
-- This is the catch, you need to alter the rest
-- of your queries to take the new column into account:
SELECT * FROM a WHERE deleted IS NULL;
-- You can then issue the following queries in a cronjob
-- to clean up the tables:
DELETE FROM a WHERE deleted IS NOT NULL;
If that, too, is not what you want, you can have a look at what the mysql docs have to say about the speed of delete statements.
BTW, after posting the above on my blog, Baron Schwartz from Percona brought to my attention that his maatkit already has a tool just for this purpose - mk-archiver. http://www.maatkit.org/doc/mk-archiver.html.
It is most likely your best tool for the job.
Obviously the SELECT query that builds the foundation of your DELETE operation is quite fast so I'd think that either the foreign key constraint or the indexes are the reasons for your extremely slow query.
Try
SET foreign_key_checks = 0;
/* ... your query ... */
SET foreign_key_checks = 1;
This would disable the checks on the foreign key. Unfortunately you cannot disable (at least I don't know how) the key-updates with an InnoDB table. With a MyISAM table you could do something like
ALTER TABLE a DISABLE KEYS
/* ... your query ... */
ALTER TABLE a ENABLE KEYS
I actually did not test if these settings would affect the query duration. But it's worth a try.
Connect datebase using terminal and execute command below, look at the result time each of them, you'll find that times of delete 10, 100, 1000, 10000, 100000 records are not Multiplied.
DELETE FROM #{$table_name} WHERE id < 10;
DELETE FROM #{$table_name} WHERE id < 100;
DELETE FROM #{$table_name} WHERE id < 1000;
DELETE FROM #{$table_name} WHERE id < 10000;
DELETE FROM #{$table_name} WHERE id < 100000;
The time of deleting 10 thousand records is not 10 times as much as deleting 100 thousand records. Then, except for finding a way delete records more faster, there are some indirect methods.
1, We can rename the table_name to table_name_bak, and then select records from table_name_bak to table_name.
2, To delete 10000 records, we can delete 1000 records 10 times. There is an example ruby script to do it.
#!/usr/bin/env ruby
require 'mysql2'
$client = Mysql2::Client.new(
:as => :array,
:host => '10.0.0.250',
:username => 'mysql',
:password => '123456',
:database => 'test'
)
$ids = (1..1000000).to_a
$table_name = "test"
until $ids.empty?
ids = $ids.shift(1000).join(", ")
puts "delete =================="
$client.query("
DELETE FROM #{$table_name}
WHERE id IN ( #{ids} )
")
end
The basic technique for deleting multiple Row form MySQL in single table through the id field
DELETE FROM tbl_name WHERE id <= 100 AND id >=200; This query is responsible for deleting the matched condition between 100 AND 200 from the certain table
참고URL : https://stackoverflow.com/questions/812512/faster-way-to-delete-matching-rows
'Program Tip' 카테고리의 다른 글
| 로컬에서 파일을 삭제 한 후 SVN 커밋 오류 (0) | 2020.12.11 |
|---|---|
| mysql 테이블 auto_increment (사후)에 ID를 만드십시오. (0) | 2020.12.11 |
| C #에서 F # List.map에 해당합니까? (0) | 2020.12.11 |
| Bash의 변경 가능한 목록 또는 배열 구조? (0) | 2020.12.11 |
| PowerShell에서 추가 된 유형을 다시 제거 할 수 있습니까? (0) | 2020.12.11 |