인덱싱 원리 - innodb를 예로 들어

1. 미리 작성

개발과 테스트 작업이 끝나면 모두가 프로젝트 출시를 앞두고 몇 가지 준비 작업을 정리하는 중입니다. 중요한 작업 중 하나는 SQL 문을 작성하는 것입니다. 동시성이 높고 트래픽이 많은 환경에서는 인덱스가 매우 필요합니다. 좋은 인덱스를 구축하면 SQL 문의 쿼리 효율성이 크게 향상될 수 있습니다. 그렇다면 인덱스란 무엇이며 어떻게 좋은 인덱스를 구축할 수 있을까요? ? 이 기사에서는 mysql Innodb 스토리지 엔진을 예로 들어 실제 프로젝트를 기반으로 좋은 인덱스를 구축하는 방법을 살펴봅니다.

2. 인덱스 정의

MySQL의 공식 인덱스 정의는 다음과 같습니다. 인덱스(Index)는 MySQL이 데이터를 효율적으로 얻을 수 있도록 돕는 데이터 구조입니다. 문장의 어간을 추출하면 색인의 본질을 얻을 수 있습니다. 색인은 데이터 구조입니다.
우리는 데이터베이스 쿼리가 다음 SQL 문과 같이 데이터베이스의 가장 중요한 기능 중 하나라는 것을 알고 있습니다. SELECT * FROM test_table WHERE id = 99; 테이블 test_table에서 ID가 99인 데이터 레코드를 얻을 수 있습니다.
우리 모두는 가능한 한 빨리 데이터를 쿼리하고 싶어하므로 데이터베이스 시스템 설계자는 쿼리 알고리즘의 관점에서 최적화합니다. 가장 기본적인 쿼리 알고리즘은 물론 선형 검색입니다. test_table을 순회한 다음 id 값이 99인지 여부를 행별로 일치시킵니다. O(n)의 복잡도를 갖는 이 알고리즘은 데이터 양이 많을 때 확실히 좋지 않습니다. 컴퓨터 과학의 발전으로 이진 검색, 이진 트리 검색 등과 같은 더 나은 검색 알고리즘이 많이 제공되었습니다. 조금만 분석해 보면 각 검색 알고리즘은 특정 데이터 구조에만 적용할 수 있다는 것을 알 수 있습니다. 예를 들어 이진 검색에서는 검색된 데이터를 정렬해야 하지만 이진 트리 검색은 이진 검색 트리에만 적용할 수 있습니다. 하지만 데이터 자체의 조직 구조는 다양한 데이터 구조를 완벽하게 만족시킬 수 없기 때문에(예를 들어 두 열을 동시에 순서대로 정리하는 것은 이론적으로 불가능합니다.) 따라서 데이터베이스 시스템은 데이터 외에도 특정 데이터 구조를 만족하는 데이터 구조를 유지합니다. 검색 알고리즘은 어떤 방식으로든 데이터를 참조(지시)하여 고급 검색 알고리즘을 이러한 데이터 구조에 구현할 수 있도록 합니다. 이 데이터 구조는 인덱스입니다.
위의 예는 주로 인덱스의 역할을 간략하게 설명하기 위해 사용됩니다. mysql Innodb를 포함한 대부분의 데이터베이스 시스템과 파일 시스템은 바이너리 트리 구조를 인덱스로 선택하지 않고 B-Tree 또는 그 변형 B+를 사용합니다. Tree는 인덱스 구조로 사용됩니다. 이 인덱스 구조는 검색 과정에서 디스크 I/O 액세스 횟수를 최소화할 수 있습니다. B-Tree 또는 B+Tree가 무엇인지, 그리고 이를 데이터베이스 인덱스 구조로 선택하는 이유에 대해서는 Go를 참조하세요. 그리고 배우세요. 아래에서는 먼저 mysql Innodb 엔진의 두 가지 B+Tree 인덱스를 소개합니다.

3. Mysql Innodb B+Tree 인덱스

하나는 기본키 인덱스인 클러스터 인덱스(Cluster Index)이다. 기본 키가 속한 데이터이므로 Innodb에서는 기본 키 인덱스가 데이터입니다.

하나는 열 값이 Key이고 기본 키 위치가 있는 비기본 키 인덱스(Secondary Index)입니다. is Value, 즉 (열 값, 기본 키 위치)

Innodb는 인덱스입니다. -구성된 테이블이며 모든 데이터는 기본 키 리프 노드 아래에 있습니다. 따라서 기본 키의 삽입 순서를 보장할 수 없는 경우 기본 키 노드 분할이 많이 발생하여 I/O 작업이 많이 발생하게 됩니다. 또한 Innodb는 단일 인덱스 필드의 길이가 768바이트를 초과해서는 안 된다고 규정하고 있습니다. 그렇지 않으면 길이가 잘려 인덱스에 배치되지 않습니다. Innodb의 비기본 키 인덱스는 모두 기본 키 인덱스를 가리킵니다. 비기본 키 인덱스를 검색하면 전체 행을 얻으려면 리프 노드 포인터를 통해 기본 키 인덱스의 위치를 ​​찾아야 합니다. 따라서 기본 키 인덱스는 최대한 작게 설계해야 합니다. 그렇지 않으면 기본 키가 아닌 인덱스가 매우 커집니다.

