场景有这样一张表，分页查询时，第一页的记录又出现下第二页中，总之每一页会出现之前出现的内容。原因是sort值重复 分析在数据库(PostgreSQL)中使用查询语句：第一页：SELECT code,name,sort FROM plat_user ORDER BY sort ASC limit 10 offset 0;第二页：SELECT code,name,sort FROM plat_user ORDER BY sort ASC limit 10 offset 10;第三页：SELECT code,name,sort FROM plat_user ORDER BY sort ASC limit 10 offset 20; 查询结果:在第一页和第二页出现了重复的记录。 原因sort的值重复了 解决方法用来order by的字段要保证唯一性。比如加上UNIQUE或者用户输入时保证其唯一性。
前言分页的sql优化是日常开发中经常遇到的问题，笔者在此做一个经验总结，并附上相应的实验过程。 实验准备若不想亲自实验的，可以直接跳过这一节。但还是建议大家做一下实验，眼见为实。 1.安装测试数据库本次实验使用的数据是mysql官方提供的employee数据库，mysql官方提供了一些测试数据库，可以在这里找到https://dev.mysql.com/doc/ind...。 2.修改测试数据库安装好employee数据库后，笔者出于测试修改了一下salaries表的结构，方便测试，修改操作如下： //修改原表的主键为idCREATE TABLE `test_salaries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `test_salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1; //导入原表数据INSERT INTO test_salaries (id,emp_no,salary,from_date,to_date) SELECT NULL,emp_no,salary,from_date,to_date FROM salaries;3.完成测试环境至此，实验的准备工作完成。可先查看一下test_salaries表中有多少数据（以下测试基于该表） SELECT count(*)FROM test_salaries; 优化分页SQL查询优化分页SQL查询的思路： 尽可能使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列（延迟关联）将limit查询转换为已知位置的查询，让mysql通过范围扫描获得对应的结果（范围扫描）延迟关联原始sql查询语句： SELECT * FROM test_salaries WHERE salary <= 94000 LIMIT 2677500,10;原始sql查询语句执行效果： ...
Oracle排序分页查询和MySQL数据库的语句还不一样，这里做简单的记录。按操作时间排序1SELECT A., ROWNUM RN FROM (SELECT * FROM v_log) A ORDER BY operatetime DESC 结果可以发现，按时间排序了，但是rownum并不是从小到大，因为oracle是先生成rownum，再进行排序，需要在套一层查询按操作时间排序2SELECT T., rownum RN FROM( SELECT * FROM (SELECT * FROM v_log) ORDER BY operatetime DESC ) T结果：顺序正确，rownum正确，在此基础上再套一层查询进行分页按操作时间排序并分页SELECT T2.* from( SELECT T., rownum RN FROM(SELECT * FROM (SELECT * FROM v_log) ORDER BY operatetime DESC )T) T2 WHERE RN BETWEEN 1 and 10测试SELECT * FROM ( SELECT A.“sku”, ROWNUM rn, A.“goods_sn” FROM AMZ_HUOPIN_SKU A WHERE ROWNUM <= 10 ORDER BY A.“goods_sn” DESC) tempWHERE temp.rn > 0;SELECT A.“sku”, A.“goods_sn”, ROWNUM RN FROM AMZ_HUOPIN_SKU A ORDER BY A.“sku” DESC## 子查询先找出所有，然后再rownum,rownum 为伪列，后再排序SELECT A.“sku”, A.“goods_sn”, ROWNUM RN FROM (SELECT * FROM AMZ_HUOPIN_SKU) A ORDER BY A.“sku” DESC## 因为oracle是先生成rownum，再进行排序，需要在套一层查询,即先拍好序，然后再生成rownumSELECT T.“sku”, T.“goods_sn”, ROWNUM RN FROM (SELECT * FROM (SELECT * FROM AMZ_HUOPIN_SKU) ORDER BY “sku” DESC) T## 上边的这两个语句是等价的SELECT T.“sku”, T.“goods_sn”, ROWNUM RN FROM (SELECT * FROM AMZ_HUOPIN_SKU ORDER BY “sku” DESC) TSELECT T2. FROM(SELECT T.“sku”, T.“goods_sn”, ROWNUM RN FROM (SELECT * FROM AMZ_HUOPIN_SKU ORDER BY “sku” DESC) T) T2 WHERE RN BETWEEN 0 AND 10注：本文为转载，原文地址：oracle先排序再分页 ...