上一篇
给mysql加索引有哪些技巧？mysql加索引的最佳实践
在 MySQL 数据库中，索引是提升查询性能的核心机制，其本质类似于书籍的目录，通过合理添加索引，可以将全表扫描（Full Table Scan）转化为索引扫描，从而大幅减少 I/O 操作和 CPU 计算量，以下是关于如何为 MySQL 添加索引的详细指南。
确定需要添加索引的列
并非所有列都适合建立索引,在决定添加索引之前，需要评估以下因素：
- 高频查询字段：在 WHERE、JOIN、ORDER BY 和 GROUP BY 子句中频繁出现的列。
- 区分度高（Cardinality）：列中唯一值的数量越多，索引效率越高，性别字段只有“男”和“女”两个值，区分度极低，通常不建议单独建索引；而用户 ID 或邮箱则具有极高的区分度。
- 数据量大小：对于数据量极小的表（如几百行），索引带来的开销可能超过查询加速的收益，此时全表扫描可能更快。
选择合适的索引类型
MySQL 支持多种索引类型，理解它们的区别有助于做出正确选择：
| 索引类型 | 说明 | 适用场景 |
|---|---|---|
| 主键索引 (PRIMARY KEY) | 唯一且非空，表通常只能有一个。 | 用于唯一标识每一行记录，通常是自增 ID 或 UUID。 |
| 唯一索引 (UNIQUE) | 允许空值，但值必须唯一。 | 用于保证业务逻辑的唯一性，如用户名、邮箱。 |
| 普通索引 (INDEX) | 最基本的索引，没有任何限制。 | 用于加速常规查询，无唯一性要求。 |
| 联合索引 (COMPOSITE) | 对多个列创建的索引。 | 用于多条件组合查询，遵循“最左前缀”原则。 |
| 全文索引 (FULLTEXT) | 用于搜索文本中的关键词。 | 适用于大文本字段（如文章内容、评论）的模糊搜索。 |
创建索引的具体语法
根据需求不同,创建索引的方式也有所区别，以下是几种常见的 SQL 语句示例：
创建表时直接定义索引
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, age INT, -创建普通索引 INDEX idx_age (age), -创建唯一索引 UNIQUE INDEX idx_email (email) );
在已存在的表中添加索引
-添加普通索引 ALTER TABLE users ADD INDEX idx_username (username); -添加唯一索引 ALTER TABLE users ADD UNIQUE INDEX idx_phone (phone_number); -添加联合索引（注意列的顺序） ALTER TABLE users ADD INDEX idx_age_status (age, status);
删除索引
-删除索引 DROP INDEX idx_username ON users; -或者 ALTER TABLE users DROP INDEX idx_username;
联合索引与最左前缀原则
当创建包含多个列的联合索引时,必须严格遵守
最左前缀原则，这意味着查询条件必须从索引的最左边列开始匹配，否则索引将失效或部分失效。
假设创建了联合索引 idx_age_status (age, status)：
- WHERE age = 25：有效，可以使用索引。
- WHERE age = 25 AND status = 'active'：有效，可以使用索引。
- WHERE status = 'active'：无效，无法使用索引（因为跳过了最左边的 age 列）。
- WHERE age > 20 AND status = 'active'：部分有效，age 列可以使用索引进行范围查找，但 status 列无法利用索引进行精确查找。
在设计联合索引时,应将区分度最高、最常作为查询条件的列放在最左侧。
索引维护与性能监控
添加索引并非一劳永逸,需要定期监控其效果：
- 使用 EXPLAIN 分析查询：在执行查询前加上 EXPLAIN 关键字，可以查看 MySQL 如何使用索引，重点关注 type 列（是否为 ref 或 range，避免 ALL 全表扫描）和 key 列（实际使用的索引名称）。
- 监控索引使用情况：可以通过 SHOW INDEX FROM table_name; 查看索引的基数和利用率，如果某个索引从未被使用，可以考虑删除以节省存储空间并提高写入性能。
- 权衡读写性能：索引虽然加速了查询，但会降低 INSERT、UPDATE 和 DELETE 的速度，因为数据库在修改数据时也需要更新索引树，应在读取频繁、写入相对较少的场景下优先使用索引。
相关问题与解答
问题 1：为什么给所有字段都加上索引反而会导致数据库性能下降？
解答：
索引是一把双刃剑，虽然索引能加速查询，但它会占用额外的磁盘空间，并在数据写入（增、删、改）时增加系统开销，每次插入或更新数据时，MySQL 不仅要修改数据行，还要维护对应的索引结构（如 B+ 树），这会导致写入速度变慢，过多的索引会使优化器在选择执行计划时变得更加复杂，甚至可能选择错误的索引，导致查询效率降低，应遵循“少而精”的原则，只为高频查询和高区分度的字段建立索引。
问题 2：在什么情况下，即使建立了索引，查询也不会使用它？
解答：
以下几种常见情况会导致索引失效：
- 函数或表达式操作：如果在索引列上使用了函数（如 WHERE YEAR(create_time) = 2023）或进行了算术运算，MySQL 通常无法使用该索引。
- 隐式类型转换：如果索引列是字符串类型，而查询条件传入的是数字（如 WHERE varchar_col = 123），MySQL 会进行隐式转换，导致索引失效。
- 模糊查询以通配符开头：使用 LIKE '%keyword' 时，由于无法确定起始位置，索引通常无法使用；而 LIKE 'keyword%' 则可以利用索引。
- OR 条件连接：OR 连接的条件中，有一部分字段没有索引，那么整个查询可能会放弃使用索引，转为全表扫描。
- 违反最左前缀原则：在使用联合索引时，如果查询条件没有包含索引的最左列，索引将失效。
