优化sql存在多数据库时，报【错误日志:Table 'db1.tableA' doesn't exist】错误
weeway opened this issue · 0 comments
weeway commented
sql
SELECT * FROM `db2`.`tableA` AS a
LEFT JOIN `db1`.`tableB` AS b ON a.company_code = b.CompanyCode
WHERE a.company_code = 'xxx' AND a.company_type = '1'
具体日志（对关键信息做了替换）
/data/sqladvisor/SQLAdvisor-master/sqladvisor/sqladvisor -h xxx -P 3306 -u xxx -p xxx -d db1 -q
"SELECT * FROM \`db2\`.\`tableA\` AS a
LEFT JOIN \`db1\`.\`tableB\` AS b ON a.company_code = b.CompanyCode
WHERE a.company_code = 'xxx' AND a.company_type = '1'" -v 1
2022-09-29 17:20:47 24980 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*`
from (`db2`.`tableA` `a` left join `db1`.`tableB` `b` on((`a`.`company_code` = `b`.`CompanyCode`)))
where ((`a`.`company_code` = 'xxx') and (`a`.`company_type` = '1'))
2022-09-29 17:20:47 24980 [Note] 第2步：开始解析where中的条件:(`a`.`company_code` = 'xxx')
2022-09-29 17:20:47 24980 [Note] show index from tableA
2022-09-29 17:20:47 24980 [Note] show table status like 'table'
2022-09-29 17:20:47 24980 [Note] select count(*) from ( select `company_code` from `tableA` FORCE INDEX( PRIMARY ) order by company_id DESC limit 1234) `a` where (`a`.`company_code` = 'xxx')
2022-09-29 17:20:47 24980 [Note] 第3步：表tableA的行数:1234,limit行数:1234,得到where条件中(`a`.`company_code` = 'xxx')的选择度:1234
2022-09-29 17:20:47 24980 [Note] 第4步：开始解析where中的条件:(`a`.`company_type` = '1')
2022-09-29 17:20:47 24980 [Note] show index from tableA
2022-09-29 17:20:47 24980 [Note] show table status like 'tableA'
2022-09-29 17:20:47 24980 [Note] select count(*) from ( select `company_type` from `tableA` FORCE INDEX( PRIMARY ) order by company_id DESC limit 1234) `a` where (`a`.`company_type` = '1')
2022-09-29 17:20:47 24980 [Note] 第5步：表tableA的行数:1234,limit行数:1234,得到where条件中(`a`.`company_type` = '1')的选择度:1
2022-09-29 17:20:47 24980 [Note] 第6步：开始解析join on条件:a.company_code=b.CompanyCode
2022-09-29 17:20:47 24980 [Note] 第7步：开始选择驱动表,一共有1个候选驱动表
2022-09-29 17:20:47 24980 [Note] explain select * from tableA as a where (`a`.`company_code` = 'xxx')
2022-09-29 17:20:47 24980 [Note] 第8步：SQLAdvisor结束！错误日志:Table 'db1.tableA' doesn't exist