如何仅通过配置降低MySQL查询执行速度？已调优参数无明显效果

I get that you're trying to intentionally slow down MySQL query performance purely through configuration tweaks, without resorting to removing indexes or writing inefficient queries—definitely a niche but interesting use case. Let's break down why your current settings aren't moving the needle, then dive into effective adjustments you can make.

Why Your Current Config Isn't Working #

Let's go through each parameter you modified and why they aren't having the impact you want:

• key_buffer = 0M: This controls the MyISAM key cache. If you're using InnoDB (the default engine for modern MySQL versions), this parameter is essentially irrelevant—changing it won't affect your query speed at all.
• max_allowed_packet = 1M: This limits the size of individual data packets. Unless your queries are returning massive datasets, this won't slow down query execution itself.
• thread_stack = 19K: Setting this this low is more likely to cause thread crashes than slow queries. MySQL needs a minimum stack size to operate, and going below that will lead to errors rather than intentional slowness.
• thread_cache_size = 2: A small thread cache increases overhead for thread creation/destruction, but this only has a noticeable impact under high query concurrency—not for single or low-volume queries.
• Disabling query cache: MySQL 8.0 completely removed the query cache, so these settings do nothing in newer versions. In older versions, disabling it just prevents result caching—it doesn't make the actual query execution slower.
• innodb_buffer_pool_size = 0M: InnoDB won't let you set this to 0. It automatically falls back to a minimum system-defined value (usually tens of MB), so your setting isn't actually taking effect.

Effective Configuration Tweaks to Slow Queries #

Below are targeted settings to force MySQL into slower execution, organized by engine:

For InnoDB (Most Common Engine)

• Slash the buffer pool size: Instead of setting it to 0, use an extremely small valid value like innodb_buffer_pool_size = 1M. The buffer pool is where InnoDB stores frequently accessed data and index pages. With this tiny size, almost every query will have to read directly from disk instead of cache—this is one of the most impactful ways to slow things down. Note: MySQL may adjust this to a minimum allowed value for your version, but it will still be drastically smaller than default.
• Disable read-ahead: Turn off InnoDB's ability to prefetch data pages with these settings:

  innodb_read_ahead_threshold = 0
  innodb_random_read_ahead = OFF
  

  Without prefetching, each data page has to be fetched on-demand, increasing disk I/O latency.
• Force immediate disk flushes: Ensure every write operation hits disk immediately with:

  innodb_flush_log_at_trx_commit = 1
  innodb_flush_method = O_DIRECT
  

  This bypasses the OS cache and forces synchronous disk writes, adding significant overhead to transactional queries.
• Limit concurrency: Restrict the number of threads MySQL can use to process queries:

  max_connections = 5
  innodb_thread_concurrency = 1
  

  This creates bottlenecks for concurrent queries, forcing them to wait in a queue.
• Sync binary logs on every commit: Enable sync_binlog = 1 to make MySQL write and sync the binary log to disk after every transaction commit, adding extra I/O overhead.

For MyISAM

• Minimize key cache size: Set key_buffer_size = 1M to starve MyISAM of memory for index caching—this will force frequent disk reads for index lookups.
• Reduce key cache block size: Use key_cache_block_size = 1024 (down from the default 16K) to limit how much data is cached per block, increasing disk access frequency.

Universal Settings (Works for Both Engines)

• Shrink sort/join buffers: Set these to extremely small values (test to avoid errors):

  sort_buffer_size = 1K
  join_buffer_size = 1K
  

  When queries need to sort results or perform joins, insufficient buffer space will force MySQL to use disk-based temporary tables, which are drastically slower than in-memory ones.
• Limit temporary table size: Force all temporary tables to use disk by setting:

  tmp_table_size = 1K
  max_heap_table_size = 1K
  

  Any query that requires a temporary table (like GROUP BY, ORDER BY) will now use disk instead of memory, adding significant latency.

Important Notes #

• After making these changes, restart MySQL for the settings to take effect.
• These configurations will cripple MySQL performance—only use them in a test environment, never in production.

内容的提问来源于stack exchange，提问作者Prakash Palnati