《MySQL DBA修炼之道》——3.6 ID主键

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 本节书摘来自华章出版社《MySQL DBA修炼之道》一书中的第3章,第3.6节,作者:陈晓勇,更多章节内容可以访问云栖社区“华章计算机”公众号查看ID主键 下面先说明选择主键的注意事项。 1) 建议主键是整型。

本节书摘来自华章出版社《MySQL DBA修炼之道》一书中的第3章,第3.6节,作者:陈晓勇,更多章节内容可以访问云栖社区“华章计算机”公众号查看

3.6 ID主键

下面先说明选择主键的注意事项。
1) 建议主键是整型。
2) 如果表中包含一列能够确保唯一、非空(NOT NULL),以及能够用来定位一条记录的字段,就不要因为传统而觉得一定要加上一个自增ID做主键。
3) 主键也遵从索引的一些约定,注意联合主键的字段顺序。
4) 为主键选择更有意义的名称,如ID这个名称太过笼统,表达的信息可能不准确。
1.自增ID主键
自增列是MySQL里的一种特殊的整型,我们定义一个列的整型的同时,可以设置它是否为自增的,一个表只能有一个列是自增列,且自增列必然是主键列。自增列的默认起始值是1,默认可以按步长为1进行递增,自增列的增长将受两个MySQL全局参数的影响。
auto_increment_offset:确定AUTO_INCREMENT列值的起点。
auto_increment_increment:控制列值增加的间隔,即步长。
也可以单独定义某个表的起始值,如:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
在复制环境中,设置这两个值可以减少主键冲突,关于这一点以后会在复制章节(第12章)中详述。
使用自增列的原因是唯一标识数据表的某行记录。它们也被用来优化表之间的连接。连接单个列比连接多个列更快,连接整数列比连接其他大多数数据类型也更快。总之,有很多使用它的理由。但也没有必要滥用自增ID,给每个表都设置一个自增ID做主键,有时可能存在另一个从逻辑上来说更加自然的主键。
另外,因为InnoDB引擎的ID主键是聚集索引,从前文可以得知,如果簇索引、数据和主键索引放在一起且是按主键索引进行排序的,那么基于自增主键的单个值查找和小范围查找将是最高效的。
研发人员有时倾向于使用字符串做主键,或者使用多个列的联合主键,但需要清楚一个事实:InnoDB的其他索引实际上存储了主键的值,这样做可能会导致索引空间大大增加。
InnoDB选择主键创建簇索引。如果没有主键,就会选取一个唯一非空的索引来替代;如果仍然找不到合适的列,那么将创建一个隐含的主键来创建簇索引。选取一个唯一非空的索引做主键可能不是我们所期待的,一般的解决办法是删除我们不期望的主键(唯一索引),创建一个非空的自增列,再增加这个唯一索引。
例如,由于未定义主键,InnoDB自动把唯一索引idx_a_b(a,b)定义为主键了。我们想增加一个自增ID主键,并设置唯一索引idx_a_b。idx_a_b表示这个索引是建立在a列和b列的复合索引。

ALTER TABLE table_name
ADD COLUMN 'id' bigint UNSIGNED NOT NULL AUTO_INCREMENT first, 
DROP PRIMARY KEY, 
ADD PRIMARY KEY('id') ,
ADD INDEX idx_a_b on table_name(a,b);
AI 代码解读

2.自增ID可以插入指定的值
自增ID还有一个特性,那就是如果插入0值或NULL值,InnoDB会认为没有设定值,然后帮你自增一个值。所以可以利用这个特性生成全局唯一ID、序列。如果数据分片到许多实例、机器上,那么就需要一个全局唯一ID来标识记录了。如下是官方文档推荐的一个创建唯一序列的方法。
创建一个表,用来控制顺序计数器并使其初始化。
mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
使用该表产生如下的序列数。
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();
高并发下,LAST_INSERT_ID函数可能会有一定的性能问题,但这种方法很简单,一般情况下是可以满足需要的。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
1408
分享
相关文章
MySQL主键谁与争锋:MySQL为何钟爱自增主键ID+UUID?
本文深入探讨了在MySQL中使用自增类型主键的优势与局限性。自增主键通过保证数据的有序性和减少索引维护成本,提升了查询和插入性能,简化了数据库管理和维护,并提高了数据一致性。然而,在某些业务场景下,如跨表唯一性需求或分布式系统中,自增主键可能无法满足要求,且存在主键值易预测的安全风险。因此,选择主键类型时需综合考虑业务需求和应用场景。
128 2
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
303 3
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
171 1
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
61 0
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
124 2
MySQL 8.0 新特性之不可见主键
【6月更文挑战第9天】MySQL 8.0 引入了不可见主键特性,提供更灵活的数据库管理方式。不可见主键能减少业务逻辑干扰,提高数据安全性和隐私,同时在某些场景下更适用。示例展示了如何创建和使用不可见主键,但需要注意它可能带来的理解和调试难题。此特性增加了设计和管理数据库的选项,适用于对数据隐私有高要求的场景。随着技术发展,不断学习和探索新特性将提升数据库性能和功能。
115 9
目录
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等