Debian MySQL索引使用指南

Debian MySQL索引使用指南

一、索引类型与适用场景

• 主键索引：唯一标识记录，自动创建唯一索引，加速主键查询。
• 唯一索引：确保列值唯一，适用于唯一性约束（如用户名）。
• 普通索引：允许重复值，加速普通查询条件（如状态、类型字段）。
• 全文索引：用于文本模糊搜索（如MATCH(content) AGAINST('keyword')）。
• 复合索引：多列组合索引，遵循最左前缀原则（查询需包含最左列，如(col1,col2)需满足col1条件）。

二、创建与管理索引

1. 创建索引

   • 建表时添加：

     CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE, -- 唯一索引  email VARCHAR(100), INDEX idx_email (email), -- 普通索引  FULLTEXT INDEX idx_content (content) -- 全文索引（需指定字段类型为TEXT等）  ); 

   • 为已有表添加：

     -- 普通索引  CREATE INDEX idx_username ON users(username); -- 复合索引  CREATE INDEX idx_name_email ON users(first_name, last_name, email); 

     或使用ALTER TABLE：

     ALTER TABLE users ADD INDEX idx_username (username); 

2. 查看索引

   SHOW INDEX FROM table_name; 

3. 删除索引

   DROP INDEX idx_username ON users; 

三、优化索引使用

1. 避免索引失效

   • 不在索引列上使用函数或运算（如WHERE YEAR(date_col) = 2023，应改为WHERE date_col BETWEEN '2023-01-01' AND '2023-12-31'）。
   • 避免以通配符开头的LIKE查询（如LIKE '%value%'），可使用全文索引替代。

2. 利用覆盖索引
   确保查询的列全部包含在索引中，避免回表查询（如SELECT id,name FROM users WHERE age > 18，索引包含(age,id,name)）。

3. 定期维护索引

   • 分析表统计信息：ANALYZE TABLE table_name;。
   • 优化表碎片：OPTIMIZE TABLE table_name;。

四、查询优化技巧

• 使用EXPLAIN分析执行计划：

  EXPLAIN SELECT * FROM users WHERE email = 'example@example.com'; 

  重点关注type（访问类型，ref/range优于ALL）、key（是否使用索引）。
• 限制结果集：使用LIMIT避免返回冗余数据。
• 选择合适存储引擎：
  • InnoDB：支持事务、行级锁，适合高并发场景（默认引擎）。
  • MyISAM：适合读多写少场景，不支持事务。

五、注意事项

• 控制索引数量：过多索引会增加写入开销，定期删除冗余索引。
• 监控慢查询：通过慢查询日志定位未使用索引的SQL语句。

参考来源：