mysql 子查询_详解mysql数据库性能优化实验--从两个sql来体会子查询的优化效果...

概述

需求：取出内部员工组(data_type=0)下的用户编号(user_id)，用户名称(user_name)、用户密码( PASSWORD_ENCRYPTED) ， 并 按 照 转正日期 的 时间(regul_date)来进行倒序排列，取出前 20条。

这里大家可以考虑下如果是你，会怎么去实现？

---

方案选择

方案1：

SELECT s.user_id, s.user_name, s.PASSWORD_ENCRYPTED FROM hr_employee h,sys_user s WHERE h.data_type = 0 AND h.EMPLOYEE_CODE = s.EMPLOYEE_CODE ORDER BYh.regul_date DESC  LIMIT 1000,20

方案2：

SELECT s.user_id, s.user_name, s.PASSWORD_ENCRYPTED FROM ( SELECT EMPLOYEE_CODE FROM hr_employee WHERE data_type = 0 ORDER BY regul_date DESC LIMIT 1000, 20 ) h, sys_user s WHERE h.EMPLOYEE_CODE = s.EMPLOYEE_CODE

---

执行计划对比分析

方案1：

解决方案一中的执行计划显示 MySQL 在对其中一个参与 Join 的表(sys_user)利用到了索引，hr_employee表走了全表扫描(其实也可以弄索引)，在参与 Join 前 hr_employee表通过 Where 过滤后的结果集与 user 表进行 Join，最后通过排序取出Join 后结果的“limit 1000,20”条结果返回。

方案2：

解决方案二的 SQL 语句利用到了子查询，所以执行计划会稍微复杂一些，首先可以看到sys_user表和解决方案1一样都利用到了索引(所使用的索引也完全一样)，执行计划显示该子查询以hr_employee表为驱动，也就是先通过employee_code(工号)进行过滤并马上进行这一轮的结果集排序，然后取得了 SQL 中的“limit 1000,20”条结果，最后与sys_user 表进行 Join，得到相应的数据。

通过比较两个解决方案的执行计划，可以看到解决方案一中需要和sys_user表参与Join的记录 数， MySQL 通过统计数据估算出来是8506，也就是通过 hr_employee表返回的所有满足data_type=0的记录数(系统中的实际数据是20000)。而第二种解决方案的执行计划中，sys_user表参与Join的数据就只有850条，可以看到两者相差很大，所以第二种解决方案明显优于第一种解决方案。

---

SQL 实际执行的profile对比

打开 profiling 功能，然后分别执行两个解决方案的 SQL 语句，由 于SQL语句执行所消耗的最大两部分资源就是IO和CPU，所以这里仅列出BLOCK IO和 CPU两项 profile 信息，涉及sql如下

--开启profiling功能及历史条数SET PROFILING=1;SET PROFILING_HISTORY_SIZE=1000;SELECT s.user_id,s.user_name,s.PASSWORD_ENCRYPTED FROMhr_employee h,sys_user s WHERE h.data_type = 0 AND h.EMPLOYEE_CODE = s.EMPLOYEE_CODE ORDER BY h.regul_date DESC LIMIT 1000,20;SELECTs.user_id,s.user_name,s.PASSWORD_ENCRYPTED FROM( SELECT EMPLOYEE_CODE FROM hr_employee WHERE data_type = 0 ORDER BY regul_date DESC LIMIT 1000, 20 ) h,sys_user s WHEREh.EMPLOYEE_CODE = s.EMPLOYEE_CODE;--查看profile信息show profiles;show profile CPU,BLOCK IO for query 329;show profile CPU,BLOCK IO for query 336;

观察两条 SQL 执行中的 IO 消耗，两者区别就在于“Sorting result”，从前面执行计划的对比可以知道两个解决方案的排序过滤数据的时机不一样，排序后需要取得的数据量一个是8506，一个是850，正好和这里的 profile 信息吻合，第一种解决方案的 “Sorting result”的 IO 值是第二种解决方案的将近3倍。

---

结论：

通过上面两条功能完全相同的 SQL 语句的执行计划分析，以及通过实际执行后的profile 数据的验证，都证明了第二种解决方案优于第一种解决方案。

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~