limit优化

limit三种形式优化

1、通过最最大ID实现

SELECT
*
FROM
center_account_virtual
WHERE
id >= (
SELECT
id
FROM
center_account_virtual
WHERE
STATUS = 1
ORDER BY
id
LIMIT 3292500,
1
)
AND STATUS = 1
ORDER BY
id
LIMIT 20;

2、通过左关联实现
SELECT
*
FROM
(
SELECT
id
FROM
center_account_virtual
WHERE
STATUS = 1
ORDER BY
id
LIMIT 3292500,
20
) a
LEFT JOIN center_account_virtual b ON a.id = b.id;

3、通过内关联实现
高性能mysql 中的建议方式
SELECT
*
FROM
center_account_virtual
INNER JOIN (
SELECT
id
FROM
center_account_virtual
WHERE
STATUS = 1
ORDER BY
id
LIMIT 3292500,
20
) a USING (id);

建表语句：

CREATE TABLEcenter_account_virtual(
idint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
user_typetinyint(1) DEFAULT NULL COMMENT '用户类型(0:平台 1:盟商 2:借款人 3:投资人 数据字典-UserType)',
subject_novarchar(30) DEFAULT '' COMMENT '科目编码',
account_namevarchar(200) DEFAULT '' COMMENT '账户名称',
account_novarchar(64) DEFAULT '' COMMENT '账号',
user_idvarchar(50) DEFAULT '' COMMENT '用户ID',
user_namevarchar(100) DEFAULT NULL COMMENT '客户名',
balancebigint(20) DEFAULT '0' COMMENT '总余额',
statustinyint(1) DEFAULT NULL COMMENT '状态1:正常,-1:删除',
remarkvarchar(200) DEFAULT '' COMMENT '备注',
create_timetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_timetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
PRIMARY KEY (id),
UNIQUE KEYidx_un_account_virtual_account_no(account_no) USING BTREE,
UNIQUE KEYidx_un_account_virtual_user_id_01(user_id,user_type,subject_no)
) ENGINE=InnoDB AUTO_INCREMENT=3729451 DEFAULT CHARSET=utf8 COMMENT='虚拟账户表';