在MySQL数据库中,索引是提高查询性能的重要工具，它类似于书籍的目录，可以帮助数据库系统快速定位数据行，从而减少数据扫描的I/O操作和时间成本，合理地添加索引可以显著提升SELECT查询的速度，但也会增加写入操作的开销和占用存储空间，掌握MySQL添加索引的命令和原则对于数据库优化至关重要，MySQL提供了多种创建索引的方式，包括在创建表时定义索引、通过ALTER TABLE语句添加索引，以及使用CREATE INDEX语句直接创建索引，下面将详细介绍这些方法及其适用场景。
在创建表时定义索引是最直接的方式之一,可以通过在CREATE TABLE语句中使用PRIMARY KEY、UNIQUE、INDEX或KEY关键字来定义索引，创建一个用户表时，可以将id字段设置为主键索引，确保其唯一性和快速查询：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
INDEX idx_username (username)
);
在上面的例子中,id字段被定义为主键索引（PRIMARY KEY），email字段被定义为唯一索引（UNIQUE），而username字段则通过INDEX关键字创建了普通索引（idx_username），这种方式适用于表结构初始设计阶段，能够一次性完成索引的定义，避免后续修改表结构带来的额外开销。
使用ALTER TABLE语句添加索引是表创建后常用的方法，ALTER TABLE语句功能强大，不仅可以修改表结构，还可以添加、删除或修改索引，如果需要在已存在的orders表中为customer_id字段添加索引，可以使用以下命令：
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);
如果需要添加唯一索引,可以使用ADD UNIQUE关键字：
ALTER TABLE orders ADD UNIQUE INDEX idx_order_id (order_id);
ALTER TABLE还支持添加主键索引，但前提是该字段必须定义为NOT NULL且尚未有主键：
ALTER TABLE orders ADD PRIMARY KEY (id);
ALTER TABLE语句的优点是灵活性高，可以在不删除表的情况下动态调整索引结构，但需要注意，对于大型表，添加索引可能会锁定表并影响性能，建议在低峰期执行。
第三种方法是使用CREATE INDEX语句直接创建索引，这种方式语法简洁，专门用于创建索引，而不涉及表的其他结构修改，为products表的name字段创建普通索引：
CREATE INDEX idx_product_name ON products (name);
创建唯一索引的语法为：
CREATE UNIQUE INDEX idx_product_code ON products (product_code);
需要注意的是,CREATE INDEX语句不能用于创建主键索引，主键索引必须通过ALTER TABLE或CREATE TABLE定义，CREATE INDEX和ALTER TABLE在功能上基本等效，但某些特定场景下（如添加全文索引），CREATE INDEX可能提供更简洁的语法。
在实际应用中,选择合适的索引类型也是关键，MySQL支持多种索引类型，包括B-Tree索引、哈希索引、全文索引和空间索引，B-Tree索引是最常用的类型，默认适用于大多数场景，支持=、>、<、BETWEEN、LIKE等查询操作；哈希索引仅支持精确匹配查询，且Memory引擎支持；全文索引专门用于文本搜索，如MATCH AGAINST语句；空间索引则用于地理空间数据类型，如GEOMETRY，为文章表的内容字段添加全文索引：
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
索引的设计需要遵循一些原则,以确保其有效性，索引应建立在经常用于查询条件的字段上，例如WHERE子句中的字段、JOIN操作的关联字段和ORDER BY排序的字段，避免对低基数字段（如性别字段，只有男、女两个值）建立索引，因为索引的选择性较低，可能无法有效减少数据量，索引列应尽量保持简洁，避免对大型文本或BLOB字段建立索引，以减少索引大小和维护成本，定期分析和优化索引使用情况，通过EXPLAIN语句检查查询执行计划，确认索引是否被正确使用。
下面通过一个表格对比不同创建索引方式的适用场景和语法特点：
| 方法 | 适用场景 | 语法示例 | 特点 |
|---|---|---|---|
| CREATE TABLE | 表创建时定义索引 | CREATE TABLE t (id INT PRIMARY KEY, INDEX idx (col)) | 一次性完成表和索引定义，结构清晰 |
| ALTER TABLE | 表创建后添加或修改索引 | ALTER TABLE t ADD INDEX idx (col) | 灵活性高，可动态调整，但可能锁表 |
| CREATE INDEX | 专门创建索引，不修改表结构 | CREATE INDEX idx ON t (col) | 语法简洁，不支持主键索引 |
在实际操作中,还需要注意索引的维护，随着数据量的增加，索引可能变得碎片化，影响查询性能，可以通过OPTIMIZE TABLE语句重建表和索引以减少碎片，当索引不再需要时，可以通过DROP INDEX语句删除，
ALTER TABLE orders DROP INDEX idx_customer_id;
删除索引可以减少写入操作的开销,但应谨慎评估，确保删除后不会影响关键查询的性能。
关于索引的局限性也需要明确,索引虽然能提升查询速度，但会降低INSERT、UPDATE、DELETE操作的速度，因为每次数据修改都需要更新索引结构，索引会占用额外的存储空间，对于数据量极大的表，需要权衡索引带来的性能提升和存储成本，索引的设计应根据实际业务需求和查询模式进行优化，避免过度索引或缺失关键索引。
相关问答FAQs：
-
问：是否所有字段都适合添加索引？
答：并非所有字段都适合添加索引，索引适用于经常用于查询条件（如WHERE子句）、排序（ORDER BY）或连接（JOIN）的字段，尤其是数据量大且选择性高的字段，对于低基数字段（如状态字段，只有几种固定值）、频繁更新的字段或大型文本/BLOB字段，建立索引可能效果不佳，甚至降低性能，索引会增加存储空间和写入开销，因此应根据实际查询需求合理选择索引字段。 -
问：如何判断索引是否被有效使用？
答：可以通过MySQL的EXPLAIN语句分析查询执行计划，检查是否使用了索引，在EXPLAIN结果中，如果type列显示为"ref"、"range"或"const"等，且key列显示了使用的索引名称，则表示索引被有效使用；如果type为"ALL"且key为"NULL"，则表示全表扫描，索引未被使用，可以通过SHOW INDEX FROM table_name查看表的所有索引信息，结合慢查询日志（slow_query_log）分析未使用索引的查询，并优化或调整索引设计。
