MySQL多列索引设置疑问：DataTables每列筛选场景是否需要全列加索引

要不要给6个列都建单独索引？ #

给所有列加索引不是最佳实践，也完全没必要，核心原因如下：

• 有额外存储开销：每新增一个索引，数据库都需要单独存储排序后的列数据，索引总量会随数据量上涨同步膨胀，占用额外磁盘空间。
• 拉低写入性能：每次执行INSERT/UPDATE/DELETE操作时，表关联的所有索引都需要同步更新，索引越多写入延迟越高，高频写入的业务表性能损耗会非常明显。
• 索引利用率极低：普通用户很少会触发所有列的单独筛选，很多冷门筛选列的索引可能长期处于闲置状态，属于无意义的资源浪费。

DataTables全列筛选场景的适配方案 #

结合你的使用场景，可以按以下规则配置索引：

• 优先构建联合索引：把用户最常筛选的2-3个高频字段放在联合索引的最左侧，覆盖80%以上的常用筛选请求即可，低频筛选字段不需要单独建索引。如果你的表数据量低于10万行，全表扫描的速度完全可以支撑前端筛选的响应要求，不需要额外加索引。
• 适配筛选匹配规则：如果你的DataTables筛选用的是前后模糊匹配LIKE '%xxx%'，普通B树索引会直接失效，这种场景要么调整为前缀匹配LIKE 'xxx%'适配索引，要么给需要模糊查询的字符串列单独建全文索引。
• 定期排查实际查询需求：可以通过数据库慢查询日志统计真实触发的筛选请求用到了哪些列，只给查询频率高、且无索引时查询速度慢的列加索引即可。

6列小表的折中方案 #

因为你的表只有6个列，规模偏小，可以根据读写特性做调整：

• 如果是只读或者写入频率极低的表（比如日更新的统计报表），可以给所有列加单独索引，牺牲少量写入性能换取全场景筛选的查询速度，这种场景下的开销完全可以接受。
• 如果是读写都比较频繁的业务表，最多建2个联合索引覆盖高频筛选场景即可，剩余低频筛选直接走全表扫描也不会有可感知的性能问题。

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