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);
建表语句:
center_account_virtual
CREATE TABLE(
id
int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
user_type
tinyint(1) DEFAULT NULL COMMENT '用户类型(0:平台 1:盟商 2:借款人 3:投资人 数据字典-UserType)',
subject_no
varchar(30) DEFAULT '' COMMENT '科目编码',
account_name
varchar(200) DEFAULT '' COMMENT '账户名称',
account_no
varchar(64) DEFAULT '' COMMENT '账号',
user_id
varchar(50) DEFAULT '' COMMENT '用户ID',
user_name
varchar(100) DEFAULT NULL COMMENT '客户名',
balance
bigint(20) DEFAULT '0' COMMENT '总余额',
status
tinyint(1) DEFAULT NULL COMMENT '状态1:正常,-1:删除',
remark
varchar(200) DEFAULT '' COMMENT '备注',
create_time
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
id
PRIMARY KEY (),
idx_un_account_virtual_account_no
UNIQUE KEY(
account_no) USING BTREE,
idx_un_account_virtual_user_id_01
UNIQUE KEY(
user_id,
user_type,
subject_no)
) ENGINE=InnoDB AUTO_INCREMENT=3729451 DEFAULT CHARSET=utf8 COMMENT='虚拟账户表';