SSD硬盘下MySQL5.6优化，性能提升入10倍_mysql

笔记本性能参数如下：

CPU：i7

内存：16G

硬盘：SSD

优化前（默认安装），批量执行1万笔insert耗时11秒，优化后，批量执行1万笔insert耗时1.03秒

测试534万笔insert耗时11分4秒340毫秒，优化后的参数如下，仅供参考。

[client]

no-beep

port=3306

[mysql]

default-character-set=utf8

port=3306

datadir=C:/ProgramData/MySQL/MySQL Server 5.6/Data

character-set-server=utf8

default-storage-engine=INNODB

sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

log-output=FILE

general-log=0

general_log_file=”LIDINGBO-PC.log”

slow-query-log=1

slow_query_log_file=”LIDINGBO-PC-slow.log”

long_query_time=10

log-error=”LIDINGBO-PC.err”

server-id=1

max_connections=151

query_cache_size=128M

table_open_cache=128

tmp_table_size=512M

thread_cache_size=1024

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=213M

key_buffer_size=1024M

read_buffer_size=8M

read_rnd_buffer_size=8M

sort_buffer_size=256K

innodb_additional_mem_pool_size=16M

innodb_flush_log_at_trx_commit=2

innodb_log_buffer_size=8M

innodb_buffer_pool_size=712M

innodb_log_file_size=48M

innodb_thread_concurrency=17

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=1024

flush_time=0

join_buffer_size=1024M

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=4161

query_cache_type=1

sort_buffer_size=8M

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

innodb_buffer_pool_size=1G

innodb_log_file_size=512M

innodb_io_capacity=2000

innodb_max_dirty_pages_pct=40

innodb_adaptive_flushing=ON

innodb_write_io_threads=4

innodb_read_io_threads=4

innodb_flush_neighbors=0

innodb_file_io_threads=4

innodb_io_capacity_max=6000

innodb_lru_scan_depth=2000

innodb_autoinc_lock_mode=2

thread_stack=256k

max_heap_table_size=256M

binlog_cache_size=2M