MySQL数据库优化总结

落日映苍穹つ 2024-03-25 14:13 65阅读 0赞

主要是一下几个方面：

### 减少数据库访问 ###

对于可以静态化的页面，尽可能静态化  
对一个动态页面中可以静态的局部，采用静态化  
部分数据可以生成XML，或者文本文件形式保存  
使用数据缓存技术，例如： MemCached

### 优化的检测方法 ###

1.  用户体验检测
2.  Mysql状态检测  
    在Mysql命令行里面使用show status命令，得到当前mysql状态。  
    主要关注下列属性：  
    key\_read\_requests (索引读的请求数）(key\_buffer\_size设置影响)  
    key\_reads（索引读响应数)  
    Key\_blocks\_used  
    Qcache\_\*  
    Open\_tables(通过table\_cache的设置影响)  
    Opened\_tables  
    table\_locks
3.  第三方工具检测  
    系统及Mysql的Log  
    系统命令： top, sar  
    Mysql的Log: slow\_query.log

### 硬件方面的优化 ###

硬件方面，最容易成为Mysql瓶颈的部分是磁盘，其次是CPU和内存  
磁盘方面  
使用更快的磁盘，会对Mysql有很好的帮助  
使用更多的硬盘，通过Raid，可以提高单块磁盘速度的问题  
对于Raid方式，建议采用Raid 0+1 或者 Raid 1+0  
CPU  
毫无疑问，更高主频的CPU和更多的CPU数量可以给Mysql更  
高的性能  
内存  
更高的内存，往往可以让Mysql中的更多的数据缓存在内存中，  
但是，一个重要的因素是，需要有正确的Mysql的配置  
网卡  
使用千兆网卡及千兆网络

### 操作系统方面的优化 ###

1.  不使用交换区。如果内存不足，增加更多的内存或配置你的系统使用较少内存
2.  不要使用NFS磁盘
3.  增加系统和MySQL服务器的打开文件数量使用ulimit –n 65535
4.  增加系统的进程和线程数量。
5.  关闭不必要的应用，优化硬盘参数，使用hdparm测试

### 应用级的优化 ###

1.  使用多服务器负载均衡(多台读和写,用复制技术进行数据同步)
2.  表的分区 (自定义分区,mysql5.1开始支持自带分区功能)
3.  使用数据缓存技术memcached

### Mysql配置的优化 ###

1.  key\_buffer(=512):索引缓冲使用的内存数量  
    这对MyISAM表来说非常重要,设定在可用内存的25%-30%较好,通过检查状态值 Key\_read\_requests和 Key\_reads,可以知道key\_buffer设置是否合理。比例key\_reads / key\_read\_requests应该尽可能的低，至少是1:100，1:1000更好 ,否则说明 key\_buffer 设置有点偏小
2.  innodb\_buffer\_pool\_size(= 512):索引缓冲使用的内存数量
3.  table\_cache (=1024):数据表缓存区的尺寸每当 MySQL 访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查运行峰值时间的 Open\_tables 和 Opened\_tables 状态值，可以决定是否需要调整 table\_cache 的值。如果你发现 open\_tables 的值等于 table\_cache，并且发现 opened\_tables 状态值在不断增长，那么你就需要增加table\_cache 参数值了,  
    也不能盲目地把 table\_cache 参数设置成很大的值，如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
4.  sort\_buffer\_size (=256):指定排序用缓冲区的长度.该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M
5.  join\_buffer\_size :关联查询用缓冲区的长度4G内存以上，建议大于32M,该参数对应的分配内存也是每连接独享！
6.  max\_connections (=1024):可以复用的线程数量  
    允许同时连接MySQL服务器的客户数量 ,可以观察和估计系统在峰值最大的并发连接数来设置
7.  thread\_cache(=\*):可以复用的线程数量一般设置为CPU数×2
8.  innodb\_buffer\_pool\_size(= 512):innodb表缓存池大小  
    这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key\_buffer\_size 设置下运行的可以，然而Innodb在默认的innodb\_buffer\_pool\_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key\_buffer 的规则有 – 如果你的数据量不大，并且不会暴增，那么无需把innodb\_buffer\_pool\_size 设置的太大了.
9.  innodb\_flush\_logs\_at\_trx\_commit(=1) :事务提交后的日志刷新模式  
    是否为Innodb比MyISAM慢1000倍而头大?看来也许你忘了修改这个参数了。默认值是 1，这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。很多应用程序，尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置为0就快很多了，不过也相对不安全了,MySQL服务器崩溃时就会丢失一些事务。设置为2指挥丢失刷新到操作系统缓存的那部分事务.