在mysql数据库的实际开发场景中，经常会遇到需要用多个字段共同唯一标识一条记录的情况，这时候就需要使用复合主键，同时为了提升多字段查询的性能，复合索引也是常用的优化手段，两者的使用规则和特性需要开发者清晰掌握。
mysql中复合主键的设置方法
建表时直接创建复合主键
在创建表的时候，可以在PRIMARY KEY关键字后括号中写入多个需要组成复合主键的字段名，多个字段之间用英文逗号分隔即可。下面是一个学生选课表的示例，选课记录需要同时用学生id和课程id共同唯一标识，避免同一个学生重复选同一门课。
-- 创建学生选课表，设置student_id和course_id为复合主键
CREATE TABLE student_course (
student_id INT NOT NULL,
course_id INT NOT NULL,
score DECIMAL(5,2),
PRIMARY KEY (student_id, course_id)
);
给已存在的表添加复合主键
如果表已经创建完成，需要后续添加复合主键，可以使用ALTER TABLE语句来修改表结构。需要注意的是，添加复合主键之前，要确保所有组成主键的字段都不存在null值，否则添加会失败。
-- 给已存在的表添加复合主键 ALTER TABLE student_course ADD PRIMARY KEY (student_id, course_id);
复合主键的注意事项
复合主键中的所有字段都不能允许为null，这是主键的基本特性。另外复合主键的唯一性校验是基于所有组成字段的组合值，单个字段的值可以重复，只要组合值不重复即可。比如上面的选课表中，同一个学生可以选多门课，同一门课可以被多个学生选，但是同一个学生选同一门课的记录只能有一条。
复合索引对查询顺序的要求
最左前缀匹配原则
复合索引的生效遵循最左前缀匹配原则，也就是说查询条件中必须包含复合索引最左边的字段，索引才会生效。如果创建的复合索引是(a, b, c)，那么以下查询条件可以使用该索引：
- 包含a的条件
- 包含a和b的条件
- 包含a、b、c的条件
如果查询条件只包含b或者c，或者只包含b和c，不包含a，那么这个复合索引就不会生效。
不同查询顺序的示例
我们创建一个包含复合索引的测试表，先插入一些测试数据，再验证不同查询顺序下索引的生效情况。
-- 创建测试表
CREATE TABLE test_user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT,
city VARCHAR(50),
INDEX idx_age_city (age, city)
);
-- 插入测试数据
INSERT INTO test_user (name, age, city) VALUES
('张三', 20, '北京'),
('李四', 20, '上海'),
('王五', 22, '北京');
接下来使用EXPLAIN关键字分析不同查询语句的索引使用情况：
-- 查询条件包含最左字段age，索引生效 EXPLAIN SELECT * FROM test_user WHERE age = 20; -- 查询条件包含age和city，顺序和索引字段顺序一致，索引生效 EXPLAIN SELECT * FROM test_user WHERE age = 20 AND city = '北京'; -- 查询条件包含age和city，顺序和索引字段顺序不一致，mysql优化器会自动调整顺序，索引仍然生效 EXPLAIN SELECT * FROM test_user WHERE city = '北京' AND age = 20; -- 查询条件只包含city，不包含最左字段age，索引不生效 EXPLAIN SELECT * FROM test_user WHERE city = '北京';
从上面的示例可以看出，查询条件中字段的书写顺序不影响复合索引的生效，只要条件中包含了复合索引的最左字段即可，mysql的查询优化器会自动调整条件的顺序来匹配索引。
复合索引的生效范围
如果复合索引是(a, b, c)，当查询条件中a是范围查询（比如a > 10）时，那么b和c的索引部分就不会生效，只有a的部分会生效。如果a是等值查询，那么b的索引会生效，如果b也是等值查询，那么c的索引也会生效。
-- age是范围查询，city的索引部分不生效 EXPLAIN SELECT * FROM test_user WHERE age > 20 AND city = '北京'; -- age是等值查询，city的索引生效 EXPLAIN SELECT * FROM test_user WHERE age = 20 AND city = '北京';
复合主键和复合索引的区别
复合主键是一种约束，用来保证表中记录的唯一性，一个表只能有一个主键，主键默认会创建唯一索引。而复合索引是一种用于提升查询性能的索引结构，一个表可以有多个复合索引，复合索引不一定需要唯一性。
如果复合主键是(a, b)，那么默认会有一个(a, b)的复合唯一索引，这个索引同样遵循最左前缀匹配原则，查询的时候如果条件包含a，也可以使用这个主键自带的索引。
总结
设置mysql复合主键可以在建表时通过PRIMARY KEY (字段1, 字段2)的方式创建，也可以在表创建后通过ALTER TABLE语句添加，所有组成复合主键的字段都不能为null。复合索引遵循最左前缀匹配原则，查询条件只要包含复合索引最左边的字段，索引就会生效，查询条件中字段的书写顺序不影响索引使用，但是范围查询会导致后续索引字段失效。合理设计复合主键和复合索引，可以有效提升数据库的存储规范性和查询性能。