MySQL 索引设计的原则

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花

MySQL索引是提升查询效率的核心手段，合理的索引设计能避免全表扫描、减少IO开销，而不合理的索引不仅无法优化性能，还会增加数据写入（插入、更新、删除）的成本，占用额外存储空间。设计索引需遵循“高效、实用、精简”的核心思路，具体原则如下：

一、优先为高频查询字段建立索引

索引的核心价值是加速查询，因此需优先为查询频率高、过滤性强的字段建立索引。比如用户表的user_id（主键，高频用于查询单个用户）、订单表的order_no（唯一索引，高频用于查询订单详情）、商品表的category_id（高频用于按分类筛选商品）。

反例：为低频查询的字段（如用户表的last_login_ip，仅偶尔统计时使用）建立索引，会浪费存储空间，且增加写入时的索引维护成本。

二、遵循“最左前缀原则”设计联合索引

当查询条件包含多个字段时，优先使用联合索引而非多个单列索引（联合索引可减少索引数量，降低维护成本），且需遵循“最左前缀原则”——联合索引的生效顺序由左至右，查询时需从最左侧字段开始匹配，否则索引会失效。

示例：若建立联合索引(a, b, c)，则查询条件包含a、a AND b、a AND b AND c时索引生效；仅包含b、c、b AND c时，索引不生效。

注意：联合索引的字段顺序需结合查询场景，将过滤性最强、查询频率最高的字段放在最左侧（比如查询频繁为WHERE a=? AND b=?，且a的区分度更高，则a放左侧）。

三、避免过度索引

索引并非越多越好，每增加一个索引，MySQL在执行插入、更新、删除操作时，都需要同步更新对应的索引结构，会显著降低写入性能；同时，过多的索引会占用大量存储空间，还可能导致MySQL优化器选择错误的索引（索引过多时，优化器判断成本升高）。

建议：单张表的索引数量控制在5-8个以内，删除无用、冗余的索引（如与联合索引最左前缀重复的单列索引，若有联合索引(a, b)，则单列索引a属于冗余索引）。

四、选择区分度高的字段作为索引

字段的区分度（ cardinality ）指字段中不同值的数量占比，区分度越高，索引的过滤效果越好，能快速定位到目标数据，减少扫描行数。

示例：用户表的user_id（唯一值，区分度100%）适合建立索引；而gender（仅男、女、未知三个值，区分度极低）不适合建立索引——即使建立索引，MySQL也可能选择全表扫描（因为过滤后的数据量依然很大，索引查询的开销高于全表扫描）。

注意：区分度的判断可通过SELECT COUNT(DISTINCT 字段名) / COUNT(*) FROM 表名计算，比值越接近1，区分度越高。

五、避免对索引字段进行函数/表达式操作

若查询条件中对索引字段进行函数、表达式或类型转换操作，会导致MySQL无法使用该索引（索引失效），进而触发全表扫描。

反例：SELECT * FROM user WHERE SUBSTR(username, 1, 3) = 'abc'（对索引字段username使用SUBSTR函数）、SELECT * FROM order WHERE create_time + INTERVAL 7 DAY > NOW()（对索引字段create_time进行表达式操作）。

正例：将函数操作转移到查询值上，如SELECT * FROM user WHERE username LIKE 'abc%'（模糊查询前缀匹配，索引生效）、SELECT * FROM order WHERE create_time > NOW() - INTERVAL 7 DAY。

六、优先使用覆盖索引，减少回表

覆盖索引是指索引中包含了查询所需的所有字段（即“查询字段 ≤ 索引字段”），此时MySQL无需通过索引定位到数据行后再去读取数据表（回表操作），直接从索引中即可获取所有所需数据，大幅提升查询效率。

示例：若建立索引(id, username, phone)，查询SELECT id, username FROM user WHERE id > 100时，无需回表，直接从索引中读取数据。

建议：结合高频查询的字段，设计包含查询所需字段的联合索引，避免“select *”（会导致无法使用覆盖索引，必须回表）。

七、考虑索引的维护成本，适配业务场景

索引的维护成本与数据写入频率正相关：写入频繁（如订单表、日志表）的表，应尽量减少索引数量（避免每次写入都同步更新多个索引）；读取频繁、写入较少（如商品表、字典表）的表，可适当增加索引，提升查询效率。

此外，对于大数据量的表，避免建立过长的索引（如 varchar(255) 字段），可通过截取字段前缀建立索引（如INDEX idx_name (name(10))），平衡索引效率和存储空间（前缀长度需根据字段区分度调整，避免过短导致区分度不足）。

八、主键索引优先选择自增整数型

MySQL的InnoDB引擎中，主键索引是聚簇索引（数据行与索引结构绑定），选择自增整数型（如INT、BIGINT）作为主键，有两个核心优势：

• 自增主键能保证新插入的数据行始终追加在索引末尾，避免索引页分裂（若主键为随机值，新数据会插入到索引中间，导致索引页拆分，增加IO开销）；
• 整数型索引的存储空间更小、查询效率更高（相比字符串主键，如UUID，整数型索引的比较和存储更高效）。

反例：使用UUID、随机字符串作为主键，会导致索引碎片化严重，写入和查询性能下降。

九、避免使用NULL值过多的字段建立索引

MySQL的索引不会存储NULL值（或仅存储NULL的标记），若字段中NULL值占比过高（如超过50%），索引的过滤效果会极差，MySQL可能直接放弃使用该索引，转而执行全表扫描。

建议：对于可能存在大量NULL值的字段，可先通过默认值（如空字符串、0）处理NULL值，再考虑是否建立索引。

总结：MySQL索引设计的核心是“平衡查询效率和维护成本”，需结合业务的查询场景、写入频率、数据量，优先保证高频查询的高效性，同时避免过度索引和无效索引，让索引真正成为提升数据库性能的工具。

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花