全选时昂贵的全表扫描 [英] Expensive full table scan on select all without condition

问题描述

我有一个具有210列的宽表(这可能是一个不好的结构，但是每次都需要所有数据).主键有一个主类型索引.

I have a wide table with 210 columns (This might be a bad structure but all data is needed every time). There is a primary type index for the primary key.

现在，当我从单表中选择*时，没有任何条件.这样会进行全表扫描.

Now when I do select * from my single table without any condition. It results in a full table scan.

它表示以下内容: 找不到该表的可用索引

这也意味着搜索范围如此之广，以至于索引无用.

This also means the search range is so broad that the index is useless.

如何避免这种全表扫描?

What could I do to avoid this full table scan?

注意:每次我都需要所有信息，因此破坏表将导致性能降低..！

Note: I need all the information every time so breaking the table will result in less performance..!

我是MySQL的新手.因此，将不胜感激.谢谢..！

I am new to MySQL. So help would be appreciated. Thanks..!

推荐答案

有关更多详细信息，请参见下面的链接

Refer below link for more details

https://dev.mysql.com /doc/refman/8.0/en/table-scan-avoidance.html

下面是全表扫描的原因

• 表是如此之小，以至于执行表扫描要比打扰键查找要快得多.对于少于10行且行长较短的表，这很常见.

• The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length.

在ON或WHERE子句中，对于索引列没有可用的限制.

There are no usable restrictions in the ON or WHERE clause for indexed columns.

您正在将索引列与常量值进行比较，MySQL计算得出(基于索引树)，常量覆盖了表的太大部分，并且表扫描会更快

You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster

您正在通过另一列使用基数较低的键(许多行与键值匹配).在这种情况下，MySQL假定通过使用键，它可能会执行许多键查找，并且表扫描会更快.

You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.

为避免全表扫描，请在下面使用:

to avoid full table scan use below:

• 使用ANALYZE TABLE tbl_name更新扫描表的密钥分布.

• 对扫描的表使用FORCE INDEX告诉MySQL与使用给定索引相比，表扫描非常昂贵:

• Use ANALYZE TABLE tbl_name to update the key distributions for the scanned table.

• Use FORCE INDEX for the scanned table to tell MySQL that table scans are very expensive compared to using the given index:

例如SELECT * FROM t1，t2 FORCE INDEX(index_for_column) 在哪里t1.col_name = t2.col_name;

e.g. SELECT * FROM t1, t2 FORCE INDEX (index_for_column) WHERE t1.col_name=t2.col_name;

这篇关于全选时昂贵的全表扫描的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！