MySql分表分页

MySql的多表分页一直是一个比较困难的问题。常见的多表分页场景主要出现在统计页面中，比如统计多维度的数据，或者按照固定顺序取多个数据表数据。

方案一：创建临时表。

创建临时表是一个最简单快捷的方案。在对数据实时性要求不高的情况下，可以创建一个临时表，使用程序整理要查询的数据，并定时把整理好的数据insert到临时表中，展示时直接从临时表中读取数据。这样做的优势是简单快速，可以把很复杂的问题简单化，并且在数据量不大的情况下有很好的表现。缺陷也很明显：实时性不高，数据量大的情况下临时表会占用大量的资源。因此个人觉得创建历史表这个方案，在项目创建初期或者快速完成内部统计功能的时候是一个很好地选择。

这个方案用于商业化的项目中一定要谨慎，因为项目上线以后，由于数据量越来越大，临时表很有可能突破预计的大小。

方案二：内存排序

内存排序的核心思想就是先把一部分要分页数据 load 到内存中，然后在内存中对读出的数据进行排序，然后对排序后的数据进行分页。

首先简单介绍一下二次查询法的基本思想。 
假设我们的数据库中有 200 条数据，分别存储在 A 表（order_table_accept）和 B 表（order_table_return）中，分页的步长是10，根据时间排序，我们要取的是第9页的数据。

假设存储时单表，那么sql非常简单

SELECT * FROM table ORDER BY create_time limit 8*10,10；

 

那么当我们分表查询时，首先就要把 sql 改写成查询两张表，如下：

select * from table_A order by create_time limit 8*10/2,10
select * from table_B order by create_time limit 8*10/2,10

那么这样现在我们就有了 20 条数据，是从 A 表，和 B 表的第 40 条位置开始处理的。

接下来比较这 20 条数据中最小的一个时间，假设是 A 表中查出的第一条数据：2017-03-01 17:00:00，然后查询 B 表结果中的最大数据，假设是 2017-03-01 17:00:10 。然后改写sql进行二次查询。

select * from B where create_time  between '2017-03-01 17:00:00' and '2017-03-01 17:00:10' order by create_time

假设这次查出了12条数据，那么现在对于2017-03-01 17:00:00这个时间点，在A表中有40条数据小于该时间点。B表中有40 -（12-10）条数据小于该时间点，那么可以得到，2017-03-01 17:00:00这个时间在整个数据库中的位置是 40+38=78 条数据，那么根据第二次查询结果，在 22 条数据中根据时间2017-03-01 17:00:00之后的 12 条数据，即可得到所需的两个表中的第80-90条数据。

其实基本的思想，在原文中已经介绍的比较清楚了，还是建议大家点开链接详细了解一下这个方案。本文主要是介绍跨表分页的代码实现，以及二次查找方案的一些问题。

首先需要一些测试数据，先创建两个测试表来模拟业务场景，分别表示有效订单和退款订单

