Mysql索引设计原则

1. 优先给高频查询条件建索引WHERE / JOIN / ORDER BY / GROUP BYWHERE避免全表扫描typeALL快速过滤数据JOIN 关联字段两张表关联条件必须建索引否则联表笛卡尔积爆炸ORDER BY索引天然有序消除Using filesort文件排序GROUP BY索引相同值连续存放消除Using temporary临时表分组2.针对数据量较大查询频繁的表建立索引小表几十 / 几百条不用索引数据库全表扫描更快只有数据多、经常查的表索引才有优 化价值。3. 选区分度高的列优先唯一索引区分度定义字段不重复值越多区分度越高高区分度学号 stu_no、class_id每条数据值不一样索引一查就能精准定位低区分度gender 性别只有 1/2筛选后还是一半数据索引几乎没用。4. 长字符串建前缀索引场景VARCHAR (200)、TEXT 超长文本完整建索引会占用大量磁盘 / 内存效率低。原理只截取字符串前 N 位建立索引足够区分数据即可。限制前缀索引不能用于 order by、group by只能用于等值 / 前缀模糊匹配like 张%。5. 优先联合索引少单列索引可做覆盖索引避免回表两层含义合并多个单列索引为 1 个联合索引减少索引总数节省磁盘设计覆盖索引索引包含 SQL 全部查询字段不用回主键表查数据Extra 出现Using index性能大幅提升。举例频繁执行select class_id,stu_name from student where class_id1不推荐单独建idx_class、idx_name两个单列索引 推荐建联合索引idx_class_name(class_id,stu_name)一条索引覆盖查询无需回表。6. 控制索引数量索引越多增删改越慢底层原理InnoDB 增、删、改数据时所有关联索引的 B 树都要同步更新。 一张表 5 个索引插入一条数据就要修改 5 棵 B 树IO 开销成倍上涨。规范单表索引建议不超过 5 个及时删除冗余索引已有idx(a,b)就不用单独建idx(a)。7. 索引字段尽量加 NOT NULL 约束原因NULL 会占用额外标识字节增大索引存储开销MySQL 优化器无法精准判断 NULL 值分布容易选错索引!、is null、not in会导致索引失效非空字段能规避这类问题。举例stu_no、class_id、stu_name 业务上一定有值建表时直接加NOT NULL不要允许为空。整体总结记忆大表高频查才建索引查询、排序、分组字段优先索引选值不重复的高区分度字段长字符串用前缀索引多用联合索引做覆盖索引少建单列索引不能太多拖累写入索引字段尽量非空方便优化器选择索引。