关联查询在 sql 语句中很常见,比如需要的数据分散在多张表中,表之间通过某个字段关联在一起,表和表之间可能有一对一、一对多或多对多的关系。这个时候我们可能需要关联多张表来查询需要的数据。其实关联查询的方式也有缺点。关联的过程中可能让数据库引擎扫描了很多不必要的数据行,降低了数据库缓存的命中率等,从而导致了查询的性能低下。下面通过一个实际案例做一个说明。

功能:根据 phone 和 appCode 两个字段从表中获取最新的「一条」符合条件的数据。

1. 表结构设计如下:

表 ma 和 mau 是一对多的关系,通过表 ma 的 id 字段关联。

2. 编写的 sql 如下

SELECT ma.titleFROM ma FORCE INDEX ( PRIMARY ) INNER JOIN mau ON ma.id = mau.ma_idWHERE mau.phone = #{phone} AND ma.app_code = #{appCode} AND ma.execute_date <= NOW() ORDER BY ma.gmt_create DESC Limit 1

这条 sql 语句,在数据量少的时候性能还是可以的,表 ma 强制走的是主键索引做了全表扫描(这样也是为了避免回表,否则如果走 execute_date 索引还会回表,性能更低)。然后和表 mau 做内连接,最后通过 where 中条件筛选得到最新的一条数据的 title 字段。这条语句虽说走了主键索引,但是表 ma 的数据量增长的很快,全表扫描的行数会越来越多,语句执行的时间逐渐增大,性能也就越来越差。

3. 线上 http 指标监控的性能截图如下:

平均查询时间需要2617 ms,最大的需要 27608 ms

1. 表结构改进:

在表 mau 中冗余了 app_code 字段,这样做是为了方便做关联查询的拆分。

2. 将之前的关联查询做了拆分,拆分 sql 如下:

-- 语句 1-- 根据 app_code 和 phone 在表 mau 中查询最新的一条数据的 ma_idSELECT  ma_id FROM  mauWHERE  mau.app_code = #{appCode} AND mau.phone = #{phone}  ORDER BY mau.gmt_create DESC LIMIT 1

-- 语句 2-- 下面的 id 就是上条语句查询的 ma_idSELECT  ma.titleFROM  maWHERE  ma.id = #{id}

”语句 1“ 中,根据 app_code 和 phone 查询最新的一条消息。表 mau 中的 phone 字段是有 索引的,所以“语句 1” 的性能是比较高的。"语句 2"中根据 “语句 1”查询出的 id 可以直接查询出需要的记录,执行的速度会更快点。

3. 拆分后,线上 http 指标监控的性能截图如下:

从图中看出,平均耗时 17 ms,最大耗时 343 ms 。

通过将一个关联查询拆分成两个简单的查询后,后者数据库引擎扫描的行数明显减少很多,提升了查询的性能。还可以减少锁的竞争。而且这种拆分,相当于我们在应用层使用了哈希关联,来代替关联查询中的嵌套循环连接。提升整体的查询性能。

“余生很长,莫要慌张”

参考:《高性能 MySQL》