mysql索引设计技巧

方才coding
24 阅读5分钟

本文转载自：fangcaicoding.cn/course/14/8…

Hello 我是方才，8人后端研发leader、6年后端经验、3年团队管理&架构经验。

专注于分享成体系的编程知识、职场经验、个人成长历程等！

文末，方才送你一份优质的技术资料，记得领取哟！

步骤4：索引的设计

关于索引，方才发现很多初中级程序员都没有形成一个方法论。很容易走两个极端，要么是除了主键没有其他任何索引，要么就是索引一大堆。

方才结合自己针对数十亿表的索引优化经验，总结如下：

image-20250120165626152

结合上面的脑图，针对kjkm_fse这个表，我们来一一分析下。

关于主键

方才建议所有的表均应该有主键，优先为数字类型，且保持自增性（若是辅助表，主键可以直接使用主表的）。

常用的主键生成机制有：

  • 数据库自增 auto_increment ；
  • 基于外部算法代码实现：比如雪花算法、百度Uid-Generator、美团Leaf等；

在kjkm_fse表是有自增id的，但因为数据量较大，使用了分区表（关于TiDB的分区表，大家若有兴趣，可以在评论区告诉方才哟），分区字段按规范需要作为主键的组合字段之一，所以该表的主键如下：

sql复制代码

-- 省略了无效内容
CREATE TABLE `kjkm_fse` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键'
  PRIMARY KEY (`id`,`zt_id`)
) PARTITION BY HASH (`zt_id`) PARTITIONS 3;

补充：如果一个表没有显示定义主键：

  • MySQL的 InnoDB 引擎会尝试使用第一个非空的唯一索引（NOT NULL 和 UNIQUE INDEX）作为聚簇索引。如果没有合适的唯一索引，InnoDB 会自动生成一个隐藏的 ROW_ID 列作为聚簇索引，这个隐藏列是递增的。
  • TiDB数据库，如果表没有主键，TiDB 会自动生成一个隐式的 _tidb_rowid 列作为行 ID。这个列的值是单调递增的。

索引创建依据

除了主键建议均有，其他索引的创建，是需要跟进实际情况进行判断的，方才总结规则如下：

  1. 前提：目标表的数据量级会超过10万；
  2. 对唯一性要求的字段；
  3. 必要的查询字段；
  4. 索引不是越多越好，过多的索引，会影响数据更新效率，同时会导致sql的自动优化出现非预期行为；
  5. 注意不是所有查询字段均需要：若可以通过其他必传查询字段，使得过滤后的数据量级小于10万，就可以不用创建；

唯一索引

方才建议，业务上有唯一特性的字段，必须建唯一索引或组合唯一索引：

理由：唯一键对 insert、update的性能损耗较小，对查询速度的提升是很明显的；同时根据墨菲定律，比如会产生脏数据

一定要注意：组合唯一键的所有字段均不能为空，否则可能导致唯一键约束失效。

  • 原因：根据MySQL官方文档，NULL表示“缺失的未知值”，它与任何其他值（包括另一个NULL值）进行比较时都不会返回真值。这种特性导致了NULL在唯一性约束中的特殊行为。
  • 效果直接看下面的示例截图就知道了，uk_typeId_mc2 是没有锁住的，数据1-3-8在逻辑上都是重复的：

image-20250122164336346

回到kjkm_fse这个表，在业务上，就要求一个账套下，一个会计期间，同一个科目的余额一定是只能有一条数据，所以就创建了组合唯一索引：

sql复制代码

UNIQUE KEY `uk_zt_kjqj_kjkm_id` (`zt_id`,`kjqj_id`,`kjkm_id`)

ps：业务上是有根据 kjkm_bm会计科目编码查询需求的，但这里方才并没有针对该字段创建索引，是因为什么呢？可以参考索引的创建依据说明，业务上明确所有的查询，一定都会携带zt_id账套id参数，根据该参数，已经可以将数据量过滤至5000以内了，就没有必要再创建了。

组合索引

关于组合索引，方才就提两个技巧：

  • 尽量将区分度高的字段放在前面（后续分享了B+树索引后，就可以理解了）；
  • 高频查询语句，可创建组合索引，利用索引覆盖机制优化sql性能。

普通索引

关于普通索引的创建，就优先参考上面的内容，有必要再创建。

需要注意的是，参考阿里规约，在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。（防止字段太长，索引内容过大，导致其他问题）。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

小结

我相信通过这个示例，大家对如何进行表结构设计，都有了自己的理解，也欢迎在评论区交流哟。

原创不易，如果对你有所帮助，记得帮方才点个赞、点个爱心，来个评论，这对方才很重要。

ps：本文的思维导图xmind源文件，都在资料库了，可以直接扫码领取。

avatar
文章
阅读
粉丝