基于MySQL自增ID字段增量扫描研究

Bertha 。 2022-03-28 09:22 184阅读 0赞

# **目录** #

目录 1

1. 问题 1

2. 背景 1

3. InnoDB表 2

3.1. 自增ID为主键 2

3.2. 自增ID为普通索引 4

3.3. 原因分析 7

4. MyISAM表 8

4.1. 自增ID为主键 8

4.2. 自增ID为普通索引 11

4.3. 原因分析 14

5. 研究结论 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**）：

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> MySQL&nbsp;[test]&gt;&nbsp;select&nbsp;version(); +-----------+ |&nbsp;version()&nbsp;| +-----------+ |&nbsp;5.7.18&nbsp;&nbsp;&nbsp;&nbsp;| +-----------+ 1&nbsp;row&nbsp;in&nbsp;set&nbsp;(0.01&nbsp;sec) </td> 
 </tr> 
 </tbody> 
</table>

# **3. InnoDB表** #

## **3.1. 自增ID为主键** ##

建表SQL语句：

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;`tableA1`; CREATE&nbsp;TABLE&nbsp;`tableA1`&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;`id`&nbsp;BIGINT&nbsp;NOT&nbsp;NULL&nbsp;AUTO_INCREMENT&nbsp;PRIMARY&nbsp;KEY, &nbsp;&nbsp;&nbsp;&nbsp;`af`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;`bf`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;`cf`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;INDEX&nbsp;`idx_af`&nbsp;(`af`), &nbsp;&nbsp;&nbsp;&nbsp;INDEX&nbsp;`idx_bf`&nbsp;(`bf`) )ENGINE=InnoDB; </td> 
 </tr> 
 </tbody> 
</table>

依顺序执行下列插入操作：

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> INSERT&nbsp;INTO&nbsp;tableA1&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(1,2,1); INSERT&nbsp;INTO&nbsp;tableA1&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(2,1,2); INSERT&nbsp;INTO&nbsp;tableA1&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(11,12,11,11); INSERT&nbsp;INTO&nbsp;tableA1&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(12,11,12,12); INSERT&nbsp;INTO&nbsp;tableA1&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(13,16,13); INSERT&nbsp;INTO&nbsp;tableA1&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(3,3,3,3); INSERT&nbsp;INTO&nbsp;tableA1&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(14,17,14); INSERT&nbsp;INTO&nbsp;tableA1&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(5,15,5,5); </td> 
 </tr> 
 </tbody> 
</table>

查看结果：

可以看到，当自增ID为主键时，自增ID乱序插入，查询结果也是按自增ID有序（实测有序插入一样有序），因此可以放心依自增ID增量查询，而不必指定“**ORDER BY f\_id**”。

## **3.2. 自增ID为普通索引** ##

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;`tableA2`; CREATE&nbsp;TABLE&nbsp;`tableA2`&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;`id`&nbsp;BIGINT&nbsp;NOT&nbsp;NULL&nbsp;AUTO_INCREMENT, &nbsp;&nbsp;&nbsp;&nbsp;`af`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;`bf`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;`cf`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;UNIQUE&nbsp;INDEX&nbsp;`idx_af`&nbsp;(`af`), &nbsp;&nbsp;&nbsp;&nbsp;INDEX&nbsp;`idx_id`&nbsp;(`id`), &nbsp;&nbsp;&nbsp;&nbsp;INDEX&nbsp;`idx_bf`&nbsp;(`bf`) )ENGINE=InnoDB; </td> 
 </tr> 
 </tbody> 
</table>

依顺序执行下列插入操作：

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> INSERT&nbsp;INTO&nbsp;tableA2&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(1,2,1); INSERT&nbsp;INTO&nbsp;tableA2&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(2,1,2); INSERT&nbsp;INTO&nbsp;tableA2&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(11,12,11,11); INSERT&nbsp;INTO&nbsp;tableA2&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(12,11,12,12); INSERT&nbsp;INTO&nbsp;tableA2&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(13,16,13); INSERT&nbsp;INTO&nbsp;tableA2&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(3,3,3,3); INSERT&nbsp;INTO&nbsp;tableA2&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(14,17,14); INSERT&nbsp;INTO&nbsp;tableA2&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(5,15,5,5); </td> 
 </tr> 
 </tbody> 
</table>

查看结果：

从测试可以看到，当LIMIT的数小于表的记录数时，结果是按自增ID有序返回。

## **3.3. 原因分析** ##

InnoDB存储数据时，即按B+树结果存储，B+树的叶子结果保存完整的记录，表文件本身即为主索引（即主键），普通索引并不直接指向数据，而是指向了主索引。

如对于表tableA2（tableA1结果相同）：

# **4. MyISAM表** #

## **4.1. 自增ID为主键** ##

建表SQL语句：

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;`tableB1`; CREATE&nbsp;TABLE&nbsp;`tableB1`&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;`id`&nbsp;BIGINT&nbsp;NOT&nbsp;NULL&nbsp;AUTO_INCREMENT&nbsp;PRIMARY&nbsp;KEY, &nbsp;&nbsp;&nbsp;&nbsp;`af`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;`bf`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;`cf`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;INDEX&nbsp;`idx_id`&nbsp;(`id`), &nbsp;&nbsp;&nbsp;&nbsp;INDEX&nbsp;`idx_bf`&nbsp;(`bf`) )ENGINE=MyISAM; </td> 
 </tr> 
 </tbody> 
</table>

