mysql 병합 유니온 병합 sort_union의 차이점
- 伊谢尔伦원래의
- 2016-11-21 15:20:241394검색
MYSQL 매뉴얼의 Index Merge Optimization을 보다가 아이디어가 떠서 다음과 같이 기록해 두었습니다.
두 가지 방법의 차이점을 먼저 설명하자면
둘 다 메소드는 동일한 테이블을 사용합니다. 보조 인덱스가 사용되며 이는 단일 테이블입니다.
병합 통합: 또는 보조 인덱스에 모든 키 부분이 포함된 경우 정렬된 클러스터형 색인의 키 값이나 ROWID를 가져올 수 있으며 추가 정렬 없이 간단한 통합만으로도 중복을 제거하기에 충분합니다.
소스 코드 인터페이스 Quick_ror_union_select 클래스
merge sort_union: 위와 다른 점은 보조 인덱스의 키 부분을 모두 포함하지 않기 때문에 먼저 정렬된 클러스터링 인덱스 키 값이나 ROWID를 구해야 클러스터링 인덱스를 정렬할 수 있습니다. 통합 연산을 위한 인덱스 키 값 또는 ROWID
소스 코드 인터페이스 Quick_index_merge_select
참조 매뉴얼: 9.2.1.4 인덱스 병합 최적화
일반적으로 mysql이 기본 키가 좋은 정렬 방법이라고 판단할 수 없는 한, 추가 정렬 작업이 필요합니다.
병합 정렬 알고리즘을 어느 정도 이해하면 이러한 처리가 필요하다는 것을 알 수 있습니다.
병합이 필요한 모든 하위 집합은 병합 시 정렬되어야 한다는 것을 알 수 있습니다. 예, 다음은 간단한 병합 알고리즘의 다이어그램입니다.
1 2 5 9와 3 4 7 8을 기본 키로 간주하면 정렬해야 합니다. .최종 병합을 완료합니다.
물론, 정렬이 완료되면 상위 계층 정렬 작업을 사용할 수도 있습니다.
정렬을 수행합니다. 데이터 구조를 사용하는 경우 외부 디스크에도 좋은 방식으로 정렬되어 있다는 것을 알아야 합니다.
여기를 이해하려면 INNODB B+ 트리 페이지 블록에서 결합 인덱스의 배열을 이해해야 합니다.
예: seq int, id1 int, id2 int seq는 기본 키이고, ID1, DI2는 B+ 인덱스
조합이고
values(1,1,2) values(2,1,3) values(3,1,2)
값을 삽입합니다. 분명히 결합된 인덱스의 리프 노드는
순서로 배열됩니다.
1 2 3 id1:1 id1:1 id1:1 id2:2 id2:2 id2:3 seq:1 seq:3 seq:2
즉, 먼저 id1로 정렬한 다음 id2로 정렬하고 마지막으로 기본 키 seq로 정렬합니다.
그러면 최종 기본 키의 순서가 1인 것을 알 수 있습니다. 3 2, 순서가 맞지 않습니다. 분명히 이러한
결과 집합은 병합된 결과 집합으로 사용할 수 없습니다. 따라서 정렬이 필요합니다. 이것이 바로
sort_union sort에서 나온 것입니다.
그럼 두 실행 계획의 차이점을 보여드리겠습니다.
스크립트:
create table testmer (seq int,id1 int,id2 int,id3 int,id4 int,primary key(seq),key(id1,id2),key(id3,id4)); insert into testmer values(1,1,2,4,4); insert into testmer values(2,1,3,4,5); insert into testmer values(3,1,2,4,4); insert into testmer values(4,2,4,5,6); insert into testmer values(5,2,6,5,8); insert into testmer values(6,2,10,5,3); insert into testmer values(7,4,5,8,10); insert into testmer values(8,0,1,3,4);
mysql> select * from testmer; +-----+------+------+------+------+ | seq | id1 | id2 | id3 | id4 | +-----+------+------+------+------+ | 1 | 1 | 2 | 4 | 4 | | 2 | 1 | 3 | 4 | 5 | | 3 | 1 | 2 | 4 | 4 | | 4 | 2 | 4 | 5 | 6 | | 5 | 2 | 6 | 5 | 8 | | 6 | 2 | 10 | 5 | 3 | | 7 | 4 | 5 | 8 | 10 | | 8 | 0 | 1 | 3 | 4 | +-----+------+------+------+------+
Using sort_union： mysql> explain select * from testmer force index(id1,id3) where id1=1 or id3=4; +----+-------------+---------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------+ | 1 | SIMPLE | testmer | NULL | index_merge | id1,id3 | id1,id3 | 5,5 | NULL | 6 | 100.00 | Using sort_union(id1,id3); Using where | +----+-------------+---------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------+ 1 row in set, 1 warning (5.07 sec)
분명히 우리는 key(id1,id2)만 봅니다. 여기 정렬이 다음과 같으므로 정렬이 필요합니다:
1 2 3
id1:1 id1:1 id1:1
id2:2 id2:2 id2:3
seq:1 seq:3 seq: 2
모두
mysql> explain select * from testmer force index(id1,id3) where id1=1 and id2=2 or id3=4 and id4=1; +----+-------------+---------+------------+-------------+---------------+---------+---------+------+------+----------+-----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------------+---------------+---------+---------+------+------+----------+-----------------------------------+ | 1 | SIMPLE | testmer | NULL | index_merge | id1,id3 | id1,id3 | 10,10 | NULL | 2 | 100.00 | Using union(id1,id3); Using where | +----+-------------+---------+------------+-------------+---------------+---------+---------+------+------+----------+-----------------------------------+
인 보조 인덱스 KEY_PART를 취하면 물론 여기서는 id1=1과 id2를 볼 필요가 없습니다. =2(id3=4 및 id4=1 동일)
은 다음과 같이 배열됩니다.
1 2 id1:1 id1:1 id2:2 id2:2 seq:1 seq:3
즉, KEY_PART에 완전한 내용이 포함되어 있으면 기본 키가 자연스럽게 정렬됩니다. ,
사실 저는 DEBUG 환경에서 실행 중입니다. 네, testmer force index(id1,id3에서 select *를 실행하면 Unique::unique_add
(gdb) info b
Num Type Disp Enb Address What
1 breakpoint keep y 0x0000000000ebd333 in main(int, char**) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/main.cc:25
breakpoint already hit 1 time
6 breakpoint keep y 0x000000000145de13 in Unique::unique_add(void*) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/uniques.h:52
breakpoint already hit 2 times에서 중단점이 발생합니다. ) 여기서 id1=1 및 id2=1 또는 id3=4 및 id4 =1;
Unique::unique_add가 트리거되지 않습니다. 즉, 정렬 작업이 수행되지 않습니다.
마지막으로 소스 코드의 merge_sort 정렬 인터페이스를 설명하겠습니다
QUICK_INDEX_MERGE_SELECT::read_keys_and_merge()
Unique::unique_add
호출(균형 이진 트리 사용, 균형 이진 트리 사용) 레드-블랙 트리가 아닙니다. 차이점 참조:
http://blog.itpub.net/7728585/viewspace-2127419/
)
다음은 소스 코드 read_keys_and_merge()의 주석입니다. :
/*
Perform key scans for all used indexes (except CPK), get rowids and merge
them into an ordered non-recurrent sequence of rowids.
The merge/duplicate removal is performed using Unique class. We put all
rowids into Unique, get the sorted sequence and destroy the Unique.
If table has a clustered primary key that covers all rows (TRUE for bdb
and innodb currently) and one of the index_merge scans is a scan on PK,
then rows that will be retrieved by PK scan are not put into Unique and
primary key scan is not performed here, it is performed later separately.
RETURN
0 OK
other error
*/ 다음은 gdb 사용 시 스택 정보입니다.
(gdb) bt
#0 tree_insert (tree=0x7fffd801c768, key=0x7fffd801ada0, key_size=0, custom_arg=0x7fffd80103d0) at /root/mysql5.7.14/percona-server-5.7.14-7/mysys/tree.c:207
#1 0x000000000145df19 in Unique::unique_add (this=0x7fffd801c260, ptr=0x7fffd801ada0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/uniques.h:56
#2 0x000000000178e6a8 in QUICK_INDEX_MERGE_SELECT::read_keys_and_merge (this=0x7fffd89083f0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/opt_range.cc:10700
#3 0x0000000001778c73 in QUICK_INDEX_MERGE_SELECT::reset (this=0x7fffd89083f0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/opt_range.cc:1601
#4 0x000000000155e529 in join_init_read_record (tab=0x7fffd8906e20) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2471
#5 0x000000000155b6a1 in sub_select (join=0x7fffd8905b08, qep_tab=0x7fffd8906e20, end_of_records=false)
at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271
#6 0x000000000155b026 in do_select (join=0x7fffd8905b08) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944
#7 0x0000000001558efc in JOIN::exec (this=0x7fffd8905b08) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199
#8 0x00000000015f91c6 in handle_query (thd=0x7fffd8000df0, lex=0x7fffd80033d0, result=0x7fffd8007a60, added_options=0, removed_options=0)
at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:184
#9 0x00000000015ac025 in execute_sqlcom_select (thd=0x7fffd8000df0, all_tables=0x7fffd8006e98) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5391
#10 0x00000000015a4640 in mysql_execute_command (thd=0x7fffd8000df0, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889
#11 0x00000000015acff6 in mysql_parse (thd=0x7fffd8000df0, parser_state=0x7ffff0fd6600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836
#12 0x00000000015a0eb5 in dispatch_command (thd=0x7fffd8000df0, com_data=0x7ffff0fd6d70, command=COM_QUERY)
at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447
#13 0x000000000159fce6 in do_command (thd=0x7fffd8000df0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010
#14 0x00000000016e1c08 in handle_connection (arg=0x3c1c880) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312
#15 0x0000000001d71ed0 in pfs_spawn_thread (arg=0x3bec1b0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188
#16 0x0000003ca62079d1 in start_thread () from /lib64/libpthread.so.0
#17 0x0000003ca5ee8b6d in clone () from /lib64/libc.so.6첨부된 함수 인터페이스 호출의 두 가지 방법은 다음과 같습니다.
merge sort_union:
T@3: | | | | | | | | | | >QUICK_INDEX_MERGE_SELECT::QUICK_INDEX_MERGE_SELECT T@3: | | | | | | | | | | <QUICK_INDEX_MERGE_SELECT::QUICK_INDEX_MERGE_SELECT 1589 T@3: | | | | | | | | | | >QUICK_INDEX_MERGE_SELECT::init T@3: | | | | | | | | | | <QUICK_INDEX_MERGE_SELECT::init 1595 T@3: | | | | | | | | >QUICK_INDEX_MERGE_SELECT::reset T@3: | | | | | | | | | >QUICK_INDEX_MERGE_SELECT::read_keys_and_merge T@3: | | | | | | | | | <QUICK_INDEX_MERGE_SELECT::read_keys_and_merge 10716 T@3: | | | | | | | | <QUICK_INDEX_MERGE_SELECT::reset 1602 T@3: | | | | | | | | >QUICK_INDEX_MERGE_SELECT::get_next T@3: | | | | | | | | <QUICK_INDEX_MERGE_SELECT::get_next 10753 T@3: | | | | | | | | >QUICK_INDEX_MERGE_SELECT::get_next T@3: | | | | | | | | <QUICK_INDEX_MERGE_SELECT::get_next 10753 T@3: | | | | | | | | >QUICK_INDEX_MERGE_SELECT::get_next T@3: | | | | | | | | <QUICK_INDEX_MERGE_SELECT::get_next 10753 T@3: | | | | | | | | >QUICK_INDEX_MERGE_SELECT::get_next T@3: | | | | | | | | <QUICK_INDEX_MERGE_SELECT::get_next 10753 T@3: | | | | | | | >QUICK_INDEX_MERGE_SELECT::~QUICK_INDEX_MERGE_SELECT T@3: | | | | | | | <QUICK_INDEX_MERGE_SELECT::~QUICK_INDEX_MERGE_SELECT 1635
merge Union:
T@3: | | | | | | | | | | >QUICK_ROR_UNION_SELECT::init T@3: | | | | | | | | | | <QUICK_ROR_UNION_SELECT::init 1942 T@3: | | | | | | | | >QUICK_ROR_UNION_SELECT::reset T@3: | | | | | | | | <QUICK_ROR_UNION_SELECT::reset 2004 T@3: | | | | | | | | >QUICK_ROR_UNION_SELECT::get_next T@3: | | | | | | | | <QUICK_ROR_UNION_SELECT::get_next 10948 T@3: | | | | | | | | >QUICK_ROR_UNION_SELECT::get_next T@3: | | | | | | | | <QUICK_ROR_UNION_SELECT::get_next 10948 T@3: | | | | | | | | >QUICK_ROR_UNION_SELECT::get_next T@3: | | | | | | | | <QUICK_ROR_UNION_SELECT::get_next 10913 T@3: | | | | | | | >QUICK_ROR_UNION_SELECT::~QUICK_ROR_UNION_SELECT T@3: | | | | | | | <QUICK_ROR_UNION_SELECT::~QUICK_ROR_UNION_SELECT 2021
호출경로와 소스코드 호출 상황을 확인할 수 있습니다. 단지 정렬이 실제로 이루어졌는지 확인하고 어떤 방법을 사용하는지 확인하고 싶을 뿐입니다.
이 글은 제 개인적인 의견일 뿐입니다. 실수가 있으면 경고해 주세요. 감사해요!
위 내용은 mysql merge Union merge sort_union의 다른 내용입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!