mysql order by limit 使用注意事项

时间:2022-01-24 02:47:04

5.7以上重复数据问题

order by limit会出现数据重复问题

--查看mysql版本
select  version()
--创建表
create table student(
  id int(10) PRIMARY KEY auto_increment,
    name varchar(32),
    age int(3)
)
--插入数据
insert into student VALUEs(null,小明,11);
insert into student VALUEs(null,王小明,5);
insert into student VALUEs(null,小二毛,11);
insert into student VALUEs(null,王小三,4);
insert into student VALUEs(null,毛小二,11);
insert into student VALUEs(null,张小二,11);
--插入表
select * from student s
order by age desc
--分页查询
select * from(select * from student s
order by age desc LIMIT 0,1)tab
union all
select * from(select * from student s
order by age desc LIMIT 1,1)tab
union all
select * from(select * from student s
order by age desc LIMIT 2,1)tab
UNION ALL
select * from(select * from student s
order by age desc LIMIT 3,1)tab

我是5.6版本无法测试,5.7及其以上版本会出现 order by的列有相同的值时 同时未做索引时, mysql会随机选取这些行 文档地址:https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

解决方式 可以增加一列含索引的排序字段

--分页查询
select * from(select * from student s
order by age desc,id asc LIMIT 0,1)tab
union all
select * from(select * from student s
order by age desc,id asc LIMIT 1,1)tab
union all
select * from(select * from student s
order by age desc,id asc LIMIT 2,1)tab
UNION ALL
select * from(select * from student s
order by age desc,id asc LIMIT 3,1)tab

limit深分页查询优化

select * from tab limit n,m     limit原理是先读取前n条数据,读取最后m条数据 所以n越大就越慢 性能也越差

优化前

select * from `cpn_coupon_code`  c   limit 10000000,10

 

mysql order by limit 使用注意事项

 

优化后

SELECT * FROM cpn_coupon_code  c
INNER JOIN (SELECT id FROM cpn_coupon_code e  LIMIT 10000000,10)tab on tab.id=c.id 

mysql order by limit 使用注意事项

 

 与优化前版本是先查出数据id 然后根据id查数据

因为只查索引页 索引 n,m     n就只会是索引 不会包含数据