mysql中添加主键索引主要有三种方式：1. 创建新表时直接添加主键，可在列定义后使用primary key或在所有列定义后单独声明；2. 在已有表上通过alter table添加主键，需确保目标列非空且唯一，必要时先清洗数据；3. 添加复合主键，适用于多列组合才能唯一标识记录的情况。主键索引在innodb中为聚簇索引，决定了数据的物理存储顺序，能显著提升查询性能，但插入随机主键（如uuid）可能导致性能下降。自增id作为主键通常更优。在大表上添加主键会面临锁表、耗时和磁盘空间问题，可通过在线ddl、选择低峰期操作、测试备份及监控应对。单一主键简洁高效，适合大多数场景；复合主键反映业务逻辑唯一性，但索引体积大、查询受限，外键引用也较复杂，应根据业务需求和查询模式权衡选择。
MySQL中添加主键索引，通常是在创建表时直接声明，或者在已有表上通过ALTER TABLE语句来完成。这不仅仅是为了一项技术上的“配置”，更是为了确保数据记录的唯一性，同时，它对数据库查询性能的提升是相当显著的，几乎可以说是核心优化手段之一。
解决方案
1. 创建新表时直接添加主键：这是最常见也最推荐的方式。在定义列的时候，直接用PRIMARY KEY关键字指定。
CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, -- 这样就直接把user_id设为主键了，还带了自增属性 username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100), registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
或者，如果你觉得在列定义后面直接跟PRIMARY KEY看起来有点乱，也可以在所有列定义之后，单独声明主键：
CREATE TABLE products ( product_id INT, product_name VARCHAR(255) NOT NULL, price DECIMAL(10, 2), PRIMARY KEY (product_id) -- 在表定义末尾指定主键);
2. 在已有表上添加主键：当你有一张已经存在但没有主键的表时，可以利用ALTER TABLE语句来添加。不过，这里有个重要的前提：你选择作为主键的列（或列组合）必须满足两个条件——所有值非空（NOT NULL）且所有值唯一（UNIQUE）。如果现有数据不满足，你需要先清洗数据。
-- 假设你有一张名为 'orders' 的表，里面有 'order_id' 列，你想把它设为主键-- 首先，确保 'order_id' 列是非空且唯一的，如果不是，可能需要先更新或修改列属性ALTER TABLE ordersMODIFY order_id INT NOT NULL UNIQUE; -- 这一步可能需要，如果order_id本来允许NULL或者有重复值-- 然后，添加主键ALTER TABLE ordersADD PRIMARY KEY (order_id);
3. 添加复合主键：有时候，单一列无法唯一标识一条记录，需要多列组合起来才能。这时，你可以创建复合主键。
-- 假设有一个订单明细表，一个订单可以有多个商品，每个商品的行由 order_id 和 item_id 共同确定ALTER TABLE order_detailsADD PRIMARY KEY (order_id, item_id); -- order_id 和 item_id 的组合作为主键
MySQL主键索引的底层原理与性能考量是什么？
说到主键索引，我们其实在谈论MySQL里InnoDB存储引擎的“核心”。它和我们平时理解的普通索引（二级索引）有点不一样。在InnoDB里，主键索引是所谓的聚簇索引（Clustered Index）。这意味着什么呢？简单来说，你的表数据在磁盘上物理存储的顺序，就是按照主键的顺序来排列的。想象一下，一本书的目录（索引）和书的内容是分开的，但如果这本书的内容本身就是按照目录的顺序一页页排好的，那查找起来是不是快多了？聚簇索引就是这个意思。
所以，当通过主键进行查询时，数据库可以直接定位到数据行所在的物理位置，避免了额外的磁盘寻道，性能自然是顶级的。这对于像WHERE id = 123这样的精确查找，或者WHERE id BETWEEN 100 AND 200这样的范围查询，都有巨大的优势。
但凡事都有两面性。由于数据是按照主键顺序物理存储的，那么插入新数据时，如果主键值是随机的（比如用UUID作主键），数据库可能需要不断地调整数据文件的物理顺序，这会引发大量的随机I/O，性能反而会下降。这就是为什么我们常说，自增ID（AUTO_INCREMENT）作为主键是个非常好的选择，因为它保证了插入的顺序性，能有效减少页分裂和随机I/O，提升插入性能。我个人在设计表的时候，几乎都会优先考虑一个自增整数作为主键，除非业务上真的有非常特殊的、非自增主键不可的需求。
在已有大量数据的表上添加主键索引会遇到哪些挑战？
在已经有大量数据的表上添加主键，这可不是一个可以掉以轻心的小操作。它通常会带来几个明显的挑战：
首先是锁表问题。在MySQL 5.6及更早的版本中，执行ALTER TABLE ... ADD PRIMARY KEY这样的DDL（数据定义语言）操作，可能会对表施加一个全局锁（MDL锁，Metadata Lock），这意味着在DDL操作完成之前，对该表的所有读写操作都会被阻塞。对于生产环境中的大表，这意味着你的业务可能会中断数分钟、数小时甚至更久，这是不可接受的。
其次是操作耗时。由于主键索引的聚簇特性，添加主键通常意味着MySQL需要重建整个表。它会创建一个新的临时表，将原表的数据按照主键顺序复制到新表中，然后删除旧表，重命名新表。这个过程涉及大量的数据复制和磁盘I/O，数据量越大，耗时越长。
再者是磁盘空间需求。重建表的过程中，你需要至少两倍于原表大小的空闲磁盘空间（一份原表数据，一份临时表数据）。如果磁盘空间不足，操作会失败。
那么，怎么应对这些挑战呢？
在线DDL (Online DDL)：MySQL 5.6及更高版本引入了在线DDL特性，允许在执行ALTER TABLE时，尽可能减少对表操作的阻塞。通过指定ALGORITHM=INPLACE和LOCK=NONE（如果支持），可以在DDL操作进行的同时，允许DML（数据操作语言，如INSERT, UPDATE, DELETE）操作继续进行。虽然不是完全无锁，但大大降低了影响。
ALTER TABLE your_large_table ADD PRIMARY KEY (id), ALGORITHM=INPLACE, LOCK=NONE;
但即便如此，也并非所有操作都支持完全无锁，而且在DDL的最后阶段，仍然会有一个短暂的元数据锁。
选择业务低峰期操作：这是最直接也最稳妥的方法。在系统负载最低的时候执行DDL，可以最大限度地减少对用户的影响。充分的测试和备份：在生产环境执行前，务必在测试环境用接近生产的数据量进行充分测试，评估操作时长和可能的影响。同时，操作前务必进行全量备份，以防万一。监控和警报：在操作过程中，密切监控数据库的性能指标，如CPU、内存、磁盘I/O和锁等待，确保能及时发现并处理问题。
复合主键与单一主键在实际应用中如何权衡选择？
在数据库设计中，选择单一主键还是复合主键，确实是一个需要深思熟虑的问题，它关乎到数据模型的清晰度、查询效率和维护成本。
单一主键，通常是一个单独的列，比如我们最常用的自增整数ID。
优点：简洁明了：一个ID就能唯一标识一条记录，无论是代码层面还是SQL查询都非常直观。查询效率高：由于只有一个列，索引结构相对简单，查找速度快。外键引用方便：其他表引用时，只需要引用一个列即可。空间占用相对小：索引存储空间更小。缺点：有时可能需要额外的唯一约束来保证业务逻辑上的唯一性（例如，用户表的用户名唯一）。
复合主键，顾名思义，是由多个列组合起来作为主键。例如，在一个订单明细表中，order_id和item_id的组合才能唯一标识一条记录。
优点：天然的唯一性约束：它直接反映了业务逻辑上的唯一性，避免了数据冗余和不一致。减少冗余列：在某些情况下，可以避免为了单一主键而引入一个“无意义”的自增ID列。缺点：索引体积增大：索引需要存储多列的值，占用更多磁盘空间。查询效率可能受限：只有当查询条件包含了复合主键的所有前缀列时，索引才能被高效利用。例如，PRIMARY KEY (col1, col2, col3)，WHERE col1 = 'A'能用到索引，WHERE col2 = 'B'就用不到了。外键引用复杂：如果其他表需要引用这个复合主键，那么外键也需要由多个列组成，增加了复杂性。
权衡选择的考量点：
业务逻辑的唯一性：这是最重要的考量。如果业务上天然就需要多列组合才能唯一标识一条记录，那么复合主键是一个非常自然的选择。比如，在一个多对多关系表中（如学生-课程关系），学生ID和课程ID的组合就是天然的主键。查询模式：你的应用主要会通过哪些列来查询数据？如果大部分查询都会涉及到复合主键的所有列，那么复合主键的性能表现会很好。如果经常只通过复合主键中的部分列进行查询，那么可能需要考虑额外的单一索引，或者重新评估主键设计。外键引用：如果这张表会被很多其他表引用，并且你希望外键关系清晰简洁，那么单一主键通常是更好的选择。数据量与性能预期：对于超大表，复合主键的索引体积和查询复杂性可能会对性能产生更显著的影响。
我的经验是，除非业务逻辑非常明确地要求复合主键，或者单一列无法保证唯一性，否则我倾向于使用一个简单的、自增的整数作为单一主键。这能让数据库设计更简洁，开发更方便，并且在大多数情况下能提供非常优秀的性能。如果需要保证多列的唯一性，可以考虑在单一主键的基础上，额外添加一个唯一索引来满足业务需求，这通常是更灵活且易于管理的方式。
以上就是mysql如何添加主键索引 mysql创建主键索引的步骤详解的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/25421.html
微信扫一扫 
支付宝扫一扫 
