MySQL的主动优化和被动优化_关于java:MySQL被动性能优化汇总

年少不知优化苦，遇坑方知优化难。 ——村口王大爷

本文内容导图如下：

我之前有很多文章都在讲性能优化的问题，比方上面这些：

《switch 的性能晋升了 3 倍，我只用了这一招！》

《String性能晋升10倍的几个办法！(源码+原理剖析)》

《局部变量居然比全局变量快 5 倍？》

《池化技术达到有多牛？看了线程和线程池的比照吓我一跳！》

《链表居然比数组慢了1000多倍？(动图+性能评测)》

《HashMap 的 7 种遍历形式与性能剖析！》

更多性能优化文章

当然，本篇也是对于性能优化的，那性能优化就应该一把梭子吗？还是要合乎一些标准和准则呢？

所以，在开始之前(MySQL 优化)，咱们先来聊聊性能优化的一些准则。

性能优化准则和分类

性能优化个别能够分为：

被动优化

被动优化

所谓的被动优化是指不须要外力的推动而自发进行的一种行为，比方当服务没有显著的卡顿、宕机或者硬件指标异样的状况下，自我登程去优化的行为，就能够称之为被动优化。

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

性能优化准则

无论是被动优化还是被动优化都要合乎以下性能优化的准则：

优化不能扭转服务运行的逻辑，要保障服务的正确性；

优化的过程和后果都要保障服务的安全性；

要保障服务的稳定性，不能为了谋求性能就义程序的稳定性。比方不能为了进步 Redis 的运行速度，而敞开长久化的性能，因为这样在 Redis 服务器重启或者掉电之后会失落存储的数据。

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

也就是说，咱们应该以预防性能问题为主，在开发阶段尽可能的躲避性能问题，而在失常状况下，应尽量避免被动优化，以避免未知的危险(除非是为了 KPI，或者是闲的没事)，尤其对生产环境而言更是如此，最初才是思考被动优化。

PS：当遇到性能迟缓降落、或硬件指标迟缓减少的状况，如明天内存的占用率是 50%，今天是 70%，先天是 90% ，并且丝毫没有发出的迹象时，咱们应该提前发现并解决此类问题(这种状况也属于被动优化的一种)。

MySQL 被动性能优化

所以咱们本文会重点介绍 MySQL 被动性能优化的常识，依据被动性能优化的常识，你就能够失去预防性能问题产生的一些办法，从而躲避 MySQL 的性能问题。

本文咱们会从问题动手，而后思考这个问题产生的起因以及相应的优化计划。咱们在理论开发中，通常会遇到以下 3 个问题：

单条 SQL 运行慢；

局部 SQL 运行慢；

整个 SQL 运行慢。

问题 1：单条 SQL 运行慢

问题剖析

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

未失常创立或应用索引；

表中数据量太大。

解决方案 1：创立并正确应用索引

索引是一种能帮忙 MySQL 进步查问效率的次要伎俩，因而个别状况下咱们遇到的单条 SQL 性能问题，通常都是因为未创立或为正确应用索引而导致的，所以在遇到单条 SQL 运行比较慢的状况下，你首先要做的就是查看此表的索引是否失常创立。

如果表的索引曾经创立了，接下来就要检查一下此 SQL 语句是否失常触发了索引查问，如果产生以下状况那么 MySQL 将不能失常的应用索引：

在 where 子句中应用 != 或者 <> 操作符，查问援用会放弃索引而进行全表扫描；

不能应用前导含糊查问，也就是 ‘%XX’ 或 ‘%XX%’，因为前导含糊不能利用索引的程序，必须一个个去找，看是否满足条件，这样会导致全索引扫描或者全表扫描；

如果条件中有 or 即便其中有条件带索引也不会失常应用索引，要想应用 or 又想让索引失效，只能将 or 条件中的每个列都加上索引能力失常应用；

在 where 子句中对字段进行表达式操作。

