最近项目不太忙，研究了一下公司里DBA对数据库的一些优化的设置，在这里给大家分享一下心得，也可以帮助大家提高个人网站的速度和承载量！
想要对MySQL进行优化，需要在了解MySQL基本知识的同时进行长时间的观察统计并且根据经验进行判断，对各个参数进行合理的判断以及设置。如果对MySQL的一些底层基本知识不了解的话也可以关注我们后续对MySQL底层一些内容的分析。
MySQL的优化主要分为两个部分，一是服务器物理硬件的优化，二是MySQL自身(my.cnf)的优化。我们今天的重点是对my.cnf的优化。
一、服务器硬件对MySQL性能的影响
①磁盘寻道能力(磁盘I/O),以目前高转速SCSI硬盘(7200转/秒)为例，这种硬盘理论上每秒寻道7200次，这是物理特性决定的，没有办法改变。MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一，对于日均访问量在100万PV以上的Discuz!论坛，由于磁盘I/O的制约，MySQL的性能会非常低下！解决这一制约因素可以考虑以下几种解决方案：使用RAID-0+1磁盘阵列，注意不要尝试使用RAID-5，MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。
②CPU 对于MySQL应用，推荐使用S.M.P.架构的多路对称CPU，例如：可以使用两颗Intel Xeon 3.6GHz的CPU，现在我较推荐用4U的服务器来专门做数据库服务器，不仅仅是针对于mysql。
③物理内存对于一台使用MySQL的Database Server来说，服务器内存建议不要小于2GB，推荐使用4GB以上的物理内存，不过内存对于现在的服务器而言可以说是一个可忽略的问题，工作中遇到了高端服务器基本上内存都超过了16G。
二、my.cnf的一些影响性能的参数设置
由于my.cnf文件的优化设置是与服务器硬件配置息息相关的， 因而我们指定一个假想的服务器硬件环境：CPU: 2颗Intel Xeon 2.4GHz 内存: 4GB DDR 硬盘: SCSI 73GB(很常见的2U服务器 ) 。
接下来我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明：
[mysql]
default-character-set=utf8mb4
#mysqlde utf8字符集默认为3位，不支持emoji表情及部分不常见汉字，推荐使用utf8mb4
[mysqld]
skip-locking
#避免MySQL的外部锁定，减少出错几率增强稳定性。
skip-name-resolve
# 禁止MySQL对外部连接进行DNS解析，可以消除MySQL进行DNS解析的时间。但是所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
back_log = 512
# MySQL在短时间内可以拥有的连接数量。默认值为50，对于Linux系统推荐设置为小于512的整数，并且不能超过所使用的操作系统对到来的TCP/IP连接的侦听队列的大小。
max_connections = 1500
# MySQL的最大连接数，默认是100。
#调高此值可以增加并行连接数量但是需要注意连接数越多，内存开销越大。可以过'conn%'通配符查看当前状态的连接数量来决定该值的大小。
# 查看最大的连接数：SHOW VARIABLES LIKE "max_connections";
# 查看已使用的最大连接：SHOW GLOBAL STATUS LIKE 'max_used_connections';
innodb_buffer_pool_size = 128M
# InnoDB引擎用来保存索引和原始数据的缓冲池的大小, 默认值为128M。
该参数数值越大，在存取数据时所需要的磁盘I/O就越少。
# 建议在一个独立数据库服务器上设置这个变量到服务器物理内存大小的80%左右,内存大可适当增大比值。如果设置的过分大会导致系统的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。
query_cache_size = 0
# MySQL的查询的缓存区域的大小(MySQL从4.0.1开始提供了查询缓存机制)
最好是将其从一开始就停用，设置query_cache_size = 0(MySQL 5.6以后的默认值)并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存(比如memcache或redis)。这个内容比较多，我们会在以后专门的文章里面进行说明。
max_connect_errors = 6000
# 最大错误连接数。同一主机有超出该数值的中断错误连接则该主机会被禁止连接。
table_open_cache = 128
# MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值为64，如果设置过大，系统无法处理时就会出现客户端无法连接的情况。最好设置为并发连接数*每个连接所需的文件描述符数目。
max_allowed_packet = 4M
# 接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。仅当你发出长查询或必须返回大的结果行时MySQLd才会分配更多内存。之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。
binlog_cache_size = 1M
# 默认32K；是缓存区域大小，该区域记录的是事务未提交时产生的日志，事务需要提交时则把日志持久化到磁盘。
max_heap_table_size = 8M
# 定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。支持动态改变。
tmp_table_size = 16M
# MySQL的heap(堆积)表缓冲大小。调高该值可提高联接查询速度。
read_buffer_size = 2M
# MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。增加此值可以提高对表频繁的顺序扫描的效率。
read_rnd_buffer_size = 8M
# MySQL的随机读缓冲区大小。若需要排序大量数据，可适当调高该值。但由于MySQL会为每个客户连接发放该缓冲空间，所以设置过大时会增大内存开销。
sort_buffer_size = 8M
# MySQL执行排序时使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引，如果不能，可以尝试适当增加该变量的大小。
join_buffer_size = 8M
# 联合查询操作所能使用的缓冲区大小，和read_rnd_buffer_size一样，该参数对应的分配内存也是每连接独享
thread_cache_size = 8
# 默认8；表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中；当线程重新被请求时，请求将从缓存中读取；如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新线程则增加这个值可以改善系统性能。
query_cache_limit = 2M
#指定单个查询能够使用的缓冲区大小，默认1M
ft_min_word_len = 4
# 分词词汇最小长度，默认4
key_buffer_size = 64M
# 指定用于索引的缓冲区大小，默认值是16M。适度增加可以使索引更容易处理。但是该参数值设置的过大反而会使服务器整体效率降低。
通过命令show global status like 'key_read%';来查看索引请求次数和未命中索引的次数。
前者与后者的比值越大越好。
transaction_isolation = REPEATABLE-READ
# MySQL支持4种事务隔离级别，他们分别是：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.。MySQL默认采用的是REPEATABLE-READ
log_error = /data/mysql/mysql-error.log
#错误日志路径
skip-external-locking
#MySQL选项以避免外部锁定。默认开启
default-storage-engine = InnoDB
#默认存储引擎
innodb_open_files = 500
#默认300； 限制Innodb能打开的表的数据，若库里的表很多的时可增加这个。
innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4；这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启MySQL服务,允许值的范围从 1-64
innodb_thread_concurrency = 0
# 推荐设置为0(默认设置,表示不限制并发数)更好去发挥CPU多核处理能力，提高并发量
innodb_purge_threads = 1
# InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中，清除操作是主线程的一部分，这意味着运行时它可能会堵塞其它的数据库操作。
从MySQL5.5.X版本开始，该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置该参数来选择清除操作是否使用单独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1
innodb_flush_log_at_trx_commit = 2
# 0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，mysqld进程崩溃的时候，就会丢失最后1秒的事务,性能最高。
# 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。最安全，但性能最差，可以保证完整的ACID。
# 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值
innodb_log_buffer_size = 4M
# 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区越大性能越高，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间
innodb_log_file_size = 32M
# 此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间
innodb_log_files_in_group = 3
# 为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
innodb_max_dirty_pages_pct = 90
# innodb主线程刷新缓存池中的数据，使脏数据比例小于90%
bulk_insert_buffer_size = 8M
# 批量插入缓存大小， 这个参数是针对MyISAM存储引擎来说的。在一次性插入100-1000+条记录时可提高效率。默认值是8M。可以针对数据量的大小，翻倍增加。
myisam_sort_buffer_size = 8M
# MyISAM设置恢复表之时使用的缓冲区的大小，当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区
myisam_repair_threads = 1
# 该值大于1时在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内)
interactive_timeout = 28800
# 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒(8小时)
[mysqldump]
max_allowed_packet = 16M
#服务器发送和接受的最大包长度
