自增主键优点
数据库AUTO_INCREMENT，innodb的索引特性导致了自增id做主键是效率最好的，而且是增量增长，按顺序存放，对于检索非常有利；
用户表user百万记录，主键为UUID和主键为自增Id，作基准测试 ，机器不同可能结果会有差异
1） 普通单条或者20条左右的记录检索，uuid为主键的相差不大几乎效率相同；
2）范围查询特别是上百成千条的记录查询，自增id的效率要大于uuid；
3）在范围查询做统计汇总的时候，自增id的效率要大于uuid；
4）在存储上面，自增id所占的存储空间是uuid的1/2；
5）在备份恢复上，自增ID主键稍微优于UUID。
用户表1000W记录测试：
1）普通单条或者20条左右的记录检索，自增主键效率是uuid主键的2到3倍；
2）但是范围查询特别是上百成千条的记录查询，自增id的效率要大于uuid；
3）在范围查询做统计汇总的时候，自增id主键的效率是uuid主键1.5到2倍；
4）在存储上面，自增id所占的存储空间是uuid的1/2；
5）在写入上面，自增ID主键的效率是UUID主键的3到10倍，相差比较明显，特别是update小范围之内的数据上面。
6）在备份恢复上，自增ID主键稍微优于UUID。数字型，占用空间小，易排序；
- 如果通过非系统增加记录时，可以不用指定该字段，不用担心主键重复问题。
缺点以及解决方案
因为自动增长，在手动要插入指定ID的记录时会显得麻烦
此处的id在生成之前应该没有业务含义
是当系统与其它系统集成时需要数据导入时，很难保证原系统的ID不发生主键冲突（前提是老系统也是数字型的）特别是在新系统上线时，新旧系统并行存在，并且是异库异构的数据库的情况下，需要双向同步时，自增主键将是你的噩梦。
当数据依然时单库，数据迁移时，必然数据量不大，增量也不大，此时可以预估未来一段时间的id增长个数，然后以远高于以前id编号的数字，开始自增，这样可以允许新旧系统并行一段时间，但是需要尽快迁移。异构数据库现在其实很少遇到这样的案例，一般都统一为mysql，如果不是，可以重新思考解决方案
若系统也是数字型的，在导入时，为了区分新老数据，可能想在老数据主键前统一加一个字符标识（例如“o”，old）来表示这是老数据，那么自动增长的数字型又面临一个挑战。
同2所示，老数据数据较小，直接可以区分
表锁
在MySQL5.1.22之前，InnoDB自增值是通过其本身的自增长计数器来获取值，该实现方式是通过表锁机制来完成的（AUTO-INC LOCKING）。锁不是在每次事务完成后释放，而是在完成对自增长值插入的SQL语句后释放，要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候，innoDB会在内存里保存一个计数器用来记录auto_increment的值，当插入一个新行数据时，就会用一个表锁来锁住这个计数器，直到插入结束。如果大量的并发插入，表锁会引起SQL堵塞。
在5.1.22之后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode，该实现方式是通过轻量级互斥量的增长机制完成的。它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：
插入类型说明：
INSERT-LIKE：指所有的插入语句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等 Simple inserts：指在插入前就能确定插入行数的语句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。 Bulk inserts：指在插入前不能确定得到插入行的语句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA. Mixed-mode inserts:指其中一部分是自增长的，有一部分是确定的。 ---查看show variables like 'innodb_autoinc_lock_mode'; 0：通过表锁的方式进行，也就是所有类型的insert都用AUTO-inc locking。 ---1：默认值，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行。 2：对所有的insert-like 自增长值的产生使用互斥量机制完成，性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式。尽量减少Bulk inserts的使用，一般情况下也很少用到，而且现在主流mysql都在5.6+
自增在bulk insert时不连续
mysql> show variables like 'innodb_autoinc_lock_mode'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 1 | +--------------------------+-------+mysql> show create table health_package; health_package | CREATE TABLE `health_package` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序号', `package_id` int(11) NOT NULL COMMENT '套系 id', `module_id` int(11) NOT NULL COMMENT '模块 id', `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time, common column by DB rules', `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modified time,common column by DB rules ', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1761 DEFAULT CHARSET=utf8 COMMENT='This table stores module and package of health for ...' |mysql> insert into health_package(package_id,module_id) select package_id,module_id from health_package limit 10; Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0现在我的表是从1761开始自增，现在使用bulk insert，插入了10条数据
mysql> show create table health_package; health_package | CREATE TABLE `health_package` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序号', `package_id` int(11) NOT NULL COMMENT '套系 id', `module_id` int(11) NOT NULL COMMENT '模块 id', `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time, common column by DB rules', `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modified time,common column by DB rules ', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1776 DEFAULT CHARSET=utf8 COMMENT='This table stores module and package of health for ...' |mysql> select * from health_package limit 1760,15 -> ; +------+------------+-----------+---------------------+---------------------+ | id | package_id | module_id | gmt_create | gmt_modified | +------+------------+-----------+---------------------+---------------------+ | 1761 | 1 | 1 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | | 1762 | 10001 | 5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | | 1763 | 10001 | 5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | | 1764 | 10001 | 5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | | 1765 | 10001 | 5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | | 1766 | 10001 | 5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | | 1767 | 10001 | 5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | | 1768 | 10001 | 5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | | 1769 | 10001 | 5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | | 1770 | 10001 | 5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 | +------+------------+-----------+---------------------+---------------------+ 10 rows in set (0.00 sec)此时再看一下，自增已经是1776了，查询数据库，发现数据库里面只是多了10条数据，如果你再插入一条数据，此时已经从1776开始了。这是因为参数innodb_autoinc_lock_mode = 1时，每次会“预申请”多余的id(handler.cc:compute_next_insert_id)，而insert执行完成后，会特别将这些预留的id空出，就是特意将预申请后的当前最大id回写到表中(dict0dict.c:dict_table_autoinc_update_if_greater)。
这个预留的策略是“不够时多申请几个”， 实际执行中是分步申请。至于申请几个，是由当时“已经插入了几条数据N”决定的。当auto_increment_offset＝1时，预申请的个数是 N-1。
所以，如果N为1，则不预申请，N为2，则预申请1个(3),N为3，已经预申请了，N为4，预申请3个(5,6,7),5,6,7已经预申请了，N为8，预申请个7个(9,10,11,12,13,14,15),所以最终的结果就是1761+15=1776，当然可以继续往后推此处不做例子，下来可以自己去试试。主从复制
- 在 statement 模式下，由于他是记录的执行语句，所以，为了让这些语句在 slave 端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在 slave 端杯执行的时候能够得到和在 master 端执行时候相同的结果。在自增主键上面，有可能会出现不一致的情况
在 row 模式下，bin-log 中可以不记录执行的 SQL 语句的上下文相关的信息，仅仅只需要记录哪一条记录被修改了，修改成什么样了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。所有自增主键也不会出现问题
但是在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容
除以下几种情况外，在运行时可以动态改变 binlog 的格式：
·存储流程或者触发器中间；
·启用了 NDB；
·当前会话使用 row 模式，并且已打开了临时表；Mixed 模式，那么在以下几种情况下会自动将 binlog 的模式由 statement 模式变为 row 模式：
·当 DML 语句更新一个 NDB 表时；
·当函数中包含 UUID() 时；
· 2 个及以上包含 AUTO_INCREMENT 字段的表被更新时；
· 执行 INSERT DELAYED 语句时；
· 用 UDF 时；
· 视图中必须要求运用 row 时，例如建立视图时使用了 UUID() 函数；这个问题我自己没有遇到过，其实很多复杂情况下都会出现主从不一致的情况，不同的模式都有可能，只要不写很复杂的sql语句，互联网公司也是禁止的，一般问题不大
主主双向复制
因为多主都可以对服务器有写权限，主键自增长一定会出现重复。必须保证两台服务器上插入的自增长数据不同
A查奇数ID，B插偶数ID，步长不一样
在这里我们在A,B上加入参数，以实现奇偶插入
A：my.cnf上加入参数
auto_increment_offset = 1
auto_increment_increment = 2
这样A的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了
B：my.cnf上加入参数
auto_increment_offset = 2
auto_increment_increment = 2
这样B的auto_increment字段产生的数值是：2, 4, 6, 8, …等偶数ID了
在每个集群节点组的master上面，设置（auto_increment_increment），让目前每个集群的起始点错开 1，步长选择大于将来基本不可能达到的切分集群数，达到将 ID 相对分段的效果来满足全局唯一的效果。自增ID主键+步长
在每个集群节点组的master上面，设置（auto_increment_increment），让目前每个集群的起始点错开 1，步长选择大于将来基本不可能达到的切分集群数，达到将 ID 相对分段的效果来满足全局唯一的效果。
优点是：实现简单，后期维护简单，对应用透明。
缺点是：第一次设置相对较为复杂，因为要针对未来业务的发展而计算好足够的步长;规划： 比如计划总共N个节点组，那么第i个节点组的my.cnf的配置为： auto_increment_offset i auto_increment_increment N 假如规划48个节点组，N为48，现在配置第8个节点组，这个i为8，第8个节点组的my.cnf里面的配置为： auto_increment_offset 8 auto_increment_increment 48不可取，禁止使用。一般情况下涉及到多库时，分库分表肯定是基于业务的考量，例如我可以根据城市来分，当某个城市数据量大了之后，我可能需要调整城市的分布。id一般会根据业务字段来生成，我只要拿到id就知道插入哪个表。如果id没有分片信息，如果要获取多余的字段，就会多增加几次sql操作，这会降低效率。使用uuid效果一样，并且还会降低效率，但是实现简单。
结论
- 小型系统或者系统架构初期，数据没有超过百万或者千万，可以使用自增主键
- 当数据成长到几百万或者超过千万时，并且增量很高时，这时就涉及到分库分表，这时就必须使用自动生成id方案
1467
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
