索引是mysql中提高查询性能的关键工具，它类似于书籍目录，可快速定位数据。创建索引主要使用create index或alter table语句，例如：create index idx_email on users (email); 或 alter table users add index idx_name (name); 常见的索引类型包括b-tree索引适用于范围查询，hash索引适合精确匹配，全文索引用于文本搜索，空间索引处理地理数据。唯一索引确保列值唯一性，组合索引则根据多个字段建立，顺序影响效率。合理使用索引应在where、join子句常用列及高区分度列上，避免在低区分度列如gender上创建。索引失效常见原因包括使用函数、类型转换、or条件、like以%开头及数据量小。优化方法包括避免上述问题，定期分析表并监控查询性能，从而提升执行效率。
索引，简单来说，就像书的目录，能帮你快速找到想要的信息，而不是一页一页地翻。在MySQL里，索引就是用来加速查询的。
解决方案
MySQL索引主要通过 CREATE INDEX 语句创建，ALTER TABLE 语句也能添加索引。最常用的索引类型是B-Tree索引，适用于范围查询和精确匹配。
-
创建索引
假设你有一个名为
users的表，里面有id，name和email字段。如果你经常根据email字段来查询用户，可以这样创建一个索引：
CREATE INDEX idx_email ON users (email);
这会在
users表的email列上创建一个名为idx_email的索引。 -
ALTER TABLE 添加索引
另一种方式是使用
ALTER TABLE语句：ALTER TABLE users ADD INDEX idx_name (name);
这会在
users表的name列上创建一个名为idx_name的索引。 -
唯一索引
如果
email字段是唯一的，可以创建唯一索引：CREATE UNIQUE INDEX idx_unique_email ON users (email);
唯一索引确保列中的所有值都是唯一的。
-
组合索引
如果你的查询经常同时使用
name和email字段，可以创建一个组合索引：CREATE INDEX idx_name_email ON users (name, email);
组合索引的顺序很重要，应该按照查询中最常用的字段顺序排列。
-
全文索引
如果需要在文本字段上进行全文搜索，可以使用全文索引：
CREATE FULLTEXT INDEX idx_fulltext_name ON users (name);
然后可以使用
MATCH ... AGAINST语法进行全文搜索。 -
删除索引
如果不再需要某个索引，可以删除它：
DROP INDEX idx_email ON users;
什么时候应该创建索引？
索引并非越多越好。创建索引会增加写操作的开销，因为每次插入、更新或删除数据时，索引也需要更新。因此，只应该在经常用于查询的列上创建索引。
-
WHERE 子句中的列： 如果一个列经常出现在
WHERE子句中，那么它可能是一个好的索引候选者。 -
JOIN 子句中的列： 如果一个列经常用于
JOIN操作，那么它也可能是一个好的索引候选者。 -
区分度高的列： 索引对于区分度高的列效果最好。例如，一个
gender列只有两个值，那么在这个列上创建索引可能不会带来很大的性能提升。
如何查看MySQL是否使用了索引？
使用 EXPLAIN 语句可以查看MySQL是否使用了索引。例如：
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN 语句会返回查询的执行计划，其中会显示是否使用了索引，以及使用了哪个索引。
索引的类型有哪些，分别有什么特点？
MySQL支持多种索引类型，每种类型都有不同的特点和适用场景。
- B-Tree索引： 这是最常用的索引类型，适用于范围查询和精确匹配。
- Hash索引： Hash索引适用于精确匹配，但不适用于范围查询。
- 全文索引： 全文索引适用于在文本字段上进行全文搜索。
- 空间索引： 空间索引适用于存储和查询地理空间数据。
选择哪种索引类型取决于你的具体需求。一般来说，B-Tree索引是最好的选择，除非你有特殊的需求。
索引失效的常见原因有哪些？
即使你创建了索引，MySQL也可能不会使用它。以下是一些常见的索引失效的原因：
-
使用了函数或表达式： 如果在
WHERE子句中使用了函数或表达式，MySQL可能无法使用索引。例如：WHERE YEAR(date) = 2023。 -
类型转换： 如果在
WHERE子句中使用了类型转换，MySQL可能无法使用索引。例如：WHERE id = '123'，如果id是一个整数类型。 -
使用了
OR： 如果在WHERE子句中使用了OR，MySQL可能无法使用索引。 -
LIKE查询以%开头： 如果LIKE查询以%开头，MySQL可能无法使用索引。例如：WHERE name LIKE '%test'。 - 数据量太小： 如果表中的数据量太小，MySQL可能认为使用索引的开销比全表扫描更大，因此不会使用索引。
如何优化索引的使用？
- 避免在
WHERE子句中使用函数或表达式。 - 避免在
WHERE子句中使用类型转换。 - 尽量避免在
WHERE子句中使用OR。 - 避免
LIKE查询以%开头。 -
定期分析和优化表： 使用
ANALYZE TABLE语句可以分析表并更新索引统计信息，这可以帮助MySQL选择更好的执行计划。 - 监控查询性能： 使用 MySQL 的性能监控工具可以帮助你识别慢查询，并找到需要优化的索引。
总的来说，索引是提高MySQL查询性能的重要手段。但是，索引并非越多越好，应该根据你的具体需求来创建和优化索引。理解索引的原理和使用方法，可以帮助你编写更高效的SQL查询。
