背景

运维反馈说线上一个接口越来越慢，大概二三十秒才有返回。

查看接口代码，定位问题出在sql查询效率上。

sql意图是将多个号码最新的数据返回给前端使用。

单表数据量 530万左右，id为主键，phone为普通索引

优化过程

1. 原sql如下，通过in + 子查询方式查询

select * from
	t_user_track 
where
	id in 
    (select max(id) as id 
  		from t_user_track 
			where phone in ('xxxxx', 'xxxxx') 
			group by phone)

执行时间在30秒左右，

explAIn查看执行计划

可以看出子查询走上了索引，主查询没有走索引，全表查询，rows=2333216

2. 同事写了一段新的sql来查询，如下

select * from (
            select
            DISTINCT *
            from t_user_track
            where
            phone in ('xxxxx', 'xxxx')
            order by locate_time desc
        ) t
        group by phone;

执行时间在4秒左右

查看执行计划

派生表查询走上了phone的索引，rows=157108，主查询没有走上索引，row=157108

3. 改成如下sql，关联表查询

select *
  from t_user_track t1,
    (select max(id) as id from t_user_track 
  			where phone in ('xxxxxx', 'xxxxx') group by phone) t2
where t1.id = t2.id;

查询时间为0.04秒

执行计划如下

4. 改成inner join关联表，如下sql

select * 
  from t_user_track t1 
  INNER JOIN
  (select max(id) as id from t_user_track where phone in ('xxxxxx', 'xxxxx') group by phone) t2
 on t1.id = t2.id

执行时间为0.041秒

执行计划如下

结果

3、4两种关联表查询效率最高

原理分析

1. 3、4两种连表查询只有一次查询，且通过小表驱动大表，所以查询效率较高
2. 第一种in + 子查询的方式，需要两次查询，查询效率较低

留下一个疑问in+子查询的方式，为什么子查询走了索引，主查询却没有走索引

MySQL官方文档

mysql手册也提到过，具体的原文在mysql文档的这个章节：

I.3. Restrictions on Subqueries

13.2.8. Subquery Syntax

摘抄：

1）关于使用IN的子查询：

Subquery optimization for IN is not as effective as for the = operator or for IN(value_list) constructs.

A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

The optimizer rewrites the statement to a correlated subquery:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.

An implication is that an IN subquery can be much slower than a query written using an IN(value_list) construct that lists the same values that the subquery would return.

2）关于把子查询转换成join的：

The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.

An exception occurs for the case where an IN subquery can be rewritten as a SELECT DISTINCT join. Example:

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);

That statement can be rewritten as follows:

SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;

But in this case, the join requires an extra DISTINCT operation and is not more efficient than the subquery