MySQL 인덱스 - 모범 사례는 무엇입니까?

MySQL 인덱스 - 모범 사례는 무엇입니까?

MySQL 데이터베이스의 인덱스를 한동안 사용했지만 제대로 배운 적이 없습니다.일반적으로 검색하거나 선택할 필드에 인덱스를 붙입니다.WHERE근데 가끔 흑백으로 안 보일 때가 있어요

MySQL 인덱스의 베스트 프랙티스는 무엇입니까?

상황/딜레마의 예:

• 테이블에 6개의 열이 있고 모두 검색할 수 있는 경우 모두 인덱싱해야 합니까, 아니면 모두 인덱싱하지 않아야 합니까?

• 인덱싱이 성능에 미치는 부정적인 영향은 무엇입니까?

• 사이트 일부에서 검색할 수 있는 VARCHAR 2500 열이 있는 경우 인덱싱해야 합니까?

인덱싱에 대해 자세히 읽어보셔야 합니다. 많은 내용이 쓰여 있습니다. 그리고 무슨 일이 일어나고 있는지 이해하는 것이 중요합니다.

일반적으로 인덱스는 표의 행에 순서를 부과한다.

단순화를 위해 테이블이 단지 큰 CSV 파일이라고 가정해 보십시오.행을 삽입할 때마다 끝에 삽입됩니다.따라서 표의 "자연적" 순서는 행을 삽입한 순서입니다.

CSV 파일을 매우 기본적인 스프레드시트 애플리케이션에 로드했다고 가정해 보십시오.이 스프레드시트에서는 데이터를 표시하고 행에 순서대로 번호를 매기기만 하면 됩니다.

이제 세 번째 열에 값 "M"이 있는 모든 행을 찾아야 한다고 가정합니다.사용할 수 있는 것은, 1개의 옵션 뿐입니다.표를 스캔하여 각 행의 세 번째 열 값을 확인합니다.행이 많으면 이 방법('테이블 스캔')에 시간이 오래 걸릴 수 있습니다.

이 표 외에 인덱스가 있다고 상상해 보세요.이 특정 지수는 세 번째 열의 값 색인입니다.인덱스는 세 번째 열의 모든 값을 의미 있는 순서(알파벳 순으로)로 나열하며, 각 열에 대해 해당 값이 나타나는 행 번호 목록을 제공합니다.

이제 세 번째 열의 값이 "M"인 모든 행을 찾는 좋은 전략이 수립되었습니다. 예를 들어 이진 검색을 수행할 수 있습니다.테이블 검사에서는 N개의 행(여기서 N은 행의 수)을 찾아야 하지만 바이너리 검색에서는 최악의 경우 log-n 인덱스 항목만 확인해야 합니다.와, 훨씬 쉬운데!

물론 이 인덱스가 있고 테이블에 행을 추가하는 경우(마지막에는 이러한 개념 테이블이 작동하므로), 매번 인덱스를 업데이트해야 합니다.따라서 새 행을 작성할 때는 작업을 조금 더 많이 수행하지만 무언가를 찾을 때는 많은 시간을 절약할 수 있습니다.

따라서 일반적으로 인덱스는 읽기 효율성과 쓰기 효율성 사이에서 균형을 이룹니다.인덱스가 없으면 삽입 속도가 매우 빠를 수 있습니다. 데이터베이스 엔진은 테이블에 행을 추가합니다.인덱스를 추가할 때 엔진은 삽입을 수행하는 동안 각 인덱스를 업데이트해야 합니다.

반면에, 읽기는 훨씬 더 빨라진다.

이것으로 처음 두 가지 질문이 해결되기를 바랍니다(다른 사람이 답했듯이 올바른 균형을 찾아야 합니다).

세 번째 시나리오는 조금 더 복잡합니다.LIKE 를 사용하고 있는 경우는, 일반적으로 색인 엔진을 사용하면, 읽기 속도가 처음 %까지 향상됩니다.즉, 'foo%bar%'와 같은 WHERE 열을 선택하는 경우 데이터베이스는 인덱스를 사용하여 열이 "foo"로 시작하는 모든 행을 찾은 다음 중간 행 집합을 검색하여 "bar"를 포함하는 하위 집합을 찾아야 합니다.선택...WHERE LIKE 열 '%bar%'은(는) 인덱스를 사용할 수 없습니다.왜 그런지 알 수 있었으면 좋겠어요.

