是否应为device_data表的key字段添加索引以优化存储与性能？

存储消耗相关 #

• 单独给key字段加索引不会减少表本身的存储占用。表的存储是实际数据的存储，key字段本身是varchar类型，索引是额外的磁盘结构，只会增加总存储量，反而让消耗更大。
• 你提到key重复率极高，想要减少存储的话，更合适的是做维度映射：新建一个data_keys表，把所有唯一的key值存进去，用一个 tinyint 或 smallint 类型的主键（比如key_id）来关联device_data表。这样device_data里存的是小整数，代替原来的varchar(60)，能大幅压缩存储，而且新增key只需要往data_keys里插数据就行，不用改表结构，完全适配你系统动态变更的需求。

索引对性能的影响 #

• 你说99%的查询都是先通过device_id过滤，那单独给key加索引不仅没用，反而有害：
  • 查询时先过滤device_id后，结果集已经很小，直接遍历这个小结果集比走索引更快，单独的key索引只会带来额外的维护成本——比如插入、更新、删除记录时，数据库要同步维护这个索引，拖慢写操作。
  • 你已经有了unique_device_data_key这个联合唯一索引（device_id, key），如果你的查询是WHERE device_id = ? AND key = ?，这个联合索引已经能高效命中，完全不需要单独给key加索引。
• 只有当存在不指定device_id，只按key过滤的查询时，单独的key索引才有用，但你这种场景占比极低，完全没必要为了1%的场景牺牲99%的性能。

具体建议 #

• 绝对不要单独给key字段加索引，既解决不了存储问题，还会增加维护成本。
• 要减少存储+保留动态性，推荐做维度表改造：
  1. 创建data_keys表：

     CREATE TABLE `data_keys` (
       `id` tinyint unsigned NOT NULL AUTO_INCREMENT,
       `key_name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL UNIQUE,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
     

  2. 修改device_data表：把key字段替换成key_id tinyint unsigned NOT NULL，添加外键关联data_keys.id，同时把原唯一索引调整为unique_device_data_key (device_id,key_id)。
  3. 迁移数据：先把device_data里所有唯一的key插入data_keys，再更新device_data的key_id对应的值。
• 这个改造后，联合索引device_id, key_id比原来的device_id, key更高效，因为整数类型的索引体积更小、查询速度更快，同时还大幅降低了主表的存储消耗。

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