Mysql数据库性能优化之子查询

Mysql数据库性能优化之子查询

什么是子查询？

以一个完整的 SELECT 语句为基础，嵌套一个子 SELECT 语句，这个子 SELECT 语句被用作基础 SELECT 语句中的一个条件或表达式，就叫做子查询。 子查询可以出现在 SELECT、FROM、WHERE、HAVING、SET 和 VALUES 等子句中，常见的有 exists、IN 和子查询作为派生表使用等等。

子查询优化原则？

通常情况下子查询不能避免的，只能通过各种优化手段降低其性能损耗。

常见的优化手段如下：

• 避免使用大量的子查询，这样会增加不必要的查询负载
• 子查询尽量使用 INNER JOIN 等连接操作实现，这样能提高效率
• 子查询的结果可以缓存到MEMORY中提高查询速度
• 应该尽量避免在子查询中使用通配符%，因为对于每个包含通配符的列，MySQL将扫描整个表来执行查询
• 子查询中的 "IN" 写法尽量改为 "EXISTS" ，因为 "IN" 的查询方式需要做排序，性能比较低

示例1：用join和子查询对比查询优化

查询一个用户列表和他们的订单数量，并按订单数量排序。

首先考虑使用子查询的方式实现：

SELECT u.id, u.name, (SELECT COUNT(*) FROM order WHERE uid = u.id) AS order_count
FROM user AS u
ORDER BY (SELECT COUNT(*) FROM order WHERE uid = u.id) DESC

显然，这个查询使用了两次子查询，并且排序也要用到子查询，性能不高。

我们可以使用 LEFT JOIN 代替查询语句中的子查询部分，有如下优化效果：

SELECT
  u.id,
  u.name,
  COUNT(o.id) AS order_count
FROM
  user AS u
  LEFT JOIN order AS o
    ON u.id = o.uid
GROUP BY u.id, u.name
ORDER BY order_count DESC

这个查询语句只使用了一次 LEFT JOIN， 显然比前一个查询语句要快。

示例2：用exists代替in

题目：查询所有有课程成绩在 top 10% 的学生名称及课程名。

如果我们使用 IN 子查询，语句如下：

SELECT s.name, c.name
FROM student AS s 
INNER JOIN score AS sc ON s.id = sc.sid 
INNER JOIN course AS c ON c.id = sc.cid 
WHERE sc.score IN (
    SELECT * FROM (
        SELECT score FROM score 
        ORDER BY score DESC 
        LIMIT 10*totcount/100, 1000000
    ) AS tmp
)

这条语句会导致运行缓慢，因为 IN 语句中用到了子查询。

我们可以使用 EXISTS 代替 IN 语句，如下：

SELECT s.name, c.name
FROM student AS s 
INNER JOIN score AS sc ON s.id = sc.sid 
INNER JOIN course AS c ON c.id = sc.cid 
WHERE EXISTS (
    SELECT * FROM (
        SELECT score FROM score 
        ORDER BY score DESC 
        LIMIT 10*totcount/100, 1000000
    ) AS tmp
    WHERE sc.score = tmp.score
)

这个语句使用了 EXISTS 而不是 IN 语句，经过测试在性能上要快很多。

总结

在使用数据库时，避免使用过多的子查询，合理使用 join 查询，尽量使用 exists 代替 in 语句，这些都是优化数据库查询性能的常用方法。