mysql千万级数据分页查询优化

1千2百万数据。

1.统计总行数

# 正常 9秒
SELECT count(*) from p_orders; 

# 加了一个普通索引 2秒左右

# EXPLAIN 0.002秒。但得到的结果是近似值
EXPLAIN SELECT count(*) from p_orders; 

# 降序查询最后一条记录，0.003秒
SELECT id from p_orders ORDER BY id desc LIMIT 0,1

2.数据查询

# 普通 12秒
SELECT id,state,`on`,pay_type,u_id,mobile,g_id,g_price,pay_price,p_name,
pay_callback_on,m_id,created_at,updated_at FROM p_orders LIMIT 10000000,100;

# join查询，2秒左右
a.id,a.state,a.`on`,a.pay_type,a.u_id,a.mobile,a.g_id,a.g_price,a.pay_price,a.p_name,
a.pay_callback_on,a.m_id,a.created_at,a.updated_at FROM p_orders as a join(SELECT id from p_orders LIMIT 10000000,100) as b on a.id = b.id;

# 子查询，2秒左右
SELECT * FROM p_orders where id >=(SELECT id FROM p_orders LIMIT 10000000,1) LIMIT 100;

# BETWEEN查询,0.002秒
SELECT * from p_orders where id BETWEEN 10000000 and 10000100

3.结论

个人推荐表不删除实际记录，还是软删除。然后总行数降序查询，数据BETWEEN查询