列数会影响MYSQL速度吗? [英] Does number of columns affect MYSQL speed?

问题描述

我有一张桌子.我只需要运行一种类型的查询:在第1列中找到给定的唯一性，然后说出前3列.

I have a table. I only need to run one type of query: to find a given unique in column 1, then get say, the first 3 columns out.

现在，如果我在表中增加一些基本用于数据存储"的列，它将对速度有多大影响.我知道我应该使用一个saparate表，但是假设我只限于1个表，因此唯一的方法是在末尾添加一些列.

now, how much would it affect speed if I added an extra few columns to the table for basically "data storage". I know I should use a saparate table, but lets assume I am constrained to having just 1 table, so the only way is to add on some columns at the end.

因此，如果我添加一些列，例如末尾添加10个列，每个列添加30个varchar，这会减慢第一句中给出的任何查询吗?如果是这样，那么与没有多余的冗余列(目前没有列)相比，您认为有多少因素呢?

So, if I add on some columns, say 10 at the end, 30 varchar each, will this slow down any query given in the first sentence? If so, by how much of a factor do you think compared to without the extra reduntant yet present columns?

推荐答案

是的，额外的数据可能会减慢查询速度，因为这意味着页面中可以容纳更少的行，这意味着更多的磁盘访问权限可以读取一定数量的行，并且可以将更少的行缓存在内存中.

Yes, extra data can slow down queries because it means fewer rows can fit into a page, and this means more disk accesses to read a certain number of rows and fewer rows can be cached in memory.

减速的确切因素很难预测.可以忽略不计，但是如果您是否能够在内存中缓存整个表之间处于界限附近，那么一些额外的列可能会对执行速度产生很大的影响.从内存或磁盘中的缓存中获取行所花费的时间差异为几个数量级.

The exact factor in slow down is hard to predict. It could be negligible, but if you are near the boundary between being able to cache the entire table in memory or not, a few extra columns could make a big difference to the execution speed. The difference in the time it takes to fetch a row from a cache in memory or from disk is several orders of magnitude.

如果添加覆盖索引，则额外的列的影响应较小，因为查询可以使用相对较窄的索引，而不必引用较宽的主表.

If you add a covering index the extra columns should have less of an impact as the query can use the relatively narrow index without needing to refer to the wider main table.

这篇关于列数会影响MYSQL速度吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！