mysql的索引有哪些？

1. 主键索引（PRIMARY KEY）

主键索引通常在创建表时定义，确保字段唯一且非空：

-- 建表时直接定义主键
CREATE TABLE users (
  id INT NOT NULL,
  name VARCHAR(50),
  PRIMARY KEY (id)  -- 单字段主键
);

-- 复合主键（多字段组合唯一）
CREATE TABLE order_details (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT,
  PRIMARY KEY (order_id, product_id)  -- 多字段组合主键
);

2. 唯一索引（UNIQUE）

确保字段值唯一（允许 NULL，但 NULL 只能出现一次）：

-- 建表时创建唯一索引
CREATE TABLE users (
  id INT PRIMARY KEY,
  phone VARCHAR(11) NOT NULL,
  email VARCHAR(32) NOT NULL,
  UNIQUE INDEX idx_email (email)  -- 为 email 字段创建唯一索引
);

-- 表已存在时添加唯一索引
CREATE UNIQUE INDEX idx_phone ON users(phone);  -- 为 phone 字段创建唯一索引

3. 普通索引（INDEX）

最基础的索引，无唯一性约束，仅用于加速查询：

-- 建表时创建普通索引
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  create_time DATETIME,
  INDEX idx_create_time (create_time)  -- 为创建时间创建普通索引
);

-- 表已存在时添加普通索引
CREATE INDEX idx_title ON articles(title);  -- 为标题创建普通索引

4. 复合索引（多列索引）

基于多个字段组合创建，遵循 “最左前缀匹配原则”：

-- 为 user_id 和 status 组合创建复合索引
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  status TINYINT,
  create_time DATETIME,
  INDEX idx_user_status (user_id, status)  -- 复合索引
);

-- 表已存在时添加复合索引
CREATE INDEX idx_name_age ON users(name, age);  -- 为 name 和 age 组合创建索引

复合索引的 “最左前缀匹配原则” 是指：当使用复合索引（多字段组合索引）时，数据库会优先匹配索引中最左侧的字段，只有当左侧字段被有效使用时，索引才会被部分或完全启用。

 

具体规则如下：

 

1. 从左到右匹配
   复合索引 (a, b, c) 会优先匹配字段 a，再匹配 a+b，最后匹配 a+b+c。
   例如，对于索引 (name, age, gender)：

   • 能触发索引的查询条件：
     WHERE name = ?
WHERE name = ? AND age = ?
WHERE name = ? AND age = ? AND gender = ?
   • 无法触发索引的查询条件：
     WHERE age = ?（跳过了最左的 name）
     WHERE age = ? AND gender = ?（缺少最左的 name）

2. 中间字段不连续时，仅匹配到连续的左侧字段
   对于索引 (a, b, c)，如果查询条件是 WHERE a = ? AND c = ?，则只有 a 字段会使用索引，c 字段无法利用索引（因为跳过了 b）。

3. 范围查询会中断后续匹配
   如果最左字段使用范围查询（>, <, BETWEEN 等），则后续字段无法利用索引。
   例如，对于索引 (name, age)：

   • WHERE name = ? AND age > ?：name 全匹配，age 范围查询，索引有效。
   • WHERE name > ? AND age = ?：name 是范围查询，age 无法利用索引。

示例：
假设有复合索引 (user_id, order_time)，以下查询的索引使用情况：

 

• SELECT * FROM orders WHERE user_id = 100 → 索引完全生效
• SELECT * FROM orders WHERE user_id = 100 AND order_time > '2023-01-01' → 索引生效（user_id 全匹配，order_time 范围匹配）
• SELECT * FROM orders WHERE order_time > '2023-01-01' → 索引失效（跳过最左的 user_id）

 

总结：创建复合索引时，应将查询频率最高、区分度最高的字段放在左侧，且查询条件需从左到右使用索引字段，才能最大限度利用复合索引的性能优势。

5. 全文索引（FULLTEXT）

用于大文本字段的全文检索（仅支持 CHAR、VARCHAR、TEXT 类型）：

-- 建表时创建全文索引
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  FULLTEXT INDEX idx_content (content)  -- 为 content 字段创建全文索引
);

-- 表已存在时添加全文索引
CREATE FULLTEXT INDEX idx_title_content ON articles(title, content);  -- 多字段组合全文索引

使用方式：查询时需用 MATCH() AGAINST() 语法：

SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库 索引' IN BOOLEAN MODE);

6. 空间索引（SPATIAL）

针对空间数据类型（如 POINT、GEOMETRY 等）的索引：

-- 建表时创建空间索引（字段必须为 NOT NULL）
CREATE TABLE locations (
  id INT PRIMARY KEY,
  position POINT NOT NULL,  -- 空间类型字段
  SPATIAL INDEX idx_position (position)  -- 空间索引
);

-- 表已存在时添加空间索引
CREATE SPATIAL INDEX idx_geo ON locations(position);

注意事项：

• 索引名（如 idx_email）建议遵循 idx_字段名 格式，便于识别。
• 复合索引的字段顺序会影响查询效率，应将查询频率高的字段放在前面。
• 过多索引会降低插入 / 更新 / 删除的性能，需根据业务查询频率权衡。