MySQL数据库如何建立索引？

MySQL数据库中建立索引是优化查询性能的关键手段，通过索引可以显著减少数据扫描范围，提高数据检索效率，索引的本质是一种数据结构，常见类型包括B+树索引、哈希索引、全文索引等，其中B+树索引是InnoDB存储引擎的默认选择，适用于大多数场景，建立索引需要综合考虑查询需求、数据特征和系统负载,避免过度索引导致写入性能下降和存储空间浪费。

索引的创建方法

在MySQL中，创建索引主要通过CREATE INDEX语句或ALTER TABLE语句实现,以下是具体语法和示例：

1. 创建普通索引
   适用于单列或多列的普通查询场景，语法如下：

   CREATE INDEX idx_column_name ON table_name (column_name);

   为users表的email列创建索引：

   CREATE INDEX idx_email ON users (email);

2. 创建唯一索引
   确保列值唯一，但允许有空值（与主键索引的区别）：

   
   （图片来源网络，侵删）

   CREATE UNIQUE INDEX idx_unique_column ON table_name (column_name);

   示例：为orders表的order_id创建唯一索引：

   CREATE UNIQUE INDEX idx_order_id ON orders (order_id);

3. 创建复合索引
   针对多列查询场景，遵循“最左前缀原则”，即查询条件必须包含索引的最左列：

   CREATE INDEX idx_composite ON table_name (column1, column2, column3);

   为products表的category_id和price创建复合索引：

   CREATE INDEX idx_category_price ON products (category_id, price);

   查询条件包含category_id或category_id + price均可使用索引，但仅查询price则无法命中。

   
   （图片来源网络，侵删）

4. 创建全文索引
   适用于文本内容的模糊查询，如文章标题、描述等：

   ALTER TABLE table_name ADD FULLTEXT INDEX idx_fulltext (column_name);

   示例：为articles表的content列创建全文索引：

   ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);

   使用时需配合MATCH() ... AGAINST()语法：

   SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);

索引的适用场景与注意事项

适用场景

1. 高频查询列：作为查询条件、排序或分组的列，如WHERE子句中的列、ORDER BY涉及的列。
2. 外键列：用于关联表的列，如user_id关联users表主键。
3. 大表数据列：数据量超过10万行的表，索引可显著提升查询速度。
4. 低选择性列需谨慎：如性别列（仅男/女），选择性低,可能不如全表扫描高效。

注意事项

1. 避免过度索引：每个索引都会占用额外存储空间，降低INSERT/UPDATE/DELETE速度，建议定期通过EXPLAIN分析查询计划，删除未使用的索引。
2. 索引列的选择性：选择性越高（唯一值越多），索引效果越好，例如主键列的选择性为1（绝对唯一）。
3. 索引列的数据类型：较短的列（如INT优于VARCHAR(255)）可减少索引大小，提升I/O效率。
4. 避免在频繁更新的列上创建索引：如更新频繁的计数器列,索引维护成本高。

索引的管理与优化

1. 查看索引信息
   通过以下命令查看表索引详情：

   SHOW INDEX FROM table_name;

2. 删除索引
   当索引不再需要时，可使用以下命令删除：

   DROP INDEX idx_name ON table_name;

3. 索引性能分析
   使用EXPLAIN分析查询语句是否命中索引：

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

   关注type列（如ref、range表示索引生效），Extra列若出现Using filesort或Using temporary则需优化索引或查询。

4. 索引碎片整理
   频繁更新数据可能导致索引碎片，可通过OPTIMIZE TABLE重建表和索引：

   OPTIMIZE TABLE table_name;

不同索引类型的对比

相关问答FAQs

Q1: 是否所有列都适合创建索引？
A1: 不是，索引适用于高频查询列、外键列或大表数据列，但对于低选择性列（如性别、布尔值）、频繁更新的列或数据量小的表（如几百行），索引可能反而降低性能，避免在LIKE通配符开头的查询列上创建索引,因为会导致索引失效。

Q2: 如何判断当前索引是否有效？
A2: 可通过EXPLAIN分析查询执行计划，查看type列是否为const（主键/唯一索引命中）、ref（普通索引命中）、range（范围索引命中）等，若出现ALL（全表扫描）且查询频繁，则需考虑优化索引或调整查询语句，通过SHOW INDEX FROM table_name查看索引使用频率，Index_rows列可反映索引数据量，Cardinality列表示唯一值数量，选择性接近Index_rows时索引效果较好。