这才是 SQL 优化的正确姿势

磊哥| 作者
Java中文社群|来源
https://mp.weixin.qq.com/s/ZosZWVeME3V652uO1yrdoA

全文内容预览:

这才是 SQL 优化的正确姿势

当然,本篇是关于性能优化的,那性能优化就应该一把梭子? 3 ] F 7 _吗?还是要符合一些规范和原则呢?

所以,在开始之前(MySQL 优化),咱们先来聊聊性能优化的一些原则。: % L M + J | n e

性能优化分类和原则

性能优化一. j } w 3 r y l般可以分为:

主动优化
被动优化

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

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

性能优化原则:

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

优化不能改变服务运行的逻辑,要保证服务的正确性
优化的过程和结果都要保证a ` vo h D # ] c f务的安全性
要保证服务的稳定性,不能为了追求性能牺牲程序的稳定性,比如:不能为了提高A 7 S f Redis 的运行速度,而关闭持久1 ? S化的功能,因为这样在 RediT R N O ps 服务器重启或者掉电之后会丢失存储的数据。

这才是 SQL 优化的正确姿势

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

也就是说,我们应该以预防性O f z X a 4 ~能问题为主,在开发阶段尽可能的规避性能问题,而在正常情况下,应尽量= ? r V l S 避免主动优化,以防止未知的风险(除非是为了 KPI,或者是闲的没事),尤其对生产环境而言更是如此,最后才是考虑被动优化

PS:当遇到性能缓慢下降、或[ / $ : 9 7 D ] ]硬件指标缓慢增加的情况,如今天内存的占用率是 50%H L | k 7 T,明天是 70%,后天是 90% ,并且丝毫没有收回的迹象时,我们应该提早发现并处理此类问题(这种情况也属于被动优化的一种)。

MySQL 被动性能优化

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

本文我们会从问题入手,然后考虑这个问题产v k e a z , ] Y L生的原因以及相应的优化方案。我们在实际开发中,通常会遇到以下 34 5 ] 个问题:

单条 SQL 运行慢
部分 SQL 运行慢
整个 SQL 运行慢

这才是 SQL 优化的正确姿势

1.单条 SQL 运行慢

问题分析

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

未正常创建或使用索引

表中数据量太大_ - J [ z r d

解决方案

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

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

如果表的索引已经创建了,接下来就要检查一下此 SQL 语句是否正常触发了索引查询,如果发生以下情况那么 MySQL 将不能正/ * q A i A g n常的使用索引:y | 0 b

在 where 子句T z k B中使用 != 或者 &l! n Qt;> 操作符,查询引用会放弃索引而进行全表扫描;

不能使3 A F T ~ c 6用前导模糊查询,也就是 ‘%XX’ 或 ‘%XX%’,由于前导模糊不能利用索引的顺序,必须` U $一个个去找,看是否满足条件,这样会导h $ P 2 H ( J致全索引扫描或者全表扫描;

如果条件中有 or 即使其中有条件带索引也不会正常使用索引,要想使( O + 1 l b : g用 or 又想让索引生效,只能将 or 条件中的每个列都加上索引才能正常使用;

在 where 子句中对( 2 j + Z ?字段进行表达式操作。

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

0 s | f r G T l量使用主键查询,而非其他索引,因为主键查询不6 n _ * 8 n U k A会触发回表查询;

查询语句尽可能简单,大语句拆小语G E d w句,减少锁时间;

尽量使用数( d w字型字段,若只B X } E I k含数值信息的字段尽量不要设计为字符型;

用 exists 替代 in 查询& T 0 r 7 h D = Y

避免在索引列上使用 is null 和 is not null。

回表查询:普通索引查询到主键索引后,回到主键索引树搜索的过程,我们称为回表查询。x [ Q U } ]

(2)数据拆分

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

垂直拆分

指的是将表进行拆分,_ ! t把一张列比较多的7 V x q % h 8表拆分为多张表。

比如:用户表中一些字段经常被访问,. P + 8 * _将这些字段放在一张c H 8 z N q表中,另外一些不常用的字段放在另一张表中,插入数据时,使用事务确保两张表的数据一致性。

垂直拆分的V ~ ` p e 9 a ~ *原则:

把不常用的字8 U v S段单独放在一张表;

把 text,blob 等大字段拆分出来放在附表中;

经常组, S B ! ~ | s #合查询的列放在一张表中。

水平拆分

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

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

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

s ; g , v +的其他优化方案:

使用可以存下数据最小的数据类型;

使用简单的数据类型,int 要比 varchar 类型在 MySQL 处理简单;

尽量使用 tia M k & + I Mnyint、smallint、mediumint 作为整数类型而非 int;

尽可能使用 not null 定义字段,因为 null 占用 4 字节空间;

尽量少用 text 类型,非用不可时` / N 3 - K最好考虑分表;

尽量使用 timestamp,而非 datetimf V u Z Pe;

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

2.部分 SQL 运行慢

问题分析

部分 SQL 运行比较慢,我们首先要做的就是先定位出这些 SQL,然后再看这些 SQL 是否正确创建并使用索引。也就是说,我们先要使用慢查询工具定位出具体的 SQL,然后再使用问题 1 的解决方案处理慢, - C m L { ( 5 S4 + F ~ 2QL。

解决方案

慢查询分析:

MySQL 中自带了慢查询日志的功能,开启它就可以用来记录在 MySQL 中响应时间超过阀值的语句,具体指运行时间超过 long_qux o ) # Qery_time 值的 SQL,则O . ! l ; o L会被记录到慢查询日志中。

long_query_time 的默认# ~ * O + 0 p Y值为 10,意思是运行 10S 以上的语句。默认情况下,MySQL 数据库并不启动慢查询日志,需要我们S J m M , M手动来设置这个参数,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查| r 4 ^ G ]询日志会给 MySQL 服务器带来一定的性能影响。

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

这才是 SQL 优化的正确姿势

slow_qu` v ] ; J iery_log 的值为OFF时,表示未开启慢查询日志。

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

mysql> set global slow_query_log / ~ E q=1

不过这种设置方式] U ) t ( @只对当前数据库生效,如果 MySQL 重启也会失效,如果要永久生效,就必须修改 MySQL 的配/ T H 2 F W ^ a置文件 my.cnf,配置如下:

slow_query_log =1 slow_query_log_U , = a h 4 Pfile=/tmp/mysql_slow.logm Z 5 p V A  )

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

3.整个 SQL 运行慢

问题分析

当出现整个 SQL 都运行比较慢就说H O i e # G j g 4明目前数据库的承v 7 # z K t q - H载能力已经到了峰值,因此T O Z y K /我们需要使用一些数据库的扩展手段来缓解 MySQL 服务器了。

解决方案C p O

读写分离:

一般情况下对数据库而言都是“读多写少”,换言之,数据库的压力多数是因为大量的读取数据的操作造成的,我们可N w j i J D + $ wx d 2采用d t * d l ] | w据库集群的方案,使用一个库作0 N D _ c 5 V主库,负责写入数据;z 1 o E其他库为从库,负责读取数据。这样可以缓解对数据库的访问压力。

MySQL 常V r e l p ^ 见的读写分离方案有以下两种:

(1)应用层解决方案

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

比如:使用 SpringMVC + Myc ( X Y s e T cBai | N n n 6 r [tis,可以将 SQ/ 2 ]L 路由交给 Spring,通过 AOP 或者 Annotation 由代码显示的控制数据源。

优点:路由策略的扩展性和可控性较强。

缺点:需要在 Spring 中添加耦合控制代码。

(2)中间件解决方8 F

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

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

优点:与应用层解耦。

缺点:增加一个服务维护的风险点,性能及7 Q稳定性待测试,需要支持代码强制主从和事务。

扩展知` p l d +识:SQL 语句分析

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

explain select * from t where id=5;

如下图所示:

这才是 SQL 优化的正确姿势

其中:

id:选择标识符,id 越大优先级越高,越先被执行;

selectN . b D } W E k_type:表示查询的类型;

table:输出Q 0 k结果O & / - q j } ?集的表;

partitions:匹配的分区;

type:表示表的连接类型;

pof S I $ * & W 5 8ssible_keys:表示查询时,可能使用的索引;

key:表示实际使用的索引;

key_le8 G Xn:索引字段的长度;

ref:列与索引的比较;

rows:大概估算的行数;

filtered:按表条件过滤的行百分比;

Extra:执行情况的描述和说明。

其中最重要的就是 type 字段,typew & 0 值类型如下:

all:扫描全表数据;

index:遍历索引;

range:索引范围查找;

index_subquery:在子查询中使用 ref;

unique_subquery:在子查询中使用 eqT Q K x * `_ref;

ref_or_nul` t / A + a 7 5 gl:对 null 进行索引的优化的 ref;

fulltext:使用全文索引;

ref:使用非唯一索引查找数据;

eq_ref:在 join 查询中使用主键或唯一索引关联;

cW _ Uono g v )st:将一个主键放置到 where 后面作为条件查询, MySQL 优化器就能把这次查询优化转化为一个常量,如何转化以及何时转化,这个取决于优化器,这个比 eq_ref 效率高一点。

总结

本文我们介绍了 M# 2 8ySQL 性能优化的原则和分类,MySQL 的性能优化可分为:主动优化和被动优化,但无论何种优a Y v化都要保证服务的正确性、安全性和稳定性。它带给我们的启发是应该采用:预防 + 被动优化的方案来确保 MySQL 服务器的稳定性,而被动优% 0 Q 3 ]化常见的问题是:

单条 SQL 运行慢
部分 SQLv @ 7 运行慢
整个 SQL 运行慢

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

站长推荐

1.云服务推荐:t F - X P d 国内主流云服务商,各类云产品的最新活动,优惠券领取。地址:阿里云腾讯云` R I + y C华为云

2.广告联盟: 整理了目前主流的广告联盟平台,如果你有流量,可以作为参考选择适合你的平台点击进入

链接: http:/` s E C 0 T S n/www.fL ! @ly63.com/article/detial/9763