把MySQL性能优化分为下面四个点:
1.硬件和操作系统层面的优化
2.架构设计层面的优化
3.MySQL程序配置优化
4.SQL执行优化
详细说明如下:
1.硬件和操作系统层面的优化
从硬件层面来说,影响MySQL性能因素主要是cpu、可用内存大小、磁盘读写速度、网络带宽;
从操作系统层面来说,应用文件句柄数、操作系统的网络配置,都会影响到MySQL的性能。
这部分的优化一般是由DBA(数据管理员)或者运维工程师去完成,在硬件基础资源的优化中,我们重点应该关注的是服务本身所承载的体量,然后提出合理的指标要求,避免出现资源浪费的一个现象。
2.架构设计层面优化
MySQL是一个磁盘IO访问非常频繁的关系型数据库,在高并发和高性能的场景中,MySQL必然会承受巨大的并发压力,在此时我们优化的方式,主要可以分为几个部分:
(1)搭建MySQL主从集群
单个MySQL服务容易去导致单点故障,一旦服务宕机,将会导致依赖MySQL数据库的应用全部无法响应。主从集群或者主主集群,都可以去保证服务高可用性;
(2)读写分离设计
在读多写少的场景中,通过读写分离的方案,可以去避免读写冲突导致的性能问题;
(3)分库分表机制
通过分库,可以降低单个服务器节省的一个IO压力,通过分表的方式可以去降低单表数据量,从而去提升sql的查询效率;
(4)针对热点数据,引入更高效的分布数据库
比如说像,Redis、MongoDB等,他们可以很好的缓解,MySQL的访问压力,同时还能提升数据的检索性能。
3.MySQL程序配置优化
MySQL是一个经过互联网大厂检验过的生产级别的成熟数据库,对于MySQL数据库本身的优化,一般可以通过MySQL配置文件:my.cnf来完成。
比如说:
MySQL5.7版本默认的最大连接数是151个,这个值可以再my.cnf中去修改;
binglog日志默认是不开启的,也可以在这个文件中修改开启;
缓存池Bufferpool,默认大小配置等,
上面,这些配置一般是和用户的安装环境以及使用场景有关系,官方只会提供一个默认的配置,具体情况还是由使用者,根据实际情况去修改。
关于配置项的修改,关注两个层面:第一个,配置的作用域 : 分为会话级别和全局范围;第二个,是否支持热加载。因此,针对这两点要注意,全局参数的设定,对于已经存在的会话是无法生效的,会话参数的设定,随着会话的销毁而失效;第三个,全局类的统一配置,建议配置在默认配置文件中,否则重启服务会导致配置失效。
4.SQL执行优化
分为三个步骤:
第一个,慢sql的定位和排查
我们可以通过慢查询日志,慢查询日志工具分析,得到有问题的SQL列表
第二个,执行计划分析
针对慢的sql,我们可以使用关键字explain,来查看当前sql的执行计划,可以重点关注type,key,rows,filterd等字段,从而去定位改sql执行慢的根本原因,再去有的放矢进行优化。
第三个,使用show profile工具
show profile工具是MySQL提供的,可以用来分析当前会话中,sql语句资源消耗情况的工具,可以用于sql调优的测量。在当前会话中,默认情况下show profile是关闭状态,打开之后会保存最近15次的运行结果,针对运行慢的sql,通过profile工具进行详细分析,可以得到sql执行过程中,所有资源的开销情况,比如IO开销,cpu开销,内存开销等等
最后,补充,总结一写常见的sql优化的一些规则:
(1)sql的查询一定要基于索引来进行数据扫描;
(2)避免索引列上使用函数或者运算符,这样会导致索引失效;
(3)where语句中like %号,尽量把%放在右侧;
(4)使用索引扫描,联合索引中的列从左往右,命中越多越好;
(5)尽量可能使用sql语句用到的索引完成排序,避免使用文件排序的方式;
(6)查询有效的列信息即可,少用 * 代替列信息,用具体列来代替;
(7)永远用小结果集驱动大结果集