依顺序执行下列插入操作：

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> INSERT&nbsp;INTO&nbsp;tableB1&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(1,2,1); INSERT&nbsp;INTO&nbsp;tableB1&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(2,1,2); INSERT&nbsp;INTO&nbsp;tableB1&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(11,12,11,11); INSERT&nbsp;INTO&nbsp;tableB1&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(12,11,12,12); INSERT&nbsp;INTO&nbsp;tableB1&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(13,16,13); INSERT&nbsp;INTO&nbsp;tableB1&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(3,3,3,3); INSERT&nbsp;INTO&nbsp;tableB1&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(14,17,14); INSERT&nbsp;INTO&nbsp;tableB1&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(5,15,5,5); </td> 
 </tr> 
 </tbody> 
</table>

查看结果：

可以看到，结果并不是按自增ID有序，但是否意味着不能用来做增量查询了？继续看下面的操作：

这里发生了有趣的事，如果LIMIT指定的数小于表实际的记录数，则仍然是按ID有序，否则是ID是乱序的。但是实际遇到：即使LIMIT指定的数小于表实际的记录数，也会返回乱序的结果。

## **4.2. 自增ID为普通索引** ##

建表SQL语句：

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;`tableB2`; CREATE&nbsp;TABLE&nbsp;`tableB2`&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;`id`&nbsp;BIGINT&nbsp;NOT&nbsp;NULL&nbsp;AUTO_INCREMENT, &nbsp;&nbsp;&nbsp;&nbsp;`af`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;`bf`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;`cf`&nbsp;INT&nbsp;NOT&nbsp;NULL, &nbsp;&nbsp;&nbsp;&nbsp;UNIQUE&nbsp;INDEX&nbsp;`idx_af`&nbsp;(`af`), &nbsp;&nbsp;&nbsp;&nbsp;INDEX&nbsp;`idx_id`&nbsp;(`id`), &nbsp;&nbsp;&nbsp;&nbsp;INDEX&nbsp;`idx_bf`&nbsp;(`bf`) )ENGINE=MyISAM; </td> 
 </tr> 
 </tbody> 
</table>

依顺序执行下列插入操作：

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> INSERT&nbsp;INTO&nbsp;tableB2&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(1,2,1); INSERT&nbsp;INTO&nbsp;tableB2&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(2,1,2); INSERT&nbsp;INTO&nbsp;tableB2&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(11,12,11,11); INSERT&nbsp;INTO&nbsp;tableB2&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(12,11,12,12); INSERT&nbsp;INTO&nbsp;tableB2&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(13,16,13); INSERT&nbsp;INTO&nbsp;tableB2&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(3,3,3,3); INSERT&nbsp;INTO&nbsp;tableB2&nbsp;(af,bf,cf)&nbsp;VALUES&nbsp;(14,17,14); INSERT&nbsp;INTO&nbsp;tableB2&nbsp;(id,af,bf,cf)&nbsp;VALUES&nbsp;(5,15,5,5); </td> 
 </tr> 
 </tbody> 
</table>

查看结果：

自增ID不影响查询结果的顺序，继续看下面的操作：

现象和自增ID为主键时完全相同。

## **4.3. 原因分析** ##

MyISAM的索引也是B+树结构，但索引文件和数据文件**分开存储**在不同文件。如果LIMIT的值达到或超过表的总记录数，则查询直接扫描数据文件，因此如果不指定“**ORDER BY f\_id**”，则返回结果和插入顺序一致。但如果LIMIT的值小于表的总记录数，则和InnoDB一样扫描索引，因此可以不指定“**ORDER BY f\_id**”。MyISAM的主键（主索引）和普通索引没有本质区别，只是主键有唯一性约束，而普通索引可重复。

# **5. 研究结论** #

实际情况会更复杂，比如有修改有删除，这些都需要是一步测试，甚至可能和版本相关。即使是聚集索引，不指定“**ORDER BY f\_id**”，也没法保证顺序。如果对数据没有严格的要求，可以考虑不指定“**ORDER BY f\_id**”，但如果必须不多不少，则必须带上“**ORDER BY f\_id**”，不管是InnoDB还是MyISAM，也不管自增ID是主键还是非主键。但是对于一张大表，加上“**ORDER BY f\_id**”后的查询性能可能降低一个甚至更多数量级。谨记：MySQL没有默认顺序这个概念。

**在使用“****ORDER BY f\_id****”时，请指定f\_id的上下限，这样能够保证较好的性能，比如：“WHERE f\_id>=N AND f\_id<=M”，否则如果只有上限或下限，性能可能会受到很大影响，建议用EXPLAIN了解详情**。

如果实在不想用**ORDER BY f\_id**”，还可以考虑如下方式（query可能返回空可能是扫描完了，也可能是该段是空隙无数据）：

<table style="width:426.1pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:426.1pt;"> const&nbsp;int&nbsp;step&nbsp;=&nbsp;1000; while&nbsp;(true) { &nbsp;&nbsp;const&nbsp;std::string&amp;&nbsp;sql&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;format_string( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"SELECT&nbsp;f_id,f_a,f_b,f_c,f_d&nbsp;FROM&nbsp;table&nbsp;" &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"WHERE&nbsp;f_id&nbsp;BETWEEN&nbsp;%u&nbsp;AND&nbsp;(%u+step)", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;id,&nbsp;id); &nbsp;&nbsp;mysql.query(sql); &nbsp;&nbsp;id&nbsp;+=&nbsp;step&nbsp;+&nbsp;1; } </td> 
 </tr> 
 </tbody> 
</table>