Why Many Large Internet Companies Avoid Using MySQL Foreign Keys

MySQL foreign keys are used to establish relationships between tables, requiring that a column or set of columns in one table match primary key values in another, thereby helping maintain data consistency and integrity.

The main benefits are:

Consistency: ensures referenced IDs exist, e.g., an order's customer ID must be present in the customers table.

Integrity: prevents deletion of records that are still referenced by other tables.

However, many large internet companies rarely use foreign keys. For example, Alibaba's Java development manual explicitly prohibits foreign keys and cascade operations, stating that they are suitable only for low‑concurrency single‑machine scenarios and can cause database update storms.

【Mandatory】Do not use foreign keys and cascade; all foreign‑key concepts must be handled at the application layer. Explanation: Using foreign keys with cascade updates is appropriate for low‑concurrency single‑machine setups but not for distributed, high‑concurrency clusters; cascade updates cause strong blocking and risk database update storms; foreign keys also affect insert performance.

Example tables illustrating a foreign‑key relationship:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    -- other order fields
);

CREATE TABLE OrderItems (
    ItemID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    -- other order‑item fields
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

Performance problems : Each INSERT, UPDATE, or DELETE must check foreign‑key constraints, maintaining both the primary‑key index on Orders and the foreign‑key index on OrderItems, which adds overhead and can slow down operations.

Before inserting a new order item, the database must verify that the referenced OrderID exists in the Orders table, further increasing execution time.

Lock contention : Concurrent transactions inserting or updating order items need to acquire additional locks on the Orders table to ensure consistency, leading to lock competition and reduced concurrency, potentially causing deadlocks.

Sharding incompatibility : In sharded environments, related data may reside in different databases, making foreign keys impractical across shards and complicating consistency and cross‑database transactions.

In summary, foreign‑key constraints introduce extra overhead and lock contention, so many large‑scale internet companies avoid them, opting for alternative mechanisms (e.g., RC isolation) to minimize locking and improve performance.