InnoDB 互斥锁 和 读写锁 通常仅在短时间内被占用。在多核系统上，线程在睡眠之前连续检查能否获得互斥锁或读写锁一段时间，可能更高效。如果在此期间互斥锁或读写锁变为可用，线程可以在同一个时间片立即继续。然而，多个线程对诸如互斥锁或读写锁的共享对象过于频繁地轮询，可能会导致 “缓存乒乓”，从而使处理器使对方缓存的部分内容失效。InnoDB 通过在轮询之间强制随机延迟来最小化这个问题，以使轮询活动不同步。随机延迟是通过自旋等待循环实现的。InnoDB mutexes and rw-locks are typically reserved for short intervals. On a multi-core system, it can be more efficient for a thread to continuously check if it can acquire a mutex or rw-lock for a period of time before it sleeps. If the mutex or rw-lock becomes available during this period, the thread can continue immediately, in the same time slice. However, too-frequent polling of a shared object such as a mutex or rw-lock by multiple threads can cause “cache ping pong”, which results in processors invalidating portions of each other's cache. InnoDB minimizes this issue by forcing a random delay between polls to desynchronize polling activity. The random delay is implemented as a spin-wait loop.

自旋等待循环的持续时间由循环中出现的 PAUSE 指令数量决定。该数量是通过随机选择一个从 0 到但不包括 innodb_spin_wait_delay 值之间的整数，然后将该值乘以 50 来生成的。例如，对于 innodb_spin_wait_delay 设置为 6 的情况，从以下范围随机选择一个整数：The duration of a spin-wait loop is determined by the number of PAUSE instructions that occur in the loop. That number is generated by randomly selecting an integer ranging from 0 up to but not including the innodb_spin_wait_delay value, and multiplying that value by 50. For example, an integer is randomly selected from the following range for an innodb_spin_wait_delay setting of 6:

{0,1,2,3,4,5}

所选整数乘以 50，得到六种可能的 PAUSE 指令值之一：The selected integer is multiplied by 50, resulting in one of six possible PAUSE instruction values:

{0,50,100,150,200,250}

对于该组值，250 是自旋等待循环中可能出现的 PAUSE 指令的最大数量。innodb_spin_wait_delay 设置为 5 将产生一组五个可能的值 {0,50,100,150,200}，其中 200 是 PAUSE 指令的最大数量，依此类推。通过这种方式，innodb_spin_wait_delay 设置控制自旋锁轮询之间的最大延迟。For that set of values, 250 is the maximum number of PAUSE instructions that can occur in a spin-wait loop. An innodb_spin_wait_delay setting of 5 results in a set of five possible values {0,50,100,150,200}, where 200 is the maximum number of PAUSE instructions, and so on. In this way, the innodb_spin_wait_delay setting controls the maximum delay between spin lock polls.

在所有处理器核心共享高速缓存内存的系统上，通过设置 innodb_spin_wait_delay=0，你可能会减少最大延迟或完全禁用繁忙循环。在具有多个处理器芯片的系统上，缓存失效的影响可能更大，因此你可能需要增加最大延迟。On a system where all processor cores share a fast cache memory, you might reduce the maximum delay or disable the busy loop altogether by setting innodb_spin_wait_delay=0. On a system with multiple processor chips, the effect of cache invalidation can be more significant and you might increase the maximum delay.

在 100MHz 的奔腾时代，innodb_spin_wait_delay 单位被校准为相当于一微秒。这个时间等值并不完全准确，但 PAUSE 指令的持续时间在处理器周期上相对于其他 CPU 指令保持相对恒定，直到 Skylake 系列处理器的引入，该系列的 PAUSE 指令相对较长。innodb_spin_wait_pause_multiplier 变量提供了一种方式来考虑 PAUSE 指令持续时间的差异。In the 100MHz Pentium era, an innodb_spin_wait_delay unit was calibrated to be equivalent to one microsecond. That time equivalence did not hold, but PAUSE instruction duration remained fairly constant in terms of processor cycles relative to other CPU instructions until the introduction of the Skylake generation of processors, which have a comparatively longer PAUSE instruction. The innodb_spin_wait_pause_multiplier variable provides a way to account for differences in PAUSE instruction duration.

innodb_spin_wait_pause_multiplier 变量控制 PAUSE 指令值的大小。例如，假设 innodb_spin_wait_delay 设置为 6，将 innodb_spin_wait_pause_multiplier 值从 50（默认值和之前的硬编码值）降低到 5，会生成一组较小的 PAUSE 指令值：The innodb_spin_wait_pause_multiplier variable controls the size of PAUSE instruction values. For example, assuming an innodb_spin_wait_delay setting of 6, decreasing the innodb_spin_wait_pause_multiplier value from 50 (the default and previously hardcoded value) to 5 generates a set of smaller PAUSE instruction values:

{0,5,10,15,20,25}

增加或减少 PAUSE 指令值的能力允许对 InnoDB 进行不同处理器架构的微调。例如，对于具有相对较长 PAUSE 指令的处理器架构，较小的 PAUSE 指令值会更合适。The ability to increase or decrease PAUSE instruction values permits fine tuning InnoDB for different processor architectures. Smaller PAUSE instruction values would be appropriate for processor architectures with a comparatively longer PAUSE instruction, for example.

innodb_spin_wait_delay 和 innodb_spin_wait_pause_multiplier 变量是动态的。它们可以在 MySQL 配置文件中指定，或在运行时使用 SET GLOBAL 语句修改。在运行时修改这些变量需要具备足够的权限来设置全局系统变量。参见 第 7.1.9.1 节，“系统变量权限”。The innodb_spin_wait_delay and innodb_spin_wait_pause_multiplier variables are dynamic. They can be specified in a MySQL option file or modified at runtime using a SET GLOBAL statement. Modifying the variables at runtime requires privileges sufficient to set global system variables. See Section 7.1.9.1, “System Variable Privileges”.