MySQL:优化

  • 2015-07-06
  • 6
  • 0
  • 1

1、硬件优化


若数据量比较大且读写频繁的数据库,建议用物理机,云主机或者虚拟机的性能较弱。
  • CPU:2颗以上
  • 内存:64G以上
  • 硬盘:最好是SSD,其次SAS,RAID级别建议用RAID10
  • 网卡:多网卡绑定(修改网卡配置文件,加载bonding模块),千兆及以上
  • 文件系统:建议用XFS
内核参数优化:
修改vm.swappiness,一般设置10以内,尽量不要设置为0(CentOS7会导致内存溢出);
net.ipv4.tcp_tw_recyclenet.ipv4.tcp_tw_reuse都设置为1,减少TIME_WAIT,提高TCP效率。

2、配置文件优化


my.cnf参数优化效果小。
设置默认引擎为InnoDB,不建议用MyISAM引擎;
调整innodb_buffer_pool_size大小,若是单实例且绝大部分是InnoDB引擎表的话,考虑设置为物理内存的50%-70%;
innodb_file_per_table=1,使用独立表空间;
调整innodb_data_file_path=ibdata:1G:autoextend,不建议使用默认的10M,在高并发场景下,性能会有很大提升;
设置innodb_log_file_size=256M,设置innodb_log_files_in_group=2,基本可以满足大部分的应用场景;
调整max_connection(最大连接数)、max_connection_error(最大错误数)设置,根据业务量大小进行设置;
open_file_limitinnodb_open_filestable_open_cachetable_definition_cache可以设置为max_connection的10倍左右;
key_buffer_size建议调小,32M左右即可,建议关闭query chache
mp_table_sizemax_heap_table_size设置不要过大,sort_buffer_sizejoin_buffer_sizeread_buffer_sizeread_rnd_buffer_size等设置不要过大;
对于频繁变动的表来说,开启query cache会影响数据库的性能,因为如果对表进行INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等操作时,之前的缓存会无效并且删除。
通过show global status\G;来查看参数的使用情况,利用mysqlreport工具来进行优化,mysqlreport提供优化参考点和衡量标准,从而进行对应调整。

3、SQL优化


慢查询日志分析工具
1)索引优化;
a.找出慢SQL,配置my.conf,long_query_timelog-slow-queries参数;
b.mysql慢查询日志分析工具:mysqlsla,包括执行某条sql出现的次数及在slow log数据的百分比、执行时间等;
c.每天0点脚本定时分析慢查询,把分析结果发到相关人员的邮箱里;
运维给出优化建议–>开发确认并处理–>运维测试并上线
2)SQL语句拆分,大的复杂语句拆分成小的语句;
子查询、JOIN连表查询,若单表数据太大,则需要拆表拆库
3)数据库是存储数据的地方,不是计算数据的地方,一般是把计算的功能放到前端应用服务器上;

4、架构优化


1)数据库前端必须加cache,如redis/Memcached等;
2)某些业务应用使用nosql持久化存储,如redis;
3)动态数据静态化,文件和页面静态化。
4)数据库集群,读写分离(dbproxy或者php/JAVA程序实现),主从同步;
5)单表超过XX万,分库拆表。
分库方案:
a.将高安全性数据与低安全性数据分库;
b.基于业务逻辑拆分;
c.基于负载压力拆分;
拆表方案:
a.忙闲拆表,将更新频繁的字段独立拆出一张数据表;
b.等分拆表,负载均衡,但扩容和一些主键不容易处理;
c.递增拆表,每隔多少数据拆一张表,负载不均匀;
d.按日拆表,同c;

5、访问控制优化


1、更改客户端登录端口,例如phpadmin的默认端口;
2、数据库的web client部署在内网,不对外访问,限制IP及端口;
3、不做公网域名解析,用host的方式实现访问或内部IP访问;
4、根据不同人员分配不同权限的账户;
5、最好用堡垒机和VPN的方式访问数据库;

6、读写分离


通过SQL语句区分调取读库还是写库;

评论

还没有任何评论,你来说两句吧