按天分表 ,检索慢。
表A,每天数据很大(800万行),现在一天一个表。web页面,可以按指定条件检索。
例如检索期间 2020/02/10 ~2020/02/26 机器ID = 5 状态是关机 的所有数据(200条),分页,每页显示20条。
检索时间 30秒。怎么提高检索速度呢?
tableList :根据期间取出所有表明 table1_20200210~table1_202002226
SELECT
YYYYY.id AS "id"
,YYYYY.name AS "name"
,YYYYY.status AS "status"
,YYYYY.machineId AS "machineId"
FROM (
<foreach collection="tableList" item="tableName" separator=" UNION ALL ">
SELECT
ZZZZZ.id AS id
,ZZZZZ.name AS name
,ZZZZZ.status AS status
,ZZZZZ.machineId AS machineId
FROM tableName AS ZZZZZ
WHERE ZZZZZ.deleted = 0
AND ZZZZZ.status = 2
AND ZZZZZ.machineId = 5
AND ZZZZZ.start_time >= '2020-02-10 09:11:06.0'
AND ZZZZZ.start_time < '2020-02-26 10:11:06.0'
</foreach>
) AS YYYYY
LEFT JOIN tbl_Machine AS machine
ON YYYYY.machineId = machine.id
LEFT JOIN tbl_team AS team
ON machine.area_id = team.id
limit 10