文章目录
前言
大家都在写SQL
,但是不同人写出的SQL
执行效率却各有不同,这里面的门道也是所有后端开发者的必修课。
所以,在开始之前(MySQL
优化),咱们先来聊聊性能优化的一些原则。
1. 性能优化原则和分类
性能优化一般可以分为:
- 主动优化
- 被动优化
所谓的主动优化是指不需要外力的推动而自发进行的一种行为,比如当服务没有明显的卡顿、宕机或者硬件指标异常的情况下,自我出发去优化的行为,就可以称之为主动优化。
而被动优化刚好与主动优化相反,它是指在发现了服务器卡顿、服务异常或者物理指标异常的情况下,才去优化的这种行为。
1.1 性能优化原则
无论是主动优化还是被动优化都要符合以下性能优化的原则:
- 优化不能改变服务运行的逻辑,要保证服务的正确性。
- 优化的过程和结果都要保证服务的安全性。
- 要保证服务的稳定性,不能为了追求性能牺牲程序的稳定性。比如不能为了提高
Redis
的运行速度,而关闭持久化的功能,因为这样在Redis
服务器重启或者掉电之后会丢失存储的数据。
以上原则看似都是些废话,但却给了我们一个启发,那就是我们性能优化手段应该是:预防性能问题为主+被动优化为辅。
也就是说,我们应该以预防性能问题为主,在开发阶段尽可能的规避性能问题,而在正常情况下,应尽量避免主动优化,以防止未知的风险(除非是为了 KPI,或者是闲的没事),尤其对生产环境而言更是如此,最后才是考虑被动优化。
PS
:当遇到性能缓慢下降、或硬件指标缓慢增加的情况,如今天内存的占用率是50%
,明天是70%
,后天是90%
,并且丝毫没有收回的迹象时,我们应该提早发现并处理此类问题(这种情况也属于被动优化的一种)。
2. MySQL 被动性能优化
所以我们本文会重点介绍 MySQL
被动性能优化的知识,根据被动性能优化的知识,你就可以得到预防性能问题发生的一些方法,从而规避 MySQL
的性能问题。
本文我们会从问题入手,然后考虑这个问题产生的原因以及相应的优化方案。我们在实际开发中,通常会遇到以下 3
个问题:
- 单条
SQL
运行慢; - 部分
SQL
运行慢; - 整个
SQL
运行慢。
2.1 问题 1:单条 SQL 运行慢
问题分析
造成单条 SQL
运行比较慢的常见原因有以下两个:
- 未正常创建或使用索引;
- 表中数据量太大。
2.1.1 解决方案 1:创建并正确使用索引
索引是一种能帮助 MySQL
提高查询效率的主要手段,因此一般情况下我们遇到的单条 SQL
性能问题,通常都是由于未创建或为正确使用索引而导致的,所以在遇到单条 SQL
运行比较慢的情况下,你首先要做的就是检查此表的索引是否正常创建。
如果表的索引已经创建了,接下来就要检查一下此 SQL
语句是否正常触发了索引查询,如果发生以下情况那么 MySQL
将不能正常的使用索引:
- 在
where
子句中使用!=
或者<>
操作符,查询引用会放弃索引而进行全表扫描; - 不能使用前导模糊查询,也就是
'%XX'
或'%XX%'
,由于前导模糊不能利用索引的顺序,必须一个个去找,看是否满足条件,这样会导致全索引扫描或者全表扫描; - 如果条件中有
or
即使其中有条件带索引也不会正常使用索引,要想使用or
又想让索引生效,只能将or
条件中的每个列都加上索引才能正常使用; - 在
where
子句中对字段进行表达式操作。
因此你要尽量避免以上情况,除了正常使用索引之外,我们也可以使用以下技巧来优化索引的查询速度:
- 尽量使用主键查询,而非其他索引,因为主键查询不会触发回表查询;
- 查询语句尽可能简单,大语句拆小语句,减少锁时间;
- 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型;
- 用 exists 替代 in 查询;
- 避免在索引列上使用 is null 和 is not null。
回表查询:普通索引查询到主键索引后,回到主键索引树搜索的过程,我们称为回表查询。
2.1.2 解决方案 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.2 部分 SQL 运行慢
问题分析
部分 SQL
运行比较慢,我们首先要做的就是先定位出这些 SQL
,然后再看这些 SQL
是否正确创建并使用索引。也就是说,我们先要使用慢查询工具定位出具体的 SQL
,然后再使用问题 1
的解决方案处理慢 SQL
。
2.2.1 解决方案:慢查询分析
请看我专门写的另一个文章——数据库优化——慢查询MySQL定位优化流程
2.3 问题 3:整个 SQL 运行慢
问题分析
当出现整个 SQL
都运行比较慢就说明目前数据库的承载能力已经到了峰值,因此我们需要使用一些数据库的扩展手段来缓解 MySQL
服务器了。
2.3.1 解决方案:读写分离
一般情况下对数据库而言都是“读多写少”,换言之,数据库的压力多数是因为大量的读取数据的操作造成的,我们可以采用数据库集群的方案,使用一个库作为主库,负责写入数据;其他库为从库,负责读取数据。这样可以缓解对数据库的访问压力。
MySQL
常见的读写分离方案有以下两种:
- 应用层解决方案
可以通过应用层对数据源做路由来实现读写分离,比如,使用 SpringMVC + MyBatis
,可以将 SQL
路由交给 Spring
,通过 AOP
或者 Annotation
由代码显示的控制数据源。优点:路由策略的扩展性和可控性较强。缺点:需要在 Spring
中添加耦合控制代码。
- 中间件解决方案
通过 MySQL
的中间件做主从集群,比如:Mysql Proxy
、Amoeba
、Atlas
等中间件都能符合需求。优点:与应用层解耦。缺点:增加一个服务维护的风险点,性能及稳定性待测试,需要支持代码强制主从和事务。
更多的可以看我其他的文章
原理探究:为什么大公司后台数据库都要搞分库分表?