mysql优化 - 有关Mysql分表分页问题

Question

我把难题简单归结为，跨多张结构一样的表查数据，怎么去实现分页？

先说下我这边的分表规则吧
这边是一个游戏web后台，现在是对玩家的登陆日志进行分表，分表的规则是按季度分表的，也就是01~03月一张表，04~06月一张表，07~09月一张表，10~12月一张表，
表结构就是这样：

player_login_01_03
player_login_04_06
player_login_07_09 目前这张表没有
player_login_10_12 目前这张表没有

不过现在是07月份，那么实际就是这样：

player_login 这个是原表名
player_login_01_03
player_login_04_06

字段

SQLplayer_id        int             用户ID，主键
player_name      varchar         用户名
last_login_time  datetime        最后登陆时间
.....其他字段

现在表已经分了，不过难题来了，web后台有个功能就是根据player_id和last_login_time来查找玩家信息。
1.时间不夸表的选择：
如果选择的时间段比如是2015-07-01 00:00:00~2015-07-31 23:59:59或者0015-01-01 00:00:00~2015-01-31 23:59:59这样的时间段的话就很好实现分页，因为这个时间段是不需要夸表的，按照现在是7月分就直接对应player_login和player_login_01_03就行了。
2.时间夸多张的选择：
假设选择的时间类似这样的：2015-03-01 00:00:00~2015-04-30 23:59:59
那就需要夸两张表去查询了，有时候可能是更多张表，那用什么方法能实现分页呢？（这个登陆的表一个季度下来数据量很大的，少说每张表也是几百万），我尝试把跨表的数据查询出来用array_slice分，数组过大的时候PHP会提示内存不足的。

Answer 1

I don’t quite understand why the table should be divided according to the last login time. If the same user logs in in April, and then logs in again in July, what should be the logic here? According to my understanding, player_id is the primary key, so you still need to ensure this after dividing the tables, so there will be one more piece of data in the table in July, and the data in April should be deleted, but your logic does not seem to be like this. I don’t know if I understand correctly? Half of the tables are divided according to the primary key to ensure that the data of the same user will only fall in the same table. Wouldn't this be simpler?

Answer 2

For example, if you span two tables, if you are in chronological order, then calculate the page and pagesize to see how much data needs to be fetched from each of the two tables
1. You get the total quantity of Table 1 and Table 2 within this time period
2. Use page/pagesize to calculate how many pieces of data need to be obtained from Table 1 and how many pieces of data need to be obtained from Table 2.

However, when the limit offset of mysql is relatively large, it seems to be less efficient. When it comes to paging = =

Answer 3

Is it possible to use redis? Put the data needed in the player_login table in redis and use the query conditions as the key