这才是 SQL 优化的正确姿势

全文内容预览：

当然，本篇是关于性能优化的，那性能优化就应该一把梭子吗？还是要符合一些规范和原则呢？

所以，在开始之前（MySQL 优化），咱们先来聊聊 性能优化 的一些原则。

性能优化分类和原则

性能优化一般可以分为：

所谓的 主动优化 是指 不需要外力的推动而自发进行的一种行为， 比如当服务没有明显的卡顿、宕机或者硬件指标异常的情况下， 自我出发去优化的行为， 就可以称之为主动优化。

而 被动优化 刚好与主动优化相反，它是指 在发现了服务器卡顿、服务异常或者物理指标异常的情况下，才去优化的这种行为。

性能优化原则：

无论是主动优化还是被动优化都要符合 以下性能优化的原则：

以上原则看似都是些废话，但却给了我们一个启发，那就是我们性能优化手段应该是： 预防性能问题为主+被动优化为辅。

也就是说，我们应该 以预防性能问题为主 ，在开发阶段尽可能的规避性能问题，而 在正常情况下，应尽量避免主动优化，以防止未知的风险 （除非是为了 KPI，或者是闲的没事），尤其对生产环境而言更是如此， 最后才是考虑被动优化 。

PS： 当遇到性能缓慢下降、或硬件指标缓慢增加的情况，如今天内存的占用率是 50%，明天是 70%，后天是 90% ，并且丝毫没有收回的迹象时，我们应该提早发现并处理此类问题（这种情况也属于被动优化的一种）。

MySQL 被动性能优化

所以我们本文会重点介绍 MySQL 被动性能优化的知识，根据被动性能优化的知识，你就可以得到 预防性能问题发生的一些方法 ，从而规避 MySQL 的性能问题。

本文我们会从问题入手，然后考虑这个问题产生的原因以及相应的优化方案。我们在实际开发中，通常会遇到 以下 3 个问题：

1.单条 SQL 运行慢

问题分析 

造成单条 SQL 运行比较慢的常见原因 有以下两个：

解决方案 

(1)创建并正确使用索引

索引 是一种能 帮助 MySQL 提高查询效率的主要手段 ，因此一般情况下我们遇到的单条 SQL 性能问题，通常都是由于未创建或为正确使用索引而导致的，所以在遇到单条 SQL 运行比较慢的情况下，你首先要做的就是 检查此表的索引是否正常创建。

如果表的索引已经创建了，接下来就要 检查一下此 SQL 语句是否正常触发了索引查询， 如果发生以下情况那么 MySQL 将 不能正常的使用索引：

因此你要尽量避免以上情况，除了正常使用索引之外，我们也可以 使用以下技巧来优化索引的查询速度：

回表查询：普通索引查询到主键索引后，回到主键索引树搜索的过程，我们称为回表查询。

(2)数据拆分

当表中数据量太大时 SQL 的查询会比较慢，你可以考虑 拆分表 ，让每张表的 数据量变小，从而提高查询效率。

垂直拆分

指的是将表进行拆分， 把一张列比较多的表拆分为多张表。

比如： 用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中，插入数据时，使用事务确保两张表的数据一致性。

垂直拆分的原则：

水平拆分

指的是将数据表行进行拆分，表的行数超过200万行时，就会变慢，这时可以 把一张的表的数据拆成多张表来存放。

通常情况下，我们使用 取模 的方式来进行表的拆分。

比如： 一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，users3，users4，然后通过用户 ID 取模的方法，同时查询、更新、删除也是通过取模的方法来操作。

表的其他优化方案：

2.部分 SQL 运行慢

问题分析 

部分 SQL 运行比较慢，我们首先要做的就是 先定位 出这些 SQL，然后再看这些 SQL 是否正确创建并使用索引。也就是说，我们先要使用慢查询工具定位出具体的 SQL，然后再使用问题 1 的解决方案处理慢 SQL。

解决方案 

慢查询分析:

MySQL 中自带了 慢查询日志 的功能，开启它就可以用来 记录在 MySQL 中响应时间超过阀值的语句 ，具体指 运行时间超过 long_query_time 值的 SQL ，则会被记录到慢查询日志中。

long_query_time 的默认值为 10，意思是运行 10S 以上的语句。默认情况下，MySQL 数据库并不启动慢查询日志，需要我们 手动来设置 这个参数， 如果不是调优需要的话，一般不建议启动该参数， 因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。

慢查询日志支持将日志记录写入文件，也支持 将日志记录写入数据库表。 使用 mysql> show variables like '%slow_query_log%'; 来查询慢查询日志是否开启， 执行效果如下图所示：

slow_query_log 的值为 OFF 时，表示未开启慢查询日志。

开启慢查询日志 ，可以 使用如下 MySQL 命令：

mysql> set global slow_query_log=1

不过这种设置方式， 只对当前数据库生效 ，如果 MySQL 重启也会失效，如果要永久生效，就必须 修改 MySQL 的配置文件 my.cnf， 配置如下：

slow_query_log =1 slow_query_log_file=/tmp/mysql_slow.log

当你开启慢查询日志之后，所有的慢查询 SQL 都会被记录在 slow_query_log_file 参数配置的文件内，默认是 /tmp/mysql_slow.log 文件，此时我们就可以打开日志 查询到所有慢 SQL 进行逐个优化。

3.整个 SQL 运行慢

问题分析 

当出现整个 SQL 都运行比较慢就说明目前数据库的承载能力已经到了峰值，因此我们需要使用一些 数据库的扩展手段来缓解 MySQL 服务器了。

解决方案 

读写分离:

一般情况下对数据库而言都是 “读多写少” ，换言之，数据库的压力多数是因为大量的读取数据的操作造成的，我们可以采用 数据库集群 的方案，使用一个库作为 主库 ，负责写入数据；其他库为 从库 ，负责读取数据。这样可以缓解对数据库的访问压力。

MySQL 常见的读写分离方案有以下两种：

(1)应用层解决方案

可以通过 应用层对数据源做路由 来实现读写分离。

比如： 使用 SpringMVC + MyBatis，可以将 SQL 路由交给 Spring，通过 AOP 或者 Annotation 由代码显示的控制数据源。

(2)中间件解决方案

通过 MySQL 的中间件做主从集群。

比如： Mysql Proxy、Amoeba、Atlas 等中间件都能符合需求。

扩展知识：SQL 语句分析

在 MySQL 中我们可以使用 explain 命令来分析 SQL 的执行情况， 比如：

explain select * from t where id=5;

如下图所示：

其中：

其中最重要的就是 type 字段，type 值类型如下：

总结

本文我们介绍了 MySQL 性能优化的原则和分类，MySQL 的性能优化可分为：主动优化和被动优化，但无论何种优化都要 保证服务的正确性、安全性和稳定性。 它带给我们的启发是应该采用：预防 + 被动优化的方案来确保 MySQL 服务器的稳定性，而 被动优化常见的问题是：

因此我们给出了每种被动优化方案的问题分析和解决方案，希望本文可以帮助到你。