核心就是 流向表 两百多万 左联 一个80多万的表 导致速度很慢 索引也已经加上了
explain SELECT * from flow_baier_id f
LEFT JOIN md_peroid p on p.`year` = left(f.ddimonth, 4) and p.month = cast(substring(f.ddimonth,-2,2) as SIGNED INTEGER)
LEFT JOIN distributor d on d.id = f.fromid
LEFT JOIN v_product pro on pro.id = f.productid
LEFT JOIN md_customers c on c.id = f.downid
explain SELECT * from flow_baier_id f
LEFT JOIN md_peroid p on p.`year` = left(f.ddimonth, 4) and p.month = cast(substring(f.ddimonth,-2,2) as SIGNED INTEGER)
LEFT JOIN distributor d on d.id = f.fromid
LEFT JOIN v_product pro on pro.id = f.productid
LEFT JOIN md_customers c on c.id = f.downid
EXPLAIN SELECT
`f`.`id` AS `id`,
`f`.`ddimonth` AS `ddimonth`,
`f`.`bizdate` AS `bizdate`,
`p`.`id` AS `peroidid`,
`p`.`year` AS `year`,
`p`.`quarter` AS `quarter`,
`p`.`month` AS `month`,
`p`.`quarterno` AS `quarterno`,
`p`.`monthno` AS `monthno`,
`d`.`id` AS `distributorid`,
`d`.`name` AS `distributorname`,
`d`.`level` AS `distributorlevel`,
`d`.`type` AS `distributortype`,
`d`.`province` AS `distributorprovince`,
`d`.`region` AS `distributorregion`,
`pro`.`id` AS `productid`,
`pro`.`sku` AS `sku`,
`pro`.`name` AS `productname`,
`pro`.`spec` AS `spec`,
`pro`.`linecode` AS `linecode`,
`pro`.`linename` AS `linename`,
`pro`.`brandcode` AS `brandcode`,
`pro`.`brandname` AS `brandname`,
`c`.`id` AS `customerid`,
`c`.`code` AS `customercode`,
`c`.`name` AS `customername`,
`c`.`categorycode` AS `categorycode`,
`c`.`province` AS `customerprovince`,
`c`.`city` AS `customercity`,
`c`.`district` AS `customerdistrict`,
`c`.`level` AS `customerlevel`,
`pro`.`inspecprice` AS `inspecprice`,
`f`.`count` AS `count`
FROM
flow_baier_id f
LEFT JOIN md_peroid p ON p.`year` = LEFT (f.ddimonth, 4)
AND p. MONTH = cast(
substring(f.ddimonth ,- 2, 2) AS SIGNED INTEGER
)
LEFT JOIN distributor d ON d.id = f.fromid
LEFT JOIN v_product pro ON pro.id = f.productid
LEFT JOIN md_customers c ON c.id = f.downid
不用 select * 了 还是没经过索引, 另外不能用where 这只是基础表 之后还要 group by 等相关操作