mysql索引的创建与使用时的存储引擎选择

InnoDB 是 MySQL 默认且更安全的存储引擎，支持事务、外键、聚簇索引和 FULLTEXT（5.6+），而 MyISAM 仅支持全文索引但不支持事务和行级锁，且自 8.0 起已被弃用。

MyISAM 和 InnoDB 对索引的支持差异

MySQL 的索引行为高度依赖存储引擎，不是所有索引类型都能在所有引擎上使用。MyISAM 支持全文索引（FULLTEXT），但不支持事务和行级锁；InnoDB 支持事务、外键和聚簇索引，但直到 MySQL 5.6 才原生支持 FULLTEXT，且语法和分词行为与 MyISAM 不同。

如果你需要 ORDER BY + LIMIT 高效分页、范围查询或高并发写入，InnoDB 是默认且更安全的选择。强行在 MyISAM 上建 UNIQUE 索引并频繁更新，容易因表级锁导致阻塞。

• InnoDB 的主键索引即聚簇索引，数据按主键物理排序，因此主键不宜过长（如用 VARCHAR(255) 做主键会显著放大二级索引体积）
• MyISAM 的索引是独立于数据的 B+ 树文件（.MYI），主键和普通索引结构一致，都是非聚簇的
• 从 MySQL 8.0 开始，MyISAM 已被标记为“deprecated”，新项目应避免使用

创建索引时必须显式指定 ENGINE，否则继承表默认引擎

执行 CREATE INDEX 本身不涉及引擎选择——它只作用于已有表，而该表的引擎早已确定。真正影响索引能力的是建表时的 ENGINE 子句。常见错误是：先用 MyISAM 建表，后期想加 FOREIGN KEY，却发现语法报错 ERROR 1005 (HY000): Can't create table，因为 MyISAM 根本不支持外键约束。

建表时务必显式声明引擎：

CREATE TABLE orders (id BIGINT PRIMARY KEY,   user_id INT NOT NULL,   status TINYINT DEFAULT 0,   created_at DATETIME) ENGINE = InnoDB;

• 省略 ENGINE 时，采用 MySQL 配置中的 default_storage_engine（5.7 默认 InnoDB，但某些旧部署可能仍是 MyISAM）
• 用 ALTER TABLE …… ENGINE = InnoDB 转换引擎可行，但大表会锁表并重建全部索引，线上慎用
• SHOW CREATE TABLE orders 可确认当前引擎，别只看 SHOW TABLE STATUS ——后者字段名不统一（5.7 是 Engine，8.0 是 ENGINE）

全文索引在 InnoDB 中的创建和查询限制

虽然 InnoDB 支持 FULLTEXT，但它对字段类型、最小词长、停用词处理比 MyISAM 更严格。例如，默认 innodb_ft_min_token_size = 3，意味着单 字节 中文或长度为 2 的英文缩写（如“AI”）无法被索引。

启用前需检查配置：

SELECT @@innodb_ft_min_token_size, @@ft_min_word_len;

• InnoDB 全文索引只能建在 CHAR/VARCHAR/TEXT 列上，且要求列字符集为 utf8mb4（utf8 在 MySQL 中实际是 utf8mb3，不支持完整 Unicode）
• 查询必须用 MATCH(col) AGAINST('xxx' IN NATURAL LANGUAGE MODE)，不能用 LIKE '%xxx%' 或普通 =，否则无法走全文索引
• IN BOOLEAN MODE 支持 +/-/* 语法，但不支持中文分词——需搭配 ngram 插件，且需在建表前设置 innodb_ft_parser = 'ngram'

索引失效常因引擎特性被忽略

一个典型场景：对 InnoDB 表的 TEXT 列加前缀索引（INDEX idx_content (content(100))），查询 WHERE content LIKE 'hello%' 能命中；但若换成 MyISAM，同样语句在某些版本中可能因统计信息不准导致优化器放弃使用索引。

• InnoDB 的索引统计是动态采样的（innodb_stats_auto_recalc = ON），而 MyISAM 是静态的，ANALYZE TABLE 后才更新，容易误判索引有效性
• ORDER BY 多列时，若排序方向不一致（如 ORDER BY a ASC, b DESC），InnoDB 8.0+ 才支持该组合的索引利用，老版本直接退化为 filesort
• 联合索引最左前缀原则在两个引擎中都生效，但 InnoDB 对覆盖索引（Using index）更积极，EXPLAIN 中看到 Extra: Using index 才算真正避免回表

引擎选错，索引建得再全也白搭。尤其当业务开始用到事务、软删除、乐观锁或 JSON 字段时，InnoDB 的底层机制决定了它是唯一能稳住的选项。