表的所​​有列应该是索引还是不是mysql数据库？ [英] All columns of table should be indexed or not mysql database?

问题描述

我想制作一个包含4列的表格。一个是主键，其他三个列是对其他表的列的引用。我想用它来连接这四个表来制作搜索过滤器。这些连接需要时间。

I want to make one table with 4 columns. One is the primary key and the other three columns are references to columns of other tables. I want to use it to join these four tables to make a search filter. These joins are taking time.

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

I was thinking I should index these columns because I read that adding indexes on column used in join conditions [makes them run faster]. My question is will there be a problem if all columns of the table are indexed? Or is there any other way to decrease time complexity of the search filter. Thanks in advance.

更多提示：
表1（主要搜索）-1000个条目
primary_key
fk1
fk2
fk3

More Hint: Table1(main search)-1000 entries primary_key fk1 fk2 fk3

表2-800条目
pk1
..（8-9列）

Table2-800 entries pk1 ..(8-9 columns)

表3-700条目
pk2
..（10-12列）

Table3-700 entries pk2 ..(10-12 columns)

表2-850条目
pk3
..（7-8列）

Table2-850 entries pk3 ..(7-8 columns)

推荐答案

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

Creating an index requires additional disk space, and that too many indexes can cause issues arising from the file systems size limits, careful thought must be used to select the correct fields to index.

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

Since indexes are only used to speed up the searching for a matching field within the records, it stands to reason that indexing fields used only for output would be simply a waste of disk space and processing time when doing an insert or delete operation, and thus should be avoided. Also given the nature of a binary search, the cardinality or uniqueness of the data is important. Indexing on a field with a cardinality of 2 would split the data in half, whereas a cardinality of 1,000 would return approximately 1,000 records. With such a low cardinality the effectiveness is reduced to a linear sort, and the query optimizer will avoid using the index if the cardinality is greater than 30% of the record number, effectively making the index a waste of space.

最好在列组上添加索引。

So better to add indexing on group of columns.

这篇关于表的所​​有列应该是索引还是不是mysql数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！