4. 색인 생성 원칙

다음으로 좋은 인덱스를 구축하기 위해 따라야 할 원칙을 살펴보고 구체적인 예를 들어 설명하겠습니다.
1. 가장 중요한 원칙인 mysql은 항상 인덱스와 일치합니다. 예를 들어 a = 1 및 b = 2 및 c > 3 및 d = 4인 범위 쿼리(>, 2. = 및 in은 a = 1, b = 2 및 c = 3과 같이 순서가 잘못될 수 있습니다. (a, b, c) 인덱스는 순서에 상관없이 설정될 수 있습니다. 식별 가능한 인덱스 형식으로 최적화합니다.
3. 구별도가 높은 열을 인덱스로 선택해 보세요. 구별 공식은 반복되지 않는 필드의 비율을 나타내는 count(distinct col)/count(*)입니다. 그러면 고유성 키의 구별은 1인 반면, 일부 상태 및 성별 필드는 빅 데이터에 직면하여 0의 구별을 가질 수 있습니다. 그러면 누군가 이 비율에 대한 실증적 가치가 있는지 물을 수 있습니다. 다양한 사용 시나리오로 인해 이 값을 결정하기가 어렵습니다. 일반적으로 조인해야 하는 필드는 0.1 이상, 즉 스캔당 평균 10개의 레코드가 필요합니다.
4. 인덱스 열은 계산에 참여할 수 없으므로 열을 유지합니다. from_unixtime(create_time) = '2015-08-14'와 같은 "clean"은 인덱스를 사용할 수 없습니다. 이유는 매우 간단합니다. b+ 트리는 데이터 테이블에 필드 값을 저장합니다. 비교하려면 모든 요소에 기능을 적용해야 합니다. 분명히 비용이 너무 높습니다. 따라서 명령문은 create_time = unix_timestamp('2015-08-14')로 작성되어야 합니다.
5. 인덱스를 최대한 확장하고, 새 인덱스를 생성하지 마세요. 예를 들어, 테이블에 이미 a라는 인덱스가 있고 이제 (a, b)라는 인덱스를 추가하려는 경우 원래 인덱스만 수정하면 됩니다.
6. order by 또는 group by 절에서 인덱스별로 정렬하려면 인덱스 열의 순서가 order by 또는 group by 절의 순서 및 모든 열의 정렬 방향과 일치해야 합니다. (역순 또는 양수 순서)은 동일합니다. 쿼리가 여러 테이블과 연결된 경우 order by 절에서 참조하는 필드가 모두 첫 번째 테이블에서 나온 경우에만 인덱스를 사용하여 정렬할 수 있습니다. group by 문과 쿼리 유형 문은 동일합니다. 인덱스의 가장 왼쪽 접두사 원칙을 충족해야 합니다. 그렇지 않으면 mysql은 정렬 작업을 수행하고 인덱스를 사용하여 정렬할 수 없습니다. by 절은 가장 왼쪽 접두사 원칙을 충족하지 않습니다. 즉, 리더가 상수인 경우 where 또는 Join이 이러한 열에 대한 상수를 지정하는 경우 인덱스 부족을 보완할 수 있습니다.

5. 예시

문 1:

문 2:

이 두 명령문에 대해 별도로 고려하는 경우 두 개의 인덱스를 생성할 수 있습니다.
명령문 1에 대해 생성(status, netting_batch_no, 채무자_agent_member_id)
명령문 2에 대해 생성(netting_batch_no, debtor_agent_member_id,transaction_currency);
모두 고려하면 실제로는 하나의 인덱스, 즉 (netting_batch_no,debtor_agent_member_id)로 충분합니다. 이 두 필드는 서로 구별되기 때문에 인덱스에 넣을 필요가 없습니다.
인덱싱 원칙 2에 따르면 문 1은 이 인덱스에 갈 수 있습니다.
인덱싱 원칙 1에 따르면 문 2도 이 인덱스에 갈 수 있습니다.
인덱스가 많을수록 좋습니다. .인덱스를 너무 많이 생성하면 데이터베이스 메모리나 디스크 소모가 늘어나고 삽입, 삭제 등의 작업 성능에 영향을 미치게 됩니다. 인덱스 생성 시 모든 사항을 고려해야 합니다. > 위 내용은 indexing의 원리입니다. innodb를 예로 들어 자세한 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!