zjdev 正常访问；
mysql> explain SELECT temp.* ,
-> (SELECT COUNT(sn) FROM AssignClientManager WHERE FIND_IN_SET(clientManagerSn,temp.managerSnlist)>0) clientAccount,
-> (SELECT
-> IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)
-> FROM
-> ProductRepayment pr ,ProductRepay p
-> WHERE
-> pr.productSn = p.productSn AND
-> pr.clientSn IN (SELECT clientSn FROM AssignClientManager WHERE FIND_IN_SET(clientManagerSn,temp.managerSnlist)>0 )
-> AND pr.status = 1 AND DATEDIFF(p.realValueEndDate,CURDATE()) <=7 AND DATEDIFF(p.realValueEndDate,CURDATE())>=0
-> ) investBalance,
-> (SELECT
-> IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0) + temp.availableBalance + temp.frozenWithDraw + temp.frozenPay
-> FROM
-> ProductRepayment
-> WHERE
-> clientSn = temp.sn IN (SELECT clientSn FROM AssignClientManager WHERE FIND_IN_SET(clientManagerSn,temp.managerSnlist)>0 )
-> AND STATUS = 1) totalBalance,
-> (SELECT
-> IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)
-> FROM
-> ProductRepayment pr
-> WHERE
-> pr.clientSn IN (SELECT clientSn FROM AssignClientManager WHERE FIND_IN_SET(clientManagerSn,temp.managerSnlist)>0 ) AND pr.status =1
-> ) unclearedBalance
-> FROM(
-> SELECT c.sn,cm.`parent`,c.`mobilePhone`,c.`userNick`,cp.`personName`,loadTreeByParent(cm.sn) AS managerSnlist,
-> cai.`availableBalance`,cai.`frozenWithDraw`,cai.`frozenPay`
-> FROM `Client` c
-> LEFT JOIN ClientManager cm ON c.`sn` = cm.`sn`
-> LEFT JOIN ClientPersonalInfo cp ON cp.`clientSn` = c.`sn`
-> LEFT JOIN ClientAssetInfo cai ON cai.`clientSn` = c.`sn`
-> WHERE cm.parent = 1496 ) temp
-> ;
+----+--------------------+---------------------+--------+---------------+---------+---------+-------------------+------+-------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------------+--------+---------------+---------+---------+-------------------+------+-------------------------------------------------------------------+
| 1 | PRIMARY | <derived9> | ALL | NULL | NULL | NULL | NULL | 56 | NULL |
| 9 | DERIVED | cm | ALL | PRIMARY | NULL | NULL | NULL | 56 | Using where |
| 9 | DERIVED | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | NULL |
| 9 | DERIVED | cp | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | NULL |
| 9 | DERIVED | cai | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | NULL |
| 7 | DEPENDENT SUBQUERY | AssignClientManager | ALL | NULL | NULL | NULL | NULL | 2126 | Using where; Start temporary |
| 7 | DEPENDENT SUBQUERY | pr | ALL | NULL | NULL | NULL | NULL | 5699 | Using where; End temporary; Using join buffer (Block Nested Loop) |
| 5 | DEPENDENT SUBQUERY | ProductRepayment | ALL | NULL | NULL | NULL | NULL | 5699 | Using where |
| 6 | DEPENDENT SUBQUERY | AssignClientManager | ALL | NULL | NULL | NULL | NULL | 2126 | Using where |
| 3 | DEPENDENT SUBQUERY | pr | ALL | NULL | NULL | NULL | NULL | 5699 | Using where |
| 3 | DEPENDENT SUBQUERY | p | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | AssignClientManager | ALL | NULL | NULL | NULL | NULL | 2126 | Using where; FirstMatch(p); Using join buffer (Block Nested Loop) |
| 2 | DEPENDENT SUBQUERY | AssignClientManager | ALL | NULL | NULL | NULL | NULL | 2126 | Using where |
+----+--------------------+---------------------+--------+---------------+---------+---------+-------------------+------+-------------------------------------------------------------------+
13 rows in set (0.00 sec)
mysql select_type :
<derived9> 派生表
DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）
DEPENDENT SUBQUERY：子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）
+----+--------------------+---------------------+------------+--------+---------------+---------+---------+-------------------+------+----------+--------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------------------+------------+--------+---------------+---------+---------+-------------------+------+----------+--------------------------------------------------------------------+
| 1 | PRIMARY | cm | NULL | ALL | PRIMARY | NULL | NULL | NULL | 92 | 10.00 | Using where |
| 1 | PRIMARY | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | 100.00 | NULL |
| 1 | PRIMARY | cp | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | 100.00 | NULL |
| 1 | PRIMARY | cai | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | 100.00 | NULL |
| 7 | DEPENDENT SUBQUERY | pr | NULL | ALL | NULL | NULL | NULL | NULL | 7559 | 10.00 | Using where |
| 7 | DEPENDENT SUBQUERY | AssignClientManager | NULL | ALL | NULL | NULL | NULL | NULL | 2817 | 10.00 | Using where; FirstMatch(pr); Using join buffer (Block Nested Loop) |
| 5 | DEPENDENT SUBQUERY | ProductRepayment | NULL | ALL | NULL | NULL | NULL | NULL | 7559 | 1.00 | Using where |
| 6 | DEPENDENT SUBQUERY | AssignClientManager | NULL | ALL | NULL | NULL | NULL | NULL | 2817 | 10.00 | Using where |
| 3 | DEPENDENT SUBQUERY | pr | NULL | ALL | NULL | NULL | NULL | NULL | 7559 | 10.00 | Using where |
| 3 | DEPENDENT SUBQUERY | p | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | AssignClientManager | NULL | ALL | NULL | NULL | NULL | NULL | 2817 | 10.00 | Using where; FirstMatch(p); Using join buffer (Block Nested Loop) |
| 2 | DEPENDENT SUBQUERY | AssignClientManager | NULL | ALL | NULL | NULL | NULL | NULL | 2817 | 100.00 | Using where |
+----+--------------------+---------------------+------------+--------+---------------+---------+---------+-------------------+------+----------+--------------------------------------------------------------------+
mysql> explain SELECT temp.* ,
-> (SELECT COUNT(sn) FROM AssignClientManager WHERE FIND_IN_SET(clientManagerSn,temp.managerSnlist)>0) clientAccount
->
->
-> FROM (
-> SELECT c.sn,cm.`parent`,c.`mobilePhone`,c.`userNick`,cp.`personName`,loadTreeByParent(cm.sn) AS managerSnlist,
-> cai.`availableBalance`,cai.`frozenWithDraw`,cai.`frozenPay`
-> FROM `Client` c
-> LEFT JOIN ClientManager cm ON c.`sn` = cm.`sn`
-> LEFT JOIN ClientPersonalInfo cp ON cp.`clientSn` = c.`sn`
-> LEFT JOIN ClientAssetInfo cai ON cai.`clientSn` = c.`sn`
-> WHERE cm.parent = 1496 ) temp
-> ;
+----+--------------------+---------------------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------------------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| 1 | PRIMARY | cm | NULL | ALL | PRIMARY | NULL | NULL | NULL | 92 | 10.00 | Using where |
| 1 | PRIMARY | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | 100.00 | NULL |
| 1 | PRIMARY | cp | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | 100.00 | NULL |
| 1 | PRIMARY | cai | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | AssignClientManager | NULL | ALL | NULL | NULL | NULL | NULL | 2817 | 100.00 | Using where |
+----+--------------------+---------------------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
5 rows in set, 2 warnings (0.02 sec)
DEPENDENT SUBQUERY | AssignClientManager 表示依赖子查询
所以这里是1驱动2
type ALL
全表扫描，MySQL 从头到尾扫描整张表查找行。
mysql> explain SELECT temp.* ,
-> (SELECT COUNT(sn) FROM AssignClientManager WHERE FIND_IN_SET(clientManagerSn,temp.managerSnlist)>0) clientAccount
->
->
-> FROM (
-> SELECT c.sn,cm.`parent`,c.`mobilePhone`,c.`userNick`,cp.`personName`,loadTreeByParent(cm.sn) AS managerSnlist,
-> cai.`availableBalance`,cai.`frozenWithDraw`,cai.`frozenPay`
-> FROM `Client` c
-> LEFT JOIN ClientManager cm ON c.`sn` = cm.`sn`
-> LEFT JOIN ClientPersonalInfo cp ON cp.`clientSn` = c.`sn`
-> LEFT JOIN ClientAssetInfo cai ON cai.`clientSn` = c.`sn`
-> WHERE cm.parent = 1496 ) temp
-> ;
+----+--------------------+---------------------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------------+--------+---------------+---------+---------+------------+------+-------------+
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 56 | NULL |
| 3 | DERIVED | cm | ALL | PRIMARY | NULL | NULL | NULL | 56 | Using where |
| 3 | DERIVED | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | NULL |
| 3 | DERIVED | cp | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | NULL |
| 3 | DERIVED | cai | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cm.sn | 1 | NULL |
| 2 | DEPENDENT SUBQUERY | AssignClientManager | ALL | NULL | NULL | NULL | NULL | 2126 | Using where |
+----+--------------------+---------------------+--------+---------------+---------+---------+------------+------+-------------+
6 rows in set (0.00 sec)
这里 DEPENDENT SUBQUERY | AssignClientManager 驱动表是3
id是一组数字，表示查询中执行select子句或操作表的顺序。
如果id相同，则执行顺序从上至下。
如果是子查询，id的序号会递增，id越大则优先级越高，越先会被执行。
id如果相同，则可以认为是一组，从上往下顺序执行，所有组中，id越高，优先级越高，越容易执行。
驱动表为ID=3 cm:
cm，c，cp cai执行出来的结果 再去驱动2
id为3 出来的结果
就是1
<derived3> 指向3 表示 是3产生的派生表
mysql> explain SELECT COUNT(sn) FROM AssignClientManager where clientSn>5;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | AssignClientManager | NULL | ALL | NULL | NULL | NULL | NULL | 2817 | 33.33 | Using where |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
type ALL
全表扫描，MySQL 从头到尾扫描整张表查找行。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
