mysql 인덱스 병합: 하나의 SQL이 여러 인덱스를 사용할 수 있습니다.

재인쇄할 소스를 표시해 주세요: mysql 인덱스 병합: 하나의 SQL은 여러 인덱스를 사용할 수 있습니다.

머리말

MySQL의 인덱스 병합은 새로운 기능이 아닙니다. mysql5.0 버전부터 구현되었습니다. 제가 아직도 이 블로그 글을 쓰는 이유는 많은 사람들이 SQL 문이 하나의 인덱스만 사용할 수 있다는 오해를 여전히 갖고 있기 때문입니다. 이 문서에서는 몇 가지 예를 통해 인덱스 병합을 사용하는 방법을 설명합니다.

인덱스 병합이란

mysql 문서

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. 
The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. 
This access method merges index scans from a single table; 
it does not merge scans across multiple tables.

에서 인덱스 병합에 대한 설명을 살펴보자. 인덱스 병합은 여러 인덱스의 범위 스캔을 하나의 인덱스로 병합하는 것입니다.
2. 인덱스 병합 시 인덱스를 먼저 결합, 교차, 교차한 후 결합하여 하나의 인덱스로 병합합니다.
3. 병합해야 하는 인덱스는 하나의 테이블에만 속할 수 있습니다. 여러 테이블에서 인덱스 병합을 수행할 수 없습니다.

인덱스 병합을 사용하면 어떤 이점이 있나요

간단히 말해서, 인덱스 병합을 통해 하나의 SQL이 여러 인덱스를 사용할 수 있습니다. 교차점, 합집합 또는 교차점을 먼저 취한 다음 이러한 지수의 합집합을 취합니다. 이렇게 하면 데이터 테이블에서 데이터를 검색하는 횟수가 줄어들고 쿼리 효율성이 향상됩니다.

인덱스 병합이 사용되었는지 확인하는 방법

explain을 사용하여 SQL문을 동작시킬 때 index merging을 사용하면 출력 내용의 type 컬럼에 index_merge가 표시되고, key 컬럼에는 사용된 모든 인덱스가 표시된다. 다음과 같습니다:

explain의 extra 필드에는 다음과 같은 유형이 있습니다:
union index를 사용하여 Union 가져오기
sort_union을 사용하여 rowid별로 검색된 데이터를 먼저 정렬한 다음 Union을 가져옵니다
intersect를 사용하여 교차점 가져오기

현재 구현에 따르면 인덱스별로 교차를 검색하려면 인덱스를 통해 검색된 데이터의 순서가 rowid 순서와 일치하는지 확인해야 하기 때문에 sort_intersect가 없다는 것을 알 수 있습니다. 따라서 정렬할 필요가 없습니다.

sort_union 인덱스 병합 예시