因而你要尽量避免以上状况，除了失常应用索引之外，咱们也能够应用以下技巧来优化索引的查问速度：

尽量应用主键查问，而非其余索引，因为主键查问不会触发回表查问；

查问语句尽可能简略，大语句拆小语句，缩小锁工夫；

尽量应用数字型字段，若只含数值信息的字段尽量不要设计为字符型；

用 exists 代替 in 查问；

防止在索引列上应用 is null 和 is not null。

回表查问：一般索引查问到主键索引后，回到主键索引树搜寻的过程，咱们称为回表查问。

解决方案 2：数据拆分

当表中数据量太大时 SQL 的查问会比较慢，你能够思考拆分表，让每张表的数据质变小，从而进步查问效率。

1.垂直拆分

指的是将表进行拆分，把一张列比拟多的表拆分为多张表。比方，用户表中一些字段常常被拜访，将这些字段放在一张表中，另外一些不罕用的字段放在另一张表中，插入数据时，应用事务确保两张表的数据一致性。

垂直拆分的准则：

把不罕用的字段独自放在一张表；

把 text，blob 等大字段拆分进去放在附表中；

常常组合查问的列放在一张表中。

2.程度拆分

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

通常状况下，咱们应用取模的形式来进行表的拆分，比方，一张有 400W 的用户表 users，为进步其查问效率咱们把其分成 4 张表 users1，users2，users3，users4，而后通过用户 ID 取模的办法，同时查问、更新、删除也是通过取模的办法来操作。

表的其余优化计划：

应用能够存下数据最小的数据类型；

应用简略的数据类型，int 要比 varchar 类型在 MySQL 解决简略；

尽量应用 tinyint、smallint、mediumint 作为整数类型而非 int；

尽可能应用 not null 定义字段，因为 null 占用 4 字节空间；

尽量少用 text 类型，非用不可时最好思考分表；

尽量应用 timestamp，而非 datetime；

单表不要有太多字段，倡议在 20 个字段以内。

问题 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 由代码显示的控制数据源。

长处：路由策略的扩展性和可控性较强。

毛病：须要在 Spring 中增加耦合控制代码。

2.中间件解决方案

通过 MySQL 的中间件做主从集群，比方：Mysql Proxy、Amoeba、Atlas 等中间件都能合乎需要。

长处：与应用层解耦。

毛病：减少一个服务保护的危险点，性能及稳定性待测试，须要反对代码强制主从和事务。

扩大常识：SQL 语句剖析

在 MySQL 中咱们能够应用 explain 命令来剖析 SQL 的执行状况，比方：

explain select * from t where id=5;

如下图所示：

其中：

id — 抉择标识符，id 越大优先级越高，越先被执行；

select_type — 示意查问的类型；

table — 输入后果集的表；

partitions — 匹配的分区；

type — 示意表的连贯类型；

possible_keys — 示意查问时，可能应用的索引；

key — 示意理论应用的索引；

key_len — 索引字段的长度；

ref—  列与索引的比拟；

rows — 大略估算的行数；

filtered — 按表条件过滤的行百分比；

Extra — 执行状况的形容和阐明。

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

all — 扫描全表数据；

index — 遍历索引；

range — 索引范畴查找；

index_subquery — 在子查问中应用 ref；

unique_subquery — 在子查问中应用 eq_ref；

ref_or_null — 对 null 进行索引的优化的 ref；

fulltext — 应用全文索引；

ref — 应用非惟一索引查找数据；

eq_ref — 在 join 查问中应用主键或惟一索引关联；

const — 将一个主键搁置到 where 前面作为条件查问， MySQL 优化器就能把这次查问优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。

总结

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

单条 SQL 运行慢；

局部 SQL 运行慢；

整个 SQL 运行慢。

因而咱们给出了每种被动优化计划的问题剖析和解决方案，心愿本文能够帮忙到你。

关注公众号「Java中文社群」获取更多精彩内容。