mysql 哈希关联,MySQL 性能优化实践 - 分解关联查询

关联查询在 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》