1 PRIMARY T_StudentAttendanceDetails201701 index ModifyTime 6 943851 Using index
2 UNION T_StudentAttendanceDetails201707 index index_ModifyTime 6 1205527 Using index
UNION RESULT <union1,2> ALL Using temporary
...全文
173411打赏收藏
大数据量情况下UnionALL分表查询分页效率极低
(SELECT ID FROM T_Student201701) UNION (SELECT ID FROM T_Student201707) LIMIT 0, 10; 比如查询2017年的学生记录,共1月份和7月份两张分表 需要分页查询并排序 这样查询效率极低,如果每个表单独查询秒查询. 怎么解决呢? 查询计划如图 1PRIMARYT_StudentAttendanceDetails201701index ModifyTime6 943851Using index 2UNIONT_Stude
忘记了一件事,select id from a union all select id from b , 在 MySQL 中是个坑
你这个查询应该把 limit 带到子查询中才行,否则它先先算出 子查询,再对子查询的结果分页
(SELECT
ID
FROM
T_Student201701 LIMIT 0, 10)
UNION
(SELECT
ID
FROM T_Student201707 LIMIT 0, 10)
LIMIT 0, 10;
PRIMARY T_StudentAttendanceDetails201701 index ModifyTime 6 943851 Using index
UNION T_StudentAttendanceDetails201707 index index_ModifyTime 6 1205527 Using index
UNION RESULT <union1,2> ALL Using temporary