一文让你彻底弄懂MySQL自增列

MYSQL的自增列在实际生产中应用的非常广泛，相信各位所在的公司or团队，MYSQL开发规范中一定会有要求尽量使用自增列去充当表的主键，为什么DBA会有这样的要求，各位在使用MYSQL自增列时遇到过哪些问题？这些问题是由什么原因造成的呢？本文由浅入深，带领大家彻底弄懂MYSQL的自增机制。

成都创新互联公司是一家集网站建设,昭苏企业网站建设,昭苏品牌网站建设,网站定制,昭苏网站建设报价,网络营销,网络优化,昭苏网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。

1.  通过auto_increment关键字来指定自增的列，并指定自增列的初始值为1。

[root@localhost][test1]Create table t(id int auto_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是永远够用的。

这里内容比较多，innodb是索引组织表，所以涉及到索引的知识，但这不是本文的重点，我们快速回顾索引知识：

1.  Innodb索引分为主键跟辅助索引，主键即全表，辅助索引叶子节点保存主键的值，而主键的叶子节点保存数据行，中间节点存着叶子节点的路由值。

2.  Innodb存储数据（索引）的单位是页，这里默认是16K，这也意味着，数据本身越小，一个页中能存数据的量越多，而检索效率不仅仅由索引的层数来决定，更是由一次能够缓存的数据量来定，也就是说数据本身越小，则一次IO能够提取到缓冲区的数据越多（OS每次IO的量是固定的4K），查询的效率越好。

其实能够理解索引的结构及索引写入插入、更新的原理，则自然就明白为何建议使用自增id。这里我直接列出使用自增id 当主键的好处吧：

1.  顺序写入，避免了叶的分裂，数据写入效率好

2.  缩小了表的体积，特别是相比于UUID当主键，甚至组合字段当主键时，效果更明显

3.  查询效率好，原因就是我上面说到索引知识的第二点。

4.  某些情况下，我们可以利用自增id来统计大表的大致行数。

5.  在数据归档or垃圾数据清理时，也可方便的利用这个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. 如果XY，那么这个表的自增值不变；

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';

+----------------+

| Auto_increment |

+----------------+

|             12 |

+----------------+

1 row in set (0.00 sec)

可以看到，批量插入，导致下一个id值不为9了，再插入数据，即产生了空洞，这里是由mysql申请自增值的机制所造成的，MySQL在批量插入时，若一个值申请一个id，效率太慢，影响了批量插入的速度，故mysql采用下面的策略批量申请id。

1.  语句执行过程中，第一次申请自增id，会分配1个；

2.  1个用完以后，这个语句第二次申请自增id，会分配2个；

3.  2个用完以后，还是这个语句，第三次申请自增id，会分配4个；

4.  依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍。

在对自增列进行操作时，存在着自增锁，mysql的innodb_autoinc_lock_mode参数控制着自增锁的上锁机制。该参数有0、1、2三种模式：

0：语句执行结束后释放自增锁，MySQL5.0时采用这种模式，并发度较低。

1：mysql的默认设置。普通的insert语句申请后立马释放，insert select、replace insert、load data等批量插入语句要等语句执行结束后才释放，并发读得到提升

2：所有的语句都是申请后立马释放，并发度大大提升！但是在binlog为statement格式时，主从数据会发生不一致。这一块网上有很多介绍，我不做介绍了。

在彻底了解了MYSQL的自增机制以后，在实际生产中就能灵活避坑，这里建议不要用自增id值去当表的行数，当需要对大表准确统计行数时，可以去count(*)从库，如果业务很依赖大表的准确行数，直接弄个中间表来统计，或者考虑要不要用mysql的innodb来存储数据，这个是需要自己去权衡。另外对于要求很高的写入性能，但写入量又比较大的业务，自增id的使用依然存在热点写入的问题，存在性能瓶颈，这时候可通过分库分表来解决。

想在mysql数据库中的表中插入一列，怎么做？

传统情况

我们先回顾一下，在没有 "立刻加列" 功能时，加列操作是怎么完成的。我们也借此来熟悉一下本期的图例：

当进行 加列操作 时，所有的数据行 都必须要 增加一段数据（图中的 列 4 数据）

如上一期图解所讲，当改变数据行的长度，就需要 重建表空间（图中灰蓝的部分为发生变更的部分）

数据字典中的列定义也会被更新

以上操作的问题在于 每次加列 操作都需要重建表空间，这就需要大量 IO以及大量的时间

立刻加列

"立刻加列" 的过程如下图：

请点击输入图片描述

请点击输入图片描述

"立刻加列" 时，只会变更数据字典中的内容，包括：

在列定义中增加 新列的定义

增加 新列的默认值

"立刻加列" 后，当要读取表中的数据时：

由于 "立刻加列" 没有 变更行数据，读取的行数据只有 3 列

MySQL 会将 新增的第 4 列的默认值，追加到 读取的数据后

以上过程描述了 如何读取 在 "立刻加列" 之前写入的数据，其实质是：在读取数据的过程中，"伪造" 了一个新列出来

那么如何读取 在 "立刻加列" 之后 写入的数据呢 ? 过程如下图：

当读取 行 4 时：

请点击输入图片描述

请点击输入图片描述

通过判断 数据行的头信息中的instant 标志位，可以知道该行的格式是 "新格式"：该行头信息后有一个新字段 "列数"

通过读取 数据行的 "列数" 字段，可以知道 该行数据中多少列有 "真实" 的数据，从而按列数读取数据

通过上图可以看到：读取 在"立刻加列" 前/后写入的数据是不同的流程

通过以上的讨论，我们可以总结 "立刻加列" 之所以高效的原因是：

在执行 "立刻加列" 时，不变更数据行的结构

读取 "旧" 数据时，"伪造" 新增的列，使结果正确

写入 "新" 数据时，使用了新的数据格式（增加了instant标志位 和 "列数" 字段)，以区分新旧数据

