MySQL 优化

---

问题描述

MySQL 的性能优化分为四个部分：

• 硬件和操作系统层面的优化
• 架构设计层面的优化
• MySQL 程序配置优
• SQL 优化

---

一、硬件及操作系统层面优化

        从硬件层面来说，影响 Mysql 性能的因素有，CPU、可用内存大小、磁盘读写速度、 网络带宽。

        从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到 Mysql 性能。 这部分的优化一般由 DBA 或者运维工程师去完成。 在硬件基础资源的优化中，我们重点应该关注服务本身承载的体量，然后提出合理的指标要求，避免出现资源浪费！

补充： 

        在生产环境Linux系统中，基本上都需要解除文件句柄数的限制。原因是，Linux的系统默认值为1024，也就是说，一个进程最多可以接受1024个socket连接。这是远远不够的。

        文件句柄，也叫文件描述符。在Linux系统中，文件可分为：普通文件、目录文件、链接文件和设备文件。文件描述符（File Descriptor）是内核为了管理已被打开的文件所创建的索引，它是一个非负整数（通常是小整数），用于指代被打开的文件。所有的IO系统调用，包括socket的读写调用，都是通过文件描述符完成的。

---

二、架构设计层面的优化

        MySQL 是一个磁盘 IO 访问量非常频繁的关系型数据库。

        在高并发和高性能的场景中。MySQL 数据库必然会承受巨大的并发压力，而此时，我们的优化方式可以分为几个部分。

        1、搭建 Mysql 主从集群，单个 Mysql 服务容易单点故障，一旦服务器宕机，将会导致                依赖Mysql 数据库的应用全部无法响应。 主从集群或者主主集群可以保证服务的高                可用性。

        2、读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导                  致的性能影响

        3、引入分库分表机制，通过分库可以降低单个服务器节点的 IO 压力，通过分表的方                  式可以降低单表数据量，从而提升SQL查询的效率。

        4、针对热点数据，可以引入更为高效的分布式数据库，比如 Redis、MongoDB 等，                  他们可以很好的缓解 Mysql 的访问压力，同时还能提升数据检索性能。

---

三、MySQL 程序配置优化

        MySQL 是一个经过互联网大厂验证过的生产级别的成熟数据库，对于 Mysql 数据库本身的优化，一般是通过 Mysql 中的配置文件 my.cnf 来完成的。

        比如。 Mysql5.7 版本默认的最大连接数是 151 个，这个值可以在 my.cnf 中修改。 binlog 日志，默认是不开启。缓存池 bufferpoll 的默认大小配置等。

         由于这些配置一般都和用户安装的硬件环境以及使用场景有关系，因此这些配置官方只会提供一个默认值，具体情况还得由使用者来修改。 关于配置项的修改，需要关注两个方面。

                1、配置的作用域，分为会话级别和全局

                2、是否支持热加载

        因此，针对这两个点，我们需要注意的是：

                1、全局参数的设定对于已经存在的会话无法生效

                2、会话参数的设定随着会话的销毁而失效

                3、全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效

---

四、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 优化规则：

        1、SQL 的查询一定要基于索引来进行数据扫描

        2、避免索引列上使用函数或者运算,这样会导致索引失效

        3、where 字句中 like %号,尽量放置在右边

        4、使用索引扫描，联合索引中的列从左往右,命中越多越好。 

        5、尽可能使用 SQL 语句用到的索引完成排序,避免使用文件排序的方式

        6、查询有效的列信息即可，少用 * 代替列信息

        7、永远用小结果集驱动大结果集。