时间戳

首页 > 解决方案 > 奇怪的行为优化查询索引（MariaDB + InnoDB）

mysql - 奇怪的行为优化查询索引（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

该表看起来包含大约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;

我已经尽可能简化了查询以更好地说明问题。我在这里使用 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 ）看起来很可怕（关于解释输出）：

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 秒“发送数据”期间做了什么？

标签： mysqlmariadbquery-optimizationinnodbindices

解决方案

分析VARIABLES和GLOBAL STATUS：

观察：

  • 版本：10.1.26-MariaDB-0+deb9u1
  • 64 GB 内存
  • 正常运行时间 = 7 天 22:50:19
  • 您没有在 Windows 上运行。
  • 运行 64 位版本
  • 您似乎完全（或大部分）运行 InnoDB。

更重要的问题：

1（或更多）的“平均负载”通常表示查询效率低下。每秒“仅”91 个查询Created_tmp_disk_tables的大值进一步证实了这一点。Handler_read_rnd_next让我们看看最慢的查询。请参阅进一步调查的建议。

thread_cache_size = 20

摆脱了MyISAM，就没有必要这么大了key_buffer_size；从5G减少到50M。

我不喜欢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 的百分比。高水位线。-- 降低 key_buffer_size 以避免不必要的内存使用。

( innodb_buffer_pool_size / _ram ) = 30720M / 65536M = 46.9%-- 用于 InnoDB buffer_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 决定 concurrency_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%-- 用于解析。占内存的百分比

( query_alloc_block_size / _ram ) = 16,384 / 65536M = 0.00%-- 用于解析。占内存的百分比

( 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-- 创建“临时”表作为复杂 SELECT 的一部分的频率。

( Created_tmp_disk_tables ) = 17,887,832 / 687019 = 26 /sec-- 创建磁盘“临时”表作为复杂 SELECT 的一部分的频率 -- 增加 tmp_table_size 和 max_heap_table_size。检查何时使用 MEMORY 而不是 MyISAM 的临时表规则。也许较小的模式或查询更改可以避免 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_table_size 和 max_heap_table_size；改进指标；避免斑点等

( tmp_table_size ) = 64M-- 限制用于支持 SELECT 的MEMORY临时表的大小 -- 减少 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-- 无索引连接 -- 为 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%-- 进程创建速度 -- 增加thread_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

推荐阅读