mysql 多表分页

多表分页思想

1.先count出每个表符合条件的数据量。
2.然后计算出需要查询的数据量卡在哪几张表的位置。(比如说有三张表a1,a2,a3 每张表分别为10条数据。这三张表符合条件数量 a1 count1=4,a2 count2=6,a3 count3=10，本次需要返回前端5条数据。则表卡在a1,a2上)
3.再从这几张表的位置中limit查询，拼接即可。(直接 a1 limit 5, a2 limit 5-4)

先count查询卡在表的位置，再分页

package com.yicong.boke.test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.log4j.Logger;

import com.yicong.boke.test.MysqlmultiTabePageHelp.Result.RobotsPo;

@SuppressWarnings("all")
public class MysqlmultiTabePageHelp {
	public static final Logger LOGGER = Logger.getLogger(MysqlmultiTabePageHelp.class);

	public Result list(RobotsPo robotsQuery) {
		long start1 = System.currentTimeMillis();
		// 根据条件查询每个表符合条件的个数
		Map<String, Integer> mapCount = multiTabePageHelpCount(robotsQuery);
		LOGGER.info("查询count耗时：" + (System.currentTimeMillis() - start1));

		int count = mapCount.values().stream().mapToInt(val -> val.intValue()).sum();
		List<RobotsPo> robotsPos = new ArrayList<>();
		if (count > 0) {
			// 分页查询
			long start2 = System.currentTimeMillis();
			robotsPos = multiTabePageHelp(robotsQuery, mapCount);
			LOGGER.info("查询分页耗时：" + (System.currentTimeMillis() - start2));
		}

		HashMap<String, Object> hashMap = new HashMap<>();
		hashMap.put("robotsPos", robotsPos);
		hashMap.put("count", count);
		hashMap.put("page", robotsQuery.getPage());
		hashMap.put("limit", robotsQuery.getLimit());
		return new Result(200, "查找完成", hashMap);
	}

