MySQL索引是一种特殊的数据库结构，它类似于书籍的目录，能够帮助数据库快速定位到需要查询的数据行，避免全表扫描，从而大幅提升查询效率。合理的索引设计是优化MySQL数据库性能的关键手段之一。
MySQL常见索引类型
MySQL支持多种索引类型，不同类型的索引适用场景有所差异，常见的索引类型如下：
- 普通索引：最基本的索引类型，没有唯一性限制，仅用于加速查询。
- 唯一索引：索引列的值必须唯一，允许为空，常用于保证列数据的唯一性。
- 主键索引：特殊的唯一索引，不允许为空，每个表只能有一个主键索引。
- 联合索引：由多个列共同组成的索引，遵循最左前缀匹配原则。
- 全文索引：用于文本内容的模糊查询，仅支持MyISAM和InnoDB引擎的CHAR、VARCHAR、TEXT类型列。
索引的创建与删除
我们可以通过SQL语句直接创建、修改或删除索引，以下是常用的操作示例：
创建索引
创建普通索引的语法如下：
-- 创建表时添加普通索引
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
age INT,
-- 给username列创建普通索引
INDEX idx_username (username)
);
-- 给已有表添加普通索引
CREATE INDEX idx_age ON user_info(age);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique_username ON user_info(username);
-- 创建联合索引，包含username和age两个列
CREATE INDEX idx_username_age ON user_info(username, age);
删除索引
删除索引的语法如下：
-- 删除普通索引 DROP INDEX idx_age ON user_info; -- 删除唯一索引 DROP INDEX idx_unique_username ON user_info;
查看索引
我们可以通过以下语句查看表的索引信息：
-- 查看user_info表的所有索引 SHOW INDEX FROM user_info;
索引的使用场景与失效情况
不是所有场景都适合使用索引，我们需要了解索引的适用场景和失效的常见原因，避免无效创建索引。
适合使用索引的场景
- 经常作为查询条件（WHERE子句）的列。
- 经常作为排序（ORDER BY）或分组（GROUP BY）的列。
- 经常用于表连接的列（JOIN条件中的列）。
索引失效的常见原因
- 查询条件中对索引列使用函数或运算，例如
WHERE YEAR(create_time) = 2023，会导致create_time上的索引失效。 - 使用LIKE进行左模糊匹配，例如
WHERE username LIKE '%test'，索引会失效，右模糊匹配LIKE 'test%'可以使用索引。 - 查询条件中使用OR，且OR两边的列不全是索引列，会导致索引失效。
- 联合索引没有遵循最左前缀匹配原则，例如联合索引是
(a,b,c)，查询条件只用了b和c，无法使用索引。 - 查询时类型转换，例如索引列是字符串类型，查询时传入数字，会导致索引失效。
MySQL索引优化技巧
为了让索引发挥最大作用，我们可以参考以下优化技巧：
- 控制索引数量，单表索引建议不超过5个，过多的索引会影响插入、更新、删除的效率。
- 优先选择区分度高的列作为索引，区分度越高，索引过滤效果越好，例如性别列的区分度低，不适合单独作为索引。
- 联合索引尽量覆盖查询的所有列，避免回表查询，提升查询效率。
- 定期分析索引使用情况，删除冗余、无效的索引，避免占用额外存储空间。
- 对于长文本类型的列，如果需要创建索引，可以使用前缀索引，减少索引占用的空间。
以下是创建前缀索引的示例：
-- 给content列创建长度为50的前缀索引 CREATE INDEX idx_content_prefix ON article(content(50));
总结
MySQL索引是提升查询性能的重要工具，我们需要根据业务场景合理设计索引，同时避免索引失效的情况。在实际开发中，要结合慢查询日志分析查询语句，针对性地优化索引，才能让数据库保持高效的运行状态。