Primary Keys in MySQL: Definition, Best Practices, and Index Implications

Primary Key

Each row in a table should have a column (or a set of columns) that uniquely identifies it.

A customer can use a customer number, an order can use an order ID, and staff can use a staff ID or social security number.

Primary key (one column or a set of columns) whose values can uniquely distinguish each row in the table.

The column(s) that uniquely identify each row are called the primary key. Without a primary key, updating or deleting specific rows is difficult because there is no safe way to guarantee targeting only the intended rows.

Although not always required, most database designers should ensure every table has a primary key to facilitate later data manipulation and management.

Any column can serve as a primary key as long as it meets the following conditions:

Any two rows must not have the same primary key value.

Every row must have a primary key value (the primary key column cannot be NULL).

The primary key value rules listed are enforced by MySQL itself.

Best practices for primary keys:

1. Do not update the primary key value.

2. Do not reuse primary key values.

3. Do not use values that may change (e.g., a name) as a primary key.

In summary, a primary key should not carry business meaning; a table must have a primary key to support scalability, loose coupling, and high availability.

Role of Primary Key in Indexing

In the absence of special requirements, InnoDB recommends using an auto‑increment ID unrelated to business logic as the primary key.

InnoDB uses a clustered index where the data rows are stored in the leaf nodes of a B+Tree ordered by the primary key. When a new record is inserted, MySQL places it in the appropriate position; if a page reaches its fill factor, a new page (node) is created.

For an auto‑increment primary key, each new record is appended to the end of the current index node, and when a page is full a new page is allocated, forming a compact, near‑sequential index structure with high insertion efficiency and low maintenance overhead.

For a non‑auto‑increment key (e.g., ID card number or student number), the value is roughly random, causing each new record to be inserted into the middle of existing index pages. This forces MySQL to move data, possibly reading pages from disk and writing them back, increasing overhead and creating fragmentation that may require OPTIMIZE TABLE to rebuild.

Therefore, when using the InnoDB storage engine, unless there is a specific need, always use a business‑agnostic auto‑increment field as the primary key.

Frequent updates and deletions generate fragmentation; heavily fragmented tables suffer slower queries and should be optimized to restore efficiency.

<section style="letter-spacing: 0px; line-height: 1.6"><section style="line-height: 26px; color: rgb(1, 1, 1)"><p style="line-height: 26px; color: black; text-align: center"><strong style='font-family: -apple-system-font, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif; font-size: 28px; letter-spacing: 0.544px'><span style="color: rgb(2, 30, 170)">END</span></strong></p></section></section><section style="letter-spacing: 0px; line-height: 1.6"><p style="font-weight: bold; font-size: 24px; line-height: 1.6; letter-spacing: 0px">十期推荐</p><p style="line-height: 1.75em"><a href="http://mp.weixin.qq.com/s?__biz=MzIyNDU2ODA4OQ==&mid=2247486865&idx=1&sn=425c3b3d55ad11f8b1395647667fe29e&chksm=e80dbbe7df7a32f1eb89e0ba061565f40283a7e4853432f8589788c655d0895394048646b37f&scene=21#wechat_redirect" style="text-decoration: underline; font-size: 14px" target="_blank">【201期】面试官：String长度有限制吗？是多少？还好我看过</a><br/></p>... (remaining list omitted for brevity) ...</section>