相关参数
read_buffer_size
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_buffer_size
If you do many sequential scans, you might want to increase this value, which defaults to 131072.
The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.
This option is also used in the following context for all storage engines:
For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
For bulk insert into partitions.
For caching results of nested queries.
read_buffer_size is also used in one other storage engine-specific way: to determine the memory block size for MEMORY tables.
For more information about memory use during different operations, see Section 8.12.3.1, “How MySQL Uses Memory”.
read_rnd_buffer_size
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_rnd_buffer_size
This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization.
When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. See Section 8.2.1.16, “ORDER BY Optimization”. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.
Multi-Range Read：减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，详细说明请参见
https://www.cnblogs.com/olinux/p/5146868.html
sort_buffer_size
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sort_buffer_size
If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing.
The optimizer tries to work out how much space is needed but can allocate more, up to the limit. Setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload. See Section B.4.3.5, “Where MySQL Stores Temporary Files”.
其他参考
https://www.cnblogs.com/wy123/p/7744171.html
mysql配置文件主要参数
https://www.jb51.net/article/48082.htm