mysql千万数据测试查询优化

4千万数据查询 mycat分库分表
大偏移分页优化
SELECT * FROM t_user_center where user_id>=(SELECT user_id FROM t_user_center ORDER BY created_at LIMIT 600000,1) limit 10;
10 rows in set (1.30 sec)
10 rows in set (1.30 sec)
10 rows in set (0.83 sec)

业务app分页 上一页 上一页
SELECT * FROM t_user_center where user_id>=305709105740251136 limit 100;
100 rows in set (0.03 sec)
100 rows in set (0.01 sec)
100 rows in set (0.01 sec)

总结 innodb b+树数据扫描磁盘page页跨度多所以第二条慢
SELECT user_id,created_at FROM t_user_center order by created_at LIMIT 510000,50;
50 rows in set (6.33 sec)
50 rows in set (2.92 sec)
50 rows in set (2.87 sec)
50 rows in set (2.75 sec)
SELECT user_id,created_at FROM t_user_center LIMIT 510000,50;
50 rows in set (8.41 sec)
50 rows in set (7.43 sec)
50 rows in set (7.45 sec)