调试过程中发现一个很坑的问题,就是临时表关联查询的时候,没有走索引,导致了查询很慢。但是开发环境因为数据比较少,所以开发过程没有发现。测试环境数据多了之后才暴露这个问题,解决方法就是,创建临时表的时候,指定表的字段结构就行了。
CREATE TEMPORARY TABLE IF NOT EXISTS account_temp(account VARCHAR ( 13 ) CHARACTER SET utf8 COLLATE utf8_general_ci) select temp.account from
<foreach collection="list" index="index" item="item" open="(" separator="union" close=")">
select #{item} as account
</foreach>
temp;
CREATE INDEX IX_account_temp ON account_temp(account);
select b.account custAccount, d.dzh_account saleAccount from wk_crm_dzh_customer a
join account_temp b on a.username = b.account
join wk_crm_customer c on a.dzh_customer_id = c.dzh_customer_id and c.business_type = 'VMEX0U'
join wk_admin_user d on c.owner_user_id = d.user_id
where d.dzh_account != '';
DROP TEMPORARY TABLE IF EXISTS account_temp;
注意:关联的结构字段类型要保持一致,字符集和排序规则都要保持一致,才会索引生效