读取 "新" 数据时，可以如实读取数据

那么 我们是否能一直 "伪造" 下去 ? "伪造" 何时会被拆穿 ?

考虑以下场景：

用 "立刻加列" 增加列 A

写入数据行 1

用 "立刻加列" 增加列 B

写入数据行 2

删除列 B

我们推测一下 "删除列 B" 的最小代价：需要修改 数据行中的instant标志位或 "列数" 字段，这至少会影响到 "立刻加列" 之后写入的数据行，成本类似于重建数据

从以上推测可知：当出现 与 "立刻加列" 操作不兼容 的 DDL 操作时，数据表需要进行重建，如下图所示：

请点击输入图片描述

请点击输入图片描述

扩展思考题：是否能设计其他的数据格式，取代instant标志位和 "列数" 字段，使得 加列/删列 操作都能 "立刻完成" ?（提示：考虑 加列 - 删列 - 再加列 的情况）

使用限制

在了解原理之后，我们来看看 "立刻加列" 的使用限制，就很容易能理解其中的前两项：

"立刻加列" 的加列位置只能在表的最后，而不能加在其他列之间

在元数据中，只记录了 数据行 应有多少列，而没有记录 这些列 应出现的位置。所以无法实现指定列的位置

"立刻加列" 不能添加主键列

加列 不能涉及聚簇索引的变更，否则就变成了 "重建" 操作，不是 "立刻" 完成了

"立刻加列"不支持压缩的表格式

按照 WL 的说法："COMPRESSED is no need to supported"（没必要支持不怎么用的格式）

总结回顾

我们总结一下上面的讨论：

"立刻加列" 之所以高效的原因是：

在执行 "立刻加列" 时，不变更数据行的结构

读取 "旧" 数据时，"伪造" 新增的列，使结果正确

写入 "新" 数据时，使用了新的数据格式 (增加了 instant 标志位 和 "列数" 字段)，以区分新旧数据

读取 "新" 数据时，可以如实读取数据

"立刻加列" 的 "伪造" 手法，不能一直维持下去。当发生 与 "立刻加列" 操作不兼容 的 DDL 时，表数据就会发生重建

回到之前遗留的两个问题：

"立刻加列" 是如何工作的 ?

我们已经解答了这个问题

所谓 "立刻加列" 是否完全不影响业务，是否是真正的 "立刻" 完成 ?

可以看到：就算是 "立刻加列"，也需要变更 数据字典，那么 该上的锁还是逃不掉的。也就是说 这里的 "立刻" 指的是 "不变更数据行的结构"，而并非指 "零成本地完成任务"

mysql给表增加字段会锁表，怎样才可以不锁表吗

锁表一般是长时间占用表导致的，

试着使SELECT语句运行得更快；你可能必须创建一些摘要(summary)表做到这点。

用--low-priority-updates启动mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下，在先前情形的最后的SELECT语句将在INSERT语句前执行。

你可以用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级。

为max_write_lock_count指定一个低值来启动mysqld使得在一定数量的WRITE锁定后给出READ锁定。

通过使用SQL命令：SET SQL_LOW_PRIORITY_UPDATES=1，你可从一个特定线程指定所有的更改应该由用低优先级完成

mysql给表增加字段会锁表,怎样才可以不锁表吗?

这个是属于系统遗留问题，也就是一种系统的保护机制。就是为了避免出现这种在线修改系统的操作。

