MySQL优化
MySQL安全优化
- 确保MySQL运行用户为一般用户;
- 修改默认端口3306,改为其它的一些端口,端口不要暴露在外网;
- 开启MySQL binlog二进制日志,在误删除数据的情况下,可以通过binlog二进制日志恢复到某个时间点;查看binlog状态的命令:show variables like ‘%log_bin%’;
用户权限方面↓↓↓ - 删除匿名账号和空口令账号;
- MySQL账号密码最好每三个月更换一次;
- Root权限太高,为了安全,禁止root账户远程访问,root账户只允许从本地访问;
- 赋予本地localhost root账户最高权限,方便以后维护使用;
- 除root账户外,其它账户尽量也不要放开远程访问,如果有这方面需求的话,要严格控制权限,根据业务需要,配置其需要的最小权限。如果不需要,应禁止远程访问。禁止网络连接,防止猜解密码攻击、溢出攻击、和嗅探攻击。
- 研发数据库配置读取规范,A所有开发人员都不能知道正式服数据库连接信息,包括数据库链接地址,用户名和密码;B开发人员读取数据库连接信息的时候,只能从配置文件中读取(配置文件测试环境一份,正式环境一份,在代码部署上线时,将正式环境的覆盖测试环境的);C配置文件可规定为某一种格式,比如json格式。
最小化原则
遵循最小化原则↓↓↓
安装系统最小化;开启程序最小化;登录最小化;权限最小化。
硬件优化
CPU:64位、高主频、高缓存,高并行处理能力;
内存:大内存、主频高,尽量不要使用swap;
磁盘:机械硬盘建议在15000转以上,使用raid10、raid5磁盘阵列,条件支持的话最好使用ssd固态硬盘;
网络:服务器标配的千兆网卡,建议是10G网卡,使用网卡bond技术。MySQL服务器尽可能和使用它的web服务器在同一局域网内,尽量避免诸如防火墙策略等不必要的开销;
注意:网卡bond是通过把多张网卡绑定为一个逻辑网卡,实现本地网卡的冗余、带宽扩容和负载均衡。
操作系统的优化 – 内核、tcp连接数量
设置tcp的连接数量限制(内核、CPU的最大并发允许的连接)
设置系统打开的最大文件限制(分用户级别和Linux系统界别)
禁用不必要启动的服务
文件系统调优
给数据一个单独的文件系统,推荐xfs,一般效率更高、更可靠。可以在挂载分区时启动noatime(不记录访问时间),这样以后系统在读取挂载的分区下的文件时,将不会再修改atime属性。
纵向拆解、横向拆解
纵向拆解:专机专用(数据库专机专用,避免额外的服务可能导致的性能下降和不稳定性)。
横向拆解:一台MySQL服务器扛不住时由多台服务器扛,这样就可以实现高可用、避免单节点故障。
数据库设计与规划
- 把字段设置为not null,字段避免null值出现。或者设置默认数字0代替null;
- 创建表的时候将集中字段的宽度设置的尽可能合理;
- 使用枚举或整数代替字符串类型,可以提高数据库的性能;
- 时间尽量使用timestamp或datatime;
- 单个表不要有太多字段,建议在20以内。
my.cnf文件内参数的优化
优化的总原则:给MySQL的资源太少的话,则MySQL施展不开,给MySQL的资源太多,可能会拖累整个OS。所以建议40%资源给OS,60%-70%给MySQL(这里指的是CPU和内存资源)。
- 对查询进行缓存,提高性能;
- 强制限制MySQL资源限制,确保系统负载不会导致资源耗尽的情况出现;
- 表高速缓存 – 每个表存储在一个文件中,为了加快从文件中读取数据的过程,MySQL对这些文件进行了缓存;
- 关键字(索引)缓冲区(决定索引处理的速度,尤其读索引读的速度)。
查询优化
- 启用MySQL的慢查询日志;(可以查找出哪些查询语句的执行效率很低,以便进行优化。)
- 使用explain执行计划;(通过在select语句前使用explain,来获取查询语句的执行计划,而不是真正的执行该语句。)
- 当只要一行数据的时候使用limit 1;
- 在查询数据的时候只取自己需要的,避免使用select*;
- 不做列运算(任何对列的操作都将导致表扫描,查询时尽可能将操作移至等号右边);
- SQL语句尽可能简单;
- or改写成in;
- 避免%xxx模糊查询;
- 尽量避免在where子句中使用不等于操作符,否则引擎将放弃使用索引 而进行全表扫描
存储引擎
MySAM
- MySAM支持表级锁;
- 计划任务数据在读写过程中相互堵塞,在数据写入的时候阻塞用户对数据的读取,在数据读取的过程中阻塞用户写入数据;
- 适合读多写少的表;
- 不支持事务,外键,不支持崩溃后的安全修复。
InnoDB
- InnoDB表数据和索引数据是放在一起的;
- 支持行级锁,
- 适合写多读少的表,但是全表扫描仍然会是表级锁定;
- 支持事务,外键,支持崩溃后的安全修复。
待补充…………