标量子查询使用分享

作者: 陈俊飞 分类: SQL 发布时间: 2017-07-06 21:10

今天来分享一下标量子查询性能分享

下面的两个sql都是计算会员的历史积分总和。

这个都是传统的先链接在分组聚合。

t_user 表1中有4W数据,而t_user_integral积分记录表2有400W条记录。

 

其中t_user 只有主键,t_user_integral有主键和t_user_integral.`userId`上的索引

 

sql1 和sql2 区别在于sql2先对主表进行了分页,在进行链接分组聚合计算,而sql1是先链接分组聚合计算,在进行分页。

-- ------sql1
-- EXPLAIN
SELECT
t.`id`,SUM(ti.`integral`) FROM `t_user` t
LEFT JOIN t_user_integral ti
ON ti.`userId` = t.`id`
GROUP BY t.`id`
ORDER BY t.`id`
LIMIT 0, 1000

-- ------sql2
-- EXPLAIN 
SELECT
tem.`id`,
SUM(ti.`integral`) FROM (
    SELECT t.`id` FROM `t_user` t
    ORDER BY t.`id`
    LIMIT 0, 1000
) tem
LEFT JOIN t_user_integral ti
ON ti.`userId` = tem.`id`
GROUP BY tem.`id`

下面的第三种写法

-- ------sql3
SELECT t.`id`,
(SELECT SUM(ti.`integral`) FROM t_user_integral ti WHERE ti.userId=t.id)
FROM `t_user` t
ORDER BY t.`id`
LIMIT 0, 1000

其中第三种写法的是标量子查询。(汗  之前自己一直叫这种写法为表头子查询,一点都不高大上,而且也找不到参考资料—-)

 

[root@localhost ~]# mysql -h127.0.0.1 -uroot -p123456

mysql> show profiles;
Empty set, 1 warning (0.00 sec)

mysql> use testdata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

 
 mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.01 sec)

mysql> SELECT 
 -> t.`id`,
 -> SUM(ti.`integral`) 
 -> FROM
 -> `t_user` t 
 -> LEFT JOIN t_user_integral ti 
 -> ON ti.`userId` = t.`id` 
 -> GROUP BY t.`id` 
 -> ORDER BY t.`id` 
 -> LIMIT 0, 1000 ;

mysql> SELECT 
 -> tem.`id`,
 -> SUM(ti.`integral`) 
 -> FROM
 -> (SELECT 
 -> t.`id` 
 -> FROM
 -> `t_user` t 
 -> ORDER BY t.`id` 
 -> LIMIT 0, 1000) tem 
 -> LEFT JOIN t_user_integral ti 
 -> ON ti.`userId` = tem.`id` 
 -> GROUP BY tem.`id` ;
mysql> SELECT t.`id`,
 -> (SELECT SUM(ti.`integral`) FROM t_user_integral ti WHERE ti.userId=t.id)
 -> FROM `t_user` t
 -> ORDER BY t.`id`
 -> LIMIT 0, 1000;

mysql> show profiles;
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.02247650 | SELECT  t.`id`, SUM(ti.`integral`) FROM `t_user` t  LEFT JOIN t_user_integral ti  ON ti.`userId` = t.`id` GROUP BY t.`id` ORDER BY t.`id` LIMIT 0, 1000 |
| 2 | 0.02934800 | SELECT  tem.`id`, SUM(ti.`integral`) FROM (SELECT  t.`id`  FROM `t_user` t  ORDER BY t.`id`  LIMIT 0, 1000) tem  LEFT JOIN t_user_integral ti  ON ti.`userId` = tem.`id` GROUP BY tem.`id` |
| 3 | 0.02262500 | SELECT t.`id`,(SELECT SUM(ti.`integral`) FROM t_user_integral ti WHERE ti.userId=t.id) FROM `t_user` t ORDER BY t.`id` LIMIT 0, 1000 |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)


mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 1.90198625 | SELECT t.`id`, SUM(ti.`integral`) FROM `t_user` t LEFT JOIN t_user_integral ti ON ti.`userId` = t.`id` GROUP BY t.`id` ORDER BY t.`id` LIMIT 0, 5000 |
| 2 | 0.07350825 | SELECT tem.`id`, SUM(ti.`integral`) FROM (SELECT t.`id` FROM `t_user` t ORDER BY t.`id` LIMIT 0, 5000) tem LEFT JOIN t_user_integral ti ON ti.`userId` = tem.`id` GROUP BY tem.`id` |
| 3 | 0.05667700 | SELECT t.`id`,(SELECT SUM(ti.`integral`) FROM t_user_integral ti WHERE ti.userId=t.id) FROM `t_user` t ORDER BY t.`id` LIMIT 0, 5000 |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> show profiles;
+----------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 247.07626525 | SELECT t.`id`, SUM(ti.`integral`) FROM `t_user` t LEFT JOIN t_user_integral ti ON ti.`userId` = t.`id` GROUP BY t.`id` ORDER BY t.`id` |
| 2 | 232.77228675 | SELECT tem.`id`, SUM(ti.`integral`) FROM (SELECT t.`id` FROM `t_user` t ORDER BY t.`id` ) tem LEFT JOIN t_user_integral ti ON ti.`userId` = tem.`id` GROUP BY tem.`id` |
| 3 | 320.77826875 | SELECT t.`id`,(SELECT SUM(ti.`integral`) FROM t_user_integral ti WHERE ti.userId=t.id) FROM `t_user` t ORDER BY t.`id` |
+----------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

这三种写法在执行上根据数据量情况查询性能还有有差异的。

sql1链接最多,在不分页情况下,查询速度比sql2快,比sql3快;在分页情况下,比sql2慢,比sql3慢

sql2链接的数据是最少的,在不分页的情况下,查询速度也比sql1慢,比sql3块;分页情况下表现比sql1快,比sql3慢

sql3链接情况视主表差距数据情况,在不分页的情况下,查询速度也比sql1慢,比sql2慢(而且是非常慢,千万不要用);分页情况下表现比sql1快,比sql3快

其实sql3关键在于主表查询出来的数据量。如果很少,比如是分页,每页10条话,这个效率其实比较高。如果做统计报表要全查数据,千万不要用标量子查询,如果是标量子查询数据很少比如是字典表数据集只有几百条,也可以考虑,分情况吧。

所以建议标量子查询可以在分页查询sql里面使用。

另外在有些分页ORM框架中,标量子查询的sql在统计count的时候会有坑,尽量在可以控制全部sql的ORM框架中使用标量子查询。比如mybatis、spring jdbc template 中,hibernate慎用。

好吧今天分享就到这里了。