mysql主键和复合索引

MySQL主键和复合索引

基础概念

主键（Primary Key）：

主键是表中的一个或多个字段，其值能唯一地标识表中的每一行记录。
主键的值必须是唯一的，且不能为NULL。
一个表只能有一个主键。

复合索引（Composite Index）：

复合索引是由多个字段组成的索引。
复合索引的顺序很重要，因为它是按照字段的顺序进行排序的。
复合索引可以提高多列查询的性能。

类型

主键类型：

单字段主键：一个字段作为主键。
复合主键：多个字段组合成一个主键。

复合索引类型：

普通复合索引：多个字段组成的索引。
唯一复合索引：多个字段组成的索引，且这些字段的组合值必须是唯一的。

应用场景

主键的应用场景：

用户表中的用户ID。
订单表中的订单ID。

复合索引的应用场景：

查询条件涉及多个字段的场景，如查询某个时间段内某个地区的订单。
多条件过滤查询，如查询某个年龄段且性别为男的用户。

遇到的问题及解决方法

问题1：为什么复合索引的顺序很重要？

原因：复合索引的顺序决定了索引的存储方式和查询效率。如果查询条件中的字段顺序与索引字段顺序不一致，可能会导致索引无法有效使用。

解决方法：根据查询条件的频率和重要性，合理安排复合索引的字段顺序。常用的查询条件应该放在前面。

问题2：如何选择主键？

原因：选择合适的主键可以提高数据库的性能和数据完整性。

解决方法：

选择唯一且不变的字段作为主键。
避免使用业务相关的字段作为主键，以免影响业务逻辑。
如果没有合适的单字段，可以考虑使用复合主键。

示例代码

-- 创建表并设置主键
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE
);

-- 创建复合索引
CREATE INDEX idx_user_role_dept ON users (role, department);