mysql按顺序⾃增长_⼀⽂让你彻底弄懂MySQL⾃增列
背景
MYSQL的⾃增列在实际⽣产中应⽤的⾮常⼴泛，相信各位所在的公司or团队，MYSQL开发规范中⼀定会有要求尽量使⽤⾃增列去充当表的主键，为什么DBA会有这样的要求，各位在使⽤MYSQL⾃增列时遇到过哪些问题？这些问题是由什么原因造成的呢？本⽂由浅⼊深，带领⼤家彻底弄懂MYSQL的⾃增机制。
基础扫盲
1. 通过auto_increment关键字来指定⾃增的列，并指定⾃增列的初始值为1。
[root@localhost][test1]>Create table t(id intauto_increment,namevarchar(10),primary key(id))auto_increment=1;
QueryOK, 0 rows affected (0.63 sec)
2. ⾃增列上必须有索引，将t表的主键索引删除掉，会报错
[root@localhost][test1]>alter table t drop primary key;
ERROR1075 (42000): Incorrect table definition; there can be only one auto column andit must be defined as a key
3. 设定auto_increment_increment参数，可以调整⾃增步长，该参数有session级跟global级，在分库分表以及双主or多主的模式下⽐较有⽤。
4. ⼀个表上只能有⼀个⾃增列
5. Mysql5.7及以下版本，innodb表的⾃增值保存在内存中，重启后表的⾃增值会设为max(id)+1，⽽myisam引擎的⾃增值是保存在⽂件中，重启不会丢失。Mysql8.0开始，innodb的⾃增id能持久化了，重启mysql，⾃增ID不会丢。
⾃增列的上限
⾸先：表中⾃增列的上限是根据⾃增列的字段类型来定的。
若设定了⾃增id充当主键，当达到了⾃增id的上限值时，会发⽣什么样的事情呢？还是以上⾯创建的 t表为例， 先回顾它的表结构：
CREATETABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_binDEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
⽆符号的int类型，上限是2147483647。这⾥我们将表的⾃增值设为2147483647，再插⼊两⾏数据：
[root@localhost][test1]>alter table t auto_increment=2147483647;
QueryOK, 0 rows affected (0.01 sec)
Records:0 Duplicates: 0 Warnings: 0
[root@localhost][test1]>insert into t(name) values ('test');
QueryOK, 1 row affected (0.01 sec)
[root@localhost][test1]>insert into t(name) values ('test');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
可以看到，第⼀个插⼊没问题，因为⾃增列的值为2147483647，这是达到了上限，还没有超过，第⼆⾏数据插⼊时，则报出主键重复，在达到上限后，⽆法再分配新的更⼤的⾃增值，也没有从1开始从头分配，在这⾥表的auto_increment值会⼀直是2147483647。
对于写⼊量⼤，且经常删除数据的表，⾃增id设为int类型还是偏⼩的，所以我们为了避免出现⾃增id涨满的情况，这边统⼀建议⾃增id的类型设为unsigned bingint，这样基本可以保障表的⾃增id是永远够⽤的。
⾃增id的优势
这⾥内容⽐较多，innodb是索引组织表，所以涉及到索引的知识，但这不是本⽂的重点，我们快速回顾索引知识：
1. Innodb索引分为主键跟辅助索引，主键即全表，辅助索引叶⼦节点保存主键的值，⽽主键的叶⼦节点保存数据⾏，中间节点存着叶⼦节点的路由值。
2. Innodb存储数据(索引)的单位是页，这⾥默认是16K，这也意味着，数据本⾝越⼩，⼀个页中能存数据的量越多，⽽检索效率不仅仅由索引的层数来决定，更是由⼀次能够缓存的数据量来定，也就是
说数据本⾝越⼩，则⼀次IO能够提取到缓冲区的数据越多(OS每次IO的量是固定的4K)，查询的效率越好。
其实能够理解索引的结构及索引写⼊插⼊、更新的原理，则⾃然就明⽩为何建议使⽤⾃增id。这⾥我直接列出使⽤⾃增id 当主键的好处吧：
1. 顺序写⼊，避免了叶的分裂，数据写⼊效率好
2. 缩⼩了表的体积，特别是相⽐于UUID当主键，甚⾄组合字段当主键时，效果更明显
3. 查询效率好，原因就是我上⾯说到索引知识的第⼆点。
4. 某些情况下，我们可以利⽤⾃增id来统计⼤表的⼤致⾏数。
5. 在数据归档or垃圾数据清理时，也可⽅便的利⽤这个id去操作，效率⾼。
⾃增id的问题
容易出现不连续的id
有的同志会发现，⾃⼰的表中id值存在空洞，如类似于1、2、3、8、9、10这样，有的适合有想依赖于
⾃增id的连续性来实现业务逻辑，所以会想⽅设法去修改id让其变的连续，其实，这是没有必要的，这⼀块的业务逻辑交由MySQL实现是很不理智的，表的记录⼩还好，要是表的数据量很⼤，修改起来就糟糕了。那么，为什么⾃增id会容易出现空洞呢？
⾃增id的修改机制如下：
在MySQL⾥⾯，如果字段id被定义为AUTO_INCREMENT，在插⼊⼀⾏数据的时候，⾃增值的⾏为如下：
1. 如果插⼊数据时id字段指定为0、null 或未指定值，那么就把这个表当前的
AUTO_INCREMENT值填到⾃增字段；
2. 如果插⼊数据时id字段指定了具体的值，就直接使⽤语句⾥指定的值。
根据要插⼊的值和当前⾃增值的⼤⼩关系，⾃增值的变更结果也会有所不同。假设，某次要插⼊的值是X，当前的⾃增值是Y。
1. 如果X
2. 如果X≥Y，就需要把当前⾃增值修改为新的⾃增值。
新的⾃增值⽣成算法是：从auto_increment_offset开始，以auto_increment_increment为步长，持续叠加，直到到第⼀个⼤于X的值，作为新的⾃增值。
Insert、update、delete操作会让id不连续。
Delete、update：删除中间数据，会造成空动，⽽修改⾃增id值，也会造成空洞(这个很少)。
Insert：插⼊报错(唯⼀键冲突与事务回滚)，会造成空洞，因为这时候⾃增id已经分配出去了，新的⾃增值已经⽣成，如下⾯例⼦：
[root@localhost][test1]> select * fromt;
+----+------+
| id | name |
| 1| aaa |
| 2| aaa |
| 3| aaa |
| 4| aaa |
+----+------+
4 rows in set (0.00 sec)
[root@localhost][test1]> selectAuto_increment from information_schema.tables where table_name='t'; +----------------+
| Auto_increment |
+----------------+
| 5 |
+----------------+
1 row in set (0.00 sec)
[root@localhost][test1]> begin;
Query OK, 0 rows affected (0.00 sec)
[root@localhost][test1]> insert intot(name) values('aaa');
Query OK, 1 row affected (0.00 sec)
[root@localhost][test1]> select * fromt;
+----+------+
| id | name |
+----+------+
| 1| aaa |
| 2| aaa |
| 3| aaa |
| 4| aaa |
| 5| aaa |
+----+------+
5 rows in set (0.00 sec)
[root@localhost][test1]> selectAuto_increment from information_schema.tables where table_name='t'; +----------------+
| Auto_increment |
+----------------+
| 6 |
1 row in set (0.00 sec)
[root@localhost][test1]> rollback;
Query OK, 0 rows affected (0.00 sec)
[root@localhost][test1]> selectAuto_increment from information_schema.tables where table_name='t'; +----------------+
| Auto_increment |
+----------------+
| 6 |
+----------------+
1 row in set (0.01 sec)
[root@localhost][test1]> select * fromt;
+----+------+
| id | name |
+----+------+
| 1| aaa |
| 2| aaa |
| 3| aaa |
| 4| aaa |
+----+------+
4 rows in set (0.00 sec)
可以看到，虽然事务回滚了，但⾃增id已经回不到从前啦，唯⼀键冲突也是这样的，这⾥就不做测试了。
在批量插⼊时(insert select等)，也存在空洞的问题。看下⾯实验：
[root@localhost][test1]> select * fromt;
+----+------+
| id | name |
+----+------+
| 1| aaa |
| 2| aaa |
| 3| aaa |
| 4| aaa |
+----+------+
4 rows in set (0.00 sec)
[root@localhost][test1]> selectAuto_increment from information_schema.tables where table_name='t';
| Auto_increment |
+----------------+
| 5 |
+----------------+
1 row in set (0.00 sec)
[root@localhost][test1]> insert intot(name) select name from t;
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
[root@localhost][test1]> select * fromt;
+----+------+
| id | name |
+----+------+
| 1| aaa |
| 2| aaa |
| 3| aaa |
| 4| aaa |
| 5| aaa |
| 6| aaa |
| 7| aaa |
| 8| aaa |
+----+------+
8 rows in set (0.00 sec)
[root@localhost][test1]> selectAuto_increment from information_schema.tables where table_name='t';
insert语句字段顺序+----------------+
| Auto_increment |
+----------------+
| 12 |
+----------------+
1 row in set (0.00 sec)
可以看到，批量插⼊，导致下⼀个id值不为9了，再插⼊数据，即产⽣了空洞，这⾥是由mysql申请⾃增值的机制所造成的，MySQL在批量插⼊时，若⼀个值申请⼀个id，效率太慢，影响了批量插⼊的速度，故mysql采⽤下⾯的策略批量申请id。
1. 语句执⾏过程中，第⼀次申请⾃增id，会分配1个；
2. 1个⽤完以后，这个语句第⼆次申请⾃增id，会分配2个；
发表评论