为什么 AUTO_INCREMENT
字段必须是索引的一部分
MySQL 要求
AUTO_INCREMENT列必须被索引（通常是主键或唯一索引），否则建表会报错：
ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key。这不是为了性能，而是为了保证自增逻辑的原子性和可重复性——InnoDB 需要通过索引快速定位当前最大值并加锁，避免并发插入时生成重复 ID。
常见误操作是给
id加了
AUTO_INCREMENT却忘了加
PRIMARY KEY或
UNIQUE约束，结果建表失败。更隐蔽的问题是：在已有表上用
ALTER TABLE ... MODIFY id INT AUTO_INCREMENT时，如果原列没索引，命令会静默失败或报错，不检查就继续后续操作容易埋坑。
AUTO_INCREMENT列可以是联合索引的**第一列**（如
(id, tenant_id)），但不能是第二列（如
(tenant_id, id)）——否则无法用于自增计数 若用
REPLACE INTO或
INSERT ... ON DUPLICATE KEY UPDATE，自增值仍会递增，即使最终行被更新而非插入；这是设计行为，不是 bug 从 MySQL 8.0 开始，
innodb_autoinc_lock_mode = 2（默认）支持“交错分配”，提升并发插入性能，但要求 binlog 格式为
ROW，否则主从不一致
AUTO_INCREMENT
值跳变的常见原因与应对
生产环境常发现 ID 不连续，比如插入 10 行后最大 ID 是 15。这不是数据丢失，而是自增机制的正常表现。最典型的三个触发点：
事务回滚：BEGIN; INSERT INTO t VALUES (); ROLLBACK;后，该 ID 已被预占，不会回收 批量插入（
INSERT ... SELECT、
LOAD DATA）：InnoDB 预分配一段 ID 范围，哪怕实际只插入部分行
INSERT IGNORE或
ON DUPLICATE KEY UPDATE冲突时：ID 已生成，但语句未插入新行
如果你依赖“ID 连续”做分页或业务逻辑（比如导出编号），必须换方案——用时间戳 + 序列号，或单独维护一个无间隙的序列表。MySQL 本身不提供 gapless 自增。
联合主键下 AUTO_INCREMENT
的限制与替代方案
MySQL 不允许在复合主键中让非首列启用
AUTO_INCREMENT。例如以下建表会失败：
CREATE TABLE orders ( tenant_id INT, order_id INT AUTO_INCREMENT, PRIMARY KEY (tenant_id, order_id) );
错误信息：
ERROR 1075: ... auto-increment column must be defined as a key，因为
order_id在联合主键中不是最左前缀。
可行做法只有两种：
把order_id放到联合主键第一位：
PRIMARY KEY (order_id, tenant_id)，但失去按租户快速范围扫描的能力 放弃
AUTO_INCREMENT，改用应用层生成 ID（如雪花算法）或数据库序列模拟（用
SELECT @next := @next + 1 FROM (SELECT @next := COALESCE((SELECT MAX(order_id) FROM orders WHERE tenant_id = 123), 0)) _+
INSERT组合），但需自己处理并发安全
注意：MySQL 8.0+ 的
SEQUENCE对象仍不支持直接绑定到列默认值，无法替代列级
AUTO_INCREMENT。
索引优化中容易被忽略的 AUTO_INCREMENT
影响
很多人以为只要主键是
AUTO_INCREMENT，其他查询就自动高效——其实不然。当查询条件不含主键前缀时，InnoDB 仍可能全表扫描，尤其在大表中。
例如表
logs (id BIGINT AUTO_INCREMENT PRIMARY KEY, app_id INT, created_at DATETIME)，执行
SELECT * FROM logs WHERE app_id = 123，即使
app_id有索引，若该索引不是覆盖索引，回表开销可能比预期高；而如果
app_id是高频查询字段，更好的设计是：
PRIMARY KEY (app_id, id)+
UNIQUE KEY (id)，让范围查询天然走聚簇索引。
AUTO_INCREMENT主键的写入是顺序的，对 SSD 友好；但若频繁
DELETE+
INSERT，会导致页分裂和碎片，定期
OPTIMIZE TABLE或调整
innodb_page_merge_threshold有必要 使用
SHOW INDEX FROM t检查
Seq_in_index，确认
AUTO_INCREMENT列是否真正在索引最左位；否则它只是“被索引”，却无法支撑该索引的范围扫描能力 如果业务从不按主键查询，只为外键关联或排序，那
AUTO_INCREMENT主键反而成了冗余负担——考虑用自然主键（如 UUID）+ 显式二级索引
真正关键的不是“有没有自增”，而是“索引结构是否匹配最重的查询模式”。自增只是写入友好，不是查询万能药。
