mysql 建立联合主键

基础概念

MySQL中的联合主键（Composite Key）是指由两个或多个字段组合而成的主键。这些字段的组合必须是唯一的，即在整个表中没有两行记录可以拥有相同的联合主键值。联合主键用于确保数据的唯一性和完整性。

优势

唯一性保证：通过多个字段的组合，可以确保数据的唯一性，适用于那些单个字段无法唯一标识记录的情况。
数据完整性：联合主键有助于维护数据的完整性，防止重复数据的插入。
灵活性：可以根据实际需求选择不同的字段组合来创建联合主键。

类型

联合主键可以是以下几种类型的组合：

单列主键：虽然不是联合主键，但作为基础了解。
两列联合主键：最常见的联合主键形式。
多列联合主键：包含三个或更多列的联合主键。

应用场景

联合主键常用于以下场景：

订单与客户关系：在订单表中，订单ID和客户ID的组合可以作为联合主键，确保每个订单与客户的组合是唯一的。
多对多关系：在多对多关系的中间表中，通常使用两个外键字段的组合作为联合主键。

示例代码

假设有一个订单表 orders，其中包含订单ID (order_id) 和客户ID (customer_id)，这两个字段的组合应该是唯一的。

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE,
    PRIMARY KEY (order_id, customer_id)
);

遇到的问题及解决方法

问题1：插入重复的联合主键值

原因：尝试插入的记录的联合主键值已经存在于表中。

解决方法：在插入数据之前，先检查该联合主键值是否已经存在。

INSERT INTO orders (order_id, customer_id, order_date)
SELECT 1, 100, '2023-10-01'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM orders WHERE order_id = 1 AND customer_id = 100
);

问题2：查询效率低下

原因：联合主键可能导致索引过大，影响查询效率。

解决方法：优化索引结构，可以考虑使用覆盖索引或分区表。

-- 创建覆盖索引
CREATE INDEX idx_order_customer ON orders (order_id, customer_id, order_date);

-- 分区表
ALTER TABLE orders
PARTITION BY HASH(order_id + customer_id) PARTITIONS 10;