MySQL - 为什么不索引每个字段？ [英] MySQL - why not index every field?

问题描述

最近我学会了索引的奇迹，并且性能得到了显着提升。然而，凭借我所学到的一切，我似乎无法找到这个问题的答案。

Recently I've learned the wonder of indexes, and performance has improved dramatically. However, with all I've learned, I can't seem to find the answer to this question.

索引很棒，但为什么不能有人只是索引所有字段使表格快速？我敢肯定有一个很好的理由不这样做，但三十字段表中的三个字段怎么样？ 30场30？应该在哪里画线，为什么？

Indexes are great, but why couldn't someone just index all fields to make the table incredibly fast? I'm sure there's a good reason to not do this, but how about three fields in a thirty-field table? 10 in a 30 field? Where should one draw the line, and why?

推荐答案

索引占用内存空间（RAM）;索引太多或太大，数据库必须将它们交换到磁盘或从磁盘交换。它们还会增加插入和删除时间（必须为插入/删除/更新的每个数据更新每个索引）。

Indexes take up space in memory (RAM); Too many or too large of indexes and the DB is going to have to be swapping them to and from the disk. They also increase insert and delete time (each index must be updated for every piece of data inserted/deleted/updated).

你没有无限的记忆。这样所有索引都适合RAM =好。

You don't have infinite memory. Making it so all indexes fit in RAM = good.

你没有无限的时间。仅索引您需要索引的列可以最小化插入/删除/更新性能命中。

You don't have infinite time. Indexing only the columns you need indexed minimizes the insert/delete/update performance hit.

这篇关于MySQL - 为什么不索引每个字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！