MYSQL 테이블의 여러 인덱스가 느린 UPDATES 및 INSERTS의 원인입니까?
내 (LAMP 스택) 사이트의 성능은 코드 업데이트가 없음에도 불구하고 지난 며칠 동안 크게 저하되었습니다. 특정 MySQL 테이블의 삽입, 업데이트 및 삭제 만 문제를 일으키는 것 같습니다. 작업 "표"항목을 업데이트, 삽입 또는 삭제하는 페이지는로드하는 데 약 10 초가 걸립니다. (예 UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)
SELECT 쿼리가 이전과 같이 실행되는 것처럼 보이지만 동시에 업데이트가 발생하면 속도가 느려집니다.
테이블에는 약 180,000 개의 항목이 있습니다. PHPMyAdmin 뷰에서 기본 필드의 "일반"인덱스 외에도 "entry_date"필드에 인덱스가 있음을 알았습니다 (이미지 참조). 이 경우 문제가 될 수 있습니까? 해당 필드에 대한 인덱스가 생성 된 이유를 모르겠습니다.
그렇지 않다면 다른 무엇이 문제의 원인 일 수 있습니까? 디스크 공간을 확인했는데 괜찮은 것 같습니다. (7GB 사용 가능) df.
SHOW CREATE TABLE job\G
Create Table: CREATE TABLE `job` (
`job_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0',
`entry_date` date NOT NULL DEFAULT '0000-00-00',
`timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00',
`title` varchar(60) COLLATE latin1_german2_ci NOT NULL,
`description` text COLLATE latin1_german2_ci NOT NULL,
`start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL,
`address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
`address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
`postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
`postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
`status` tinyint(4) NOT NULL DEFAULT '0',
`cat_id` int(4) NOT NULL DEFAULT '0',
`price` decimal(4,2) NOT NULL DEFAULT '1.00',
`emailcount` smallint(5) NOT NULL DEFAULT '-1',
`emailcount2` int(11) NOT NULL DEFAULT '-1',
`recemailcount` int(11) NOT NULL DEFAULT '-1',
`archive` tinyint(4) NOT NULL DEFAULT '0',
`post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL,
PRIMARY KEY (`job_id`),
KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
업데이트 -먼저 모든 기여자에게 감사드립니다. 대단히 감사합니다. 그래서 지난 며칠 동안 문제가 멈춰서 문제가 무엇인지 찾기가 훨씬 더 어려울 수 있습니다. 그러나 지금은 다시 한 번 돌아온 것 같습니다. Google 클라우드에서 호스팅되는 머신 유형 인 g1-small (vCPU 1 개, 메모리 1.7GB)을 제공하는 것으로 시작하겠습니다. 댓글 작성자가 요청한 추가 정보로 계속 업데이트하겠습니다.
답변
구체적인 질문에 답하려면 :
- 아니요 ,
(entry_date)한두 개의 추가 인덱스 는 다른title열 을 업데이트하는 성능에 영향을주지 않습니다 .
추가적으로 :
- 아니요 , MySQL 5.6 버전은 일부 최신 기능 (예 : 창 기능)이 누락 되어도 너무 오래되지 않았습니다. 괜찮은 하드웨어로 괜찮은 성능을 가져야합니다.
우리는 손에있는 문제에 대해서만 추측 할 수 있지만 무엇이 잘못되었거나 설명 할 수 있는지 :
오래되고 비효율적 인 하드웨어 : 디스크 사양을 확인하고 성능을 측정합니다.
조각난 인덱스 / 테이블. MySQL 문서 및 MyISAM 테이블 조각 모음 (OPTIMIZE TABLE) 방법에 대한 이전 질문 / 답변을 확인하십시오.
마지막으로 중요한 것은 :
테이블은 오래된 뉴스 인 MyISAM 엔진을 사용하고 있습니다. InnODB는 몇 년 전에 MySQL의 기본 엔진으로 대체했습니다. 이 엔진의 적극적인 개발이 없습니다. InnoDB (트랜잭션, 외래 키 제약 등)에 비해 몇 가지 기능이 부족 하고 대부분의 작업 부하에서 성능 이 부족 합니다 . InnoDB를 사용하도록 테이블을 변경하는 것이 좋습니다 (물론 앱과 절차가 중단되지 않는지 테스트 한 후).
중요한 것은 당신의 질문에, InnoDB는 수행 할 수
SELECT와UPDATE같은 시간 (보통)에서. MyISAM은 테이블을 완전히 잠급니다. 선택 또는 업데이트가 시작되기 전에 업데이트 또는 선택이 완료되어야합니다.MyISAM에서 InnoDB로 전환 할 때
key_buffer_size및innodb_buffer_pool_size.
인덱스는 INSERT 및 DELETE 작업의 성능에 약간 영향을 미치지 만 일반적으로 특히 인덱스가 잘 계획되어 있고 30 개의 서로 다른 필드 조합이있는 동일한 테이블에 30 개의 인덱스를 배치하여 초과하지 않는 경우 일반적으로 무시할 수 있습니다. (일반적으로 저는 5 x 5 지침을 고수합니다-최대 5 개의 인덱스, 최대 인덱스 당 5 개의 필드. 물론 이것은 단지 지침 일 뿐이며 엄격한 규칙은 아닙니다).
즉, 인덱스가 UPDATE 또는 SELECT에 대한 행을 찾는 데 사용되기 때문에 인덱스는 실제로 UPDATE 및 SELECT 쿼리의 성능을 높이는 데 사용됩니다.
당신이보고있는 급격한 변화는 당신이 발견 한 entry_date 인덱스와 관련이 있을지 의심 스럽습니다 (최근에 추가되었는지 아니면 성능 변경 이전에 이미 존재했는지 아십니까?).
주의해야 할 두 가지 사항은 다음과 같습니다.
특히 UPDATE 및 SELECT 쿼리에 대해 쿼리에 대한 이전 인덱스가 계속 사용되고있는 경우 (그리고 검색중인 경우 또는 지금 발생하는 스캔 작업 인 경우). 이는 시간 경과에 따른 데이터의 테이블 통계 변경으로 인해 변경 될 수 있습니다. ANALYZE 문을 사용하여 테이블의 통계를 확인할 수 있습니다 .
다른 한가지 살펴볼 수있는 것은 인덱스 조각화 인데, 이는 시간이 지남에 따라 자연스럽게 발생합니다. 이것은 더 많은 데이터가 테이블에 추가 될 때 발생합니다. (일반적으로 이것은 당신과 같은 작은 테이블에서 문제가되지 않아야하지만 어쨌든 그것을 확인할 가치가 있습니다.)
나는 그들을 생각할 때 살펴볼 더 많은 것들로 내 대답을 계속 업데이트 할 것입니다. 쿼리에 EXPLAIN을 실행하면 문제에 대한 단서도 도움이 될 수 있습니다. 느리게 실행되는 UPDATE 및 INSERT 문을 분리하고 동일한 근본 원인에 의해 영향을 받는지 이해하려고합니다 (다시 인덱스에 대해 다소 역으로 작동하므로 INSERT는 무시할 정도로 느리지 만 UPDATE는 일반적으로 올바른 인덱스의 이점을 얻고 더 빨라야합니다.)
OPTIMIZE TABLE 작업; 조각화를 제거하고 모든 인덱스를 다시 생성합니다.
그런 다음 UPDATE 쿼리 타이밍을 확인하십시오.
성능 조정을 지원하기 위해 무료로 다운로드 할 수있는 유틸리티 스크립트에 대한 프로필, 네트워크 프로필을 봅니다.