MYSQL 업데이트 및 최적화를 기억하세요

소개

오늘(2015년 8월 5일 오후 5시 34분) 데이터베이스의 테이블 구조를 조정하고 여러 필드를 추가한 후 이전 데이터를 새로 고쳤습니다. 기존 필드 url 중 하나와 일치시킨 다음 새로 추가된 필드 type 및 typeid를 업데이트하세요. 나중에 쉘스크립트를 작성해서 데이터를 새로 실행해보니 왜 이렇게 느려졌는지 헷갈렸네요~~

장면의 재현

<code>CREATE TABLE `fuckSpeed` (
  `uin` bigint(20) unsigned NOT NULL DEFAULT 0,
  `id` int(11) unsigned NOT NULL DEFAULT 0,
  `url` varchar(255) NOT NULL DEFAULT '',
  `type` int(11) unsigned NOT NULL DEFAULT 0,
  `typeid` varchar(64) NOT NULL DEFAULT '',
  ......
  KEY `uin_id` (`uin`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code>

테이블 구조는 아마도 위와 같을 것입니다(많은 필드가 생략됨). 테이블에는 결합 인덱스 uin_id가 하나만 있으며 업데이트할 때 다음과 같은 생각이 듭니다.

• 먼저 특정 항목을 얻습니다. ID 범위에 따른 데이터 양
  funkSpeed에서 id,url을 선택하세요. 여기서 id>=101 및 id
• 모든 데이터를 탐색하고 각 데이터 조각을 업데이트하세요
  #먼저 데이터를 처리하고 일치 유형 및 유형 ID 가져오기
  update fuckSpeed ​​​​set type=[type],typeid=[typeid] where id=[id]
  위 아이디어를 따른 후 발견한 것은 업데이트가 초당 평균 3~5개 정도로 매우 느립니다. 업데이트할 데이터를 살펴보니 총 24시간 이상이 소요됩니다. 하루 이상.. 생각해보니 뭔가 잘못됐나 보다.

문제 발견

가장 먼저 생각한 것은 프로세스 업데이트가 1개만 있어서 속도가 많이 느려진 것이 아닌가 하는 생각이었는데 5개의 프로세스를 시작해서 세분화했습니다. ID는 다음과 같습니다

<code>./update_url.sh 0 10000 &
./update_url.sh 10000 20001 &
./update_url.sh 20001 30001 &
./update_url.sh 30002 40002 &
./update_url.sh 40003 50003 &</code>

실행해 보니 여전히 속도는 크게 개선되지 않았고, 여전히 초당 3~5건 정도 업데이트가 이루어지고 있습니다. 생각해보면 데이터를 삽입하기 전의 단계(SQL 문을 매칭하고 조합하는 것 등)에 시간을 낭비할 수 없고, 삽입할 때 문제가 있을 것입니다

내 SQL 문을 살펴보겠습니다select id,url from funkSpeed where id>=101 and id. 여기서 커맨드라인으로 실행해 보았는데 결과는 다음과 같습니다

<code>mysql> select id,url from funkSpeed where id>=0 and id</code>

실제로 0.18초가 걸렸습니다. 이때 갑자기 조인트 인덱스를 사용하지 않았음을 깨달았습니다. 조인트 인덱스가 적용되기 위한 조건은 왼쪽에 필드가 있어야 한다는 것인데 explain으로 확인해 보니 다음과 같았습니다.

<code>mysql> explain id,url from funkSpeed where id>=0 and id</code>

그런 다음 조인트 인덱스를 사용합니다. 🎜>

<code>mysql> select uin,id from funkSpeed where uin=10023 and id=162;
+------------+----------+
| uin        |   id     |
+------------+----------+
| 10023      | 162      |
+------------+----------+
1 row in set (0.00 sec)

mysql> explain select uin,id from funkSpeed where uin=10023 and id=162;
+-------------+------+---------------+----------+---------+-------------+------+-------------+
| table       | type | possible_keys | key      | key_len | ref         | rows | Extra       |
+-------------+------+---------------+----------+---------+-------------+------+-------------+
| funkSpeed   | ref  | uin_id        | uin_id   | 12      | const,const |    4 | Using index |
+-------------+------+---------------+----------+---------+-------------+------+-------------+
1 row in set (0.00 sec)</code>

거의 2차 확인이라고 보시면 됩니다. 이때 기본적으로 인덱스에 문제가 발생했다는 결론을 내릴 수 있습니다.

선택하면 횟수가 상대적으로 많습니다. 작고 두 선택 항목 각각의 ID 차이가 10,000이므로 여기서는 무시할 수 있으며 ID에 인덱스를 추가하지 않으면 여기서 최적화할 수 있는 방법이 없습니다.

에서 문제가 발생합니다. 내 mysql 버전이 5.5이고 update fuckSpeed set type=[type],typeid=[typeid] where id=[id]일 수 없습니다. 그렇지 않으면 여기서 32w+를 업데이트해야 한다는 것을 확실히 확인할 수 있습니다. 데이터 하나하나가 업데이트되는데 데이터 하나하나가 2초정도 걸리네요 너무 무섭네요~~explain update

문제해결

문제를 찾으면 훨씬 수월해집니다 해결~~

선택 시

필드를 추가하고, 아래와 같이 uin로 변경한 후, 업데이트 시 select uin,id,url from funkSpeed where id>=101 and id를 사용하여 인덱스를 사용하도록 합니다. <code>update fuckSpeed set type=[type],typeid=[typeid] where uin=[uin] id=[id]

코드를 3번, 5번, 2번 변경한 후 효과를 확인하기 위해 프로세스를 시작했지만, 효과는 조금도 개선되지 않았으며 평균 30번 이상이었습니다. , 약 3시간이 소요되었습니다. 모든 업데이트가 완료되었습니다.

WeChat ID: love_skills

열심히 노력할수록 행운이 찾아옵니다! 운이 좋을수록 더 열심히 일하세요!

CEO는 꿈이 아니다

바이푸메이 우승은 꿈이 아니다

언니의 반격은 꿈이 아니다

지금이다! ! 어서

위 내용은 내용의 측면을 포함하여 MYSQL 업데이트의 최적화를 소개합니다. PHP 튜토리얼에 관심이 있는 친구들에게 도움이 되기를 바랍니다.