分页查询优化.docx

分页查询优化分页查询是很常见的一种业务需求，因此，分页查询的性能问题就是我们需要重点关注的。本章案例中会介绍三种分页查询的写法，让大家能应对分页查询的性能问题。案例中所使用的表及数据可以从hHps:问题现象一个客户业务系统带有分页查询功能，但是随着查询页数的增加，越往后查询性能越差，有时一个查询可能需要1分钟左右的时间。分页查询的写法类似于：select * from employees limit 250000,5000;这是最传统的一种分页查询写法，但问题也是最多的。随着limit值的增大，往往在越往后翻页的过程中速度越慢，原因是MySQL会读取表中的前M+N条数据，M越大，性能就越差。这里多说几句，在服务的很多客户中，还是有很多客户使用这种传统的分页查询写法的，主要有两点原因：系统早期建设时数据量不大，性能问题没有暴露出来；很多开发商把这种写法固化到了产品框架中，导致后期开发人员根本不关心这类问题。优化方案1 .普通优化写法针对分页查询，我们可以使用最简单的一种优化写法：select from (select enp_no from employees limit 250000,5000) b , enf)loyees a wherea. emp_no = b. emp_no;优化后的分页查询写法，会先查询翻页中需要的N条数据的主键值(emp_no),然后根据主键值回表查询所需要的N条数据、在此过程中查询N条数据的主键id在索引中完成，所以效率会高一些。2 .业务优化写法上面的写法虽然可以达到一定程度的优化，但还是存在性能问题。最佳的方式是在业务上进行配合修改为以下语句：select * from employees where enp_no > #la8t_emp_no# order by emp_no limit 20;采用这种写法，在页面上只能通过点击More来获得更多数据，而不是纯粹的翻页。因此，每次查询只需要使用上次查询出的数据中的id来获取接下来的数据即可，但这种写法需要业务配合。3 .性能对比传统的分页查询写法：mysql> select * from exiloyees limit 250000,5000;5000 rows in set (1.31 sec)执行计划（关于执行计划详解，可参考本书下载资源中的“附录D”）如图37-1所示。图 37-1优化写法:mysql> select * from (select mp_no from employees limit 250000,5000) b , employees awhere a. emp_no = b. empjno;5000 rows in set (0.94 sec)执行计划如图37-2所示。4| Sd| select.type | table| partitions | type |possible.keys | key| key.ten | ref| rows | filtered | Extra|1 11】1 2. |PRIMARY|<derived2>|NULL|ALL|PRIMARYIa1NULL|eq.ref|DERIVED|employees|NULL|Index|NULL|NULL|NULL|NULL|255906|166.66|NULL|PRIMARY|PRIMARY|4|b.emp.no|1|106.M|NULL|NULL|PRIMARY|4|NULL|2751G3|190.86|Usingindex|图 37-2从执行计划中可以看出，首先执行子查询中的employees表，根据主键做索引全表扫描，然后与a表通过emp_no做主键关联查询，相比传统写法中的全表扫描效率会高一些。从两种写法上能看出性能有一定的差距，虽然并不明显，但是随着数据量的增大，两者执行的效率便会体现出来。