mysql 查询优化案例
2024-09-09 16:34:13
mysql> explain SELECT c.`sn` clientSn,asm.`clientManagerSn`,pry.`productSn`,1 TYPE,pr.`capitalBalance`,pr.`yieldBalance`,pry.`realValueEndDate`,pr.`status`
-> FROM `ProductRepayment` pr
-> LEFT JOIN `ProductRepay` pry ON pry.`productSn` = pr.`productSn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = pr.`productSn`
-> LEFT JOIN `Client` c ON c.`sn` = pr.`clientSn`
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`=pr.`clientSn`
-> WHERE pa.`supportTransfer` =2
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,hd.productSn,2,thd.capitalBalance,thd.yieldBalance,thd.settlementDate,IF(thd.status =1 OR thd.status=2,1,2)
-> FROM `TransferProduct` tp
-> LEFT JOIN `TransferProductHolding` thd ON thd.transferProductSn = tp.sn
-> LEFT JOIN `ClientProductHolding` hd ON hd.sn = tp.holdingSn
-> LEFT JOIN `Client` c ON c.`sn` = thd.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = hd.productSn
-> WHERE pa.`supportTransfer` =1
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,ch.productSn,3,ch.capitalBalance,ch.yieldBalance,pr.settlementDate,ch.status-1
-> FROM `ClientHolding` ch
-> LEFT JOIN `Client` c ON c.`sn` = ch.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = ch.productSn
-> LEFT JOIN `ProductRepay` pr ON pr.`productSn` = pa.`productSn`
-> WHERE pa.`supportTransfer` =1
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,ci.productSn,1,ci.investAmount,NULL,NULL,0
-> FROM `ClientInvestOrder` ci
-> LEFT JOIN `Client` c ON c.`sn` = ci.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`;
+----+--------------+----------------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+----------------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------------+
| 1 | PRIMARY | pr | ALL | NULL | NULL | NULL | NULL | 7081 | NULL |
| 1 | PRIMARY | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | Using where |
| 1 | PRIMARY | pry | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | NULL |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.clientSn | 1 | Using index |
| 1 | PRIMARY | asm | ALL | NULL | NULL | NULL | NULL | 4618 | Using where; Using join buffer (Block Nested Loop) |
| 2 | UNION | tp | ALL | NULL | NULL | NULL | NULL | 53 | NULL |
| 2 | UNION | hd | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.tp.holdingSn | 1 | NULL |
| 2 | UNION | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.hd.productSn | 1 | Using where |
| 2 | UNION | thd | ALL | NULL | NULL | NULL | NULL | 78 | Using where; Using join buffer (Block Nested Loop) |
| 2 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.thd.clientSn | 1 | Using index |
| 2 | UNION | asm | ALL | NULL | NULL | NULL | NULL | 4618 | Using where; Using join buffer (Block Nested Loop) |
| 3 | UNION | ch | ALL | NULL | NULL | NULL | NULL | 6426 | NULL |
| 3 | UNION | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.productSn | 1 | Using where |
| 3 | UNION | pr | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.productSn | 1 | NULL |
| 3 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.clientSn | 1 | Using index |
| 3 | UNION | asm | ALL | NULL | NULL | NULL | NULL | 4618 | Using where; Using join buffer (Block Nested Loop) |
| 4 | UNION | ci | ALL | NULL | NULL | NULL | NULL | 7258 | NULL |
| 4 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ci.clientSn | 1 | Using index |
| 4 | UNION | asm | ALL | NULL | NULL | NULL | NULL | 4618 | Using where; Using join buffer (Block Nested Loop) |
| NULL | UNION RESULT | <union1,2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+----------------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------------+
20 rows in set (0.00 sec) 创建索引； mysql> create index AssignClientManager_idx1 on AssignClientManager(clientSn);
mysql> explain SELECT c.`sn` clientSn,asm.`clientManagerSn`,pry.`productSn`,1 TYPE,pr.`capitalBalance`,pr.`yieldBalance`,pry.`realValueEndDate`,pr.`status`
-> FROM `ProductRepayment` pr
-> LEFT JOIN `ProductRepay` pry ON pry.`productSn` = pr.`productSn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = pr.`productSn`
-> LEFT JOIN `Client` c ON c.`sn` = pr.`clientSn`
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`=pr.`clientSn`
-> WHERE pa.`supportTransfer` =2
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,hd.productSn,2,thd.capitalBalance,thd.yieldBalance,thd.settlementDate,IF(thd.status =1 OR thd.status=2,1,2)
-> FROM `TransferProduct` tp
-> LEFT JOIN `TransferProductHolding` thd ON thd.transferProductSn = tp.sn
-> LEFT JOIN `ClientProductHolding` hd ON hd.sn = tp.holdingSn
-> LEFT JOIN `Client` c ON c.`sn` = thd.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = hd.productSn
-> WHERE pa.`supportTransfer` =1
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,ch.productSn,3,ch.capitalBalance,ch.yieldBalance,pr.settlementDate,ch.status-1
-> FROM `ClientHolding` ch
-> LEFT JOIN `Client` c ON c.`sn` = ch.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = ch.productSn
-> LEFT JOIN `ProductRepay` pr ON pr.`productSn` = pa.`productSn`
-> WHERE pa.`supportTransfer` =1
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,ci.productSn,1,ci.investAmount,NULL,NULL,0
-> FROM `ClientInvestOrder` ci
-> LEFT JOIN `Client` c ON c.`sn` = ci.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`;
+----+--------------+----------------+--------+--------------------------+--------------------------+---------+-------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+----------------+--------+--------------------------+--------------------------+---------+-------------------+------+----------------------------------------------------+
| 1 | PRIMARY | pr | ALL | NULL | NULL | NULL | NULL | 7081 | NULL |
| 1 | PRIMARY | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | Using where |
| 1 | PRIMARY | pry | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | NULL |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.clientSn | 1 | Using index |
| 1 | PRIMARY | asm | ref | AssignClientManager_idx1 | AssignClientManager_idx1 | 4 | zjzc.pr.clientSn | 1 | NULL |
| 2 | UNION | tp | ALL | NULL | NULL | NULL | NULL | 53 | NULL |
| 2 | UNION | hd | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.tp.holdingSn | 1 | NULL |
| 2 | UNION | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.hd.productSn | 1 | Using where |
| 2 | UNION | thd | ALL | NULL | NULL | NULL | NULL | 78 | Using where; Using join buffer (Block Nested Loop) |
| 2 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.thd.clientSn | 1 | Using index |
| 2 | UNION | asm | ref | AssignClientManager_idx1 | AssignClientManager_idx1 | 4 | zjzc.c.sn | 1 | NULL |
| 3 | UNION | ch | ALL | NULL | NULL | NULL | NULL | 6426 | NULL |
| 3 | UNION | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.productSn | 1 | Using where |
| 3 | UNION | pr | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.productSn | 1 | NULL |
| 3 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.clientSn | 1 | Using index |
| 3 | UNION | asm | ref | AssignClientManager_idx1 | AssignClientManager_idx1 | 4 | zjzc.c.sn | 1 | NULL |
| 4 | UNION | ci | ALL | NULL | NULL | NULL | NULL | 7258 | NULL |
| 4 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ci.clientSn | 1 | Using index |
| 4 | UNION | asm | ref | AssignClientManager_idx1 | AssignClientManager_idx1 | 4 | zjzc.c.sn | 1 | NULL |
| NULL | UNION RESULT | <union1,2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+----------------+--------+--------------------------+--------------------------+---------+-------------------+------+----------------------------------------------------+
20 rows in set (0.00 sec)
最新文章
- iOS开发中的这些权限，你搞懂了吗？
- 微信小程序之登录态维护(十一)
- css笔记--web端小于1px设计的处理方法
- SQL Server分区动态生成脚本（三）（按年份划分）
- linux添加JAVA环境变量
- 【剑指offer】面试题28：字符串的排列
- Android开发之异步具体解释（二）之AsyncTask
- 一个请求中，ADF、JSF究竟做了哪些工作
- 使用dropwizard(4)-加入测试-jacoco代码覆盖率
- NGINX压力测试
- 如何在Java中避免equals方法的隐藏陷阱
- adb 查看 android手机的CPU架构
- 第四周Java作业
- python链接mysql获得某列最大值
- vs2015 加载项目的时启动：无法启动 IIS Express Web 服务器
- java_oop_类与对象
- 【刷题】LOJ 6012 「网络流 24 题」分配问题
- Delphi中,除了应用程序主窗口会显示在任务栏上,其它窗口默认都不会显示在任务栏.
- Express应用程序目录结构
- mysql数据类型及存储过程
热门文章
- [置顶] Android开发之XML文件的解析
- 怎样删除在Github中创建的项目
- Unity3d 网络编程（二）（Unity3d内建网络各项參数介绍）
- ubuntu apt-get 遇到的问题
- 【开源框架】Android之史上最全最简单最有用的第三方开源库收集整理，有助于快速开发，欢迎各位网友补充完善
- tomcat设置IP地址或者域名访问
- Android 程式开发：（廿一）消息传递 —— 21.3 使用Intent发送短信
- Win8.1应用开发之动态磁贴
- 《Javascript权威指南》学习笔记之十二：数组、多维数组和符合数组（哈希映射）
- 终于懂了：TWinControl主要是Delphi官方用来封装Windows的官方控件，开发者还是应该是有TCustomControl来开发三方控件