mysql中索引的基本概念与创建方法

索引是加速查询的B+树结构，但会降低写入性能并占用空间；低基数字段、长文本无前缀、联合索引顺序不当均会导致失效；需用EXPLaiN验证执行计划，优先选type=ref/range、rows精准、无using filesort/temporary。

索引是什么，为什么加了索引反而变慢

索引本质是一棵 B+ 树结构的附加数据结构，用来加速 WHERE、ORDER BY、JOIN 等操作的查找过程。但它不是免费的：每次 INSERT、UPDATE、delete 都要同步更新索引树，还会额外占用磁盘空间。所以字段上堆满索引，写入性能会明显下降，甚至拖慢整体吞吐。

常见误判场景：

• 给 status 这类低基数字段（比如只有 0/1）建普通索引，mysql 很可能直接放弃使用，优化器认为全表扫描更快
• 在 TEXT 或很长的 VARchar 字段上建全文索引以外的索引，不指定前缀长度会报错或浪费空间
• 联合索引字段顺序不合理，比如 (a, b) 无法加速 WHERE b = ? 查询

怎么创建单列索引和联合索引

用 CREATE INDEX 最直观，语法简单且不影响表锁（MySQL 5.6+ 支持在线 DDL）。

CREATE INDEX idx_user_email ON users(email); CREATE INDEX idx_order_status_created ON orders(status, created_at);

注意点：

• 索引名建议带表名前缀，避免跨表重名；不命名则 MySQL 自动生成（如 idx_1），可读性差
• 联合索引字段顺序按「过滤性高 → 范围查询字段靠后 → 排序字段尽量放末尾」排列，例如 (tenant_id, status, created_at) 比 (created_at, tenant_id, status) 更实用
• UNIQUE INDEX 和 PRIMARY KEY 也属于索引，但有唯一性约束语义，不能混用作普通加速索引

什么时候该用 FULLTEXT 索引

普通索引对 LIKE '%关键词%' 无效，而 FULLTEXT 是专为文本模糊匹配设计的，仅支持 MyISAM 和 InnoDB（5.6+），且只作用于 CHAR、VARCHAR、TEXT 类型。

ALTER table articles ADD FULLTEXT(title, content); SELECT * FROM articles WHERE MATCH(title, content) AGAINST('数据库 优化' IN NATURAL LANGUAGE MODE);

关键限制：

• 默认忽略少于 4 个字符的词（可通过 ft_min_word_len 修改，但需重启 MySQL）
• AGAINST() 中的搜索词不能含 SQL 特殊字符，否则要预处理或改用布尔模式
• 不支持前导通配符，AGAINST('数据库*' IN Boolean MODE) 可以，但 '*据库' 不行

如何确认索引是否生效

别只看 EXPLAIN 输出里有没有 key 字段，重点看 type、rows、Extra：

• type = ref 或 range 通常表示走了索引；ALL 就是全表扫描
• rows 值越接近实际命中行数越好，如果显示几十万但只查 10 条，说明索引没选对或条件没走索引
• Extra 出现 Using filesort 或 Using temporary，大概率是排序/分组没利用上索引，得检查联合索引覆盖是否完整

真实调试时，用 EXPLAIN format=jsON 能看到更细的决策依据，比如 used_columns 和 range_analysis。

索引不是建了就完事，尤其是线上大表，加索引前先用 pt-online-schema-change 测一遍影响，别让 ALTER TABLE 把主库夯住。