mysql添加索引命令 mysql创建普通唯一复合索引教程

在mysql中给表加索引的核心目的是提升查询效率。解决方案是通过create index或alter table语句创建不同类型的索引：1. 普通索引用于加快非唯一列的查询；2. 唯一索引确保列值唯一性并提升性能；3. 复合索引支持多列组合查询，遵循最左前缀原则；4. 复合唯一索引结合复合和唯一特性，确保多列组合值唯一；5. 删除不再需要的索引可用drop index命令；若查询未命中索引、存在隐式类型转换或or条件使用不当，可能导致索引失效；判断索引是否生效可通过explain分析执行计划；复合索引列顺序应根据查询模式、选择性、排序分组需求合理设置；索引过多会降低写入性能、增加存储开销、影响优化器选择并提高维护成本，因此应按需创建并定期优化。

在MySQL里给表加索引，核心就是为了提升查询效率，尤其是数据量大了以后，那点性能提升可能就是天壤之别。无论是普通的、唯一的，还是多列组合的索引，都有它特定的应用场景和对应的创建方式。理解这些命令和背后的逻辑，能让你在数据库优化这条路上少走很多弯路。

解决方案

给MySQL表添加索引，主要通过CREATE INDEX或ALTER TABLE语句来完成。它们都能达到目的，但使用场景上可能有点细微偏好，比如CREATE INDEX更像一个独立的DDL操作，而ALTER TABLE则是在修改表结构时顺带完成。

创建普通索引 (Normal Index)

当你希望加快某一列或几列的查询速度，但不强制要求这些列的值唯一时，就用普通索引。

-- 方式一：使用CREATE INDEX语句
CREATE INDEX idx_user_name ON users (username);

-- 方式二：使用ALTER TABLE语句
ALTER TABLE products ADD INDEX idx_product_category (category_id);

创建唯一索引 (Unique Index)

唯一索引不仅能提升查询性能，更重要的是，它强制确保了索引列（或多列组合）中的所有值都是唯一的。这对于需要保证数据完整性的字段非常有用，比如用户邮箱、身份证号等。

-- 方式一：使用CREATE UNIQUE INDEX语句
CREATE UNIQUE INDEX uniq_user_email ON users (email);

-- 方式二：使用ALTER TABLE语句
ALTER TABLE orders ADD UNIQUE INDEX uniq_order_number (order_number);

创建复合索引 (Composite Index)

当你的查询条件经常涉及多个列的组合时，复合索引就能派上大用场。它按照你指定的列顺序进行索引，遵循“最左前缀原则”。

-- 方式一：使用CREATE INDEX语句
-- 查询经常是 WHERE last_name = 'Smith' AND first_name = 'John'
CREATE INDEX idx_user_name_full ON users (last_name, first_name);

-- 方式二：使用ALTER TABLE语句
-- 查询经常是 WHERE product_id = 123 AND warehouse_id = 456
ALTER TABLE inventory ADD INDEX idx_product_warehouse (product_id, warehouse_id);

创建复合唯一索引 (Composite Unique Index)

这是复合索引和唯一索引的结合体，它确保了你指定的多个列的组合值是唯一的。比如，一个用户在一个产品下只能有一个评价。

-- 方式一：使用CREATE UNIQUE INDEX语句
-- 确保每个用户对每个产品只有一个评分
CREATE UNIQUE INDEX uniq_user_product_rating ON product_ratings (user_id, product_id);

-- 方式二：使用ALTER TABLE语句
-- 确保在一个课程中，学生ID和学期ID的组合是唯一的
ALTER TABLE student_enrollments ADD UNIQUE INDEX uniq_course_student_semester (course_id, student_id, semester_id);

删除索引

如果某个索引不再需要，或者发现它反而拖慢了系统，可以删除它。

-- 删除普通索引、唯一索引、复合索引都可以用这个命令
DROP INDEX idx_user_name ON users;

为什么我的查询还是很慢，索引是不是没起作用？

这问题问得太好了，简直是数据库优化路上的“灵魂拷问”。很多人兴冲冲地加了索引，结果发现查询速度提升不明显，甚至有些时候，明明有索引，MySQL就是不用。这里面学问可大了，远不是“加个索引就完事”那么简单。

一个很常见的原因是，你的查询条件没有“命中”索引。比如，你给name列加了索引，但查询的时候用了WHERE SUBSTRING(name, 1, 3) = 'ABC'，或者WHERE name LIKE '%ABC'（注意是前缀通配符在前面），这种情况下，MySQL优化器就可能觉得走索引不划算，干脆全表扫描了。因为它不知道%ABC到底会匹配到什么，或者函数操作后，索引的有序性就被破坏了。

还有一种情况，是隐式类型转换。比如你的id列是INT类型，但查询时写成了WHERE id = '123'。MySQL可能会先将id列的值转换为字符串再进行比较，这也会导致索引失效。我个人就遇到过好几次这种低级错误，查了半天才发现是类型不匹配。

