8.0的配置文件my.ini在ProgramData里面。
主要是这两参数,一定要改:
innodb_buffer_pool_size=4G
innodb_log_file_size=1G
# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
# indexes and thus perform full table scans.default 512K.影响join操作
join_buffer_size=16M
# 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. default 256K.影响排序操作
sort_buffer_size=2M
# The size in bytes of the buffer pool, the memory area where InnoDB caches table
# and index data. The default value is 134217728 bytes (128MB). The maximum value
# depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit
# systems and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems,
# the CPU architecture and operating system may impose a lower practical maximum
# size than the stated maximum. When the size of the buffer pool is greater than
# 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve
# the scalability on a busy server. default 128M.影响很多,服务器卡,主要是这个原因
innodb_buffer_pool_size=4G
# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process. default 48M.影响写操作,越大写操作越快
innodb_log_file_size=1G
#日志存储类型,可选FILE和TABLE,存储在mysql.general_log
log-output=TABLE
#打开日志
general-log=1
#慢查询超时时间 慢查询明细在mysql.slow_log
long_query_time=3