MariaDB亿级用户表索引选型及username设主键相关技术问询

首先明确两个前提：

• MariaDB默认使用InnoDB存储引擎（除非主动切换为MyISAM，后者不适合2亿行规模的大表），主键默认是聚簇索引，叶子节点直接存储整行数据。
• 主键必须满足非空、全局唯一的硬性要求，若业务中username可能为空或重复，根本不能设为主键。

基于上述前提，分两种场景判断最优方案：

• 若username长度≤16字节（比如强制要求的短用户名、手机号）：直接将username设为主键完全可行，且是当前单一路径查询下的最优解——所有WHERE username=?的查询直接命中聚簇索引，无需回表，性能最高。
• 若username长度>32字节（比如邮箱、自定义昵称等允许长字符的场景）：不建议直接设为主键，更优方案是：
  • 用BIGINT UNSIGNED AUTO_INCREMENT作为聚簇主键（短、有序，所有二级索引的存储开销最小）
  • 在username上建唯一覆盖索引：UNIQUE INDEX idx_username_lastname (username, lastname)
    这个方案下，查询会直接命中二级覆盖索引，不需要回表取lastname，性能和走username主键几乎一致，但整个表的磁盘占用、缓存命中率会比长字符串主键高30%以上。

注意：如果只在username上建普通唯一索引、不把lastname加入索引，查询时会需要回表读取聚簇索引拿lastname，多一次随机IO，性能会下降很多，这一步是新手最容易漏的。

针对2亿行规模的大表，以及首次接触超20万行表的情况，必须注意这些配置：

• 首先调整InnoDB核心参数：专用数据库服务器需将innodb_buffer_pool_size设置为可用内存的50%-70%，确保热数据和索引能尽可能放入内存，否则哪怕索引建对了，查询也会因为磁盘IO过慢无法使用。默认的buffer_pool配置（通常128M）完全无法支撑2亿行表的查询。
• 建索引/主键时优先选择业务低峰期操作：MariaDB 10.3及以上版本支持在线DDL，可通过添加ALGORITHM=INPLACE, LOCK=NONE参数避免锁全表读，但操作过程会占用大量IO/CPU，仍会影响业务性能。
• 如果是新建表/导入数据阶段，先建主键/覆盖索引再导入数据，比导完2亿行再加索引的速度快3-5倍。
• 优化username字段类型：如果用户名只允许数字、字母、符号，优先使用ascii字符集，不要用默认的utf8mb4，可直接将索引体积缩小75%。
• 所有大表操作必须先在测试环境用全量备份验证流程，不要直接在生产环境操作。

耗时没有固定值，完全取决于硬件配置，以下为常见场景的参考范围（基于2亿行、每行平均100字节、总表大小约20G的场景）：

• 空表先建索引再导入数据：普通SATA SSD下耗时2-4小时，NVMe SSD下耗时0.5-1.5小时。
• 现有2亿行在线加主键/索引：普通SATA SSD下耗时4-8小时，NVMe SSD下耗时1-2小时；若使用机械硬盘，耗时会超过24小时，且会严重影响业务，不建议尝试。
• 若username为长字符串（≥64字节），构建耗时会比短字段高30%-50%。

内容的提问来源于stack exchange，提问作者Champn