增加字段属于系统的修改操作。尽量不要在线操作，因为可能出现。未知的漏洞。一定要。离线。修改完毕，然后经过测试后。认为已经没有问题了。在。次日的凌晨发一个通知。停机维护。这样才能保证系统的正常运转。

如果在前期设置系统的时候就预留了。热升级的空间。这样才能达到在线操作的目的，而且系统的金融群总是一部分先升级。

很多情况下，你需要使用系统里边的工具集。在线修改表格。原理其实非常的简单，新建的和原表的表格结构。要一模一样。对这个表格进行修改，然后把结构变更的日期。插入进去。而且还建议您尽量在业务的低缝隙进行修改。避免发生不可控的未知状况。

使用说明：

1、如果是用 MySQL + Apache，使用的又是 FreeBSD 网络操作系统的话，安装时候你应按注意到FreeBSD的版本问题，在FreeBSD 的 3.0 以下版本来说，MySQL Source 内含的 MIT-pthread 运行是正常的，但在这版本以上，你必须使用 native threads。

2、如果在 COMPILE 过程中出了问题，请先检查你的 gcc版本是否在 2.81 版本以上，gmake 版本是否在3.75以上。

3、如果不是版本的问题，那可能是你的内存不足，请使用configure--with-low-memory 来加入。

4、如果要重新做你的configure，那么你可以键入rm config.cache和make clean来清除记录。

5、把 MySQL 安装在 /usr/local 目录下，这是缺省值，您也可以按照你的需要设定你所安装的目录。

mysql主不锁定的状况下怎么添加新的从节点

目前的基本环境：

主DB：192.168.1.101

从DB1：192.168.1.102

从DB2：192.168.1.103(准备在线添加做为从机的mysql数据库)

数据库版本：mysql 5.1.61(select version())

服务器版本：red hat linux 6

注：目前主从正常(show slave statusG)，同步的数据库为 login

3台DB server的备份软件(xtrbackup)都已经安装完成

同步的表为innodb引擎的表，不然用xtrabackup还是会锁表

假设环境为：现在login库下的表a,备份的时候插入数据，最终行为5418272

注：思路是备份，可是用mysqldump备份会锁表，如果数据量很大的话，线上环境肯定不允许使用mysqldump备份！

mysql自增id列怎么设置？

create table cdat

(

localt char(20) not null,

cd char(5) not null,

snosat char(2) not null,

rnorec char(3) not null,

id INT(20) not null AUTO_INCREMENT,

primary key (id)

);

MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

系统特性

1．使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性

2．支持AIX、FreeBSD、HP-UX、Linux、Mac OS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris、Windows等多种操作系统

3．为多种编程语言提供了API。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

4．支持多线程，充分利用CPU资源

5．优化的SQL查询算法，有效地提高查询速度

6．既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中。

7．提供多语言支持，常见的编码如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名。

8．提供TCP/IP、ODBC和JDBC等多种数据库连接途径。

9．提供用于管理、检查、优化数据库操作的管理工具。

10．支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

11．支持多种存储引擎。

索引功能

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。索引不是万能的，索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE选项的作用将非常明显。另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。从理论上讲，完全可以为数据表里的每个字段分别建一个索引，但MySQL把同一个数据表里的索引总数限制为16个。

1．InnoDB数据表的索引

与InnoDB数据表相比，在InnoDB数据表上，索引对InnoDB数据表的重要性要大得多。在InnoDB数据表上，索引不仅会在搜索数据记录时发挥作用，还是数据行级锁定机制的苊、基础。“数据行级锁定”的意思是指在事务操作的执行过程中锁定正在被处理的个别记录，不让其他用户进行访问。这种锁定将影响到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE命令以及INSERT、UPDATE和DELETE命令。出于效率方面的考虑，InnoDB数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。显然，数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。

2．限制

如果WHERE子句的查询条件里有不等号（WHEREcoloum!=），MySQL将无法使用索引。类似地，如果WHERE子句的查询条件里使用了函数（WHEREDAY（column）=），MySQL也将无法使用索引。在JOIN操作中（需要从多个数据表提取数据时），MySQL只有在主键和外键的数据类型相同时才能使用索引。

如果WHERE子句的查询条件里使用比较操作符LIKE和REGEXP，MySQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKE'abc%‘，MySQL将使用索引；如果查询条件是LIKE'%abc’，MySQL将不使用索引。

在ORDERBY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。（虽然如此，在涉及多个数据表查询里，即使有索引可用，那些索引在加快ORDERBY方面也没什么作用）。如果某个数据列里包含许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含的净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

分享题目：mysql不锁表怎么去增加列,mysql加索引不锁表
文章路径：http://pwwzsj.com/article/hojjgd.html