MySQL数据库优化

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

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

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

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

mysql

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

[client]#[client]和[mysql]都是客户端port = 3306#port参数表示的是MySQL数据库的端口，默认的端口是3306socket = /tmp/mysql.sock#客户端连接本地mysql时使用sock文件，通信文件[mysqld]#主配置user = mysql#运行时用户server_id = 10#主从区分IDport = 3306#守护进程监听端口socket = /tmp/mysql.sock#本地mysql.sock文件datadir = /data/mysql/mysql数据目录old_passwords = 1#当服务器生成长密码哈希值时，允许你维持同4.1之前的客户端的向后兼容性。lower_case_table_names = 1#lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写#lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的#lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的character-set-server = utf8#服务器字符集，默认情况下所采用的。default-storage-engine = INNODB#默认mysql引擎default_tmp_storage_engine#表示临时表的默认存储引擎。log-bin = bin.log#开启binlog日志log-error = error.log#开启错误日志pid-file = mysql.pid#记录的是当前 mysqld 进程的 pid，pid 亦即 Process ID。long_query_time = 2#慢查询超时时间，默认为10s，MYSQL5.5以上可以设置微秒；slow_query_log#关闭慢查询日志slow_query_log_file = slow.log#慢查询日志文件binlog_cache_size = 4M#服务器配置了 log-bin，为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。binlog_format = mixed#二进制日志 (binlog) 有 3 种不同的格式可选：Mixed,Statement,Rowmax_binlog_cache_size = 16M#表示的是binlog 能够使用的最大cache 内存大小max_binlog_size = 1G#如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。expire_logs_days = 30#主要用来控制binlog日志文件保留时间，超过保留时间的binlog日志会被自动删除。单位是天ft_min_word_len = 1#设置mysql最小索引长度是4back_log = 512#在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。max_allowed_packet = 64M# 值设置过小将导致单个记录超过限制后写入数据库失败，且后续记录写入也将失败，为了数据完整性，需要考虑到事务因素。max_connections = 4096#mysql的最大连接数max_connect_errors = 100#一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系。join_buffer_size = 2M# 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享read_buffer_size = 2M# MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。read_rnd_buffer_size = 2M# MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，sort_buffer_size = 2M# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。query_cache_size = 64M#（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。table_open_cache = 10000# open_tables 表示打开表的数量，opened_tables表示打开过的表数量，如果opened_tables数量过大，说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小thread_cache_size = 256#当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数：max_heap_table_size = 64M#为了防止意外创建一个超大的内存表导致永尽所有的内存资源.tmp_table_size = 64M# #临时HEAP数据表的最大长度thread_stack = 192K#每个连接线程被创建时，MySQL给它分配的内存大小。thread_concurrency = 24local-infile = 0skip-show-databaseskip-name-resolveskip-external-lockingconnect_timeout = 600interactive_timeout = 600wait_timeout = 600#*** MyISAMkey_buffer_size = 512M#Key Buffer大小，用于缓存MyISAM表的索引块。决定数据库索引处理的速度（尤其是索引读）（1G 内存——>256M）bulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 64Mmyisam_max_sort_file_size = 1G# mysql重建索引时允许使用的临时文件最大大小myisam_repair_threads = 1concurrent_insert = 2myisam_recover#*** INNODBinnodb_buffer_pool_size = 64G#动态分配资源innodb_additional_mem_pool_size = 32Minnodb_data_file_path = ibdata1:1G；ibdata2:1G:autoextendinnodb_read_io_threads = 8#控制后台线程处理数据页上的写请求， 默认是4，不支持动态修改，建议根据服务器的核数以及读写请求 的比例加以调整。innodb_write_io_threads = 8#该参数值之和=2*cpu个数*cpu核数;如果你的系统读>写,可以设置innodb_read_io_threads值相对大点;反之，也可以.innodb_file_per_table = 1innodb_flush_log_at_trx_commit = 2#如果是游戏服务器，建议此值设置为2；如果是对数据安全要求极高的应用，建议设置为1；设置为0性能最高，但如果发生故障，数据可能会有丢失的危险！ 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存 （Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系 统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失 事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。innodb_lock_wait_timeout = 120#是innodb等待行锁直到放弃的秒数innodb_log_buffer_size = 8M#这个参数就是用来设置 Innodb 的 Log Buffer 大小的，系统默认值为 1MB。Log Buffer 的主要作用就是缓冲 Log 数据，提高写 Log 的 IO 性能。一般来说，如果你的系统不是写负载非常高且以 大事务居多的话，8MB 以内的大小就完全足够了。innodb_log_file_size = 256M#在一个日志组中每一个日志文件大小。日志文件合并后的大小（(innodb_log_file_size *innodb_log_files_in_group）可以到达512G。默认是48M。值明智范围从1MB到缓冲池的大小的1/n，N是日志文件组中文件数。值越大，在缓冲池执行checkpoint 刷新活动次数越小，节省磁盘I/O。innodb_log_files_in_group = 3# #在日志组的日志文件数。innodb_max_dirty_pages_pct = 90#关闭数据库之前，可以手工调整@@global.innodb_max_dirty_pages_pct为比较小的数值，然后等待dity pages变少，然后restart，可以减少启动要恢复的时间。innodb_thread_concurrency = 16#线程已满时，Slave 端复制线程的延迟时间（ms), 默认为0， 不延迟，可动态修改。innodb_open_files = 10000#innodb_force_recovery = 4#*** Replication Slaveread-only#1是只读，0是读写, mysql设置为只读后，无法增删改。#skip-slave-start#slave复制进程不随mysql启动而启动relay-log = relay.log中继日志，就像二进制日志一样，由一组编号的文件组成，其中包含描述数据库更改的事件，以及包含所有使用的中继日志文件名称的索引文件。log-slave-updates#该参数就是为了让从库从主库复制数据时可以写入到binlog日志#是一个全局非动态选项，其值为布尔型，即TRUE和FALSE。缺省为FALSE，修改该参数需要重启实例。#从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。

分享完毕，谢谢支持