【MySQL建索引】

一、哪些列可以建索引

1. 能建立索引的字段类型

1. 整数：tinyint / smallint / int / bigint
2. 小数：decimal
3. 字符串：char、varchar、text（text只能前缀索引）
4. 时间：date / time / datetime / timestamp
5. 枚举 enum

不能做主键/普通索引的：
blob、longtext 完整字段不能直接建索引，只能截取一小段前缀。

2. 建索引的硬性规则

1. 主键索引 PRIMARY KEY
   • 字段 NOT NULL（不能为空）
   • 整列数据不能重复
   • 一张表只能1个主键
2. 普通索引 KEY / INDEX（你写的 KEY cid）
   • 字段允许 NULL、允许重复
   • 一张表可以建几十条普通索引，无数量硬性上限（太多会拖慢插入更新）
3. 唯一索引 UNIQUE KEY
   • 数据不能重复，但可以存1个NULL（多个NULL会冲突）

3. 外键专属要求

如果这个字段要当外键：

• 数据类型、字符集、长度，必须和关联表主键完全一致
• 必须给该字段建立普通索引（KEY），否则创建外键报错1215

二、索引有两大核心作用：查询加速 + 排序分组优化

1. 快速查找 WHERE 条件

SELECT * FROM book WHERE cid = '1';

cid 有索引：直接定位符合条件数据，不用全表扫描。

2. 优化 ORDER BY 排序

可以排序，而且索引能避免文件排序，速度极快
例：

SELECT * FROM book ORDER BY cid;

cid 建有索引 → MySQL 直接按索引有序读取，不用事后再排序；
无索引：查到所有数据后，内存/磁盘重新排序，数据量大很慢。

3. 优化 GROUP BY、DISTINCT 分组去重

根据索引字段分组，不用临时表计算。

4. 关联表 JOIN 查询提速（外键最常用场景）

SELECT * FROM book b
LEFT JOIN category c ON b.cid = c.cid;

book.cid 有索引，两表匹配速度大幅提升，这也是外键强制要求建索引的根本原因。

三、索引不能干什么（避坑）

1. 不能加速 !=、NOT LIKE、%开头模糊查询
WHERE cid != '1' / WHERE bname LIKE '%Java'，索引失效，走全表扫描
2. 索引会降低插入 INSERT、修改 UPDATE、删除 DELETE 速度
   改数据时，不仅要改表数据，还要同步更新所有索引，索引越多写入越慢
3. 字段函数运算会让索引失效

   -- 索引失效，不走KEY
   SELECT * FROM book WHERE LEFT(cid,1)='1';
   

四、哪些列适合建索引（开发经验）

1. 经常出现在 WHERE 的字段（cid、uid、oid）
2. 经常用于 JOIN 关联的外键字段（必须建）
3. 经常 ORDER BY / GROUP BY 的字段
4. 主键、唯一标识（必建主键索引）

五、哪些列不要建索引

1. 重复度极高的字段（比如state只有0和1，建索引几乎无提升）
2. 很少查询、只做展示的字段（author、image、address）
3. 频繁修改更新的字段（每次更新都要维护索引）