마지막으로 둘 이상의 열에 대한 인덱스에 대해 생각해야 합니다.개념은 동일하며 LIKE와 유사하게 동작합니다.기본적으로 인덱스가 켜져 있으면 엔진은 인덱스를 가능한 한 왼쪽에서 오른쪽으로 계속 사용합니다.따라서 열 a에 대한 검색에서는 (a, b, c) 인덱스를 (a, b)에 대한 인덱스와 같이 사용할 수 있습니다.그러나 WHERE b=5 AND c=1)를 검색하는 경우에는 엔진이 전체 테이블 스캔을 수행해야 합니다.

이것이 조금 빛을 발하는 데 도움이 되기를 바라지만, 나는 다시 한 번 강조해야 할 것은 이런 것들에 대해 자세히 설명하는 좋은 기사들을 찾는 데 몇 시간을 할애하는 것이 가장 좋다는 것이다.또한 특정 데이터베이스 서버의 설명서를 읽는 것도 좋습니다.쿼리 플래너에 의해 인덱스가 구현되고 사용되는 방법은 매우 다양할 수 있습니다.

인덱싱 기술의 추가 마스터링과 같은 프레젠테이션을 확인하십시오.

업데이트 12/2012: 새로운 프레젠테이션을 게시했습니다.인덱스의 설계 방법, 정말로.2012년 10월 산타클라라의 ZendCon에서, 2012년 12월 Percona Live London에서 이것을 발표했습니다.

최적의 인덱스 설계는 앱에서 실행하는 쿼리와 일치해야 하는 프로세스입니다.

어떤 열을 인덱싱하는 것이 가장 좋은지, 모든 열을 인덱싱할지 여부, 열을 인덱싱하지 않고 여러 열에 걸쳐야 하는지 등에 대한 범용 규칙은 권장하기 어렵습니다.실행할 쿼리에 따라 달라집니다.

예, 약간의 오버헤드가 있으므로 인덱스를 불필요하게 생성해서는 안 됩니다.그러나 신속하게 실행하는 데 필요한 쿼리에 이점을 제공하는 인덱스를 생성해야 합니다.지수의 오버헤드는 보통 그 이익보다 훨씬 크다.

VARCHAR(2500) 컬럼의 경우 FULLTEXT 인덱스 또는 프리픽스인덱스를 사용할 수 있습니다.

CREATE INDEX i ON SomeTable(longVarchar(100));

이 긴 varchar의 중간에 있을 수 있는 단어를 검색하는 경우 일반적인 인덱스는 도움이 되지 않습니다.이를 위해 전체 텍스트 색인을 사용합니다.

다른 답변에서는 몇 가지 좋은 조언을 반복하지 않고 다음과 같이 덧붙입니다.

복합 인덱스

복합 인덱스(복수 열을 포함하는 인덱스)를 만들 수 있습니다.MySQL은 왼쪽에서 오른쪽으로 사용할 수 있습니다.다음과 같은 경우:

Table A
Id
Name
Category
Age
Description

이름/카테고리/연령을 순서대로 포함하는 복합 인덱스가 있는 경우 다음 WHERE 절에서는 인덱스를 사용합니다.

WHERE Name='Eric' and Category='A'

WHERE Name='Eric' and Category='A' and Age > 18

그렇지만

WHERE Category='A' and Age > 18

모든 것이 왼쪽에서 오른쪽으로 사용되어야 하기 때문에 이 인덱스를 사용하지 않습니다.

설명하라.

Expend / Expended를 사용하여 MySQL에서 사용할 수 있는 인덱스와 실제로 선택한 인덱스를 파악합니다.MySQL은 쿼리당 하나의 키만 사용합니다.

EXPLAIN EXTENDED SELECT * from Table WHERE Something='ABC'

느린 쿼리 로그

느린 쿼리 로그를 켜서 어떤 쿼리가 느리게 실행되고 있는지 확인합니다.

와이드 컬럼

MOST 구별이 처음 몇 글자로 나타나는 넓은 열이 있는 경우 인덱스의 첫 번째 N자만 사용할 수 있습니다.예:varchar(255)로 정의된 Reference Number 열이 있지만 97%의 경우 참조 번호는 10자 이하입니다.처음 10자만 보도록 인덱스를 변경하여 성능을 대폭 향상시켰습니다.

테이블에 6개의 열이 있고 모두 검색할 수 있는 경우 모두 인덱싱해야 합니까, 아니면 모두 인덱싱하지 않아야 합니까?

