MySql 分页SQL 大数据量limit替代和优化（试验）

来源：互联网发布：java 贪吃蛇程序源代码编辑：程序博客网时间：2024/05/16 07:19

参考：https://my.oschina.net/cart/blog/354999

select SQL_NO_CACHE  u.id, u.user_id, u.user_name, u.user_name_index, u.email, u.pwd, u.email_token, u.email_active_date,       u.real_name, u.real_name_index, u.identity_card, u.identity_card_index  from t_user u ORDER BY u.id LIMIT 100000, 100;-- 试验方法1select SQL_NO_CACHE  u.id, u.user_id, u.user_name, u.user_name_index, u.email, u.pwd, u.email_token, u.email_active_date,       u.real_name, u.real_name_index, u.identity_card, u.identity_card_index  from t_user u where u.id in (select t.id from (select id from t_user_basic_info order by id limit 100000,100) t );-- 试验方法2 -- EXPLAIN select SQL_NO_CACHE  u.id, u.user_id, u.user_name, u.user_name_index, u.email, u.pwd, u.email_token, u.email_active_date,       u.real_name, u.real_name_index, u.identity_card, u.identity_card_index  from t_user uinner join (select id from t_user_basic_info order by id limit 100000,100) as t USING(id);-- 试验方法3-- EXPLAINselect SQL_NO_CACHE  u.id, u.user_id, u.user_name, u.user_name_index, u.email, u.pwd, u.email_token, u.email_active_date,       u.real_name, u.real_name_index, u.identity_card, u.identity_card_index  from t_user u where u.id >= (select id from t_user_basic_info order by id limit 100000,1) order by id limit 100;

运行结果：

[SQL]
select SQL_NO_CACHE
u.id, u.user_id, u.user_name, u.user_name_index, u.email, u.pwd, u.email_token, u.email_active_date,
u.real_name, u.real_name_index, u.identity_card, u.identity_card_index
from t_user u
ORDER BY u.id
LIMIT 100000, 100;
受影响的行: 0
时间: 0.069s

[SQL]

select SQL_NO_CACHE
u.id, u.user_id, u.user_name, u.user_name_index, u.email, u.pwd, u.email_token, u.email_active_date,
u.real_name, u.real_name_index, u.identity_card, u.identity_card_index
from t_user u
where u.id in (
select t.id from (select id from t_user_basic_info order by id limit 100000,100) t
);
受影响的行: 0
时间: 0.119s

[SQL]

-- EXPLAIN
select SQL_NO_CACHE
u.id, u.user_id, u.user_name, u.user_name_index, u.email, u.pwd, u.email_token, u.email_active_date,
u.real_name, u.real_name_index, u.identity_card, u.identity_card_index
from t_user u
inner join (select id from t_user_basic_info order by id limit 100000,100) as t USING(id)
;
受影响的行: 0
时间: 0.034s

[SQL]

-- EXPLAIN
select SQL_NO_CACHE
u.id, u.user_id, u.user_name, u.user_name_index, u.email, u.pwd, u.email_token, u.email_active_date,
u.real_name, u.real_name_index, u.identity_card, u.identity_card_index
from t_user u
where u.id >= (select id from t_user_basic_info order by id limit 100000,1)
order by id
limit 100
;
受影响的行: 0
时间: 0.099s

方案1和3,，速度竟然都不如原生limit，试验表数据样本也就十多万条，有机会用百万千万级别来测试。。

阅读全文

0 0