准备一般分页查询使用子查询优化使用id限制优化使用临时表优化关于数据表的id说明速度慢，尤其是数据量大的时候，需要使用分页查询。对于数据库分页查询，也有很多方法和优化点。以下是我知道的一些方法。准备工作为了测试下面列出的一些优化，下面描述一个现有的表。表名：order_history描述：某业务的历史订单表主要字段：unsignedintid,tinyint(4)inttype字段条件：该表一共37个字段，不包括text等大数据，最大为varchar(500),id字段是一个索引，是递增的。数据量：5709294MySQL版本：5.7.16百万级别的测试表离线也不容易找到。如果需要自己测试，可以写个shell脚本什么的插入数据进行测试。下面sql中所有语句的执行环境都没有改变。下面是基本的测试结果：selectcount(*)fromorders_history;返回结果：5709294三个查询时间分别是：8903ms8323ms8401ms一般分页查询一般分页查询使用简单的limit子句就可以了。limit子句声明如下：SELECT*FROMtableLIMIT[offset,]rows|rowsOFFSEToffsetLIMIT子句可用于指定SELECT语句返回的记录数。需要注意以下几点：第一个参数指定第一个返回记录行的偏移量，第二个参数指定从0开始的最大返回记录行数。如果只给一个参数：表示返回最大的记录行数的第二个参数是-1，表示检索从某个偏移量到记录集末尾的所有记录行。初始记录行的偏移量为0（而不是1）。下面是一个应用示例：select*fromorders_historywheretype=8limit1000,10;该语句会从表orders_history中查询offset:1000后的10条数据，即第1001条到第1010条数据（1001<=id<=1010）。数据表中的记录默认按主键（通常是id）排序。上面的结果相当于：select*fromorders_historywheretype=8orderbyidlimit10000,10;三个查询时间分别是：3040ms3063ms3018ms对于这种查询方式，下面测试查询记录量对时间的影响：select*fromorders_historywheretype=8limit10000,1;select*fromorders_historywheretype=8limit10000,10;select*fromorders_historywheretype=8limit10000,100;select*fromorders_historywheretype=8limit10000,1000;select*fromorders_historywheretype=8limit10000,10000;三次查询时间Asfollows:Query1record:3072ms3092ms3002msQuery10records:3081ms3077ms3032msQuery100records:3118ms3200ms3128msQuery1000records:3412ms3468ms3394ms查询10000条记录：3749ms3802ms3696查询，从查询时间来看，基本可以确定，当查询记录量小于100条时，查询时间基本没有差距。随着查询记录量的增加，花费的时间也会增加。select*fromorders_historywheretype=8limit100,100;select*fromorders_historywheretype=8limit1000,100;select*froforders_historywheretype=8limit100100,100;select*fromorders_hereType=8limit100,100;select*forterers_historywhereType=8limit=8limit100,100,100;时间如下：查询100偏移量：25ms24ms查询1000偏移：78ms76ms77ms查询10000偏移量：3092ms3212ms3212ms3128ms查询100000偏移量：3878ms3812ms3798ms3798ms查询1000000偏移：14608ms1408ms14062ms14062ms140700msthequerethequereniks当查询偏移量大于100,000时，查询时间急剧增加。这种分页查询方式会从数据库中的第一条记录开始扫描，所以越往前查询速度越慢，查询的数据越多，也会拖慢总的查询速度。使用子查询优化，先定位到偏移位置的id，再向后查询。这种方法适用于id自增的情况。选择*fromorders_historyWheretype=8limit100000,1;selectidfromorders_historywheretype=8limit100000,1;select*froforders_herordersorywheretype=8andId>=（selectidfromorders_historywheretywheretype=8limit100000limit100limit100;:3674ms第2条语句：1315ms第3条语句：1327ms第4条语句：3710ms上述查询注意事项：第1条语句与第2条语句比较：使用selectid代替select*，速度提高了3倍第2条语句第三条语句：速度相差几十毫秒比较第三条语句和第四条语句：得益于selectid速度的提升，第三条语句的查询速度提升了3倍。与原来的通用查询方式相比会快数倍。使用id来限制和优化这个方法。假设数据表的id是不断增加的，我们可以根据查询的页数和查询的记录数来计算查询id的范围。我们可以使用idbetweenand来查询：select*fromorders_historywheretype=2andidbetween1000000and1000100limit100;查询时间：15ms12ms9ms这种查询方式可以大大优化查询速度，基本可以在几十毫秒内完成。限制是只有明确知道id的情况下才能使用，但是一般在建表的时候都会加上一个基本的id字段，这给分页查询带来了很多方便。也可以有另一种写法：select*fromorders_historywhereid>=1000001limit100;当然也可以使用in方法查询，这种方法常用于多表关联时查询，使用其他表查询的id集合进行查询：select*fromorders_historywhereid>=1000001limit100;在查询方法中需要注意这一点：某些mysql版本不支持在in子句中使用limit。使用临时表优化的方法已经不是查询优化了，所以这里提一下。对于使用id限制优化的问题，id需要不断递增，但是在某些场景下，比如使用历史表的时候，或者出现数据缺失的问题时，可以考虑使用临时存储表来记录页面的id。在查询中使用分页ID。这样可以大大提高传统分页查询的速度，尤其是数据量在千万级的时候。关于数据表的id，一般情况下，在数据库中建表的时候，强制为每张表添加一个id自增字段，方便查询。如果订单数据库等数据量很大，一般会分库分表。这个时候不建议使用数据库的id作为唯一标识，而是使用分布式高并发唯一id生成器来生成，在数据表中使用另外一个字段来存储唯一标识。使用范围查询先定位id（或索引），再使用索引定位数据，可以提高数倍的查询速度。即先selectid，再select*；
