基于MySQL自增ID字段增量扫描研究
基于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”。http:/www.gw638.cn3.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 r