mysql increament_Mysql auto_increment总结

一、为什么InnoDB表要建议用自增列做主键

我们先了解下InnoDB引擎表的一些关键特征：

InnoDB引擎表是基于B+树的索引组织表(IOT)；

每个表都需要有一个聚集索引(clustered index)；

所有的行记录都存储在B+树的叶子节点(leaf pages of the tree)；

基于聚集索引的增、删、改、查的效率相对是最高的；

如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；

如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；

如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：

使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；

该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；

除此以外，如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。

实际情况是如何呢？经过简单TPCC基准测试，修改为使用自增列作为主键与原始表结构分别进行TPCC测试，前者的TpmC结果比后者高9%倍，足见使用自增列做InnoDB表主键的明显好处，其他更多不同场景下使用自增列的性能提升可以自行对比测试下。

注意：同时推荐使用UNSIGNED自增列作为主键。

DROP TABLE IF EXISTS`test_auto_increment`;CREATE TABLE`test_auto_increment` (

`id`int(11) unsigned NOT NULLAUTO_INCREMENT,

`name`varchar(255) DEFAULT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

二、mysql获取自增ID的最大值

在关系型数据库的表结构中，一般情况下，都会定义一个具有‘AUTO_INCREMENT’扩展属性的‘ID’字段，以确保数据表的每一条记录都有一个唯一标识。

而实际应用中，获取到最近最大的ID值是必修课之一，针对于该问题，实践整理如下：

1、新建测试数据表get_max_id

--------------------------------Table structure for test_auto_increment------------------------------

DROP TABLE IF EXISTS`test_auto_increment`;CREATE TABLE`test_auto_increment` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`name`varchar(255) DEFAULT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

2、未初始化表获取最大自增ID

创建完数据表之后，我们知道，表中的内容暂时为空，此时，查询max(id)获取到的内容将是NULL；

方式1 - max(id)：

该方式的优点是简单粗暴，直奔主题；

同时，它无视其它客户端连接(db_connection)的影响，可以直奔第3点位置；

select max(id) from test_auto_increment;

方式2 - LAST_INSERT_ID()函数：

LAST_INERT_ID()，返回最后一个INSERT或 UPDATE 查询中， AUTO_INCREMENT列设置的第一个表的值。

这玩意儿的使用还是有些限制的：

1、同一个Connection连接对象(同一客户端)中，SELECT的结果为最后一次INSERT的AUTO_INCREMENT属性列的ID。这句话的重点在于“同一个”，即其他连接的客户端不对其查询的结果造成影响。假设客户端A和B，表ta原自增ID为3，在A中插入记录后产生自增ID为4，在客户端A中通过该函数查询的结果为4，但在客户端B中查询的结果值仍为3；(已验证)

2、与表无关，即假设ta表和tb表，向ta插入记录后，再向tb插入记录，结果值为tb的max(id)值；(已验证)

3、使用非魔术方法(‘magic‘)来INSERT或UPDATE一条记录时，即使用非0/非NULL值作为插入的字段，则LAST_INSERT_ID()返回值不会发生变化；(已验证)

4、同一条INSERT语句中，传入多个VALUES值，则LAST_INSERT_ID()返回值为该查询第一条记录的ID；(已验证)

5、在进阶方面，可运用作分表ID的唯一性。

初始化查询的结果，得到的是0，这点和max(id)还是有区别的；

mysql>select LAST_INSERT_ID();

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

| LAST_INSERT_ID() |

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

| 0 |

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

1 row in set (0.00 sec)

方式3 - 查看表状态show table status

该方式提供了当前DB(use db_name;)下每个表的基本信息；可以通过where条件获取到Auto_increment属性的值；

下述提供的结果值，为下一个自增ID的数值。

mysql> show table status where Name=‘get_max_id‘;

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

| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options| Comment |

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

| get_max_id | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 10485760 | 1 | 2015-04-20 11:49:07 | NULL | NULL | utf8_general_ci | NULL | | |

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

1 row in set (0.00 sec)

方式4 - information_schema.tables

提供关于数据库中的表(包括视图)的信息。详细描述了某个表属于哪个schema，表类型，表引擎等等信息；

下述提供的结果值，为下一个自增ID的数值。

mysql> select table_name, AUTO_INCREMENT from information_schema.tables where table_name="get_max_id";

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

| table_name | AUTO_INCREMENT |

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

| get_max_id | 1 |

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

1 row in set (0.01 sec)

方式5 - @@IDENTITY全局变量

基础：以@@开头的变量为全局变量，而以@开头的变量为用户自定义的变量。

此处 @@IDENTITY表示最近一次向具有identity属性(auto_increment)的表INSERT数据时对应的自增列的值。此处得到的值是0。

1、类似于LAST_INSERT_ID()函数，该方式必须在同一个客户端内进行的INSERT与SELECT，且不受其他客户端影响；(已验证)

2、与表无关；(已验证)

3、非魔术方法插入不影响结果值；(已验证)

4、同一INSERT插入多条记录，取第一条记录的ID值为结果；(已验证)

mysql> select @@IDENTITY;

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

| @@IDENTITY |

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

| 0 |

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

1 row in set (0.00 sec)

三、自增ID在服务器重启后会根据表中当前最大值重新计算

验证如下：

1、先向测试表插入如下行

2、删除2-6的行，再插入新的7行数据，如下所示，ID继续增长

3、删除最后的5行数据

4、重启mysql

5、插入新的数据