InnoDB 使用操作系统的线程来处理来自用户事务的请求。（事务在提交或回滚之前可能会向InnoDB发出许多请求。）在支持多核处理器的现代操作系统和服务器上，如果上下文切换效率高，大多数工作负载在没有限制并发线程数量的情况下运行良好。InnoDB uses operating system threads to process requests from user transactions. (Transactions may issue many requests to InnoDB before they commit or roll back.) On modern operating systems and servers with multi-core processors, where context switching is efficient, most workloads run well without any limit on the number of concurrent threads.

在有助于最小化线程之间上下文切换的情况下，InnoDB 可以使用多种技术来限制同时执行的操作系统线程数量（从而限制任何时候处理的请求数量）。当 InnoDB 收到来自用户会话的新请求时，如果当前执行的线程数量已达到预设限制，新请求会先休眠一小段时间，然后再尝试执行。等待锁的线程不计入同时执行的线程数量。In situations where it is helpful to minimize context switching between threads, InnoDB can use a number of techniques to limit the number of concurrently executing operating system threads (and thus the number of requests that are processed at any one time). When InnoDB receives a new request from a user session, if the number of threads concurrently executing is at a pre-defined limit, the new request sleeps for a short time before it tries again. Threads waiting for locks are not counted in the number of concurrently executing threads.

您可以通过设置配置参数 innodb_thread_concurrency 来限制并发线程的数量。一旦正在执行的线程数量达到此限制，额外的线程将在被放入队列之前，根据配置参数 innodb_thread_sleep_delay 设置的微秒数进行休眠。You can limit the number of concurrent threads by setting the configuration parameter innodb_thread_concurrency. Once the number of executing threads reaches this limit, additional threads sleep for a number of microseconds, set by the configuration parameter innodb_thread_sleep_delay, before being placed into the queue.

您可以将配置选项 innodb_adaptive_max_sleep_delay 设置为您允许的 innodb_thread_sleep_delay 的最高值，并且 InnoDB 会根据当前的线程调度活动自动向上或向下调整 innodb_thread_sleep_delay。这种动态调整有助于线程调度机制在系统负载较轻以及接近满负荷运行时都能顺畅工作。You can set the configuration option innodb_adaptive_max_sleep_delay to the highest value you would allow for innodb_thread_sleep_delay, and InnoDB automatically adjusts innodb_thread_sleep_delay up or down depending on the current thread-scheduling activity. This dynamic adjustment helps the thread scheduling mechanism to work smoothly during times when the system is lightly loaded and when it is operating near full capacity.

在 MySQL 的各个版本以及 InnoDB 中，innodb_thread_concurrency 的默认值以及隐含的并发线程数限制已经发生了变化。innodb_thread_concurrency 的默认值为 0，因此默认情况下并发执行线程的数量不受限制。The default value for innodb_thread_concurrency and the implied default limit on the number of concurrent threads has been changed in various releases of MySQL and InnoDB. The default value of innodb_thread_concurrency is 0, so that by default there is no limit on the number of concurrently executing threads.

InnoDB 仅在并发线程数量受限时才会使线程进入休眠状态。当线程数量没有限制时，所有线程都会平等竞争调度。也就是说，如果 innodb_thread_concurrency 为 0，则会忽略 innodb_thread_sleep_delay 的值。InnoDB causes threads to sleep only when the number of concurrent threads is limited. When there is no limit on the number of threads, all contend equally to be scheduled. That is, if innodb_thread_concurrency is 0, the value of innodb_thread_sleep_delay is ignored.

当线程数量有限制时（当innodb_thread_concurrency大于 0 时），InnoDB通过允许在执行单个 SQL 语句期间发出的多个请求进入而无需遵守由设置的限制，从而减少上下文切换开销。InnoDB由于一个 SQL 语句（例如连接）可能在innodb_thread_concurrency中包含多个行操作，它会分配指定数量的InnoDB, InnoDB票“允许线程以最小的开销重复调度。”您无法在索引级别修改When there is a limit on the number of threads (when innodb_thread_concurrency is > 0), InnoDB reduces context switching overhead by permitting multiple requests made during the execution of a single SQL statement to enter InnoDB without observing the limit set by innodb_thread_concurrency. Since an SQL statement (such as a join) may comprise multiple row operations within InnoDB, InnoDB assigns a specified number of “tickets” that allow a thread to be scheduled repeatedly with minimal overhead.

当一个新的 SQL 语句开始时，一个线程没有票，它必须遵守 innodb_thread_concurrency。一旦线程有权进入 InnoDB，它会被分配一些票，这些票可用于随后进入 InnoDB 执行行操作。如果票用完了，线程将被驱逐，并且再次遵守 innodb_thread_concurrency，这可能会将线程重新放回等待线程的先进先出队列。当线程再次有权进入 InnoDB 时，票会再次分配。分配的票数由全局选项 innodb_concurrency_tickets 指定，默认值为 5000。等待锁的线程在锁可用时会被分配一张票。When a new SQL statement starts, a thread has no tickets, and it must observe innodb_thread_concurrency. Once the thread is entitled to enter InnoDB, it is assigned a number of tickets that it can use for subsequently entering InnoDB to perform row operations. If the tickets run out, the thread is evicted, and innodb_thread_concurrency is observed again which may place the thread back into the first-in/first-out queue of waiting threads. When the thread is once again entitled to enter InnoDB, tickets are assigned again. The number of tickets assigned is specified by the global option innodb_concurrency_tickets, which is 5000 by default. A thread that is waiting for a lock is given one ticket once the lock becomes available.

这些变量的正确值取决于您的环境和工作负载。尝试一系列不同的值以确定哪一个值适合您的应用程序。在限制同时执行线程的数量之前，请先查看可能提高InnoDB在多核和多处理器计算机上性能的配置选项，例如innodb_adaptive_hash_index。The correct values of these variables depend on your environment and workload. Try a range of different values to determine what value works for your applications. Before limiting the number of concurrently executing threads, review configuration options that may improve the performance of InnoDB on multi-core and multi-processor computers, such as innodb_adaptive_hash_index.

有关 MySQL 线程处理的一般性能信息，请参阅第 7.1.12.1 节，“连接接口”。For general performance information about MySQL thread handling, see Section 7.1.12.1, “Connection Interfaces”.