mysql 自适应hash索引作用 mysql自适应哈希索引

转载

误会一场 2023-12-09 18:00:14

文章标签 mysql 自适应hash索引作用数据库 mysql 自适应缓存 文章分类 MySQL 数据库

mysql中innodb-内存结构-自适应哈希索引与日志缓存区

1、自适应哈希索引简介
2、日志缓存区

1、innodb_flush_log_at_trx_commit
2、innodb_flush_log_at_timeout

3、命令描述文档

首先这里要简要说明，该文章翻译自

mysql手册，并经过我的整合，所以文字内容较多，但是实际含金量比较高，

建议大家仔细阅读，

必有收获。

1、自适应哈希索引简介

自适应哈希索引能够InnoDB在具有适当组合的工作负载和足够的缓存池内存的系统上执行更像内存数据库，而不会牺牲事务功能或可靠性。自适应哈希索引由innodb_adaptive_hash_index 变量启用，或在服务器启动时由关闭 --skip-innodb-adaptive-hash-index。

根据观察到的搜索模式，使用索引键的前缀构建散列索引。前缀可以是任意长度，也可能只有 B 树中的某些值出现在哈希索引中。哈希索引是针对经常访问的索引页面按需构建的。

如果一个表几乎完全适合主内存，哈希索引通过启用任何元素的直接查找来加速查询，将索引值转换为某种指针。 InnoDB具有监视索引搜索的机制。如果InnoDB注意到查询可以从构建哈希索引中受益，它会自动这样做。

对于某些工作负载，哈希索引查找的加速大大超过了监视索引查找和维护哈希索引结构的额外工作。对自适应哈希索引的访问有时会成为繁重工作负载下的争用源，例如多个并发连接。使用LIKE运算符和% 通配符的查询也往往不会受益。对于无法从自适应哈希索引中受益的工作负载，将其关闭可减少不必要的性能开销。由于很难提前预测自适应哈希索引功能是否适合特定系统和工作负载，请考虑在启用和禁用它的情况下运行基准测试。

在 MySQL 5.7 中，自适应哈希索引功能是分区的。每个索引都绑定到一个特定的分区，每个分区都由一个单独的闩锁保护。分区由innodb_adaptive_hash_index_parts 变量控制。在早期版本中，自适应哈希索引功能受到单个锁存器的保护，这在繁重的工作负载下可能会成为一个争用点。该 innodb_adaptive_hash_index_parts 变量默认设置为 8。最大设置为 512。

可以在SHOW ENGINE INNODB STATUS输出的SEMAPHORES部分监控自适应哈希索引的使用和争用。如果有许多线程在btr0sea.c中创建的rw-latches上等待，请考虑增加自适应哈希索引分区的数量或禁用自适应哈希索引。

2、日志缓存区

日志缓存区是保存要写入磁盘上日志文件的数据的内存区域。日志缓存区大小由innodb_log_buffer_size变量定义。默认大小为 16MB。日志缓存区的内容会定期刷新到磁盘。大型日志缓存区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果您有更新、插入或删除许多行的事务，增加日志缓存区的大小可以节省磁盘 I/O。

该 innodb_flush_log_at_trx_commit 变量控制日志缓存区的内容如何写入和刷新到磁盘。该 innodb_flush_log_at_timeout 变量控制日志刷新频率。

1、innodb_flush_log_at_trx_commit

控制提交操作的严格ACID合规性与当与提交相关的 I/O 操作重新排列并批量完成时可能实现的更高性能之间的平衡。您可以通过更改默认值来获得更好的性能，但是您可能会在崩溃时丢失事务。

完全符合 ACID 需要默认设置 1。日志在每次事务提交时写入并刷新到磁盘。
设置为 0 时，每秒将日志写入并刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。
设置为 2 时，日志在每次事务提交后写入，并每秒刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。
对于设置0和2，不能100%保证每秒一次刷新。由于DDL更改和其他内部InnoDB活动导致日志刷新独立于innodb_flush_log_at_trx_commit设置，刷新可能会发生得更频繁，但有时由于调度问题，刷新频率会降低。如果日志每秒刷新一次，那么在崩溃中最多会丢失一秒钟的事务。如果日志刷新的频率大于或小于每秒一次，则可能丢失的事务量会相应变化。
日志存洗频率由innodb_flush_log_at_timeout控制，它允许你集日志存洗频率 N秒（其中 N是1 … 2700，为1的默认值）。但是，任何意外的mysqld进程退出都可以擦除长达N数秒的事务。
DDL 更改和其他内部InnoDB 活动独立于innodb_flush_log_at_trx_commit 设置刷新日志。
无论innodb_flush_log_at_trx_commit设置如何，InnoDB崩溃恢复都是有效的。事务要么完全应用，要么完全擦除

在使用InnoDB和事务的复制设置中，为了持久性和一致性:
如果启用了二进制日志记录，则设置 sync_binlog=1.
始终设置 innodb_flush_log_at_trx_commit=1。
许多操作系统和一些磁盘硬件欺骗了刷新到磁盘操作。他们可能会告诉mysqld刷新已经发生，即使它没有发生。在这种情况下，即使采用推荐的设置，也无法保证事务的持久性，在最坏的情况下，断电可能会损坏 InnoDB数据。在 SCSI 磁盘控制器或磁盘本身中使用电池供电的磁盘缓存可加快文件刷新，并使操作更安全。您还可以尝试禁用硬件缓存中磁盘写入的缓存。

2、innodb_flush_log_at_timeout

每N秒写入和刷新日志。Innodb_flush_log_at_timeout允许增加两次刷新之间的超时时间，以减少刷新并避免影响二进制日志组提交的性能。innodb_flush_log_at_timeout的默认设置是每秒一次。

name	value
Command-Line Format	–innodb-flush-log-at-timeout=#
System Variable	Innodb_flush_log_at_timeout
System Variable	Innodb_flush_log_at_timeout
Dynamic	yes
Type	Integer
Default Value	1
Minimum Value	1
Maximum Value	2700