面试题：MySQL中MyISAM键缓存配置的关键参数有哪些

Question

请详细阐述MySQL里MyISAM存储引擎键缓存配置涉及的关键参数，比如它们的作用以及如何影响数据库性能，并且说明在不同业务场景下，这些参数应如何调整。

Answer 1

1. 关键参数及作用

1. key_buffer_size
   • 作用：指定MyISAM存储引擎用于缓存索引块的内存总量。这是MyISAM存储引擎性能调优中最重要的参数之一。MyISAM使用该缓存来加速索引的读取和写入操作。当查询执行时，如果所需的索引页在该缓存中，就可以直接从内存读取，而无需从磁盘读取，大大提高查询速度。
   • 对性能影响：过小的key_buffer_size可能导致频繁的磁盘I/O，因为索引数据不能有效地缓存，每次查询可能都需要从磁盘读取索引页，从而降低查询性能。相反，过大的key_buffer_size可能会占用过多的系统内存，导致其他进程可用内存不足，影响整个系统的性能。
2. myisam_max_sort_file_size
   • 作用：定义MyISAM在执行排序操作（例如创建索引、ALTER TABLE等操作时涉及的排序）时，能够使用的临时文件的最大大小。如果排序所需的临时文件大小超过此值，MySQL会采用更复杂但可能更慢的排序算法。
   • 对性能影响：如果该值设置过小，在进行大型索引创建或表结构修改等涉及排序的操作时，可能会触发较慢的排序算法，导致操作时间显著增加。而设置过大，则可能在磁盘空间不足时引发问题，并且如果实际不需要这么大的临时文件空间，也会造成资源浪费。
3. myisam_sort_buffer_size
   • 作用：用于MyISAM存储引擎在进行排序操作时分配的缓冲区大小。例如在创建索引或对MyISAM表进行ORDER BY操作时会用到。更大的排序缓冲区可以容纳更多的数据进行排序，减少磁盘I/O的次数。
   • 对性能影响：较小的myisam_sort_buffer_size可能导致排序操作需要多次从磁盘读取数据，增加I/O负担，降低排序速度。而过大的设置会占用过多内存，特别是在有多个并发排序操作时，可能导致系统内存不足。
4. key_cache_age_threshold
   • 作用：确定一个索引块在被标记为“old”之前，在键缓存中停留的时间（以秒为单位）。当一个索引块被访问时，它会被移动到键缓存的“young”区域。如果在key_cache_age_threshold时间内没有再次被访问，它将被移动到“old”区域。
   • 对性能影响：较小的值会使索引块更快地进入“old”区域，可能导致经常使用的索引块过早被淘汰出缓存，增加磁盘I/O。较大的值则可能使一些很少使用的索引块长时间占用缓存空间，影响缓存的有效利用率。
5. key_cache_division_limit
   • 作用：决定键缓存中“young”区域和“old”区域的划分比例。它表示“young”区域在整个键缓存中所占的百分比。例如，若设置为 100，则整个键缓存都是“young”区域；若设置为 0，则整个键缓存都是“old”区域。
   • 对性能影响：不合适的比例设置可能导致缓存利用率低下。如果“young”区域设置过小，频繁访问的索引块可能没有足够空间缓存，增加磁盘I/O。如果“young”区域设置过大，可能会使一些很少使用的索引块长时间占用“young”区域，浪费缓存空间。

2. 不同业务场景下的参数调整

1. 读密集型业务场景
   • key_buffer_size：应尽量增大，以确保尽可能多的索引数据可以被缓存。例如，如果服务器有足够的内存，并且MyISAM表的索引大小已知，可以将key_buffer_size设置为接近或略小于可用内存减去操作系统和其他必要进程所需内存的值，以充分利用内存加速索引读取。
   • myisam_max_sort_file_size：由于读操作通常不涉及大规模排序操作，保持默认值或适当增大即可，以应对偶尔的索引重建等操作。
   • myisam_sort_buffer_size：可适当调大，但要注意不要过度占用内存，因为读密集型场景下主要资源应优先保证索引缓存。例如，可以根据系统内存情况，将其设置为几百MB到1GB左右。
   • key_cache_age_threshold：可适当增大，使经常被读取的索引块能在“young”区域停留更长时间，减少磁盘I/O。例如可以设置为3600秒（1小时）。
   • key_cache_division_limit：可适当增大“young”区域的比例，比如设置为80 - 90，以更好地缓存频繁读取的索引块。
2. 写密集型业务场景
   • key_buffer_size：由于写操作会频繁更新索引，需要为索引缓存提供足够空间，但不能像读密集型场景那样设置得过大，因为写操作也需要内存用于其他操作（如排序、临时数据存储等）。可以根据索引大小和系统内存情况，设置为可用内存的30% - 50%。
   • myisam_max_sort_file_size：应适当增大，因为写操作（如插入大量数据后可能需要重建索引）可能涉及大规模排序。例如可以设置为几GB，具体取决于磁盘空间和系统内存。
   • myisam_sort_buffer_size：要适当增大，以提高写操作中涉及的排序效率。可根据系统内存情况，设置为1GB - 2GB左右。
   • key_cache_age_threshold：适当减小，因为写操作频繁更新索引，可能使旧的索引块很快不再被使用，应尽快将其从缓存中淘汰。例如可以设置为60 - 120秒。
   • key_cache_division_limit：适当减小“young”区域的比例，比如设置为50 - 70，因为写操作可能会频繁引入新的索引块，需要给新的索引块更多机会进入缓存。
3. 混合读写业务场景
   • key_buffer_size：需要平衡读写需求，根据读/写操作的比例来调整。如果读操作略多于写操作，可以将key_buffer_size设置为可用内存的50% - 70%；如果写操作略多于读操作，则设置为30% - 50%。
   • myisam_max_sort_file_size：和写密集型场景类似，适当增大以应对可能的排序操作。
   • myisam_sort_buffer_size：根据读写比例调整，若读多写少，设置相对较小（如几百MB）；若写多读少，设置相对较大（如1GB - 2GB）。
   • key_cache_age_threshold：设置一个适中的值，如300 - 600秒，平衡新旧索引块在缓存中的停留时间。
   • key_cache_division_limit：设置“young”区域比例为60 - 80，兼顾频繁读写的索引块的缓存需求。