CREATE TABLE `order_table_accept` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `user_name` char(30) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `order_table_return` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` char(20) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8mb4;

java程序插入一些测试数据

DBConnection db = new DBConnection();
        List<Map<String, Object>> list = db.getList("select * from student");
        System.out.println(list);
        for (int i = 1;i < 4;i++) {
            Thread.sleep(1000);
            String sql ="INSERT INTO order_table_accept (user_name,create_time) VALUES ('Name_"+ i +"',NOW())";
            db.updateData(sql);
        }

        Thread.sleep(1000);

        for(int i = 1;i < 4;i++){
            Thread.sleep(1000);
            String sql ="INSERT INTO order_table_return (user_name,create_time) VALUES ('Name_"+ i +"',NOW())";
            db.updateData(sql);
        }

java实现如下 :

public class Test {
    public static <T> void main(String[] args) throws SQLException, ParseException, InterruptedException {
        /*
         * 插入测试数据
         * 
        DBConnection db = new DBConnection();
        List<Map<String, Object>> list = db.getList("select * from student");
        System.out.println(list);
        for (int i = 1;i < 4;i++) {
            Thread.sleep(1000);
            String sql ="INSERT INTO order_table_accept (user_name,create_time) VALUES ('Name_"+ i +"',NOW())";
            db.updateData(sql);
        }

        Thread.sleep(1000);

        for(int i = 1;i < 4;i++){
            Thread.sleep(1000);
            String sql ="INSERT INTO order_table_return (user_name,create_time) VALUES ('Name_"+ i +"',NOW())";
            db.updateData(sql);
        }*/

        int pageSize = 10; //步长
        int pageCount = 8; //当前页数
        int totalCount = pageSize*pageCount;
        int oneTableCount = totalCount/2; //每个表的

        //第一次查询
        DBConnection db = new DBConnection();
        List<Map<String, Object>> list1 = db.getList("select * from order_table_accept order by create_time  limit "+pageSize+" offset "+oneTableCount+"");
        List<Map<String, Object>> list2 = db.getList("select * from order_table_return order by create_time  limit "+pageSize+" offset "+oneTableCount+"");

        //获得第一次查询最小时间
        String dateStr1 = list1.get(0).get("create_time").toString();
        String dateStr2 = list2.get(0).get("create_time").toString();

        //获得第一次查询最大时间
        String dateStr1Max = list1.get(pageSize -1).get("create_time").toString();
        String dateStr2Max = list2.get(pageSize -1).get("create_time").toString();

        Date date1 = new SimpleDateFormat("yyyy-mm-dd").parse(dateStr1);
        Date date2 = new SimpleDateFormat("yyyy-mm-dd").parse(dateStr2);

        System.out.println("一次查询结果："+list1.size());
        System.out.println("一次查询结果："+list2.size());

        //获得最小时间
        String minCreateTime = date2.before(date1) ? dateStr2 :dateStr1;

        System.out.println("list1的最大时间："+dateStr1Max);
        System.out.println("list1的最小时间："+dateStr1);
        System.out.println("list2的最大时间："+dateStr2Max);
        System.out.println("list2的最小时间："+dateStr2);
        System.out.println("获得的最小时间"+minCreateTime);

        //使用最小时间进行第二次查询
        List<Map<String, Object>> list3 = db.getList("select * from order_table_accept where create_time  between '"+minCreateTime+"' and '"+dateStr1Max+"' order by create_time ");
        List<Map<String, Object>> list4 = db.getList("select * from order_table_return where create_time between '"+minCreateTime+"' and '"+dateStr2Max+"' order by create_time");

        System.out.println("二次查询结果："+list3.size());
        System.out.println("二次查询结果："+list4.size());

        //使用二次查询结果获得minCreateTime在全局的位置
        int countDifference1 = 0;
        int countDifference2 = 0;

        //与各自的第一次查询最小值作比较
        Date minDate1 = stringToDate(dateStr1);
        Date minDate2 = stringToDate(dateStr2);
        for (Map<String, Object> map : list3) {
            String createTime = (String)map.get("create_time");
            Date createTimeDate = stringToDate(createTime);
            if(createTimeDate.before(minDate1)){
                countDifference1 ++;
            }
        }

        for (Map<String, Object> map : list4) {
            String createTime = (String)map.get("create_time");
            Date createTimeDate = stringToDate(createTime);
            if(createTimeDate.before(minDate2)){
                countDifference2 ++;
            }
        }

        //使用差值获得最终位置：
        int index = oneTableCount + oneTableCount -countDifference2;
        System.out.println("最终位置为："+index);

        //对两次查询结果进行排序，获得最终结果
        List<Map<String, Object>> sortedList  = new ArrayList<Map<String, Object>>();
        sortedList.addAll(list3);
        sortedList.addAll(list4);
        Collections.sort(sortedList, new Comparator<Map<String, Object>>() {
            @Override
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                String str1 = (String)o1.get("create_time");
                Date date1 = stringToDate(str1);
                String str2 = (String)o2.get("create_time");
                Date date2 = stringToDate(str2);
                return date1.compareTo(date2);
            }
        });


        for(int i = countDifference2-1 ;i<pageSize+countDifference2-1 ;i++){
            System.out.println(sortedList.get(i));
        }

    }

    /**
     * 字符串转Date
     * @param date
     * @return
     */
    private static Date stringToDate(String date) {
        try {
            return new SimpleDateFormat("yyyy-mm-dd HH:mm:ss.S").parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }
    }
}

关于二次查找法的个讨论，在一般情况下，二次查找法是可以满足业务需求的，但是下面的情境中会出现问题。

假设第一次查询出的最小时间是 2017-03-13 17:50:43 ，取 5 条，当数据库中有相同时间时，如果没有其他的排序条件，那么使用 between 进行二次查询的时候，就会出现查询记录扩大的情况。 
例如数据库中数据为： 
-+id+——+时间+———- 
0 2017-03-13 17:50:43 
1 2017-03-13 17:50:43 
2 2017-03-13 17:50:44 
3 2017-03-13 17:50:45 
4 2017-03-13 17:50:46 
5 2017-03-13 17:50:47

当 1 2017-03-13 17:50:43 这条数据前仍然有一条数据 0 2017-03-13 17:50:43 时，第二次查询就会出现查询结果扩大的情况。这个时候我们的全局标识由于没有第二个排序结果，那么这时候这条数据的位置是不准确的。