另外，如果查询条件里有OR，比如WHERE status = 'active' OR type = 'premium'，如果status和type是不同的索引，MySQL可能也无法同时利用这两个索引，或者只会选择其中一个，甚至放弃索引。这时候，考虑拆分成两个UNION ALL的查询，或者建立一个覆盖这两个字段的复合索引可能更有效。

要判断索引是否真的被使用了，最直接、最权威的方式就是用EXPLAIN。在你的SELECT语句前面加上EXPLAIN，然后执行，它会告诉你查询优化器是如何执行这条SQL的，包括是否使用了索引（看key和Extra列），以及扫描了多少行（rows列）。这就像是给SQL做了一次“CT扫描”，能清楚地看到内部运行机制。如果Extra列出现了Using filesort或Using temporary，那通常意味着性能瓶颈，需要进一步优化。

复合索引的列顺序到底怎么选才合理？

选择复合索引的列顺序，这确实是个技术活，直接关系到索引的效率。核心原则就是那个著名的“最左前缀原则”。简单来说，如果你创建了一个idx_a_b_c的复合索引（列顺序是a, b, c），那么这个索引可以支持：

• 查询条件只包含a的（WHERE a = ?）
• 查询条件包含a和b的（WHERE a = ? AND b = ?）
• 查询条件包含a、b和c的（WHERE a = ? AND b = ? AND c = ?）

但它就无法单独支持只包含b或c的查询，或者只包含b和c的查询。就像你有一本按姓氏、名字、中间名排序的电话簿，你可以很快找到“张三”，但想找所有“三”字开头的，就得翻遍了。

所以，在实际选择列顺序时，我通常会考虑以下几点：

1. 最常用于等值查询的列放前面： 如果你的查询语句经常用WHERE col1 = 'value1' AND col2 = 'value2'，那么col1就应该放在复合索引的最前面。因为它能最快地缩小搜索范围。
2. 选择性高的列放前面（针对等值查询）： 选择性指的是列中不重复值的数量占总行数的比例。比如，一个gender列（男/女）的选择性就很低，而email列的选择性就很高。对于等值查询，通常把选择性高的列放在前面，这样能更快地过滤掉大量数据。但这里有个小陷阱，如果你的查询条件是范围查询（BETWEEN, >,
3. 考虑排序和分组： 如果你的查询经常有ORDER BY col1, col2或者GROUP BY col1, col2，那么把这些列按照顺序放在复合索引中，可以利用索引的有序性，避免额外的文件排序（Using filesort），这在性能上提升巨大。
4. 覆盖索引： 如果一个复合索引包含了你查询语句中所有需要返回的列（不仅仅是WHERE条件中的列），那么MySQL可以直接从索引中获取数据，而无需回表查询，这被称为“覆盖索引”。这能显著提升查询速度，尤其是在I/O密集型操作中。比如SELECT col1, col2 FROM table WHERE col1 = ?，如果idx_col1_col2是复合索引，那就实现了覆盖。

实际操作中，没有一劳永逸的方案，往往需要结合业务场景、SQL查询模式以及EXPLAIN的分析结果来反复调整和验证。有时候，甚至需要创建多个不同的复合索引来满足不同的查询需求。

索引加多了会有什么负面影响？

索引并非越多越好，这就像给一本书加索引，加得太多太细，虽然找东西可能快，但写书、修改书的时候，你得花更多时间去更新目录。在数据库里，这个“更新目录”的成本，就是索引的负面影响。

1. 写入性能下降： 这是最直接的。每次对表进行INSERT、UPDATE、DELETE操作时，MySQL不仅要修改表中的数据，还要同步更新所有相关的索引。索引越多，需要更新的地方就越多，自然就拖慢了写入操作的速度。对于写多读少的应用，过度索引可能会带来严重的性能问题。
2. 磁盘空间占用： 索引本身也是数据，它们需要存储在磁盘上。一个大型表如果有很多索引，索引文件的大小可能会非常可观，甚至超过数据文件本身。这会增加存储成本，并且在备份、恢复、迁移数据库时也会耗费更多时间和资源。
3. 优化器选择困难： 索引多了，MySQL查询优化器在执行SELECT语句时，选择最佳执行计划的难度也会增加。它需要评估每个索引的成本，有时候可能会“选错”索引，导致查询效率反而不如预期。这就像你有一堆工具，但不知道哪个是最好的，反而浪费时间去尝试。
4. 维护成本增加： 随着业务发展和数据模式的变化，有些索引可能变得不再有效，或者需要调整。索引数量庞大时，管理和维护它们会变得复杂和耗时。你需要定期检查索引的使用情况，删除不常用的，调整不合理的。

所以，在创建索引时，我通常会遵循一个“够用就好”的原则。先分析最核心、最频繁、最慢的查询语句，针对性地创建索引。然后通过EXPLAIN和慢查询日志来观察效果，持续优化。避免盲目地给每个可能用到的列都加上索引。记住，索引是提升读性能的利器，但它是有代价的。

以上就是mysql添加索引命令 mysql创建普通唯一复合索引教程的详细内容，更多请关注php中文网其它相关文章！