版权说明：本文档由用户提供并上传，收益归属内容提供方，若内容存在侵权，请进行举报或认领
文档简介
基于MySQL自增ID字段增量扫描研究1. 问题12. 背景13. InnoDB表 23.1. 自增ID为主键 23.2. 自增ID为普通索引 43.3. 原因分析74. MyISAM表 84.1. 自增ID为主键 84.2. 自增ID为普通索引 114.3. 原因分析145. 研究结论14
1.
问题对于MySQL表，如果自增ID不是主键时，是否可以用来做增量查询？2.
背景需要按照自增ID字段进行增量查询，有些表的自增ID是主键，而有些表的自增只是普通索引，有些采用MyISAM，有些采用InnoDB。如果采用粗暴的“SELECT
*
FROM
table
WHERE
f_id>M
ORDER
BY
f_id
LIMIT
N”，功能上没有任何问题，但当表的记录数很大时（比如1000万条），“ORDER
BY
f_id”会极影响查询效率。为此，需要弄清楚“SELECT
*
FROM
table
WHERE
f_id>M
LIMIT
N”的可行性，即增量查询时，不指定“ORDER
BY
f_id”。研究基于的MySQL（注：5.6.7之前最大分区数限制为1024，从5.6.7开始调整为8192，另外5.6版本分区表不支持HANDLER）：MySQL
[test]>
select
version();+-----------+|
version()
|+-----------+|
5.7.18
|+-----------+1
row
in
set
(0.01
sec)3.
InnoDB表3.1.
自增ID为主键建表SQL语句：DROP
TABLE
IF
EXISTS
`tableA1`;CREATE
TABLE
`tableA1`
(
`id`
BIGINT
NOT
NULL
AUTO_INCREMENT
PRIMARY
KEY,
`af`
INT
NOT
NULL,
`bf`
INT
NOT
NULL,
`cf`
INT
NOT
NULL,
INDEX
`idx_af`
(`af`),
INDEX
`idx_bf`
(`bf`))ENGINE=InnoDB;
依顺序执行下列插入操作：INSERT
INTO
tableA1
(af,bf,cf)
VALUES
(1,2,1);INSERT
INTO
tableA1
(af,bf,cf)
VALUES
(2,1,2);INSERT
INTO
tableA1
(id,af,bf,cf)
VALUES
(11,12,11,11);INSERT
INTO
tableA1
(id,af,bf,cf)
VALUES
(12,11,12,12);INSERT
INTO
tableA1
(af,bf,cf)
VALUES
(13,16,13);INSERT
INTO
tableA1
(id,af,bf,cf)
VALUES
(3,3,3,3);INSERT
INTO
tableA1
(af,bf,cf)
VALUES
(14,17,14);INSERT
INTO
tableA1
(id,af,bf,cf)
VALUES
(5,15,5,5);
查看结果：//
按自增ID有序（自增ID为主键）MySQL
[test]>
SELECT
*
FROM
tableA1;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
2
|
1
||
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
5
|
15
|
5
|
5
||
11
|
12
|
11
|
11
||
12
|
11
|
12
|
12
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
|+----+----+----+----+8
rows
in
set
(0.00
sec)//
按自增ID有序（自增ID为主键）MySQL
[test]>
SELECT
*
FROM
tableA1
WHERE
id>=1
LIMIT
10;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
2
|
1
||
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
5
|
15
|
5
|
5
||
11
|
12
|
11
|
11
||
12
|
11
|
12
|
12
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
|+----+----+----+----+8
rows
in
set
(0.00
sec)
//
按自增ID有序（自增ID为主键）MySQL
[test]>
SELECT
*
FROM
tableA1
WHERE
id>=2
LIMIT
10;
+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
5
|
15
|
5
|
5
||
11
|
12
|
11
|
11
||
12
|
11
|
12
|
12
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
|+----+----+----+----+7
rows
in
set
(0.00
sec)
//
按自增ID有序（自增ID为主键）MySQL
[test]>
SELECT
*
FROM
tableA1
WHERE
id>=2
LIMIT
7;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
5
|
15
|
5
|
5
||
11
|
12
|
11
|
11
||
12
|
11
|
12
|
12
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
|+----+----+----+----+7
rows
in
set
(0.00
sec)
可以看到，当自增ID为主键时，自增ID乱序插入，查询结果也是按自增ID有序（实测有序插入一样有序），因此可以放心依自增ID增量查询，而不必指定“ORDER
BY
f_id”。3.2.
自增ID为普通索引DROP
TABLE
IF
EXISTS
`tableA2`;CREATE
TABLE
`tableA2`
(
`id`
BIGINT
NOT
NULL
AUTO_INCREMENT,
`af`
INT
NOT
NULL,
`bf`
INT
NOT
NULL,
`cf`
INT
NOT
NULL,
UNIQUE
INDEX
`idx_af`
(`af`),
INDEX
`idx_id`
(`id`),
INDEX
`idx_bf`
(`bf`))ENGINE=InnoDB;
依顺序执行下列插入操作：INSERT
INTO
tableA2
(af,bf,cf)
VALUES
(1,2,1);INSERT
INTO
tableA2
(af,bf,cf)
VALUES
(2,1,2);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(11,12,11,11);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(12,11,12,12);INSERT
INTO
tableA2
(af,bf,cf)
VALUES
(13,16,13);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(3,3,3,3);INSERT
INTO
tableA2
(af,bf,cf)
VALUES
(14,17,14);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(5,15,5,5);
查看结果：//
总共8条记录MySQL
[test]>
SELECT
COUNT(1)
FROM
tableA2;+----------+|
COUNT(1)
|+----------+|
8
|+----------+1
row
in
set
(0.00
sec)
//
按自增ID无序，但按唯一索引有序MySQL
[test]>
SELECT
*
FROM
tableA2;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
2
|
1
||
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
12
|
11
|
12
|
12
||
11
|
12
|
11
|
11
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
||
5
|
15
|
5
|
5
|+----+----+----+----+8
rows
in
set
(0.00
sec)
//
按自增ID无序，但按唯一索引有序MySQL
[test]>
SELECT
*
FROM
tableA2
WHERE
id>=1
LIMIT
10;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
2
|
1
||
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
12
|
11
|
12
|
12
||
11
|
12
|
11
|
11
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
||
5
|
15
|
5
|
5
|+----+----+----+----+8
rows
in
set
(0.00
sec)
//
按自增ID无序，但按唯一索引有序MySQL
[test]>
SELECT
*
FROM
tableA2
WHERE
id>=2
LIMIT
10;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
12
|
11
|
12
|
12
||
11
|
12
|
11
|
11
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
||
5
|
15
|
5
|
5
|+----+----+----+----+7
rows
in
set
(0.00
sec)
//
按自增ID有序，但按唯一索引无序（LIMIT数小于表总记录数）MySQL
[test]>
SELECT
*
FROM
tableA2
WHERE
id>=2
LIMIT
5;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
5
|
15
|
5
|
5
||
11
|
12
|
11
|
11
||
12
|
11
|
12
|
12
|+----+----+----+----+5
rows
in
set
(0.00
sec)
//
按自增ID有序，但按唯一索引无序（LIMIT数小于表总记录数）MySQL
[test]>
SELECT
*
FROM
tableA2
WHERE
id>=1
LIMIT
7;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
2
|
1
||
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
5
|
15
|
5
|
5
||
11
|
12
|
11
|
11
||
12
|
11
|
12
|
12
||
13
|
13
|
16
|
13
|+----+----+----+----+7
rows
in
set
(0.00
sec)
//
更新一条记录MySQL
[test]>
UPDATE
tableA2
SET
id=15
WHERE
id=12;Query
OK,
1
row
affected
(0.00
sec)Rows
matched:
1
Changed:
1
Warnings:
0
//
按自增ID是无序的MySQL
[test]>
SELECT
*
FROM
tableA2
LIMIT
7;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
2
|
1
||
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
15
|
11
|
12
|
12
||
11
|
12
|
11
|
11
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
|+----+----+----+----+7
rows
in
set
(0.00
sec)//
按自增ID是有序的（LIMIT数小于表记录数）//
按唯一自增ID无序MySQL
[test]>
SELECT
*
FROM
tableA2
WHERE
id>=1
LIMIT
7;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
2
|
1
||
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
5
|
15
|
5
|
5
||
11
|
12
|
11
|
11
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
|+----+----+----+----+7
rows
in
set
(0.00
sec)
//
按自增ID是无序的（LIMIT数等于或大于表记录数）//
按唯一自增ID有序MySQL
[test]>
SELECT
*
FROM
tableA2
WHERE
id>=1
LIMIT
8;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
2
|
1
||
2
|
2
|
1
|
2
||
3
|
3
|
3
|
3
||
15
|
11
|
12
|
12
||
11
|
12
|
11
|
11
||
13
|
13
|
16
|
13
||
14
|
14
|
17
|
14
||
5
|
15
|
5
|
5
|+----+----+----+----+8
rows
in
set
(0.00
sec)
从测试可以看到，当LIMIT的数小于表的记录数时，结果是按自增ID有序返回。3.3.
原因分析InnoDB存储数据时，即按B+树结果存储，B+树的叶子结果保存完整的记录，表文件本身即为主索引（即主键），普通索引并不直接指向数据，而是指向了主索引。http://www.f-1.cc如对于表tableA2（tableA1结果相同）：INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(1,1,1,1);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(2,5,1,1);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(3,2,1,1);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(4,8,1,1);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(5,3,1,1);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(6,4,1,1);INSERT
INTO
tableA2
(id,af,bf,cf)
VALUES
(7,7,1,1);
MySQL
[test]>
SELECT
*
FROM
tableA2
WHERE
af>0
LIMIT
3;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
3
|
2
|
1
|
1
||
5
|
3
|
1
|
1
|+----+----+----+----+3
rows
in
set
(0.00
sec)
MySQL
[test]>
SELECT
*
FROM
tableA2
WHERE
af>0
LIMIT
10;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
3
|
2
|
1
|
1
||
5
|
3
|
1
|
1
||
6
|
4
|
1
|
1
||
2
|
5
|
1
|
1
||
7
|
7
|
1
|
1
||
4
|
8
|
1
|
1
|+----+----+----+----+7
rows
in
set
(0.00
sec)4.
MyISAM表4.1.
自增ID为主键建表SQL语句：DROP
TABLE
IF
EXISTS
`tableB1`;CREATE
TABLE
`tableB1`
(
`id`
BIGINT
NOT
NULL
AUTO_INCREMENT
PRIMARY
KEY,
`af`
INT
NOT
NULL,
`bf`
INT
NOT
NULL,
`cf`
INT
NOT
NULL,
INDEX
`idx_id`
(`id`),
INDEX
`idx_bf`
(`bf`))ENGINE=MyISAM;
依顺序执行下列插入操作：INSERT
INTO
tableB1
(af,bf,cf)
VALUES
(1,2,1);INSERT
INTO
tableB1
(af,bf,cf)
VALUES
(2,1,2);INSERT
INTO
tableB1
(id,af,bf,cf)
VALUES
(11,12,11,11);INSERT
INTO
tableB1
(id,af,bf,cf)
VALUES
(12,11,12,12);INSERT
INTO
tableB1
(af,bf,cf)
VALUES
(13,16,13);INSERT
INTO
tableB1
(id,af,bf,cf)
VALUES
(3,3,3,3);INSERT
INTO
tableB1
(af,bf,cf)
VALUES
(14,17,14);INSERT
INTO
tableB1
(id,af,bf,cf)
VALUES
(5,15,5,5);
查看结果：//
乱序MySQL
[test]>
SELECT
*
FROM
tableB1;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
2
|
2
|
2
|
2
||
11
|
11
|
11
|
11
||
12
|
12
|
12
|
12
||
13
|
13
|
13
|
13
||
3
|
3
|
3
|
3
||
14
|
14
|
14
|
14
||
5
|
5
|
5
|
5
|+----+----+----+----+8
rows
in
set
(0.00
sec)
//
乱序了MySQL
[test]>
SELECT
*
FROM
tableB1
WHERE
id>1
LIMIT
10;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
2
|
2
|
2
|
2
||
11
|
11
|
11
|
11
||
12
|
12
|
12
|
12
||
13
|
13
|
13
|
13
||
3
|
3
|
3
|
3
||
14
|
14
|
14
|
14
||
5
|
5
|
5
|
5
|+----+----+----+----+7
rows
in
set
(0.00
sec)
可以看到，结果并不是按自增ID有序，但是否意味着不能用来做增量查询了？继续看下面的操作：MySQL
[test]>
SELECT
COUNT(1)
FROM
tableB1;+----------+|
COUNT(1)
|+----------+|
8
|+----------+1
row
in
set
(0.00
sec)
MySQL
[test]>
SELECT
*
FROM
tableB1
WHERE
id>0
LIMIT
3;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
2
|
2
|
2
|
2
||
3
|
3
|
3
|
3
|+----+----+----+----+3
rows
in
set
(0.01
sec)
//
未乱序MySQL
[test]>
SELECT
*
FROM
tableB1
WHERE
id>0
LIMIT
7;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
2
|
2
|
2
|
2
||
3
|
3
|
3
|
3
||
5
|
5
|
5
|
5
||
11
|
11
|
11
|
11
||
12
|
12
|
12
|
12
||
13
|
13
|
13
|
13
|+----+----+----+----+7
rows
in
set
(0.00
sec)
//
乱序MySQL
[test]>
SELECT
*
FROM
tableB1
WHERE
id>0
LIMIT
8;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
2
|
2
|
2
|
2
||
11
|
11
|
11
|
11
||
12
|
12
|
12
|
12
||
13
|
13
|
13
|
13
||
3
|
3
|
3
|
3
||
14
|
14
|
14
|
14
||
5
|
5
|
5
|
5
|+----+----+----+----+8
rows
in
set
(0.00
sec)
MySQL
[jay_data]>
SELECT
*
FROM
tableB1
WHERE
id>6
LIMIT
3;
+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
11
|
11
|
11
|
11
||
12
|
12
|
12
|
12
||
13
|
13
|
13
|
13
|+----+----+----+----+3
rows
in
set
(0.01
sec)
MySQL
[jay_data]>
SELECT
*
FROM
tableB1
WHERE
id>8
LIMIT
3;
+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
11
|
11
|
11
|
11
||
12
|
12
|
12
|
12
||
13
|
13
|
13
|
13
|+----+----+----+----+3
rows
in
set
(0.00
sec)
这里发生了有趣的事，如果LIMIT指定的数小于表实际的记录数，则仍然是按ID有序，否则是ID是乱序的。但是实际遇到：即使LIMIT指定的数小于表实际的记录数，也会返回乱序的结果。4.2.
自增ID为普通索引建表SQL语句：DROP
TABLE
IF
EXISTS
`tableB2`;CREATE
TABLE
`tableB2`
(
`id`
BIGINT
NOT
NULL
AUTO_INCREMENT,
`af`
INT
NOT
NULL,
`bf`
INT
NOT
NULL,
`cf`
INT
NOT
NULL,
UNIQUE
INDEX
`idx_af`
(`af`),
INDEX
`idx_id`
(`id`),
INDEX
`idx_bf`
(`bf`))ENGINE=MyISAM;
依顺序执行下列插入操作：INSERT
INTO
tableB2
(af,bf,cf)
VALUES
(1,2,1);INSERT
INTO
tableB2
(af,bf,cf)
VALUES
(2,1,2);INSERT
INTO
tableB2
(id,af,bf,cf)
VALUES
(11,12,11,11);INSERT
INTO
tableB2
(id,af,bf,cf)
VALUES
(12,11,12,12);INSERT
INTO
tableB2
(af,bf,cf)
VALUES
(13,16,13);INSERT
INTO
tableB2
(id,af,bf,cf)
VALUES
(3,3,3,3);INSERT
INTO
tableB2
(af,bf,cf)
VALUES
(14,17,14);INSERT
INTO
tableB2
(id,af,bf,cf)
VALUES
(5,15,5,5);
查看结果：//
乱序MySQL
[test]>
SELECT
*
FROM
tableB2;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
2
|
2
|
2
|
2
||
11
|
11
|
11
|
11
||
12
|
12
|
12
|
12
||
13
|
13
|
13
|
13
||
3
|
3
|
3
|
3
||
14
|
14
|
14
|
14
||
5
|
5
|
5
|
5
|+----+----+----+----+8
rows
in
set
(0.00
sec)
自增ID不影响查询结果的顺序，继续看下面的操作：MySQL
[test]>
SELECT
COUNT(1)
FROM
tableB2;+----------+|
COUNT(1)
|+----------+|
8
|+----------+1
row
in
set
(0.01
sec)
MySQL
[test]>
SELECT
*
FROM
tableB2
WHERE
id>0
LIMIT
3;
+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
2
|
2
|
2
|
2
||
3
|
3
|
3
|
3
|+----+----+----+----+3
rows
in
set
(0.00
sec)
//
未乱序MySQL
[test]>
SELECT
*
FROM
tableB2
WHERE
id>0
LIMIT
7;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
2
|
2
|
2
|
2
||
3
|
3
|
3
|
3
||
5
|
5
|
5
|
5
||
11
|
11
|
11
|
11
||
12
|
12
|
12
|
12
||
13
|
13
|
13
|
13
|+----+----+----+----+7
rows
in
set
(0.00
sec)
//
乱序MySQL
[test]>
SELECT
*
FROM
tableB2
WHERE
id>0
LIMIT
8;+----+----+----+----+|
id
|
af
|
bf
|
cf
|+----+----+----+----+|
1
|
1
|
1
|
1
||
2
|
2
|
2
|
2
||
11
|
11
|
11
|
11
||
12
|
12
|
12
|
12
||
13
|
13
|
13
|
13
||
温馨提示
- 1. 本站所有资源如无特殊说明，都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等，如果需要附件，请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸，网页内容里面会有图纸预览，若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间，仅对用户上传内容的表现方式做保护处理，对用户上传分享的文档内容本身不做任何修改或编辑，并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容，请与我们联系，我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 产业帮扶协议书范本
- 产品设备补发补充协议书
- 2024年印度私有云和公有云金融服务行业状况及未来发展趋势报告
- 太宗治国之道苏教版必修三谏疏探微
- 北师大版初中生数学公式
- 基于学生认知的负数教学策略研究
- 北师大版五年级数学寒假作业题解
- 北师大近似数教学策略分析
- 燕子苏教版课文教学成果展示
- 四季变换一年级教学
- 云南省市政基础设施工程施工质量
- 儿科医生面试专业性问题及答案
- 建筑施工现场生活住宿区安全检查表
- 小学英语-Unit 4 What can you do C story time教学课件设计
- 信息安全技术与应用 完整全套教学课件 第1-6章 信息安全的形势与发展 - 新兴技术应用安全
- 2023年职业技能“母婴护理”资格证及继续教育基础知识考试题库（附含答案）
- 钢筋混凝土工程事故分析与处理
- 协医院手术操作特殊治疗同意书模板
- 第40届全国中学生物理竞赛预赛试题（2023.09.02）（PDF版含答案）
- 2022年日历表含农历A4打印版
- 食堂日常加工流程及标准
评论
0/150
提交评论