主键索引 唯一索引 复合索引

# 主键索引、唯一索引与复合索引详解 ## 1. 主键索引 (PRIMARY KEY) ### 基本特性 - **唯一性**：确保每行的主键值唯一 - **非空约束**：主键列不允许NULL值 - **聚集索引**：在InnoDB中，主键默认是聚集索引（决定数据物理存储顺序） - **表只能有一个主键**：但可以是单列或多列组合（复合主键） ### 使用场景 ```sql -- 创建单列主键 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL ); -- 创建复合主键 CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id) -- 复合主键 ); ``` ### 特点与注意事项 - 主键通常作为其他表的外键引用 - 自增主键(AUTO_INCREMENT)有利于插入性能，但可能不适合分布式系统 - UUID作为主键可能影响索引性能（因随机性导致页分裂） ## 2. 唯一索引 (UNIQUE INDEX) ### 基本特性 - **唯一性**：确保索引列值唯一（允许NULL值，但多个NULL是否被视为相同取决于数据库实现） - **非聚集索引**：不影响数据物理存储顺序 - **表可以有多个唯一索引** ### 使用场景 ```sql -- 创建单列唯一索引 CREATE TABLE employees ( id INT PRIMARY KEY, email VARCHAR(100) UNIQUE, -- 列级约束 phone VARCHAR(20) ); -- 显式创建唯一索引 CREATE UNIQUE INDEX idx_emp_phone ON employees(phone); -- 复合唯一索引 CREATE TABLE room_allocations ( room_id INT, date DATE, user_id INT, UNIQUE (room_id, date) -- 同一房间同一天只能分配一次 ); ``` ### 特点与注意事项 - 与主键不同，唯一索引允许NULL值（在MySQL中，NULL被视为特殊值，不违反唯一性） - 常用于业务唯一约束（如用户名、邮箱、手机号等） - 性能与普通索引相当，但插入/更新时需要额外唯一性检查 ## 3. 复合索引 (Composite Index) ### 基本特性 - **多列组合**：索引包含多个列（最多16列，具体取决于DBMS） - **最左前缀原则**：查询条件必须包含索引最左边的列才能使用索引 - **排序优化**：可以优化多列排序（ORDER BY） ### 使用场景 ```sql -- 创建复合索引 CREATE TABLE sales ( id INT PRIMARY KEY, region VARCHAR(50), sale_date DATE, amount DECIMAL(10,2), INDEX idx_region_date (region, sale_date) -- 复合索引 ); -- 有效使用索引的查询 SELECT * FROM sales WHERE region = 'East' AND sale_date > '2023-01-01'; -- 使用索引 SELECT * FROM sales WHERE region = 'West'; -- 使用索引(最左列) SELECT * FROM sales WHERE sale_date = '2023-05-01'; -- 不使用索引(违反最左前缀) ``` ### 设计原则 1. **列顺序**：高选择性(区分度高)的列放前面 2. **覆盖索引**：包含查询所需的所有列，避免回表 3. **查询模式**：根据实际查询条件设计索引顺序 4. **列大小**：较小的数据类型放前面（减少索引大小） ## 比较与选择策略 | 特性 | 主键索引 | 唯一索引 | 复合索引 | |------------|------------------|------------------|------------------| | **唯一性** | 强制唯一 | 强制唯一 | 不强制唯一 | | **NULL值** | 不允许 | 允许(视DBMS而定) | 允许 | | **数量** | 每表1个 | 每表多个 | 每表多个 | | **主要用途** | 行唯一标识 | 业务唯一约束 | 优化多条件查询 |