쿼리 인덱스를 최적화하는 이상한 동작(MariaDB + InnoDB)

쿼리 인덱스를 최적화하는 이상한 동작(MariaDB + InnoDB)

저는 현재 프로젝트의 꽤 큰 테이블에 대한 인덱스를 최적화하려고 노력하고 있으며 설명 결과와 실제 쿼리 실행 시간 사이에서 매우 역직관적인 동작을 경험하고 있습니다.

서버에서 MariaDB 버전 10.1.26-MariaDB-0+deb9u1을 실행하고 있으며 다음 구성 옵션이 있습니다.

key_buffer_size         = 5G
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam_sort_buffer_size = 512M
read_buffer_size        = 2M
read_rnd_buffer_size    = 1M

query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 0M

join_buffer_size = 8M
sort_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 4K
performance_schema = ON
innodb_buffer_pool_size = 30G
innodb_log_buffer_size = 4MB
innodb_log_file_size = 1G
innodb_buffer_pool_instances = 10

테이블 모양에는 최대 12.1까지 합하여 약 680만 개의 행이 포함되어 있습니다.GB이며 다음과 같습니다.

CREATE TABLE `ad_master_test` (
    `ID_AD_MASTER` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    /* Some more attribute fields (mainly integers) ... */
    `FK_KAT` BIGINT(20) UNSIGNED NOT NULL,
    /* Some more content fields (mainly varchars/integers) ... */
    `STAMP_START` DATETIME NULL DEFAULT NULL,
    `STAMP_END` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ID_AD_MASTER`),
    INDEX `TEST1` (`STAMP_START`, `FK_KAT`),
    INDEX `TEST2` (`FK_KAT`, `STAMP_START`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=14149037;

저는 이미 문제를 더 잘 설명하기 위해 가능한 한 간단하게 질의를 마쳤습니다.저는 여기서 제 문제를 설명하기 위해 FORCE INDEX를 사용하고 있습니다.

이 첫 번째 인덱스는 설명문을 사용하여 최적화되었으며 (설명 출력과 관련하여) 상당히 유망해 보입니다.

SELECT * 
FROM `ad_master_test`
FORCE INDEX (TEST1)
WHERE FK_KAT IN
    (94169,94163,94164,94165,94166,94167,94168,94170,94171,94172,
     94173,94174,94175,94176,94177,94162,99606,94179,94180,94181,
     94182,94183,94184,94185,94186,94187,94188,94189,94190,94191,
     94192,94193,94194,94195,94196,94197,94198,94199,94200,94201,
     94202,94203,94204,94205,94206,94207,94208,94209,94210,94211,
     94212,94213,94214,94215,94216,94217,94218,94219,94220,94221,
     94222,94223,94224,94225,94226,94227,94228,94229,94230,94231,
     94232,94233,94234,94235,94236,94237,94238,94239,94240,94241,
     94178,94161)

ORDER BY STAMP_START DESC
LIMIT 24

이 결과는 다음을 설명합니다.

id     select_type   table            type     possible_keys   key     key_len    ref        rows      Extra
1      SIMPLE        ad_master_test   index    (NULL)          TEST1   14         (NULL)     24        Using where

그리고 이 프로필은:

Status                  Duration
starting                0.000180
checking permissions    0.000015
Opening tables          0.000041
After opening tables    0.000013
System lock             0.000011
Table lock              0.000013
init                    0.000115
optimizing              0.000044
statistics              0.000050
preparing               0.000039
executing               0.000009
Sorting result          0.000016
Sending data            4.827512
end                     0.000023
query end               0.000008
closing tables          0.000004
Unlocking tables        0.000014
freeing items           0.000011
updating status         0.000132
cleaning up             0.000021

두 번째 인덱스는 필드가 뒤바뀐 것입니다(여기서는 https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html ). 이는 꽤 끔찍해 보입니다(설명 출력 regarding).

SELECT * 
FROM `ad_master_test`
FORCE INDEX (TEST2)
WHERE FK_KAT IN (94169,94163,94164,94165,94166,94167,94168,94170,94171,94172,94173,94174,94175,94176,94177,94162,99606,94179,94180,94181,94182,94183,94184,94185,94186,94187,94188,94189,94190,94191,94192,94193,94194,94195,94196,94197,94198,94199,94200,94201,94202,94203,94204,94205,94206,94207,94208,94209,94210,94211,94212,94213,94214,94215,94216,94217,94218,94219,94220,94221,94222,94223,94224,94225,94226,94227,94228,94229,94230,94231,94232,94233,94234,94235,94236,94237,94238,94239,94240,94241,94178,94161)
ORDER BY STAMP_START DESC
LIMIT 24

이 결과는 다음을 설명합니다.

id     select_type   table            type     possible_keys   key     key_len    ref        rows      Extra
1      SIMPLE        ad_master_test   range    TEST2           TEST2   8          (NULL)     497.766   Using index condition; Using filesort

그리고 이 프로필은:

Status                 Duration
starting               0.000087
checking permissions   0.000007
Opening tables         0.000021
After opening tables   0.000007
System lock            0.000006
Table lock             0.000005
init                   0.000058
optimizing             0.000023
statistics             0.000654
preparing              0.000480
executing              0.000008
Sorting result         0.433607
Sending data           0.001681
end                    0.000010
query end              0.000007
closing tables         0.000003
Unlocking tables       0.000011
freeing items          0.000010
updating status        0.000158
cleaning up            0.000021

편집: 힘 인덱스를 사용하지 않을 경우 다음과 같이 변경됩니다.

id     select_type   table            type     possible_keys   key     key_len    ref        rows      Extra
1      SIMPLE        ad_master_test   index    TEST2           TEST1   14         (NULL)     345       Using where

프로파일 및 런타임은 TEST1 인덱스에서 FORCE INDEX를 사용할 때와 동일하게 유지됩니다(예상대로).

/편집

저는 솔직히 머리를 싸매지 못합니다.설명과 실제 쿼리 성능이 크게 다른 이유는 무엇입니까?5초 "데이터 보내기" 동안 서버는 무엇을 합니까?

뭔가 있는 것 같아요.TEXT아니면BLOB또는 심지어 큰VARCHAR기둥??12.1GB/6.8M = 1.8KB.필요 없는 경우에는 가져오지 마십시오. 이렇게 하면 쿼리 속도가 빨라질 수 있습니다.램을 얼마나 가지고 있습니까?

두 지수는 (4.8초 대 0.4초) 다른 시간이 소요되는 것으로 보입니다.

(STAMP_START,FK_KAT)

이렇게 하면 인덱스 B트리를 원하는 순서로 스캔하여 "파일 정렬"을 방지할 수 있습니다.그것은 일치하는 fk_kat에 대해 모든 항목을 확인해야 합니다.24시 이후에 멈출 것 같습니다(참조).LIMIT행이 일치하지만 처음 24개(빠름), 마지막 24개(매우 느림) 또는 그 사이에 있을 수 있습니다.

(FK_KAT,STAMP_START)

이 쇼는 82개의 모든 ID로 직접 이동하여 각각의 ID를 스캔하고(유일하지 않다고 가정), 수백 개의 행을 수집합니다.그런 다음 "파일 정렬"을 수행합니다. (참고: 디스크 정렬이 있는 경우 디스크 정렬이 됩니다.TEXT열을 가져오는 중입니다.)그럼 처음 24개를 전달해주세요. (이런, MariaDB 10.1에는 그런 기능이 없는 것 같네요.)

이 작업은 더 많은 단계를 거치더라도 전체 인덱스 검색을 피하면 더 빠른 것으로 나타납니다.

기타 참고사항

key_buffer_size = 20G- 마이 아이샘 사용하지 마세요.하지만 만약 그렇다면, 이것을 RAM의 10%로 바꾸세요.그렇지 않으면 다음으로 바꿉니다.30MRAM의 70%를 제공합니다.innodb_buffer_pool_size.

추가적인 논의를 원하시면 다음을 제공해 주시기 바랍니다.EXPLAIN FORMAT=JSON SELECT ...각각의 질의에 대해이것은 왜 더 나쁜 지수를 선택했는지를 설명하는 "비용" 분석을 하게 될 것입니다.

다른실험

대신에SELECT *, 타이밍을 조절하고 그리고EXPLAINs단번에SELECT ID_AD_MASTER. "빠름"이 입증되면 쿼리를 재구성하여 다음과 같이 처리합니다.

SELECT b.*   -- (or selected columns from `b`)
    FROM ( SELECT ID_AD_MASTER FROM ... ) AS a
    JOIN ad_master_test AS b  USING(ad_master_test)
    ORDER BY STAMP_START DESC ;   -- (yes, repeat the ORDER BY)

my.cnf [mysqld] 섹션(RPS는 Rate Per Second)에 고려할 제안

thread_handling=pool-of-threads  # from one-thread-per-connection see refman
max_connections=100  # from 151 because max_used_connections < 60
read_rnd_buffer_size=256K  # from 1M to reduce RAM used, < handler_read_rnd_next RPS
aria_pagecache_division_limit=50  # from 100 for WARM cache for < aria_pagecache_reads RPS
key_cache_division_limit=50  # from 100 for WARM cache for < key_reads
key_buffer_size=2G  # from 5G  Mysqltuner reports 1G used (this could be WRONG-test it)
innodb_io_capacity=30000  # from 200 since you have SSD
innodb_buffer_pool_instances=8  # from 16 for your volume of data
innodb_lru_scan_depth=128  # from 1024 to conserve CPU every SECOND see refman
innodb_buffer_pool_size=36G  # from 30G for effective size of 32G when
innodb_change_buffer_pool_size=10  # from 25% set aside for Del,Ins,Upd activities

Skype ID를 포함한 연락처 정보를 보려면 프로필, 네트워크 프로필을 확인하십시오.구성을 개선할 수 있는 추가적인 기회가 있습니다.

조언을 하루에 한 번만 변경하고, 긍정적인 결과가 나오면 다음 제안으로 넘어갑니다.그렇지 않으면 심각한 결과와 어떤 변화가 문제를 일으켰는지 알려주시기 바랍니다.

분석VARIABLES그리고.GLOBAL STATUS:

관측치:

• 버전: 10.1.26-MariaDB-0+deb9u1
• 64GB의 RAM
• 가동시간 = 7d 22:50:19
• Windows에서 실행되고 있지 않습니다.
• 64비트 버전 실행 중
• 전체(또는 대부분) InnoDB를 실행하고 있는 것 같습니다.

더 중요한 문제:

"로드 평균"이 1(또는 그 이상)이면 일반적으로 비효율적인 쿼리를 나타냅니다.이는 다음에 대한 큰 값으로 더욱 확인할 수 있습니다.Created_tmp_disk_tables그리고.Handler_read_rnd_next"단순히" 초당 91개의 쿼리를 수행할 수 있습니다.가장 느린 쿼리를 보겠습니다.자세한 내용은 권장 사항을 참조하십시오.

thread_cache_size = 20

My ISAM을 없앴으니 이렇게 크게 할 필요가 없습니다.key_buffer_size; 5G에서 5M로 감소합니다.

저는 팬이 아닙니다.ROW_FORMAT=COMPRESSED; 이는 질문에 다음과 같은 두 가지 관련 영향을 미칩니다.압축/압축 해제를 위한 CPU 증가 및 추가 buffer_pool 공간이 필요합니다.반면에.GLOBAL STATUS는 30GB가 "너무 작음"을 나타내지 않습니다.디스크 공간 사용을 줄일 필요가 있습니까?

최적화 기능을 꺼 놓으셨습니까?이것은 다른 문제에 대한 대응이었습니까?

세부사항 및 기타 관측치:

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (5120M - 1.2 * 25 * 1024) / 65536M = 7.8%-- key_buffer에서 낭비된 RAM 비율입니다. -- key_buffer_size를 줄입니다.

( Key_blocks_used * 1024 / key_buffer_size ) = 25 * 1024 / 5120M = 0.00%-- key_buffer 사용 비율입니다.하이워터마크. -- 불필요한 메모리 사용을 방지하기 위해 키_버퍼_크기를 낮춥니다.

( innodb_buffer_pool_size / _ram ) = 30720M / 65536M = 46.9%-- InnoDB 버퍼_pool에 사용된 RAM 비율(%)

( table_open_cache ) = 4,096-- 캐시할 테이블 설명자 수 -- 일반적으로 수백 개가 좋습니다.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 6,714,002,432 / (687019 / 3600) / 2 / 1024M = 0.0164-- 비율 -- (분 참조)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 687,019 / 60 * 1024M / 6714002432 = 1,831-- InnoDB 로그 회전 간격 분 5.6.8부터는 동적으로 변경할 수 있습니다. my.cnf도 변경해야 합니다. -- (회전 간격 60분 권장 사항은 다소 자의적입니다.)innodb_log_file_size 조정 (AWS에서는 변경 불가)

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( Innodb_rows_deleted / Innodb_rows_inserted ) = 1,319,619 / 2015717 = 0.655-- 전환 - "대기열에 넣지 말고 그냥 해." (MySQL이 대기열로 사용되는 경우)

( innodb_thread_concurrency ) = 0-- 0 = InnoDB가 currency_tickets에 가장 적합한 항목을 결정하도록 합니다. -- 0 또는 64로 설정합니다.그러면 CPU가 줄어들 수 있습니다.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 모든 데드락을 기록할지 여부. - 데드락에 시달리고 있다면, 이 설정을 실행합니다.주의:데드락이 많은 경우 디스크에 많이 쓸 수 있습니다.

( innodb_buffer_pool_populate ) = OFF = 0-- NUMA 제어

( query_prealloc_size / _ram ) = 24,576 / 65536M = 0.00%-- 파싱용.램(RAM)의 pct

( query_alloc_block_size / _ram ) = 16,384 / 65536M = 0.00%-- 파싱용.램(RAM)의 pct

( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%

( bulk_insert_buffer_size / _ram ) = 8M / 65536M = 0.01%-- 다중 행 INSERT 및 LOAD DATA의 버퍼 - 너무 크면 RAM 크기가 위협받을 수 있습니다.너무 작으면 그러한 작업이 방해될 수 있습니다.

( Created_tmp_tables ) = 19,436,364 / 687019 = 28 /sec-- 복잡한 SELECT의 일부로 "temp" 테이블을 만드는 빈도입니다.

( Created_tmp_disk_tables ) = 17,887,832 / 687019 = 26 /sec-- tmp_table_size 증가 및 max_heap_table_size 증가와 같은 복잡한 SELECT의 일부로 디스크 "temp" 테이블을 만드는 빈도입니다.MyISAM 대신 MEMORY를 사용할 때의 온도 테이블 규칙을 확인합니다.사소한 스키마나 쿼리 변경으로 MyISAM을 피할 수도 있습니다.더 나은 인덱스와 쿼리 재구성이 도움이 될 가능성이 높습니다.

( Created_tmp_disk_tables / Questions ) = 17,887,832 / 62591791 = 28.6%-- 디스크에 있는 tmp 테이블이 필요한 쿼리의 pct. -- 더 나은 인덱스 / no blobs / 등입니다.

( Created_tmp_disk_tables / Created_tmp_tables ) = 17,887,832 / 19436364 = 92.0%-- 디스크에 흘린 임시 테이블의 백분율 -- tmp_table_size 및 max_heap_table_size를 늘리거나 인덱스를 개선하거나 blob을 방지할 수 있습니다.

( tmp_table_size ) = 64M-- RAM 부족을 방지하기 위해 SELECT -- Reduction tmp_table_size를 지원하는 데 사용되는 메모리 온도 테이블 크기 제한.아마도 64M 이상은 아닐 것입니다.

( Handler_read_rnd_next ) = 703,386,895,308 / 687019 = 1023824 /sec-- 테이블 검색이 많은 경우 높음 - 부적절한 키일 수 있습니다.

( Handler_read_rnd_next / Com_select ) = 703,386,895,308 / 58493862 = 12,024-- SELECT마다 스캔한 평균 증가율(약) -- read_buffer_size를 높이는 방법을 고려합니다.

( Select_full_join ) = 15,981,913 / 687019 = 23 /sec-- joins without index - JOIN에 사용되는 테이블에 적합한 인덱스를 추가합니다.

( Select_full_join / Com_select ) = 15,981,913 / 58493862 = 27.3%-- 인덱스 없는 조인을 선택한 비율(%) - JOIN에 사용되는 테이블에 적합한 인덱스를 추가합니다.

( Select_scan ) = 1,510,902 / 687019 = 2.2 /sec-- 전체 테이블 검색 - 인덱스 추가/쿼리 최적화(작은 테이블이 아닌 경우)

( sort_buffer_size ) = 8M-- 스레드당 하나씩이고 5.6.4까지 풀 사이즈로 배치되므로 낮게 유지하십시오. 그 이후에는 더 큰 것이 좋습니다. 사용 가능한 RAM을 소모하는 것일 수 있습니다. 2M 이하를 권장하지 마십시오.

( binlog_format ) = binlog_format = STATEMENT-- 문장/행/혼합.ROW가 선호되므로 기본값이 될 수 있습니다.

( slow_query_log ) = slow_query_log = OFF-- 느린 쿼리 기록 여부 (5.1.12)

( long_query_time ) = 10-- "느린" 쿼리를 정의하기 위한 컷오프(초) -- 제안 2

( Threads_created / Connections ) = 3,081 / 303642 = 1.0%-- 프로세스 생성의 신속성 - 스레드_cache_size 증가(Windows 이외)

비정상적으로 큼:

Connection_errors_peer_address = 2
Handler_icp_attempts = 71206 /sec
Handler_icp_match = 71206 /sec
Handler_read_next / Handler_read_key = 283
Handler_read_prev = 12522 /sec
Handler_read_rnd_deleted = 16 /sec
Innodb_rows_read = 1255832 /sec
Key_blocks_unused = 4.24e+6
Performance_schema_table_instances_lost = 32
Select_range / Com_select = 33.1%
Sort_scan = 27 /sec
Tc_log_page_size = 4,096
innodb_lru_scan_depth / innodb_io_capacity = 5.12
innodb_max_dirty_pages_pct_lwm = 0.10%
max_relay_log_size = 100MB
myisam_sort_buffer_size = 512MB

비정상 문자열:

Compression = ON
innodb_cleaner_lsn_age_factor = HIGH_CHECKPOINT
innodb_empty_free_list_algorithm = BACKOFF
innodb_fast_shutdown = 1
innodb_foreground_preflush = EXPONENTIAL_BACKOFF
innodb_log_checksum_algorithm = INNODB
myisam_stats_method = NULLS_UNEQUAL
opt_s__engine_condition_pushdown = off
opt_s__mrr = off
opt_s__mrr_cost_based = off

언급URL : https://stackoverflow.com/questions/51499098/weird-behavior-optimizing-query-indices-mariadb-innodb