表中的所有列都应该索引，还是不应该索引mysql数据库？

Question

我想做一个有4列的表格。一列是主键，另外三列是对其他表的列的引用。我想使用它来连接这四个表，以创建一个搜索过滤器。这些连接需要时间。

我想我应该索引这些列，因为我读到在join条件中使用的列上添加索引会使它们运行得更快。我的问题是，如果表的所有列都被索引，会有问题吗？或者是否有任何其他方法来降低搜索过滤器的时间复杂度。提前谢谢。

更多提示:Table1(主搜索)-1000 entries primary_key fk1 fk2 fk3

表2-800条目pk1 ..(8-9列)

表3-700条pk2 ..(10-12列)

表2-850条pk3 ..(7-8列)

Answer 1

创建索引需要额外的磁盘空间，并且过多的索引可能会导致文件系统大小限制引起的问题，因此必须仔细考虑选择要索引的正确字段。

由于索引仅用于加快在记录中搜索匹配字段的速度，因此在执行插入或删除操作时，对仅用于输出的字段进行索引将只是浪费磁盘空间和处理时间，因此应该避免使用索引。此外，考虑到二进制搜索的性质，数据的基数或唯一性也很重要。对基数为2的字段进行索引会将数据一分为二，而基数为1,000的字段将返回大约1,000条记录。在基数如此低的情况下，效率将降低为线性排序，如果基数大于记录数的30%，查询优化器将避免使用索引，从而有效地使索引浪费空间。

所以最好是在一组列上添加索引。

Answer 2

要正确地索引数据以提高性能，您需要了解您的数据。假设我正在创建一个人口普查数据库表：

CREATE TABLE CENSUS
(
    ID INTEGER NOT NULL,
    GENDER CHAR(1) NOT NULL,
    FAVOURITEFOOD NVARCHAR(20) NOT NULL,
    STATE NVARCHAR(20) NOT NULL
);

由于处理了这些数据，我可能知道：

加州的性别比例为50%男性，50%女性

• 60%喜欢烤肉，20%素食，20%喜欢亚洲食物，20%喜欢意大利面

• 州立大学是25%加州人，25%是纽约州人，剩下的1%是每个州。

如果我想搜索喜欢烘焙和生活在加州的男性，我会考虑建立多列索引，将州放在第一位(州，性别，FAVOURITEFOOD)。我使FAVOURITEFOOD成为索引中的最后一列。这是因为状态筛选器将把数据砍掉25%，而FAVOURITEFOOD将返回数据库的大部分(并不比全表扫描好)。

如果我想搜索喜欢素食并生活在纽约的女性，我会考虑建立一个多列索引，并将FAVOURITEFOOD放在第一位(FAVOURITEFOOD，州，性别)。在这里，FAVOURITEFOOD将数据削减了20%，因此它是比其他两列更好的选择。

如果我经常同时运行这两个查询，我应该建立哪一个索引？答案是两者都有：

CREATE INDEX IX_CENSUS_001 ON CENSUS (STATE, GENDER, FAVOURITEFOOD);
CREATE INDEX IX_CENSUS_002 ON CENSUS (FAVOURITEFOOD, STATE, GENDER);
ANALYZE TABLE CENSUS;

ANALYZE TABLE命令存储表的键分布。现在，当您运行这两个查询中的任何一个时，它将确定IX_CENSUS_001或IX_CENSUS_002是执行计划的最佳索引。

如果我希望开始运行不同类型的查询，我会停下来重新考虑我的数据。我可能需要添加一个新索引，并且可能需要再次运行ANALYZE TABLE。

因此，回到您的场景；这取决于您的表中的数据以及您希望对其执行的查询。

Answer 3

索引不是灵丹妙药。

当然，它们可以加快查询速度，但也会减慢写入(插入/更新/删除)并占用宝贵的RAM。

小心使用它们。