记一次MySQL查询优化 | 顾澜的技术小站

起因：

快下班的时候被同事A叫住，说是某个连表查询导致整个程序卡住，连Debug都停止了，让我帮忙瞅瞅，本着乐于助人的精神，我爽快的答应了。

排查：

大致代码如下:

func GetCustomerInfo(){
    ....
    db.Select(".......")
	db.Joins("LEFT JOIN customer_info ON customer_info.customer_id = customer.id")
	db.Joins("LEFT JOIN customer_group ON  customer_group.id = customer.group_id ")
	db.Joins("LEFT JOIN vest_customer_relation ON vest_customer_relation.customer_id = customer.id") // 新加的代码
    ......
    err = db.Debug()Order("customer.id DESC").Limit(limit).Offset(offset).Find(&customers).Error
    return customers, err
}

因为同事A的描述是加了新的连表导致程序直接卡住，再加上打断点调试时都是走到最后一步卡住，倒也没有考虑可能是SQL的问题，甚至觉得是Gorm里面什么奇奇怪怪的错误导致的(原因大概是因为Gorm的Debug()没有触发，后续猜想应该是需要语句执行完毕才会打印出对应的SQL)。

因为上诉排查无果，也猜想到可能是因为慢SQL的原因，所以随即手写SQL测试：

SELECT * FROM `customer`
	LEFT JOIN customer_info ON customer_info.customer_id = customer.id
	LEFT JOIN customer_group ON customer_group.id = customer.group_id
	LEFT JOIN vest_customer_relation ON vest_customer_relation.customer_id = customer.id
WHERE
	`customer`.`deleted_at` IS NULL
ORDER BY
	customer.id DESC
	LIMIT 10

然后，很久过去了……。这是一个慢SQL确认无疑，不过另我好奇的是不过多join了一个表而已，为何会这么夸张，让我祭出大杀器EXPLAIN：

EXPLAIN SELECT * FROM `customer`
	LEFT JOIN customer_info ON customer_info.customer_id = customer.id
	LEFT JOIN customer_group ON customer_group.id = customer.group_id
	LEFT JOIN vest_customer_relation ON vest_customer_relation.customer_id = customer.id
WHERE
	`customer`.`deleted_at` IS NULL
ORDER BY
	customer.id DESC
	LIMIT 10

从上图不难看出对于表customer与vest_customer_relation为全表扫描，customer倒是理所应当，但是对表vest_customer_relation也全表扫描就属实有点离谱了，因为Mysql默认连接方式为笛卡尔积，所以上诉SQL运行时扫描的数据为33437 * 2 * 1 * 64686，大概40亿的样子，而且据我所知，同事A的业务要写完还需要连接一个表，无论为未写上去的表被扫描的数量是多少，后续的增长都是以40亿为单位，这都是一个非常可怕的数量。

解决：

仔细观察EXPLAIN的结果得知，表vest_customer_relation是没有索引的，所以每次连接表的时候都会去全表扫描，这才导致了一次查询扫描了40亿条数据，为表vest_customer_relation加上索引即可：

1 2	ALTER TABLE `gva`.`vest_customer_relation` ADD INDEX `vest_customer_relation_customer_id_index`(`customer_id`) USING BTREE;

此时执行上文SQL查看效果：

可以明显看到，新建的索引精准命中，表vest_customer_relation只扫描了一行，总扫描条数也就降到了3W 左右，达到了理想的状态。

思考：

到了这里，问题已经得到了解决，但却不是最优的解决方案，由于用户数据增加的缘故，数据量还会继续增加，如果以后每次遇到类似的问题都通过索引来解决的话，显然不是最佳方案，索引滥用也会导致各种问题。业务问题业务解决，我们应该避免更多笛卡尔积的产生，将SQL拆分，通过业务代码将数据组装才是最佳的解决方案，简单拆分上诉SQL得到：

EXPLAIN SELECT * FROM `customer`
	LEFT JOIN customer_info ON customer_info.customer_id = customer.id
	LEFT JOIN customer_group ON customer_group.id = customer.group_id
WHERE
	`customer`.`deleted_at` IS NULL
ORDER BY
	customer.id DESC
	LIMIT 10;
EXPLAIN SELECT * FROM `vest_customer_relation` WHERE customer_id in (3,4,5,6,7,8,9,10,11,12)