데이터 테이블 구조

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `key1_part1` int(11) NOT NULL DEFAULT '0', `key1_part2` int(11) NOT NULL DEFAULT '0', `key2_part1` int(11) NOT NULL DEFAULT '0', `key2_part2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `key1` (`key1_part1`,`key1_part2`), KEY `key2` (`key2_part1`,`key2_part2`) ) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

데이터

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

mysql> select * from test; +----+------------+------------+------------+------------+ | id | key1_part1 | key1_part2 | key2_part1 | key2_part2 | +----+------------+------------+------------+------------+ | 1 | 1 | 1 | 1 | 1 | | 2 | 1 | 1 | 2 | 1 | | 3 | 1 | 1 | 2 | 2 | | 4 | 1 | 1 | 3 | 2 | | 5 | 1 | 1 | 3 | 3 | | 6 | 1 | 1 | 4 | 3 | | 7 | 1 | 1 | 4 | 4 | | 8 | 1 | 1 | 5 | 4 | | 9 | 1 | 1 | 5 | 5 | | 10 | 2 | 1 | 1 | 1 | | 11 | 2 | 2 | 1 | 1 | | 12 | 3 | 2 | 1 | 1 | | 13 | 3 | 3 | 1 | 1 | | 14 | 4 | 3 | 1 | 1 | | 15 | 4 | 4 | 1 | 1 | | 16 | 5 | 4 | 1 | 1 | | 17 | 5 | 5 | 1 | 1 | | 18 | 5 | 5 | 3 | 3 | | 19 | 5 | 5 | 3 | 1 | | 20 | 5 | 5 | 3 | 2 | | 21 | 5 | 5 | 3 | 4 | | 22 | 6 | 6 | 3 | 3 | | 23 | 6 | 6 | 3 | 4 | | 24 | 6 | 6 | 3 | 5 | | 25 | 6 | 6 | 3 | 6 | | 26 | 6 | 6 | 3 | 7 | | 27 | 1 | 1 | 3 | 6 | | 28 | 1 | 2 | 3 | 6 | | 29 | 1 | 3 | 3 | 6 | +----+------------+------------+------------+------------+ 29 rows in set (0.00 sec)

使用索引合并的案例

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from test where (key1_part1=4 and key1_part2=4) or (key2_part1=4 and key2_part2=4)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: index_merge possible_keys: key1,key2 key: key1,key2 key_len: 8,4 ref: NULL rows: 3 Extra: Using sort_union(key1,key2); Using where 1 row in set (0.00 sec)

未使用索引合并的案例

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from test where (key1_part1=1 and key1_part2=1) or key2_part1=4\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ALL possible_keys: key1,key2 key: NULL key_len: NULL ref: NULL rows: 29 Extra: Using where 1 row in set (0.00 sec)

sort_union 요약

위의 두 가지 경우를 보면, 같은 모드의 SQL 문에서는 인덱스를 사용할 수 있는 경우도 있고, 인덱스를 사용하지 못하는 경우도 있는 것을 알 수 있습니다. 인덱스를 사용할 수 있는지 여부는 MySQL 쿼리 최적화 프로그램이 통계 데이터를 분석한 후 인덱스를 사용하는 것이 더 빠르다고 생각하는지 여부에 따라 달라집니다.
따라서 단순히 SQL 문이 인덱스를 사용할 수 있는지 여부를 논의하는 것은 다소 일방적이며 데이터도 고려해야 합니다.

Union Index 병합 사용 사례

데이터 테이블 구조

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `key1_part1` int(11) NOT NULL DEFAULT '0', `key1_part2` int(11) NOT NULL DEFAULT '0', `key2_part1` int(11) NOT NULL DEFAULT '0', `key2_part2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `key1` (`key1_part1`,`key1_part2`,`id`), KEY `key2` (`key2_part1`,`key2_part2`,`id`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

数据结构和之前有所调整。主要调整有如下两方面：
1、引擎从myisam改为了innodb。
2、组合索引中增加了id，并把id放在最后。

数据

数据和上面的数据一样。

使用索引合并的案例

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from test where (key1_part1=4 and key1_part2=4) or (key2_part1=4 and key2_part2=4)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: index_merge possible_keys: key1,key2 key: key1,key2 key_len: 8,8 ref: NULL rows: 2 Extra: Using union(key1,key2); Using where 1 row in set (0.00 sec)

노동조합 개요

동일한 데이터, 동일한 SQL 문이지만 데이터 테이블 구조가 sort_union에서 Union으로 조정되었습니다. 여러 가지 이유가 있습니다:
1. 인덱스를 통해 검색된 데이터가 rowid별로 정렬되어 있으면 Union을 사용할 수 있습니다.
2. 결합된 인덱스 끝에 id 필드를 추가합니다. 인덱스의 처음 두 필드에서 검색된 데이터를 id별로 정렬하는 것이 목적입니다.
3. 엔진을 myisam에서 innodb로 변경합니다. id와 rowid의 순서를 일관되게 만드는 것이 목적입니다.

교차 사용 사례

mysql 인덱스 병합: 하나의 SQL이 여러 인덱스를 사용할 수 있습니다
http://www.php.cn/

위는 mysql 인덱스 병합입니다. :하나의 SQL은 여러 인덱스의 내용을 사용할 수 있습니다. 더 많은 관련 내용은 PHP 중국어 웹사이트(www.php.cn)를 참고하세요!