MySQL 分页 Limit性能测试

版本：5.6.30

一般的分页查询使用简单的 limit 子句就可以实现。limit 子句声明如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

 

SELECT * FROM resource ORDER BY id DESC LIMIT 50000,10;

 

该条语句将会从表 resource 中查询offset: 50001开始之后的10条数据，也就是第1条到第50010条数据。（50001<= id <= 50010）

MySQL的查询并非先跳过50000条，再查询10条，而是先查询再跳过。所以上面例子，要先查询出50010行之后，再取10条，速度当然很慢，并且跳过的行数越多，会越慢。

就比如看一本500页的书，上次看到480页，下次再看从第一页翻到480，然后再接着上次的继续看。 MySQL在执行上面这个SQL的时候大部分时间用在了翻书这个动作上。

 

数据准备：

resource_b表数据1000万条数据

CREATE TABLE `resource_b` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',

  `resource_code` varchar(64) DEFAULT NULL COMMENT '资源编号',

  `original_resource_amount` decimal(14,2) DEFAULT NULL COMMENT '原始金额',

  `resource_amount` decimal(14,2) DEFAULT NULL COMMENT '金额',

  `ab_amount` decimal(14,2) DEFAULT NULL COMMENT '金额',

  `ab_net_amount` decimal(14,2) DEFAULT NULL COMMENT '净额',

  `shangbiao_fee_amount` decimal(14,2) DEFAULT NULL COMMENT '化妆资源品牌费金额',

  `shangbiao_fee_ratio` decimal(14,4) DEFAULT NULL COMMENT '资源品牌方点位',

  `pps_amount` decimal(14,2) DEFAULT NULL COMMENT '信息服务费',

  `resource_chl_id` varchar(64) DEFAULT NULL COMMENT 'Test资源商户',

  `auth_user_mobile` varchar(32) DEFAULT NULL,

  `city_code` varchar(64) DEFAULT NULL COMMENT '城市',

  `product_id` varchar(64) DEFAULT NULL COMMENT '产品实例ID',

  `fund_id` varchar(64) DEFAULT NULL COMMENT '资金方ID',

  `applicant_uid` varchar(64) DEFAULT NULL COMMENT '资源人'


  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COMMENT='资源单'

 

resource 50万条数据

CREATE TABLE `resource` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',

  `resource_code` varchar(64) DEFAULT NULL COMMENT '资源编号',

  `original_resource_amount` decimal(14,2) DEFAULT NULL COMMENT '金额',

  `resource_amount` decimal(14,2) DEFAULT NULL COMMENT '金额',

  `ab_amount` decimal(14,2) DEFAULT NULL COMMENT '金额',

  `ab_net_amount` decimal(14,2) DEFAULT NULL COMMENT '净额',

  `shangbiao_fee_amount` decimal(14,2) DEFAULT NULL COMMENT '资源品牌费金额',

  `shangbiao_fee_ratio` decimal(16,6) DEFAULT NULL COMMENT '代付资源品牌方点位',

  `pps_amount` decimal(14,2) DEFAULT NULL COMMENT ' 服务费',

  `auth_user_mobile` varchar(20) DEFAULT NULL,

  `resource_chl_id` varchar(64) DEFAULT NULL COMMENT '资源商户',

  `city_code` varchar(64) DEFAULT NULL COMMENT '城市',

  `product_id` varchar(64) DEFAULT NULL COMMENT '产品实例ID',

  `fund_id` varchar(64) DEFAULT NULL COMMENT '资金方',

  `applicant_uid` varchar(64) DEFAULT NULL COMMENT '资源人',

  `fee_rate` decimal(10,4) DEFAULT NULL COMMENT '费率',

  `resource_ratio` decimal(14,4) DEFAULT NULL COMMENT '折扣',

  `status` int(4) DEFAULT NULL COMMENT ' ',

  `resource_time` datetime DEFAULT NULL COMMENT '资源时间'


  


  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='资源单'

 

resource_a 100万条数据

CREATE TABLE `resource_a` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',

  `resource_code` varchar(64) DEFAULT NULL COMMENT '资源编号',

  `original_resource_amount` decimal(14,2) DEFAULT NULL COMMENT '原始金额',

  `resource_amount` decimal(14,2) DEFAULT NULL COMMENT '金额',

  `ab_amount` decimal(14,2) DEFAULT NULL COMMENT '应收金额',

  `ab_net_amount` decimal(14,2) DEFAULT NULL COMMENT '实际金额',

  `shangbiao_fee_amount` decimal(14,2) DEFAULT NULL COMMENT '资源品牌费金额',

  `shangbiao_fee_ratio` decimal(14,4) DEFAULT NULL COMMENT '资源品牌方点位',

  `pps_amount` decimal(14,2) DEFAULT NULL COMMENT '服务费',

  `resource_chl_id` varchar(64) DEFAULT NULL COMMENT '资源商户',

  `auth_user_mobile` varchar(32) DEFAULT NULL,

  `city_code` varchar(64) DEFAULT NULL COMMENT '城市',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='资源单'

 

