mysql 设置主键和索引

基础概念

MySQL中的主键（Primary Key）和索引（Index）是数据库设计中的两个重要概念。

主键：

主键是表中的一个或多个字段，其值能唯一地标识表中的每一行。
主键列不允许有重复值和NULL值。
主键的作用是确保数据的唯一性和完整性。

索引：

索引是一种数据结构，用于快速查询、更新数据库表中的数据。
索引可以显著提高查询速度，但会降低插入、删除和更新操作的速度。
索引可以是单列索引或多列索引。

类型

主键类型：

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

索引类型：

单列索引：对单个字段创建索引。
复合索引：对多个字段组合创建索引。
唯一索引：确保索引列的值唯一。
全文索引：用于全文搜索。

应用场景

主键的应用场景：

在需要唯一标识记录的场景中使用主键，如用户表中的用户ID。

索引的应用场景：

在经常用于查询条件的字段上创建索引，如搜索框中的关键词。
在连接多个表的字段上创建索引，以提高连接查询的效率。

常见问题及解决方法

问题1：为什么在创建表时没有设置主键？

原因：可能是因为设计者没有考虑到数据的唯一性需求，或者没有选择合适的字段作为主键。
解决方法：在设计表结构时，选择一个能唯一标识每行数据的字段作为主键。如果没有合适的字段，可以考虑添加一个自增的ID字段。

问题2：为什么索引没有提高查询速度？

原因：可能是索引没有被正确使用，或者查询条件不适合使用索引。
解决方法：
- 确保查询条件中使用了索引列。
- 检查查询语句是否使用了LIKE操作符，并且前缀不是通配符（如LIKE 'keyword%'）。
- 使用EXPLAIN命令分析查询计划，查看是否使用了索引。

问题3：如何优化索引？

解决方法：
- 根据查询需求创建合适的索引类型（单列索引、复合索引、唯一索引等）。
- 避免在经常更新的字段上创建索引。
- 定期分析和优化索引，删除不必要的索引。

示例代码

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

-- 创建单列索引
CREATE INDEX idx_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_email_username ON users(email, username);