MySQL 索引：用 EXPLAIN 验证查询假设

import java.util.*;
import java.util.function.Predicate;
public class Main {
    record Key(long user,long time,long id) implements Comparable<Key> {
        public int compareTo(Key b) {
            int c=Long.compare(user,b.user);
            if (c==0) c=Long.compare(time,b.time);
            return c==0?Long.compare(id,b.id):c;
        }
    }
    static class Index {
        final List<Key> rows;
        Index(List<Key> input) {
            rows=List.copyOf(input);
            // 先验证完整联合键严格有序且无重复。
            for (int i=1;i<rows.size();i++)
                if (rows.get(i-1).compareTo(rows.get(i))>=0)
                    throw new IllegalArgumentException("order");
        }
        int first(Predicate<Key> condition) {
            int low=0,high=rows.size();
            while (low<high) {
                int mid=low+(high-low)/2;
                if (condition.test(rows.get(mid))) high=mid;
                else low=mid+1;
            }
            return low;
        }
        List<Long> page(long user,Key cursor,int limit) {
            if (limit<1||limit>100||(cursor!=null&&cursor.user()!=user))
                throw new IllegalArgumentException("page");
            // 直接比较用户边界，避免用user+1构造溢出哨兵。
            int start=first(k->k.user()>=user);
            int end=first(k->k.user()>user);
            if (cursor!=null) end=Math.min(end,first(k->k.compareTo(cursor)>=0));
            List<Long> ids=new ArrayList<>();
            // 排他游标从end-1开始，且始终不跨越用户左边界。
            for (int i=end-1;i>=start&&ids.size()<limit;i--) ids.add(rows.get(i).id());
            return List.copyOf(ids);
        }
    }
    static void eq(Object expected,Object actual) {
        if (!Objects.equals(expected,actual)) throw new AssertionError(actual);
    }
    public static void main(String[] args) {
        Index index=new Index(List.of(new Key(1,10,1),new Key(1,10,2),
            new Key(1,20,3),new Key(2,30,4)));
        eq(List.of(3L,2L),index.page(1,null,2));
        eq(List.of(1L),index.page(1,new Key(1,10,2),2));
        eq(List.of(),index.page(3,null,2));
        eq(List.of(2L,1L),index.page(1,new Key(1,15,99),2));
        eq(List.of(),new Index(List.of()).page(1,null,1));
        eq(List.of(7L),new Index(List.of(new Key(Long.MAX_VALUE,0,7)))
            .page(Long.MAX_VALUE,null,1));
        try { index.page(1,new Key(2,30,4),2); throw new AssertionError("cursor"); }
        catch (IllegalArgumentException expected) {}
        System.out.println("PASS 7 composite cursor tests");
    }
}