mysql 大数据量分页处理-优快云博客

本文探讨了SQL查询中使用复合索引、ORDER BY及INNER JOIN优化查询性能的方法，并通过实例展示了如何通过调整查询语法来显著提高数据库查询效率。

select * from user where xx=1 limit 10,10

你需要有ORDER　BY 字段。然后需要（xx, orderCol) 的复合索引，再使用

select * from user a inner join (select id from user where xx=1 order by id

limit 10,10) b using(id)

select * from emp where deptno = 20 limit 900000,3;

ysql> alter table emp add index(deptno,empno);
uery OK, 0 rows affected (2 min 17.45 sec)
ecords: 0 Duplicates: 0 Warnings: 0

ysql> select * from emp where deptno = 20 limit 900000,3;
--------+--------+----------+-----+------------+---------+--------+--------+
empno | ename | job      | mgr | hiredate   | sal     | comm   | deptno |
--------+--------+----------+-----+------------+---------+--------+--------+
898210 | YfDEig | SALESMAN |   1 | 2013-10-13 | 2000.00 | 400.00 |     20 |
898211 | ecaTrf | SALESMAN |   1 | 2013-10-13 | 2000.00 | 400.00 |     20 |
898212 | ydVsaZ | SALESMAN |   1 | 2013-10-13 | 2000.00 | 400.00 |     20 |
--------+--------+----------+-----+------------+---------+--------+--------+
rows in set (8.47 sec)

ysql> select * from emp a inner join
   -> (select empno from emp where deptno=20
   -> order by empno limit 900000,3) b using(empno);
--------+--------+----------+-----+------------+---------+--------+--------+
empno | ename | job      | mgr | hiredate   | sal     | comm   | deptno |
--------+--------+----------+-----+------------+---------+--------+--------+
898210 | cOOBpV | SALESMAN |   1 | 2013-10-10 | 2000.00 | 400.00 |    334 |
898210 | YfDEig | SALESMAN |   1 | 2013-10-13 | 2000.00 | 400.00 |     20 |
898211 | BINPKg | SALESMAN |   1 | 2013-10-10 | 2000.00 | 400.00 |    279 |
898211 | ecaTrf | SALESMAN |   1 | 2013-10-13 | 2000.00 | 400.00 |     20 |
898212 | pUDigi | SALESMAN |   1 | 2013-10-10 | 2000.00 | 400.00 |    207 |
898212 | ydVsaZ | SALESMAN |   1 | 2013-10-13 | 2000.00 | 400.00 |     20 |
--------+--------+----------+-----+------------+---------+--------+--------+
rows in set (1.17 sec)

ysql> select version();
-----------+
version() |
-----------+
5.6.10    |
-----------+