mysql数据库主键的思考

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: mysql数据库主键的思考

自增主键优点

1.数据库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。

2.数字型,占用空间小,易排序;

3.如果通过非系统增加记录时,可以不用指定该字段,不用担心主键重复问题。

缺点以及解决方案

1.因为自动增长,在手动要插入指定ID的记录时会显得麻烦

此处的id在生成之前应该没有业务含义

2.是当系统与其它系统集成时需要数据导入时,很难保证原系统的ID不发生主键冲突(前提是老系统也是数字型的)特别是在新系统上线时,新旧系统并行存在,并且是异库异构的数据库的情况下,需要双向同步时,自增主键将是你的噩梦。

当数据依然时单库,数据迁移时,必然数据量不大,增量也不大,此时可以预估未来一段时间的id增长个数,然后以远高于以前id编号的数字,开始自增,这样可以允许新旧系统并行一段时间,但是需要尽快迁移。异构数据库现在其实很少遇到这样的案例,一般都统一为mysql,如果不是,可以重新思考解决方案

3.若系统也是数字型的,在导入时,为了区分新老数据,可能想在老数据主键前统一加一个字符标识(例如“o”,old)来表示这是老数据,那么自动增长的数字型又面临一个挑战。

同2所示,老数据数据较小,直接可以区分

4.表锁

在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+

5.自增在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 ...'
ql> 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:

现在我的表是从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,当然可以继续往后推此处不做例子,下来可以自己去试试。

6.主从复制

  • 在 statement 模式下,由于他是记录的执行语句,所以,为了让这些语句在 slave 端也能正确执行,那么他还必须记录每条语句在执行的时候的一些相关信息,也就是上下文信息,以保证所有语句在 slave 端杯执行的时候能够得到和在 master 端执行时候相同的结果。在自增主键上面,有可能会出现不一致的情况
  • 在 row 模式下,bin-log 中可以不记录执行的 SQL 语句的上下文相关的信息,仅仅只需要记录哪一条记录被修改了,修改成什么样了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节,非常容易理解。所有自增主键也不会出现问题
  • 但是在 row 模式下,所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容
    除以下几种情况外,在运行时可以动态改变 binlog 的格式:

·存储流程或者触发器中间;
·启用了 NDB;
·当前会话使用 row 模式,并且已打开了临时表;

3.Mixed 模式,那么在以下几种情况下会自动将 binlog 的模式由 statement 模式变为 row 模式:

·当 DML 语句更新一个 NDB 表时;
·当函数中包含 UUID() 时;
· 2 个及以上包含 AUTO_INCREMENT 字段的表被更新时;
· 执行 INSERT DELAYED 语句时;
· 用 UDF 时;
· 视图中必须要求运用 row 时,例如建立视图时使用了 UUID() 函数;

这个问题我自己没有遇到过,其实很多复杂情况下都会出现主从不一致的情况,不同的模式都有可能,只要不写很复杂的sql语句,互联网公司也是禁止的,一般问题不大

7.主主双向复制
因为多主都可以对服务器有写权限,主键自增长一定会出现重复。

必须保证两台服务器上插入的自增长数据不同

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 相对分段的效果来满足全局唯一的效果。

8.自增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效果一样,并且还会降低效率,但是实现简单。

结论

1.小型系统或者系统架构初期,数据没有超过百万或者千万,可以使用自增主键
2.当数据成长到几百万或者超过千万时,并且增量很高时,这时就涉及到分库分表,这时就必须使用自动生成id方案

作者:glowd

原文:https://blog.csdn.net/zengqiang1/article/details/79312774

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
关系型数据库 MySQL
MYSQL:约束(主键约束)
MYSQL:约束(主键约束)
|
7月前
|
关系型数据库 数据库 索引
关系型数据库主键的非空性
【5月更文挑战第15天】
72 2
|
7月前
|
关系型数据库 数据库 索引
关系型数据库主键的唯一性
【5月更文挑战第15天】
155 2
|
1月前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
31 0
|
2月前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
|
3月前
|
存储 关系型数据库 MySQL
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
|
4月前
|
安全 数据管理 关系型数据库
深入理解数据库主键
【8月更文挑战第31天】
100 0
|
6月前
|
SQL Java 数据库连接
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
81 2
|
6月前
|
关系型数据库 MySQL 数据库
MySQL 8.0 新特性之不可见主键
【6月更文挑战第9天】MySQL 8.0 引入了不可见主键特性,提供更灵活的数据库管理方式。不可见主键能减少业务逻辑干扰,提高数据安全性和隐私,同时在某些场景下更适用。示例展示了如何创建和使用不可见主键,但需要注意它可能带来的理解和调试难题。此特性增加了设计和管理数据库的选项,适用于对数据隐私有高要求的场景。随着技术发展,不断学习和探索新特性将提升数据库性能和功能。
86 9
|
6月前
|
存储 SQL 关系型数据库
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
293 1