随便看看

workbench 下载地址
https://dev.mysql.com/downloads/workbench/

环境

虚拟机： 16C 32G
mysql： percona 5.7.19

测试

CREATE TABLE `a` (
`account` varchar(50) NOT NULL,
KEY `idx_1` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `b` (
`stu_account` varchar(50) NOT NULL,
KEY `idx_1` (`stu_account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into a 
select account from sys_depart sd 
inner join sys_user_detail sud on sd.code = sud.faculty_code；
Records: 13423  

insert into b
select stu_account from alert_result ar
inner join sys_user su on su.id = ar.stu_id and su.user_type = 2 and su.status = 0
inner join sys_depart_user sdu on ar.stu_id = sdu.user_id	
Records: 98022 

执行计划

View Source 就是看format = json

MySQL中优化器改写SQL,看下面的 JOIN_condition_to_WHERE

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
                            QUERY: select count(1) from alert_result ar inner join sys_user_detail sud on ar.stu_account = sud.account inner join sys_depart sd on sd.code = sud.faculty_code
                            TRACE: {
"steps": [
    {
    "join_preparation": {
        "select#": 1,
        "steps": [
        {
            "expanded_query": "/* select#1 */ select count(1) AS `count(1)` from ((`alert_result` `ar` join `sys_user_detail` `sud` on((`ar`.`stu_account` = `sud`.`account`))) join `sys_depart` `sd` on((`sd`.`code` = `sud`.`faculty_code`)))"
        },
        {
            "transformations_to_nested_joins": {
            "transformations": [
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
            ] /* transformations */,
            "expanded_query": "/* select#1 */ select count(1) AS `count(1)` from `alert_result` `ar` join `sys_user_detail` `sud` join `sys_depart` `sd` where ((`sd`.`code` = `sud`.`faculty_code`) and (`ar`.`stu_account` = `sud`.`account`))"
            } /* transformations_to_nested_joins */
        }
        ] /* steps */
    } /* join_preparation */
    },