복합 인덱스는 언제 사용해야 합니까?

복합 인덱스는 언제 사용해야 합니까?

1. 데이터베이스에서 복합 인덱스를 사용해야 하는 경우
2. 컴포지트 인덱스)를 사용하여 퍼포먼스에 미치는 영향은 무엇입니까?
3. 복합 인덱스를 사용해야 하는 이유는 무엇입니까?

를 들어, 저는 를어, 는 가 있습니다.homes 삭제:

CREATE TABLE IF NOT EXISTS `homes` (
  `home_id` int(10) unsigned NOT NULL auto_increment,
  `sqft` smallint(5) unsigned NOT NULL,
  `year_built` smallint(5) unsigned NOT NULL,
  `geolat` decimal(10,6) default NULL,
  `geolng` decimal(10,6) default NULL,
  PRIMARY KEY  (`home_id`),
  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),
) ENGINE=InnoDB  ;

하는 것이 요?geolat ★★★★★★★★★★★★★★★★★」geolng를 들면 다음과 같은 것입니다

대체 항목:

  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),

포함:

KEY `geolat_geolng` (`geolat`, `geolng`)

이 경우:

• 왜요?
• 컴포지트 인덱스)를 사용하여 퍼포먼스에 미치는 영향은 무엇입니까?

갱신:

많은 사람들이 전적으로 내가 실행하는 쿼리에 의존한다고 말했기 때문에, 가장 일반적으로 실행되는 쿼리는 다음과 같습니다.

SELECT * FROM homes
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

업데이트 2:

다음 데이터베이스 스키마:

