MySQL数据库优化

Mysql数据库优化是一项非常重要的工作,而且是一项长期的工作,MYSQL优化三分靠配置文件及硬件资源的优化,七分靠sql语句的优化。

Mysql数据库具体优化包括:配置文件的优化、sql语句的优化、表结构的优化、索引的优化,而配置的优化包括:系统内核优化、硬件资源、内存、CPU、mysql本身配置文件的优化。

硬件上的优化:增加内存和提高磁盘读写速度,都可以提高MySQL数据库的查询,更新的速度。另一种提高MySQL性能的方式是使用多块磁盘来存储数据。因为可以从多块磁盘上并行读取数据,这样可以提高读取数据的速度。

MySQL参数的优化:内存中会为MySQL保留部分的缓冲区,这些缓冲区可以提高MySQL的速度,缓冲区的大小可以在MySQL的配置文件中进行设置。

附企业级MYSQL百万量级真实环境配置文件my.cnf内容,可以根据实际情况修改:

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

user = mysql

server_id = 10

port = 3306

socket = /tmp/mysql.sock

datadir = /data/mysql/

old_passwords = 1

lower_case_table_names = 1

character-set-server = utf8

default-storage-engine = MYISAM

log-bin = bin.log

log-error = error.log

pid-file = mysql.pid

long_query_time = 2

slow_query_log

slow_query_log_file = slow.log

binlog_cache_size = 4M

binlog_format = mixed

max_binlog_cache_size = 16M

max_binlog_size = 1G

expire_logs_days = 30

ft_min_word_len = 4

back_log = 512

max_allowed_packet = 64M

max_connections = 4096

max_connect_errors = 100

join_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 2M

sort_buffer_size = 2M

query_cache_size = 64M

table_open_cache = 10000

thread_cache_size = 256

max_heap_table_size = 64M

tmp_table_size = 64M

thread_stack = 192K

thread_concurrency = 24

local-infile = 0

skip-show-database

skip-name-resolve

skip-external-locking

connect_timeout = 600

interactive_timeout = 600

wait_timeout = 600

#*** MyISAM

key_buffer_size = 512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 1G

myisam_repair_threads = 1

concurrent_insert = 2

myisam_recover

#*** INNODB

innodb_buffer_pool_size = 64G

innodb_additional_mem_pool_size = 32M

innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 2

innodb_lock_wait_timeout = 120

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_thread_concurrency = 16

innodb_open_files = 10000

#innodb_force_recovery = 4

#*** Replication Slave

read-only

#skip-slave-start

relay-log = relay.log

log-slave-updates