01
背景
开发过程经常会遇到接口响应慢的问题,用户也会反馈页面卡顿,响应太慢,导致用户体验差,这个时候作为开发就要赶紧排查了,可能是程序处理的问题、也可能是并发量大导致排队问题、也可能是sql查询性能导致等;大多数时候sql查询缓慢是主要原因,下面我们聊下排查sql慢的思路。
02
技术简介
说到sql就要先了解下sql的架构图:
客户端:这里指连接MySQL各种形式,如.Net中使用的ADO连接、Java使用JDBC连接等,MySQL是客户端和服务器模式,前提先建立连接,才能传输数据,处理相关逻辑。
业务逻辑:在MySQL内部有很多模块组成,分别处理相关业务逻辑。
连接管理:负责连接认证、连接数判断、连接池处理等业务逻辑处理。
查询缓存:当一个SQL进来时,如果开启查询缓存功能,MySQL会优先去查询缓存,平时我们开发都会选择不开启。
解析器:对于一个sql语句,MySql根据语法规则需要对其进行解析,并生成一个内部能识别的解析树。
优化器:负责对解析器得到的解析树进行优化,MySQL会根据内部算法找到一个MySQL认为最优的执行计划,后续就按照这个执行计划执行。
执行器:得到执行计划之后,就会找到对应的存储引擎,根据执行计划给出的指令依次执行。
存储引擎:数据的存储和提取最后是靠存储引擎,平时我们使用最多的就是支持事务的InnoDb。
物理文件:数据存储的最终位置,即磁盘上,协同存储引擎对数据进行读写操作。
03
实现与效果
通过上面的逻辑我们知道一个sql发送到服务端时候要经过优化器,而使用explain关键字可以模拟优化器执行sql查询语句,从而知道MySQL是如何处理sql的即sql的执行计划。
概要描述:
id: 选择标识符
select_type: 表示查询的类型
table: 输出结果集的表
partitions: 匹配的分区
type: 表示表的连接类型
possible_keys: 表示查询时,可能使用的索引
key: 表示实际使用的索引
key_len: 索引字段的长度
ref: 列与索引的比较
rows: 扫描出的行数(估算的行数)
filtered: 按表条件过滤的行百分比
Extra: 执行情况的描述和说明
这里面重点需要关注 type, key, Extra
01 Type
开发中比较常见的几个值,性能从好到坏
const->eq_ref->ref->range->index->all
const:表示通过索引一次就找到数据,用于primary key或者unique索引,很快就能找到对应的数据,如图:
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常用于主键或唯一索引扫描。
Ref:非唯一索引扫描,返回匹配的所有行。
两者区别匹配出多条结果时候用到的是否是唯一索引,如图:
range:使用一个索引检索指定范围的行,一般在where语句中会出现between、<、>、in等范围查询,如图:
index:全索引扫描,只遍历索引树,如图:
ALL:全表扫描,找到匹配行。与index比较,ALL需要扫描磁盘数据,index值需要遍历索引树,如图:
02 Key
实际使用到的索引,如果为NULL代表没有使用到索引;这也是平时小伙伴判断是否用上索引的关键
03 Extra
一般常见会出现下面的信息:
Using index:表示查询语句中用到了覆盖索引,不访问表的数据行,查询效率比较好,如图:
如果用SELECT *进行查询,就不会有Using index。
Using filesort:代表MySQL会使用一个外部索引对数据进行排序(文件排序),而不是使用表内索引。这种情况在SQL查询需要避免,最好不要在Extra中出现此类型,如图:
通常会是使用ORDER BY语句导致,上图中使用无索引的字段进行排序会出现。
Using temporary:产生临时表保存中间结果,这种SQL是不允许的,遇见数据量大的场景,就会特别慢,如图:
这种类型常常因为ORDER BY 和 GROUP BY导致,所以在进行数据排序和分组查询时,要注意是否加了索引以及索引是否生效。
04
总结
上面只是列出了一些重要的点,大家可以通过重要信息看出sql是否需要优化,从而定位问题。另外大家如果不好定位哪条sql比较慢的话,可以打开mysql的慢查询来定位到。