Ubuntu MariaDB索引使用指南

一、索引的基本概念

索引是数据库中特殊的数据结构（如B+树），用于快速定位表中的数据行，类似于书籍的目录。其核心作用是加速查询（如SELECT、WHERE、JOIN操作），但会降低写操作（如INSERT、UPDATE、DELETE）的速度，因为写操作需要同步更新索引。

二、索引的类型

MariaDB支持多种索引类型，适用于不同场景：

• 普通索引（Normal Index）：最基本的索引，无唯一性约束，仅加速查询。
• 唯一索引（Unique Index）：保证列值的唯一性（允许NULL），用于避免重复数据（如用户邮箱）。
• 主键索引（Primary Key Index）：特殊的唯一索引，不允许NULL，用于唯一标识表中的每一行（如用户ID）。
• 复合索引（Composite Index）：多列组合的索引，遵循“最左前缀”原则（如(user_id, order_date)索引可用于WHERE user_id=1或WHERE user_id=1 AND order_date='2025-01-01'）。
• 全文索引（Fulltext Index）：用于全文搜索（如文章内容），支持MATCH...AGAINST语法。

三、索引的创建与管理

1. 创建索引

• 建表时创建：直接在CREATE TABLE语句中定义索引。

  CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(50) NOT NULL,
      email VARCHAR(100),
      INDEX idx_username (username),          -- 普通索引
      UNIQUE INDEX idx_email (email),         -- 唯一索引
      FULLTEXT INDEX idx_content (content)    -- 全文索引（仅MyISAM/InnoDB支持）
  );
  

• 已有表添加索引：使用ALTER TABLE或CREATE INDEX语句。

  ALTER TABLE users ADD INDEX idx_phone (phone);                      -- 普通索引
  CREATE UNIQUE INDEX idx_username_unique ON users (username);        -- 唯一索引
  CREATE FULLTEXT INDEX idx_description ON articles (description);    -- 全文索引
  

2. 查看索引

• 使用SHOW INDEX命令查看表的索引详情：

  SHOW INDEX FROM users;
  

• 使用DESCRIBE（或DESC）命令快速查看表的索引信息：

  DESCRIBE users;
  

3. 删除索引

• 使用DROP INDEX命令删除指定索引：

  DROP INDEX idx_username ON users;
  

四、索引的使用技巧

1. 索引列选择

• 高频查询列：为WHERE、ORDER BY、GROUP BY、JOIN关联列创建索引（如WHERE status=1中的status列）。
• 高区分度列：选择不同值多的列（如user_id比gender更适合建索引，因为user_id的区分度更高）。
• 避免过度索引：单张表索引数量建议不超过5个，过多索引会增加写操作开销并占用存储空间。

2. 复合索引优化

• 最左前缀原则：复合索引(a,b,c)只能用于WHERE a=1、WHERE a=1 AND b=2或WHERE a=1 AND b=2 AND c=3，无法用于WHERE b=2（除非a是常数）。
• 索引列顺序：将区分度高、长度小、使用频繁的列放在左侧（如(user_id, order_date)比(order_date, user_id)更高效）。

3. 覆盖索引

覆盖索引是指索引包含查询所需的所有列，无需回表查询（即直接从索引中获取数据，避免访问表数据文件）。例如：

-- 假设有复合索引 (username, email)
SELECT username, email FROM users WHERE username='john_doe';
-- 该查询可使用覆盖索引，无需访问表

4. 避免索引失效

• 避免在索引列上使用函数或表达式：如WHERE UPPER(username)='JOHN_DOE'会导致索引失效，应改为WHERE username='john_doe'。
• 避免类型转换：如WHERE id=100（id为INT）比WHERE CAST(id AS CHAR)='100'更高效，后者会导致索引失效。
• 避免OR条件过多：如WHERE a=1 OR b=2 OR c=3（若a、b、c无联合索引）会导致全表扫描，建议用UNION ALL代替（如SELECT * FROM table WHERE a=1 UNION ALL SELECT * FROM table WHERE b=2 UNION ALL SELECT * FROM table WHERE c=3）。
• 避免LIKE以%开头：如WHERE content LIKE '%keyword'会导致索引失效，建议用全文索引或LIKE 'keyword%'（后者可使用索引）。

五、索引的维护

1. 分析索引

使用ANALYZE TABLE命令更新索引的统计信息，帮助优化器选择更优的执行计划：

ANALYZE TABLE users;

2. 优化索引

使用OPTIMIZE TABLE命令整理表的碎片，提高索引的查询效率（尤其适用于频繁更新的表）：

OPTIMIZE TABLE users;

3. 监控索引使用情况

通过SHOW INDEX命令查看索引的Cardinality（基数，即列中不同值的数量），基数越高表示索引的选择性越好（如user_id的基数通常远高于gender）。

六、注意事项

• 主键选择：建议使用自增ID作为主键（连续增长，减少页分裂），避免使用UUID、MD5等随机字符串（会导致索引碎片化）。
• 外键约束：建立外键关系的列需保持字符集一致（如父表utf8mb4，子表也需为utf8mb4），否则会影响索引效率。
• 测试环境验证：所有索引优化操作需先在测试环境验证，避免生产环境出现问题（如过度索引导致写性能下降）。