mysql数据库的性能优化

cheng029

最近公司系统上线后，时不时的tomcat挂掉，经过多方面的查找，最后发现系统进行大批量的操作时，访问mysql数据库的数据超慢。所以开始着手对系统和mysql数据库进行优化。mysql使用的是5.1的，以下是仅对mysql优化的内容，主要是修改my.ini文件的配置，配置修改如下：

#数据库最大连接数，根据实际需要设置.
#之前有过经验，connections超过370的时候，线程的栈空间被占满，mysqld无法工作，所以限制为200
max_connections=200
#为缓存查询结果分配的内存的数量，加快查询的速度。默认值是0，即禁用查询缓存。
#可以参考innodb_buffer_pool_size的25 - 50 %进行设置，但不要太大
query_cache_size = 256M
#所有线程打开的表的数目(一个表使用2个文件描述符)，表数量多，就要大一些
table_cache=512
#联合查询或者view的时候，可能会建立临时表，如果在磁盘上建的话，会导致性能低，设置此值可以一定程度上加快速度
tmp_table_size = 64M
#服务器应缓存多少线程以便重新使用。当客户端断开连接时，如果线程少于thread_cache_size，则客户端的线程被放入缓存。
thread_cache_size=16
#每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP BY操作
sort_buffer_size=2M
myisam_sort_buffer_size = 64M
#索引缓冲区(仅作用于MYISAM表和临时表)，决定了数据库索引处理的速度
key_buffer_size=256M
#每个线程连续扫描时为扫描的每个表分配的缓冲区的大小,如果进行多次连续扫描，可能需要增加该值
read_buffer_size=2M
#当排序后按排序后的顺序读取行时，则通过该缓冲区读取行，避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是，这是为每个客户端分配的缓冲区，因此你不应将全局变量设置为较大的值。相反，只为需要运行大查询的客户端更改会话变量。
read_rnd_buffer_size=8M
#主要用来存储表结构和数据字典，表越多要求内存就越大
innodb_additional_mem_pool_size=20M
#1表示每次事务结束都写日志并刷新磁盘；2表示每次事务写日志但不刷新磁盘(每秒刷新)；0(默认值)表示每秒写日志并刷新磁盘。0表示最多丢失1秒的数据，但性能最好。
innodb_flush_log_at_trx_commit=0
#InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。因此，如果有大的事务，设置大的日志缓冲可以减少磁盘I/O。
innodb_log_buffer_size=8M
#数据库专用服务器时可以设置内存的50 - 80 %，32位系统最大不要超过2-3.5G，如果mysql无法启动，请尝试减小该值为512M
innodb_buffer_pool_size=1G
#日志文件大小设置。增大该值可减少刷新日志频率，但会延长mysql启动和灾难恢复的时间。一般设置innodb_buffer_pool_size的25%
#先删除C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data里面的log文件才可以修改
innodb_log_file_size = 256M
#并发线程。2*(内核数量+磁盘数量)
innodb_thread_concurrency = 4

#通信缓冲区的最大长度。包或任何生成的/中间字符串的最大大小。
max_allowed_packet = 8M
#在查询之间将通信缓冲区重设为该值。如果语句超出该长度，缓冲区自动扩大，直到max_allowed_packet字节。
net_buffer_length = 8K
#将为两个表之间的每个完全联接分配联接缓冲区。对于多个表之间不使用索引的复杂联接，需要多联接缓冲区。
join_buffer_size = 8M
#不要缓存大于该值的结果，默认值是1MB。
query_cache_limit = 2M
#InnoDB行锁导致的死锁等待时间(默认值是50S)
innodb_lock_wait_timeout = 50
#连接进程帧听队列的大小
back_log = 1024
#记录慢查询SQL，0：OFF，1：ON
slow_query_log=1
#慢查询日志记录方式 FILE,TABLE or TABLE or NONE
log_output=FILE
slow_query_log_file=mysql_slow.log
#慢查询时间设置
long_query_time=2
#是否支持分布式事务，关闭以提高性能(默认是ON),0：OFF，1：ON
innodb_support_xa=0
#适合于高并发读写，文件连续性，设置为2会出现碎片，设置为1不会出现碎片
concurrent_insert=2
#下面的接近或超过于 2G 将会很危险：
#innodb_buffer_pool_size + key_buffer_size + max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size) + max_connections * 2 MB

  对my.ini文件中存在的配置进行修改，没有的需要在添加上。所有的配置修改好后，重新启动mysql服务时，需要注意的是，要把data文件夹下的文件删除（文件夹的不要动），这些主要都是log文件，还有，特别要当心的是不能把ibdate1删除掉，它是存储数据的，一旦删除，只能看到表名，存储的数据都会丢失。
   以上是对mysql的性能优化的配置，仅作参考，如有纰漏还请指正，谢谢！

zj2092

不错不错，楼主您辛苦了。。。

yl197837

男人靠的住，母猪能上树!

sonyet

如果有一双眼睛陪我一同哭泣，就值得我为生命受苦。

suncool

走自己的路，让别人打车去吧。

yanfangsheng123

我身在江湖，江湖里却没有我得传说。

q789321

这是什么东东啊