MySQL-5.7-8.2.1.20 Avoiding Full Table Scans

The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query. This usually happens under the following conditions:

当使用全表扫描时EXPLAIN 的type字段显示ALL。通常发生于一下几种情况：

• 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.

• 表很小，全表扫描比纠结试用那个索引使更快，这对于行少于10行且行长度较短的表来说很常见。

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

• 在索引列的ON或WHERE子句中没有可用的限制条件。
• 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 is likely to perform many key lookups and that a table scan would be faster.

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

For small tables, a table scan often is appropriate and the performance impact is negligible. For large tables, try the following techniques to avoid having the optimizer incorrectly choose a table scan:

对于较小的表，表扫描通常是合适的，对性能的影响可以忽略不计。对于大型表，尝试以下技术，以避免优化器错误地选择表扫描:

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

使用ANALYZE TABLE tbl_name更新扫描表的键分布。

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

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

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

Start mysqld with the --max-seeks-for-key=1000 option or use SET max_seeks_for_key=1000 to tell the optimizer to assume that no key scan causes more than 1,000 key seeks.

使用——max- searches -for-key=1000选项启动mysqld，或者使用SET max_seeks_for_key=1000来告诉优化器假设键扫描不会导致超过1000个键查找