性能测试

50万条数据测试

查询数据量的测试：

-- 0.125 sec

SELECT * FROM resource ORDER BY id DESC LIMIT 50000,1;

-- 0.126 sec

SELECT * FROM resource ORDER BY id DESC LIMIT 50000,10;

-- 0.126 sec

SELECT * FROM resource ORDER BY id DESC LIMIT 50000,100;

 -- 0.126 sec

SELECT * FROM resource ORDER BY id DESC LIMIT 50000,1000;

查询偏移量的测试：

-- 0.001 sec

SELECT * FROM resource ORDER BY id DESC LIMIT 100,1;

-- 0.003 sec

SELECT * FROM resource ORDER BY id DESC LIMIT 1000,10;

-- 0.026 sec

SELECT * FROM resource ORDER BY id DESC LIMIT 10000,10;

-- 1.681 sec

SELECT * FROM resource ORDER BY id DESC LIMIT 490000,10;

100万条数据测试

-- 0.001 sec

SELECT * FROM resource_a ORDER BY id DESC LIMIT 1000,10;

-- 0.009 sec

SELECT * FROM resource_a ORDER BY id DESC LIMIT 10000,10;

-- 0.085 sec

SELECT * FROM resource_a ORDER BY id DESC LIMIT 90000,10;

 -- 0.905 sec

SELECT * FROM resource_a ORDER BY id DESC LIMIT 990000,10;

 

1000万条数据测试

-- 翻书 7.874334

-- 总耗时：13.842sec

SELECT * FROM resource_b ORDER BY id DESC LIMIT 9000000,10;

Sending data(收集+发送数据）

优化方案 

方案A：（《高性能MySQL》书中提到的）

--1000万条数据

-- 翻书 2.134621 

-- 总耗时 3.753sec

SELECT

  *

FROM

  resource_b a

  INNER JOIN

    (SELECT

      id

    FROM

      resource_b

    LIMIT 9000000, 10) AS b

    ON a.id = b.id;

方案B：（ id 递增的情况，中间的记录没有被物理删除过 ）

SELECT * FROM resource_b WHERE id > 9000000  ORDER BY id DESC  LIMIT 10;

 

 

优化方案性能对比

方法一：

--1000万条数据

-- 翻书 2.134621 

-- 总耗时 3.753sec

SELECT

  *

FROM

  resource_b a

  INNER JOIN

    (SELECT

      id

    FROM

      resource_b

    LIMIT 9000000, 10) AS b

    ON a.id = b.id;

 

-- 100万条数据

-- 翻书 0.156227

-- 总耗时 0.271sec

 SELECT

  *

FROM

  resource_a a

  INNER JOIN

    (SELECT

      id

    FROM

      resource_a

    LIMIT 900000 , 10) AS b

    ON a.id = b.id;

-- 50万条数据

-- 翻书 0.310197

-- 总耗时 0.558sec

 SELECT

  *

FROM

  resource a

  INNER JOIN

    (SELECT

      id

    FROM

      resource

    LIMIT 490000   , 10) AS b

    ON a.id = b.id;

 

方法二:

-- 1000万条数据

 -- 翻书 0.000049

 -- 总耗时0.0015sec

--  rang index

SELECT * FROM resource_b WHERE id > 9000000  ORDER BY id DESC  LIMIT 10;

 

-- 100万条数据

-- 翻书 0.000039

-- 总耗时0.001sec

SELECT * FROM resource_a WHERE id > 900000 ORDER BY id DESC LIMIT 10;

 

-- 50万条数据

 -- 翻书 0.000103

 -- 总耗时0.001sec

SELECT * FROM resource WHERE id > 490000  ORDER BY id DESC  LIMIT 10;

 

总结：

 在查询数据量limit a,b时b的大小没有太大影响，最终的影响点在a的大小上面。

50万数据resource表里LIMIT 490000,10 需要消耗1.68秒时间

100万数据resource_b表里LIMIT 990000,10耗时0.905秒（表结构不一样） 

1000万数据resource_a表里LIMIT 9000000,10总耗时：13.842秒（大概需要14秒时间）

优化方案A通过 join方式查询，1千万条数据 LIMIT 9000000,10总耗时 3.753sec性能提升了3倍

优化方案A通过id>9000000方式查询，1千万条数据总耗时0.0015sec