CREATE TABLE IF NOT EXISTS `homes` (
  `home_id` int(10) unsigned NOT NULL auto_increment,
  `primary_photo_group_id` int(10) unsigned NOT NULL default '0',
  `customer_id` bigint(20) unsigned NOT NULL,
  `account_type_id` int(11) NOT NULL,
  `address` varchar(128) collate utf8_unicode_ci NOT NULL,
  `city` varchar(64) collate utf8_unicode_ci NOT NULL,
  `state` varchar(2) collate utf8_unicode_ci NOT NULL,
  `zip` mediumint(8) unsigned NOT NULL,
  `price` mediumint(8) unsigned NOT NULL,
  `sqft` smallint(5) unsigned NOT NULL,
  `year_built` smallint(5) unsigned NOT NULL,
  `num_of_beds` tinyint(3) unsigned NOT NULL,
  `num_of_baths` decimal(3,1) unsigned NOT NULL,
  `num_of_floors` tinyint(3) unsigned NOT NULL,
  `description` text collate utf8_unicode_ci,
  `geolat` decimal(10,6) default NULL,
  `geolng` decimal(10,6) default NULL,
  `display_status` tinyint(1) NOT NULL,
  `date_listed` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `contact_email` varchar(100) collate utf8_unicode_ci NOT NULL,
  `contact_phone_number` varchar(15) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`home_id`),
  KEY `customer_id` (`customer_id`),
  KEY `city` (`city`),
  KEY `num_of_beds` (`num_of_beds`),
  KEY `num_of_baths` (`num_of_baths`),
  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),
  KEY `account_type_id` (`account_type_id`),
  KEY `display_status` (`display_status`),
  KEY `sqft` (`sqft`),
  KEY `price` (`price`),
  KEY `primary_photo_group_id` (`primary_photo_group_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;

다음 SQL 사용:

EXPLAIN SELECT  homes.home_id,
                    address,
                    city,
                    state,
                    zip,
                    price,
                    sqft,
                    year_built,
                    account_type_id,
                    num_of_beds,
                    num_of_baths,
                    geolat,
                    geolng,
                    photo_id,
                    photo_url_dir
            FROM homes
            LEFT OUTER JOIN home_photos ON homes.home_id = home_photos.home_id
                AND homes.primary_photo_group_id = home_photos.home_photo_group_id
                AND home_photos.home_photo_type_id = 2
            WHERE homes.display_status = true
            AND homes.geolat BETWEEN -100 AND 100
            AND homes.geolng BETWEEN -100 AND 100

반품 설명:

id  select_type  table        type  possible_keys                                    key                  key_len  ref     rows  Extra
----------------------------------------------------------------------------------------------------------
1   SIMPLE       homes        ref   geolat,geolng,display_status                     display_status       1        const   2     Using where
1  SIMPLE        home_photos  ref   home_id,home_photo_type_id,home_photo_group_id   home_photo_group_id  4        homes.primary_photo_group_id   4  

설명 명령어를 읽는 방법을 잘 모르겠습니다.이게 좋아 보여요, 나빠 보여요?지금은 지렁이와 지렁이의 복합지수를 사용하지 않고 있습니다.그래야 하나요?

복합 인덱스의 이점을 제공하는 쿼리를 사용할 때는 복합 인덱스를 사용해야 합니다.다음과 같은 복합 인덱스:

index( column_A, column_B, column_C )

는 이러한 필드를 참여, 필터링 및 경우에 따라 선택에 사용하는 쿼리에 도움이 됩니다.또한 해당 복합에서 가장 왼쪽 열의 하위 집합을 사용하는 쿼리에도 유용합니다.따라서 위의 인덱스는 또한 필요한 쿼리를 충족시킬 것입니다.

index( column_A, column_B, column_C )
index( column_A, column_B )
index( column_A )

그러나 (적어도 직접적이지는 않지만, 더 나은 인덱스가 없는 경우 부분적으로 도움이 될 수 있음) 필요한 쿼리에 대해서는 도움이 되지 않습니다.

index( column_A, column_C )

column_B가 누락되어 있습니다.

원래 예에서 2차원에 대한 복합 인덱스는 주로 두 치수 또는 맨 왼쪽 치수를 조회하는 쿼리에 도움이 되지만 맨 오른쪽 치수 자체에는 도움이 되지 않습니다.항상 2차원을 쿼리하는 경우 복합 인덱스가 가장 먼저(대부분) 중요한 것은 아닙니다.

다음 세 가지 질문이 있다고 가정합니다.

질문 I:

SELECT * FROM homes WHERE `geolat`=42.9 AND `geolng`=36.4

쿼리 II:

SELECT * FROM homes WHERE `geolat`=42.9

쿼리 III:

SELECT * FROM homes WHERE `geolng`=36.4

열마다 별도의 인덱스가 있는 경우 세 가지 쿼리 모두 인덱스를 사용합니다.(MySQL)가 (geolat,geolng쿼리 I 및 쿼리 II(복합 인덱스의 첫 번째 부분을 사용)만 인덱스를 사용합니다.이 경우 쿼리 III에는 전체 테이블 검색이 필요합니다.

매뉴얼의 다중 열 인덱스 섹션에 여러 열 인덱스의 작동 방식이 명확하게 설명되어 있으므로 매뉴얼을 다시 입력하고 싶지 않습니다.

MySQL Reference Manual 페이지에서 다음 작업을 수행합니다.

다중 열 인덱스는 인덱스된 열의 값을 연결하여 만든 값을 포함하는 정렬된 배열로 간주할 수 있습니다.

지오랫 열과 지오렝 열에 대해 분리된 인덱스를 사용하는 경우 테이블에 독립적으로 검색할 수 있는 두 개의 인덱스가 있습니다.

INDEX geolat
-----------
VALUE RRN
36.4  1
36.4  8
36.6  2
37.8  3
37.8  12
41.4  4

INDEX geolng
-----------
VALUE RRN
26.1  1
26.1  8
29.6  2
29.6  3
30.1  12
34.7  4

복합 인덱스를 사용하는 경우 두 열에 대해 인덱스가 하나만 있습니다.

INDEX (geolat, geolng)
-----------
VALUE      RRN
36.4,26.1  1
36.4,26.1  8
36.6,29.6  2
37.8,29.6  3
37.8,30.1  12
41.4,34.7  4

RRN은 상대 레코드 번호입니다(간단히 말하면 ID라고 할 수 있습니다).처음 두 지수는 별도로 생성되고 세 번째 지수는 복합 지수이다.보시는 것처럼 지오랏에 의해 인덱싱되므로 복합에서 지오롱을 기반으로 검색할 수 있지만 지오랏 또는 "geolat AND 지오롱"으로 검색할 수 있습니다(geolng은 2단계 인덱스이므로).

또한 MySQL이 인덱스를 사용하는 방법 매뉴얼 섹션도 참조하십시오.

복합지수의 기능에 대한 오해가 있을 수 있습니다.은 복합 할 수 합니다.where된 열을 , 이 경우에는 색인화된 컬럼은 과 같습니다.geolat ★★★★★★★★★★★★★★★★★」geolng더 파헤쳐 보겠습니다.

주택의 좌표에 대한 데이터는 랜덤 소수점일 것입니다.

home_id  geolat  geolng
   1    20.1243  50.4521
   2    22.6456  51.1564
   3    13.5464  45.4562
   4    55.5642 166.5756
   5    24.2624  27.4564
   6    62.1564  24.2542
...

★★geolat ★★★★★★★★★★★★★★★★★」geolng가치관은 좀처럼 반복되지 않는다.의 복합 :geolat ★★★★★★★★★★★★★★★★★」geolng다음과 같이 됩니다.

index_id  geolat  geolng
   1     20.1243  50.4521
   2     20.1244  61.1564
   3     20.1251  55.4562
   4     20.1293  66.5756
   5     20.1302  57.4564
   6     20.1311  54.2542
...

따라서 종합지수의 두 번째 열은 기본적으로 무용지물입니다.복합 인덱스를 사용하는 쿼리 속도는 아마도 다음 인덱스의 인덱스와 비슷할 것입니다.geolat★★★★★★ 。

Will이 언급한 바와 같이 MySQL은 공간 확장을 지원합니다.공간 점이 두 개의 개별 열 대신 단일 열에 저장됩니다.lat lng컬럼을 클릭합니다.이러한 컬럼에는 공간 인덱스를 적용할 수 있습니다.하지만 제 개인적인 경험에 비추어 볼 때 효율이 과대평가될 수 있습니다.공간 지수가 2차원 문제를 해결하는 것이 아니라 2차 분할이 있는 R-Tree를 사용하여 검색 속도를 높일 수 있습니다.

단점은 8바이트의 두 배 정밀도를 사용하여 좌표를 저장하기 때문에 공간 포인트가 훨씬 더 많은 메모리를 소비한다는 것입니다.내가 틀렸다면 정정해 주세요.

복합 인덱스는 다음과 같은 경우에 유용합니다.

• 0개 이상의 "=" 절과 더하기
• 최대 1개의 range 절.

복합 인덱스는 두 범위를 처리할 수 없습니다.나는 이것을 내 색인 요리책에서 더 자세히 논의한다.

가장 가까운 검색 - 질문이 최적화에 관한 것이라면

WHERE geolat BETWEEN ??? AND ???
  AND geolng BETWEEN ??? AND ???

어떤 지표도 두 차원을 모두 처리할 수 없습니다.

대신에, 사람은 '개념에서 벗어나' 생각해야 한다. 치수가 으로 구현되고 PRIMARY KEYlat/lg 룩업의 매우 큰 테이블에 대해 대폭적인 효율을 얻을 수 있습니다.§ latling 블로그는 지구상에서 "가장 가까운 검색"을 구현하는 방법에 대한 자세한 내용을 다루고 있습니다.코드가 포함되어 있습니다.

PARTITIONs이치노PRIMARY KEY유용한 행이 같은 블록에 있을 가능성이 높도록 의도적으로 경도로 시작합니다.는 Stored Route를 합니다.order by... limit...커피숍이 충분히 생길 때까지(또는 무엇이든) 목표물을 중심으로 '광장'을 늘려야 합니다.또한 대원 계산과 날짜 및 폴 처리도 수행합니다.

더

다른 블로그를 작성했습니다.이 블로그는 lat/lng 검색의 5가지 방법을 비교하고 있습니다.http://mysql.rjweb.org/doc.php/latlng#representation_choices (위의 링크를 5가지 중 하나로 참조하고 있습니다.)다른 방법 중 하나는 다음과 같습니다.이 방법들은 특정 경우에 최적임을 나타냅니다.

INDEX(geolat, geolng),
INDEX(geolng, geolat)

즉, 두 개의 인덱스에 두 개의 컬럼이 있고, 지오랏과 지오렝에 대한 단일 컬럼 인덱스가 없는 것이 중요합니다.

복합 인덱스는 다음과 같은 점에서 매우 강력합니다.

• 구조 무결성 적용
• FILTED ID에서 정렬 사용

구조 무결성 강화

복합 인덱스는 단순한 인덱스 유형이 아닙니다. 기본 키로 무결성을 적용함으로써 테이블에 필요한 구조를 제공할 수 있습니다.

Mysql의 Innodb는 클러스터링을 지원하며, 다음 예는 복합 인덱스가 필요한 이유를 보여줍니다.

두 이 필요합니다.user_id, friend_id.

테이블 구조

user_id (medium_int)
friend_id (medium_int)

Primary Key -> (user_id, friend_id)

Key(PK; 프라이머리 키는 자동으로 PK(PK)에 중복이 합니다.user_id, friend_id새로운 레코드가 추가되었을 때 존재합니다.이것은 는 「Relationship Link 링크)」를 수 입니다.사용자는 1개 이상의 레코드(관계 링크)를 가지고 있으면 안 되기 때문입니다.friend_id = 2예를 들어.

복합 PK가 없으면 대리 키를 사용하여 다음 스키마를 만들 수 있습니다.

user_friend_id
user_id
friend_id

Primary Key -> (user_friend_id)

새로운 될 때마다 이 레코드가 된 이전 .user_id, friend_id는 아직 존재하지 않습니다.

따라서 복합 인덱스는 구조 무결성을 적용할 수 있습니다.

필터링된 ID에서 정렬 사용

투고 시각(타임 스탬프 또는 날짜 시간)에 따라 레코드 세트를 정렬하는 것은 매우 일반적입니다.통상, 이것은 특정의 아이디로 투고하는 것을 의미합니다.여기 예가 있습니다.

Table User_Wall_Posts (Facebook의 벽 게시물인지 생각해 보세요)

user_id (medium_int)
timestamp (timestamp)
author_id (medium_int)
comment_post (text)

Primary Key -> (user_id, timestamp, author_id)

.user_id = 10를 「」로 합니다.timestamp(신호)

SQL 쿼리

SELECT * FROM User_Wall_Posts WHERE user_id = 10 ORDER BY timestamp DES

복합 PK를 사용하면 Mysql은 인덱스를 사용하여 결과를 필터링 및 정렬할 수 있습니다.Mysql은 결과를 가져오기 위해 임시 파일 또는 파일 정렬을 사용할 필요가 없습니다.복합 키가 없으면 이 작업이 불가능하고 매우 비효율적인 쿼리를 발생시킬 수 있습니다.

해서 '찾아보고 '는보다는 ' 싶다'는 문제가 더 잘 .column_a, column_b이치노현재 데이터베이스 스키마의 경우 단일 키와 동일한 수의 복합 키가 있습니다.합합키 사사용사 !!!!! !!!!!

공간 검색을 수행하려면 지리적 영역을 매우 빠르게 검색할 수 있는 R-Tree 알고리즘이 필요합니다.이 일에 필요한 게 바로 그거야

일부 데이터베이스에는 공간 색인이 내장되어 있습니다.Google의 빠른 검색 결과 MySQL 5에 MySQL이 있습니다(SQL을 보면 MySQL을 사용하고 있는 것 같습니다).

할 때 합니다.group byclause (이 기사 http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html) 를 참조해 주세요).주의해 주세요.

GROUP BY에 인덱스를 사용하기 위한 가장 중요한 전제조건은 모든 GROUP BY 열이 동일한 인덱스의 속성을 참조하고 인덱스가 키를 순서대로 저장하는 것입니다(예를 들어 이것은 BTREE 인덱스이며 해시 인덱스가 아닙니다).

흑백은 없고, 모든 답변에 맞는 사이즈입니다.

쿼리 작업 부하가 유리할 경우 복합(또는 다중 열) 색인을 사용해야 합니다.

이를 확인하려면 쿼리 작업 부하를 프로파일링해야 합니다.

복합 인덱스는 쿼리가 해당 인덱스에서 완전히 충족될 수 있을 때 작동합니다. 즉, 쿼리에 필요한 모든 열이 인덱스에 의해 포함됨을 의미합니다.

업데이트(게시된 질문에 대한 편집 응답):테이블에서 *를 선택하는 경우 복합 인덱스를 사용할 수도 있고 사용할 수도 없습니다.확실히 하기 위해서는 EXPLY PLAN을 실행해야 합니다.

저는 @Mitch와 함께하고 있습니다.질문에 전적으로 의존합니다.다행히 언제든지 인덱스를 만들고 삭제할 수 있습니다.또한 쿼리에 EXPLINE 키워드를 추가하여 쿼리 분석기가 인덱스를 사용하고 있는지 여부를 확인할 수 있습니다.

정확한 위도/긴 쌍을 찾는다면 이 지수는 의미가 있을 것입니다.그러나 특정 장소에서 특정 거리 내에 있는 집을 찾을 수 있기 때문에 다음과 같은 질문을 할 수 있습니다(출처 참조).

select *, sqrt(  pow(h2.geolat - h1.geolat,  2) 
               + pow(h2.geolng - h1.geolng, 2) ) as distance
from homes h1, homes h2
where h1.home_id = 12345 and h2.home_id != h1.home_id
order by distance

그리고 지수는 전혀 도움이 되지 않을 것이다.지리공간 쿼리에는 이와 같은 것이 필요합니다.

업데이트: 이 쿼리 사용:

SELECT * FROM homes
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

쿼리 분석기는 Geolat에만 인덱스를 사용하거나 Geolng에만 인덱스를 사용하거나 두 인덱스를 모두 사용할 수 있습니다.종합지수를 사용하지 않을 것 같습니다.그러나 실제 데이터 세트에서 이러한 각 순열을 시험해 보고 (a) EXPLY가 무엇을 의미하는지 확인하고 (b) 쿼리에 실제로 걸리는 시간을 측정하는 것은 쉽습니다.

언급URL : https://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index