	private Map<String, Integer> multiTabePageHelpCount(RobotsPo robotsQuery) {

		Map<String, Integer> mapCount = new ConcurrentHashMap<>();// 存表名，count数
		Map<String, Integer> mapLinkCount = new LinkedHashMap<>();// 存表名，count数，保持顺序

		String domainName = robotsQuery.getDomainName();
		List<Thread> threads = new ArrayList<>();
		// 根据条件循环查询，获得所有的表
		List<String> allTable = allTables();
		for (String tableName : allTable) {
			mapLinkCount.put(tableName, null);
			Thread thread = new Thread(() -> {
				// 查询数据库，表和总数对应,这里采用多线程
				mapCount.put(tableName, robotsService.listCount(new RobotsPo(domainName, tableName)));
			});
			thread.start();
			threads.add(thread);
		}
		for (Thread thread : threads) {
			try {
				thread.join();
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		// 为了保证顺序
		mapCount.forEach((k, v) -> mapLinkCount.put(k, v));
		return mapLinkCount;
	}

	private List<RobotsPo> multiTabePageHelp(RobotsPo robotsQuery, Map<String, Integer> mapCount) {
		// 分装参数
		Integer page = robotsQuery.getPage();
		Integer limit = robotsQuery.getLimit();
		int offset = Page.getOffset(page, limit);// 计算offset
		List<RobotsPo> robotsPos = new ArrayList<>();// 装取查询的数据\
		// 求和,总量
		int count = mapCount.values().stream().mapToInt(val -> val.intValue()).sum();
		if (count > 0) {
			int size = 0;
			// 说明有数据需要进行分页查询,需要找到查询数据的范围，在哪张表
			for (Map.Entry<String, Integer> entry : mapCount.entrySet()) {
				if (entry.getValue().intValue() > 0) {
					if (size <= offset && (size + entry.getValue().intValue() > offset) && robotsPos.size() == 0) {
						// 说明这个时候是在该表中要起始查询
						robotsQuery.setTableName(entry.getKey());
						robotsQuery.setOffset(offset - size);
						// 查询语句,自己定义sql
						robotsPos.addAll(robotsService.list(robotsQuery));
						continue;
					}
					// 说明该表中所有数据加起来都没达到offset
					if ((size += entry.getValue().intValue()) < offset) {
						continue;
					}
					if (robotsPos.size() >= limit) {
						break;
					} else {
						robotsQuery.setTableName(entry.getKey());
						robotsQuery.setOffset(0);
						robotsQuery.setLimit(limit - robotsPos.size());
						// 查询语句,自己定义sql
						robotsPos.addAll(robotsService.list(robotsQuery));
					}
				}

			}
		}
		return robotsPos;
	}

	public class Result {
		/* 状态码 */
		private Integer code;
		/* 提示信息 */
		private String message;
		/* 具体的内容 */
		private Object data;

		public Result(Integer code, String message, Object data) {
			super();
			this.code = code;
			this.message = message;
			this.data = data;
		}

		public String getMessage() {
			return message;
		}

		public void setMessage(String message) {
			this.message = message;
		}

		public Integer getCode() {
			return code;
		}

		public void setCode(Integer code) {
			this.code = code;
		}

		public Object getData() {
			return data;
		}

		public void setData(Object data) {
			this.data = data;
		}

		public Result() {
			super();
		}

		public class RobotsPo {

			private String domainName;

			private Integer offset;

			private String tableName;
			private Integer page;
			private Integer limit;

			public RobotsPo(String domainName, String tableName) {
				super();
				this.domainName = domainName;
				this.tableName = tableName;
			}

			public String getDomainName() {
				return domainName;
			}

			public void setDomainName(String domainName) {
				this.domainName = domainName;
			}

			public Integer getOffset() {
				return offset;
			}

			public void setOffset(Integer offset) {
				this.offset = offset;
			}

			public String getTableName() {
				return tableName;
			}

			public void setTableName(String tableName) {
				this.tableName = tableName;
			}

			public Integer getPage() {
				return page;
			}

			public void setPage(Integer page) {
				this.page = page;
			}

			public Integer getLimit() {
				return limit;
			}

			public void setLimit(Integer limit) {
				this.limit = limit;
			}

			public void valid(int maxLimit, int defaultLimit) {
				if (page == null || page < 1) {
					page = 1;
				}
				if (limit == null || limit < 1 || limit > maxLimit) {
					limit = defaultLimit;
				}
			}

		}
	}

	public static class Page {

		private long offset;
		private long limit;
		private long total;

		private long totalPage;
		private long currentPage;

		public Page(long offset, long limit, long total) {
			if (offset < 0) {
				offset = 0;
			}
			if (limit < 1) {
				limit = 10;
			}
			if (total < 0) {
				total = 0;
			}
			this.offset = offset;
			this.limit = limit;
			this.total = total;

			this.totalPage = (total - 1) / limit + 1;
			this.currentPage = offset / limit + 1;
		}

		public long getOffset() {
			return offset;
		}

		public void setOffset(long offset) {
			this.offset = offset;
		}

		public long getLimit() {
			return limit;
		}

		public void setLimit(long limit) {
			this.limit = limit;
		}

		public long getTotal() {
			return total;
		}

		public void setTotal(long total) {
			this.total = total;
		}

		public long getTotalPage() {
			return totalPage;
		}

		public void setTotalPage(long totalPage) {
			this.totalPage = totalPage;
		}

		public long getCurrentPage() {
			return currentPage;
		}

		public void setCurrentPage(long currentPage) {
			this.currentPage = currentPage;
		}

		public static int getOffset(int page, int limit) {
			return (page - 1) * limit;
		}
	}

	private List<String> allTables() {
		return new ArrayList<String>() {
			{
				add("t_robots_0");
				add("t_robots_2");
				add("t_robots_3");
				add("t_robots_4");
				add("t_robots_5");
			}
		};
	}
}

注：代码不能直接运行，需要稍微调整。