MySQL索引

# MySQL索引 \> 索引（Index）在数据库里，其实就像书的\*\*目录\*\*： \> \> - 有了目录，你能快速翻到想看的章节（快速定位数据行） \> - 没有目录，你只能一页一页翻（全表扫描） \> \> 在 MySQL 中，索引就是一组\*\*数据结构\*\*，用来加快数据的检索速度。MySQL 常用的索引底层结构是 \*\*B+ 树\*\*（平衡多叉树）： \> \> - 按照\*\*有序\*\*方式存储数据 \> - 查询某个范围或值时，只需要从树根一层层找到对应叶子节点 \> - 查找效率稳定在 \*\*O(log n)\*\* \> \> （InnoDB 的索引是 \*\*聚簇索引\*\*，数据本身存放在 B+ 树叶子节点里） ## 一、\*\*索引作用\*\* - \*\*加速查询\*\*（减少扫描行数） - \*\*保证数据唯一性\*\*（唯一索引/主键索引） - \*\*优化排序与分组\*\*（\`ORDER BY\` / \`GROUP BY\`） - \*\*辅助关联查询\*\*（连接条件上的字段） \> 代价：占用额外存储 + 影响 \`INSERT/UPDATE/DELETE\` 性能（需要维护索引结构） ## 二、索引创建在 MySQL 里添加索引主要有三种方式，下面我给你一个面试+实战速记版。 ------ ### \*\*1. 创建表时添加索引\*\* \`\`\`sql CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), INDEX idx_name(name) -- 普通索引 ); \`\`\` ------ ### \*\*2. 已有表添加索引\*\* #### \*\*(1) 普通索引\*\* \`\`\`sql CREATE INDEX idx_name ON user(name); \`\`\` #### \*\*(2) 唯一索引\*\* \`\`\`sql CREATE UNIQUE INDEX idx_email ON user(email); \`\`\` #### \*\*(3) 组合索引\*\* \`\`\`sql CREATE INDEX idx_name_email ON user(name, email); \`\`\` ------ ### \*\*3. 通过修改表结构添加\*\* \`\`\`sql ALTER TABLE user ADD INDEX idx_name(name); ALTER TABLE user ADD UNIQUE INDEX idx_email(email); ALTER TABLE user ADD FULLTEXT INDEX idx_content(content); -- 全文索引 \`\`\` ------ ## \*\*4. 组合索引 \& 最左匹配原则\*\* 组合索引（多列）会根据\*\*最左前缀原则\*\*工作： \`\`\`sql CREATE INDEX idx_name_age ON user(name, age); \`\`\` 该索引能命中： \`\`\`sql WHERE name = 'Tom'; WHERE name = 'Tom' AND age = 20; \`\`\` 但不能直接命中： \`\`\`sql WHERE age = 20; -- name 没用上 \`\`\` 💡 \*\*提示\*\* \> MySQL 常用 \`ALTER TABLE\` 或 \`CREATE INDEX\` 添加索引；索引字段选择应遵循"高选择性、经常查询、少更新"原则；对于组合索引要注意\*\*最左匹配原则\*\*。 ------ ## \*\*三、MySQL 索引类型\*\* \| 类型 \| 语法关键字 \| 说明 \| \| -------- \| ---------------- \| ------------------------------------------ \| \| 普通索引 \| \`INDEX\` 或 \`KEY\` \| 加快查询，允许重复和 NULL \| \| 唯一索引 \| \`UNIQUE INDEX\` \| 保证唯一性，允许 NULL（多个 NULL） \| \| 主键索引 \| \`PRIMARY KEY\` \| 唯一且不允许 NULL \| \| 全文索引 \| \`FULLTEXT INDEX\` \| 用于全文检索（MyISAM/部分 InnoDB） \| \| 空间索引 \| \`SPATIAL INDEX\` \| 地理位置数据（需 MyISAM/特定 InnoDB 版本） \| ------ ## \*\*四、索引优化原则\*\* 1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列 2. 基数较小的表，索引效果较差，没有必要在此列建立索引 3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。 4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。 5. 定义有外键的数据列一定要建立索引。 6. 更新频繁字段不适合创建索引。 7. 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低) 8. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。 9. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。 10. 对于定义为text、image和bit的数据类型的列不要建立索引。 11. \`LIKE 'xxx%'\` 可以走索引，\`LIKE '%xxx'\` 会失效（除非用全文索引）(本别是指以xxx开头和结尾) 12. 尽量用\*\*覆盖索引\*\*（减少回表） 13. 避免在索引列上使用函数/运算（会失效） \> Q：MySQL 创建索引的最佳实践？ \> \> A：\*\*选择性高的列优先，组合索引按查询条件的使用顺序创建，遵循最左匹配原则，减少索引数量以平衡读写性能。\*\* ------ ## 四、Mysql中九种索引失效场景分析 MySQL 索引失效通常意味着\*\*优化器没有使用索引\*\*，导致走全表扫描（\`type=ALL\`），查询性能下降。下面是常见的 \*\*9 种索引失效场景\*\*（以 InnoDB 为例）。 ------ ## 1. 使用 \`LIKE '%xxx'\` 前缀模糊匹配 \`\`\`sql -- 索引：idx_name(name) SELECT \* FROM user WHERE name LIKE '%abc'; -- 索引失效 SELECT \* FROM user WHERE name LIKE 'abc%'; -- 可以走索引 \`\`\` \*\*原因\*\*：前缀 \`%\` 会导致无法利用 B+Tree 索引的\*\*有序性\*\*。 \*\*解决\*\*： - 改成后缀匹配 \`LIKE 'abc%'\` - 或使用 \*\*全文索引\*\* / \*\*倒排索引\*\*（MySQL 5.6+） ------ ## 2. 在索引列上进行函数运算或表达式计算 \`\`\`sql SELECT \* FROM user WHERE YEAR(create_time) = 2025; -- 索引失效 SELECT \* FROM user WHERE create_time \>= '2025-01-01' AND create_time \< '2026-01-01'; -- 走索引 \`\`\` \*\*原因\*\*：函数或计算会改变列的值，导致无法利用索引中的原始有序数据。 ------ ## 3. 隐式类型转换 \`\`\`sql -- phone 为 varchar SELECT \* FROM user WHERE phone = 13800138000; -- int → varchar 转换，索引失效 SELECT \* FROM user WHERE phone = '13800138000'; -- 正确，走索引 \`\`\` \*\*原因\*\*：类型不一致会触发隐式转换，相当于对列执行函数。 ------ ## 4. 不满足联合索引的"最左前缀原则" \`\`\`sql -- 联合索引 idx_name_age(name, age) SELECT \* FROM user WHERE age = 20; -- 索引失效 SELECT \* FROM user WHERE name = 'Tom'; -- 走索引 SELECT \* FROM user WHERE name = 'Tom' AND age = 20; -- 走索引 \`\`\` \*\*原因\*\*：B+Tree 索引是按照最左列优先排序的，跳过第一列就无法利用。 ------ ## 5. 在索引列上使用 \`!=\` 或 \`\< \>\`、\`NOT IN\` \`\`\`sql SELECT \* FROM user WHERE age != 18; -- 索引失效 \`\`\` \*\*原因\*\*：\`!=\` / \`NOT IN\` 会导致范围过大，优化器可能选择全表扫描。 ------ ## 6. \`OR\` 条件混合非索引列 \`\`\`sql -- name 有索引，age 无索引 SELECT \* FROM user WHERE name = 'Tom' OR age = 20; -- 索引失效 \`\`\` \*\*原因\*\*：\`OR\` 中如果有一列没有索引，会导致整体走全表扫描。 \*\*解决\*\*： - 给所有条件字段加索引 - 或拆成两条 SQL 用 \`UNION ALL\` ------ ## 7. 查询条件中使用 \`IS NULL\` 或 \`IS NOT NULL\` \`\`\`sql SELECT \* FROM user WHERE name IS NULL; -- 可能索引失效 SELECT \* FROM user WHERE name IS NOT NULL; -- 索引失效概率高 \`\`\` \*\*原因\*\*：MySQL 对 NULL 的处理特殊，\`IS NOT NULL\` 往往全表扫描。 ------ ## 8. 查询范围条件（\`\>\`, \`\<\`, \`BETWEEN\`）后再加等值匹配 \`\`\`sql -- 联合索引 idx_name_age(name, age) SELECT \* FROM user WHERE name \> 'A' AND age = 20; -- age 条件索引失效 \`\`\` \*\*原因\*\*：在联合索引中，一旦某列使用了范围查询，后面的列就无法继续利用索引。 ------ ## 9. 数据分布不均衡（低选择性） \`\`\`sql -- sex 列只有 '男' / '女' SELECT \* FROM user WHERE sex = '男'; -- 优化器可能选择全表扫描 \`\`\` \*\*原因\*\*：如果索引列重复值太多，使用索引的成本高于全表扫描，优化器会放弃索引。 ------ ## 索引失效排查建议 1. 用 \`EXPLAIN\` 看执行计划，检查 \`type\` 是否为 \`ALL\`、\`key\` 是否为 \`NULL\` 2. 关注 \`rows\` 是否过大 3. 检查 SQL 是否触犯了上面 9 个禁忌 \`\`\`sql EXPLAIN SELECT \* FROM user WHERE id = 1; \`\`\` ------