MySQL优化-CSDN博客
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
MySQL 的性能优化可以分为 4 大部分
- 硬件和操作系统层面的优化
- 架构设计层面的优化
- MySQL 程序配置优化
- SQL 优化
硬件及操作系统层面优化
从硬件层面来说影响 Mysql 性能的因素有CPU、可用内存大小、磁盘读写速度、网络带宽
从操作系层面来说应用文件句柄数、操作系统网络的配置都会影响到 Mysql 性能。
这部分的优化一般由 DBA 或者运维工程师去完成。
在硬件基础资源的优化中我们重点应该关注服务本身承载的体量然后提出合理的指
标要求避免出现资源浪费
架构设计层面的优化
MySQL 是一个磁盘 IO 访问量非常频繁的关系型数据库在高并发和高性能的场景中.MySQL 数据库必然会承受巨大的并发压力而此时我们的优化方式可以分为几个部分
- 搭建 Mysql 主从集群单个 Mysql 服务容易单点故障一旦服务器宕机将会导致依赖 Mysql 数据库的应用全部无法响应。 主从集群或者主主集群可以保证服务的高可用性。
- 读写分离设计在读多写少的场景中通过读写分离的方案可以避免读写冲突导致的性能影响
- 引入分库分表机制通过分库可以降低单个服务器节点的 IO 压力通过分表的方式可以降低单表数据量从而提升 sql 查询的效率。
- 针对热点数据可以引入更为高效的分布式数据库比如 Redis、MongoDB 等他们可以很好的缓解 Mysql 的访问压力同时还能提升数据检索性能。
MySQL 程序配置优化
MySQL 是一个经过互联网大厂验证过的生产级别的成熟数据库对于 Mysql 数据库本身的优化一般是通过 Mysql 中的配置文件 my.cnf 来完成的比如。Mysql5.7 版本默认的最大连接数是 151 个这个值可以在 my.cnf 中修改。binlog 日志默认是不开启缓存池 bufferpoll 的默认大小配置等。
由于这些配置一般都和用户安装的硬件环境以及使用场景有关系因此这些配置官方只
会提供一个默认值具体情况还得由使用者来修改。
关于配置项的修改需要关注两个方面
- 配置的作用域分为会话级别和全局
- 是否支持热加载
因此针对这两个点我们需要注意的是
- 全局参数的设定对于已经存在的会话无法生效
- 会话参数的设定随着会话的销毁而失效
- 全局类的统一配置建议配置在默认配置文件中否则重启服务会导致配置失效
SQL优化
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 优化规则
SQL 的查询一定要基于索引来进行数据扫描
- 避免索引列上使用函数或者运算,这样会导致索引失效
- where 字句中 like %号,尽量放置在右边
- 使用索引扫描,联合索引中的列从左往右,命中越多越好.
- 尽可能使用 SQL 语句用到的索引完成排序,避免使用文件排序的方式
- 查询有效的列信息即可.少用 * 代替列信息
- 永远用小结果集驱动大结果集。