필드 단위로 검색합니까, 아니면 여러 필드를 사용하는 검색도 있습니까?가장 많이 검색되는 필드는 무엇입니까?필드 유형은 무엇입니까?(예를 들어 인덱스는 VARCHAR보다 INT에서 더 잘 작동합니다) 실행 중인 쿼리에서 EXPLIVE를 사용해 본 적이 있습니까?

인덱싱이 성능에 미치는 영향은 무엇입니까?

UPDATE 및 INSERT는 속도가 느려집니다.또한 추가 스토리지 공간 요구 사항도 있지만, 오늘날에는 이러한 요구 사항이 일반적으로 중요하지 않습니다.

내 사이트의 일부에서 검색할 수 있는 VARCHAR 2500 열이 있는 경우 인덱싱해야 합니까?

아니요, UNIQURE(이미 색인화됨)가 아니거나 해당 필드에서 정확한 일치 항목만 검색합니다(LIKE 또는 mySQL의 전체 텍스트 검색 사용 안 함).

일반적으로 WHERE 절을 사용하여 검색하거나 선택할 필드에 인덱스를 붙입니다.

일반적으로 가장 많이 쿼리되는 필드를 인덱싱하고 다음으로 INT/BOOLAN/ENUM을 VARCHARS인 필드를 인덱싱합니다.종종 개별 필드에 인덱스가 아닌 결합된 필드에 인덱스를 작성해야 합니다.EXPLY를 사용하여 느린 로그를 확인합니다.

효율적인 데이터 로드:인덱스를 사용하면 검색 속도는 빨라지지만 삽입 및 삭제는 느려지고 인덱스된 열의 값 업데이트도 느려집니다.즉, 인덱스는 쓰기와 관련된 대부분의 작업을 느리게 합니다.이 문제는 행을 작성하려면 데이터 행뿐만 아니라 인덱스를 변경해야 하기 때문에 발생합니다.테이블에 인덱스가 많을수록 더 많은 변경이 필요하며 평균 성능 저하가 커집니다.대부분의 테이블은 많은 읽기와 적은 쓰기를 수신하지만 쓰기 비율이 높은 테이블의 경우 인덱스 업데이트 비용이 상당할 수 있습니다.

인덱스 회피:쿼리 성능을 향상시키기 위해 특정 인덱스가 필요하지 않은 경우 만들지 마십시오.

디스크 공간: 인덱스가 디스크 공간을 차지하므로 여러 인덱스가 이에 따라 더 많은 공간을 차지합니다.이로 인해 인덱스가 없는 경우보다 테이블 크기 제한에 더 빨리 도달할 수 있습니다.가능한 한 인덱스를 사용하지 마십시오.

테이크 아웃:인덱스를 오버하지 않음

일반적으로 인덱스는 데이터베이스 검색 속도를 높이는 데 도움이 되며 디스크 공간을 추가로 사용하고 속도가 느려진다는 단점이 있습니다.INSERT/UPDATE/DELETE문의합니다.사용하다EXPLAIN결과를 읽고 MySQL이 인덱스를 사용하는 시기를 확인합니다.

테이블에 6개의 열이 있고 모두 검색할 수 있는 경우 모두 인덱싱해야 합니까, 아니면 아예 인덱싱하지 않아야 합니까?

6개의 열을 모두 인덱싱하는 것이 항상 최선의 방법은 아닙니다.

(a) 특정 정보를 검색할 때 그 중 하나를 사용할 예정입니까?

(b) 이들 열의 선택성은 어느 정도인가(표 상의 기록 총량과 비교하여 몇 개의 개별 값이 저장되어 있는가)?

MySQL은 쿼리를 수행할 때 "싸구려" 경로를 찾는 비용 기반 최적화 도구를 사용합니다.그리고 선택성이 낮은 분야는 좋은 후보가 아닙니다.

인덱싱이 성능에 미치는 영향은 무엇입니까?

이미 답변 완료: 추가 디스크 공간, 삽입 - 업데이트 - 삭제 시 성능 저하.

사이트 일부에서 검색할 수 있는 VARCHAR 2500 열이 있는 경우 인덱싱해야 합니까?

FULLTEXT 색인을 사용해 보십시오.

1/2) 인덱스는 특정 선택 작업의 속도를 높이지만 삽입, 업데이트, 삭제 등의 다른 작업은 느려집니다.그것은 훌륭한 균형일 수 있다.

3) 전체 텍스트 색인 또는 스핑크스를 사용합니다.

언급URL : https://stackoverflow.com/questions/3049283/mysql-indexes-what-are-the-best-practices