怪异行为优化查询索引(mariadb+innodb)

kcrjzv8t  于 2021-06-20  发布在  Mysql
关注(0)|答案(3)|浏览(362)

我目前正在尝试为一个相当大的项目表优化索引,并且在explain结果和实际查询运行时之间遇到了一个非常违反直觉的行为。
服务器正在运行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

该表包含约680万行,总计12.1gb,如下所示:

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;

为了更好地说明问题,我已经尽可能地简化了查询。我用力指数来说明我的问题。
第一个索引使用explain语句进行了优化,看起来非常有前途(关于explain输出):

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 )这看起来很可怕(关于解释输出):

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索引时保持相同(如预期的那样)。
/编辑
我真的不知道该怎么办。为什么解释和实际的查询性能相差那么大呢。服务器在5秒钟“发送数据”时做什么?

4sup72z8

4sup72z81#

为my.cnf[mysqld]部分考虑的建议(rps是ratepersecond)

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在内的联系信息。还有其他机会可以改进您的配置。
记住建议,每天只有一次改变,如果有积极的结果,继续下一个建议。否则让我知道任何严重的不良后果,以及哪些变化似乎导致了问题,请。

0aydgbwb

0aydgbwb2#

看起来有一些 TEXT 或者 BLOB 或者更大 VARCHAR 柱??12.1gb/6.8m=1.8kb。如果你不需要它们,就不要拿来;这可能会加快任何此类查询的速度。你有多少公羊?
这两个指数似乎需要不同的时间(4.8秒对0.4秒)。
( STAMP_START , FK_KAT )
这可以通过按所需顺序扫描索引btree来避免“文件排序”。它必须检查每个条目是否有匹配的fk\u kat。我想24点以后就会停了(见 LIMIT )匹配行,但可能是前24行(快),后24行(非常慢),或者介于两者之间。
( FK_KAT , STAMP_START )
这个节目直接转到所有82个ID,扫描每个ID(假设不是唯一的),收集可能数百行。然后进行“文件排序”(注意:这将是一个磁盘排序,如果有的话 TEXT 列)然后传递前24个(哎呀;我认为mariadb 10.1没有这个功能。)
尽管这需要更多的步骤,但通过避免全索引扫描,速度会更快。
其他注意事项 key_buffer_size = 20G -别用myisam。但如果你这样做了,把这个改成10%的内存。如果你没有,就改成 30M 把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)
qco9c6ql

qco9c6ql3#

分析 VARIABLES 以及 GLOBAL STATUS :
观察:
版本:10.1.26-mariadb-0+deb9u1
64 gb内存
正常运行时间=7d 22:50:19
您没有在windows上运行。
运行64位版本
您似乎正在运行全部(或大部分)innodb。
更重要的问题是:
“平均负载”为1(或更多)通常表示查询效率低下。这一点进一步得到了 Created_tmp_disk_tables 以及 Handler_read_rnd_next 每秒“仅仅”91个查询。让我们看看最慢的查询。见进一步调查的建议。 thread_cache_size = 20 除掉米桑之后,就没有必要这么大的开支了 key_buffer_size ; 从5克减少到50米。
我不喜欢 ROW_FORMAT=COMPRESSED ; 对于您的问题,这有两个相关的影响:增加了用于压缩/解压缩的cpu,以及需要额外的缓冲池空间。另一方面 GLOBAL STATUS 并不表示30gb“太小”。是否需要缩减磁盘空间使用量?
你关闭了一些优化?这是对其他问题的回应吗?
细节和其他观察: ( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (5120M - 1.2 * 25 * 1024) / 65536M = 7.8% --在密钥缓冲区中浪费的ram百分比。-减小密钥缓冲区大小。 ( Key_blocks_used * 1024 / key_buffer_size ) = 25 * 1024 / 5120M = 0.00% --使用的密钥缓冲区百分比。高水位线。-降低键缓冲区大小以避免不必要的内存使用。 ( innodb_buffer_pool_size / _ram ) = 30720M / 65536M = 46.9% --innodb缓冲池使用的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 --从5.6.8开始的innodb日志循环间隔分钟,可以动态更改;一定要同时更改我的.cnf。-(旋转间隔60分钟的建议有些随意。)调整innodb\u log\u file\u大小(无法在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决定并发的最佳票证。-设置为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百分比 ( query_alloc_block_size / _ram ) = 16,384 / 65536M = 0.00% --用于解析。ram百分比
( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10% ( bulk_insert_buffer_size / _ram ) = 8M / 65536M = 0.01% --用于多行插入和加载数据的缓冲区—太大可能会威胁ram大小。规模过小可能会妨碍此类行动。 ( Created_tmp_tables ) = 19,436,364 / 687019 = 28 /sec --作为复杂选择的一部分,创建“临时”表的频率。 ( Created_tmp_disk_tables ) = 17,887,832 / 687019 = 26 /sec --作为复杂选择的一部分,创建磁盘“temp”表的频率—增加tmp\u table\u size和max\u heap\u table\u size。当使用内存而不是myisam时,检查temp表的规则。也许,对模式或查询的微小更改可以避免myisam。更好的索引和查询的重新格式化更有可能有所帮助。 ( Created_tmp_disk_tables / Questions ) = 17,887,832 / 62591791 = 28.6% --磁盘tmp表上所需查询的百分比。-更好的索引/无斑点/等。 ( Created_tmp_disk_tables / Created_tmp_tables ) = 17,887,832 / 19436364 = 92.0% --溢出到磁盘的临时表的百分比--可能会增加tmp\u table\u size和max\u heap\u table\u size;改进指标;避免斑点等。 ( tmp_table_size ) = 64M --限制用于支持select的内存临时表的大小--减小tmp表的大小以避免ram耗尽。也许不超过六千四百万。 ( 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_full_join ) = 15,981,913 / 687019 = 23 /sec --没有索引的联接——向联接中使用的表添加适当的索引。 ( Select_full_join / Com_select ) = 15,981,913 / 58493862 = 27.3% --%的选择是无索引联接--向联接中使用的表添加适当的索引。 ( Select_scan ) = 1,510,902 / 687019 = 2.2 /sec --全表扫描—添加索引/优化查询(除非它们是小表) ( sort_buffer_size ) = 8M --每根线一根,在5.6.4之前保持全尺寸,所以保持低;再大一点就可以了这可能是吃进可用的公羊;建议不超过2米。 ( 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% --进程创建的快速性--增加线程\u缓存\u大小(非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

相关问题