乐趣区

关于java:MySQL被动性能优化汇总

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

本文内容导图如下:

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

  1. 《switch 的性能晋升了 3 倍,我只用了这一招!》
  2. 《String 性能晋升 10 倍的几个办法!(源码 + 原理剖析)》
  3. 《局部变量居然比全局变量快 5 倍?》
  4. 《池化技术达到有多牛?看了线程和线程池的比照吓我一跳!》
  5. 《链表居然比数组慢了 1000 多倍?(动图 + 性能评测)》
  6. 《HashMap 的 7 种遍历形式与性能剖析!》
  7. 更多性能优化文章

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

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

性能优化准则和分类

性能优化个别能够分为:

  • 被动优化
  • 被动优化

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

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

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

  1. 优化不能扭转服务运行的逻辑,要保障服务的 正确性
  2. 优化的过程和后果都要保障服务的 安全性
  3. 要保障服务的 稳定性,不能为了谋求性能就义程序的稳定性。比方不能为了进步 Redis 的运行速度,而敞开长久化的性能,因为这样在 Redis 服务器重启或者掉电之后会失落存储的数据。

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

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

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

MySQL 被动性能优化

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

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

  1. 单条 SQL 运行慢;
  2. 局部 SQL 运行慢;
  3. 整个 SQL 运行慢。

问题 1:单条 SQL 运行慢

问题剖析

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

  1. 未失常创立或应用索引;
  2. 表中数据量太大。

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

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

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

  1. 在 where 子句中应用 != 或者 <> 操作符,查问援用会放弃索引而进行全表扫描;
  2. 不能应用前导含糊查问,也就是 ‘%XX’ 或 ‘%XX%’,因为前导含糊不能利用索引的程序,必须一个个去找,看是否满足条件,这样会导致全索引扫描或者全表扫描;
  3. 如果条件中有 or 即便其中有条件带索引也不会失常应用索引,要想应用 or 又想让索引失效,只能将 or 条件中的每个列都加上索引能力失常应用;
  4. 在 where 子句中对字段进行表达式操作。

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

  1. 尽量应用主键查问,而非其余索引,因为主键查问不会触发回表查问;
  2. 查问语句尽可能简略,大语句拆小语句,缩小锁工夫;
  3. 尽量应用数字型字段,若只含数值信息的字段尽量不要设计为字符型;
  4. 用 exists 代替 in 查问;
  5. 防止在索引列上应用 is null 和 is not null。

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

解决方案 2:数据拆分

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

1. 垂直拆分

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

  • 把不罕用的字段独自放在一张表;
  • 把 text,blob 等大字段拆分进去放在附表中;
  • 常常组合查问的列放在一张表中。
2. 程度拆分

指的是将数据表行进行拆分,表的行数超过 200 万行时,就会变慢,这时能够把一张的表的数据拆成多张表来寄存。
通常状况下,咱们应用取模的形式来进行表的拆分,比方,一张有 400W 的用户表 users,为进步其查问效率咱们把其分成 4 张表 users1,users2,users3,users4,而后通过用户 ID 取模的办法,同时查问、更新、删除也是通过取模的办法来操作。

表的其余优化计划:
  1. 应用能够存下数据最小的数据类型;
  2. 应用简略的数据类型,int 要比 varchar 类型在 MySQL 解决简略;
  3. 尽量应用 tinyint、smallint、mediumint 作为整数类型而非 int;
  4. 尽可能应用 not null 定义字段,因为 null 占用 4 字节空间;
  5. 尽量少用 text 类型,非用不可时最好思考分表;
  6. 尽量应用 timestamp,而非 datetime;
  7. 单表不要有太多字段,倡议在 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 中文社群」获取更多精彩内容。

退出移动版