MySQL如何避免全表扫描

MySQL全表扫描在大多数场景下性能都是非常低下的，尤其在表数据量特别大的情况下，全表扫描会耗尽数据库资源，严重时会导致数据库服务不可用，数据库崩溃，因此，业务SQL在发布上线前，应当检查是否会导致全表扫描。

1. MySQL全表扫描的原因

通过对SQL执行explain，在输出信息中如果type字段为All，那么这个SQL就是走的全表扫描。全表扫描通常由以下原因产生：

1. 表非常小，全表扫描比走索引有更好的性能。通常这样的表记录数小于10，并且每行记录的数据量也不大。
2. 在where或者on子句上涉及到的字段，没有可用的索引。
3. 通过索引字段与常数值进行条件匹配，MySQL优化器基于索引计算出扫描的记录数太多，超过全表记录的30%，优化器认为全表扫描性能将比走索引更好。
4. 在一个区分度不好的字段上建索引，比如性别，查询男性或者女性，即使在性别字段上建索引，优化器认为走索引性能并不如全表扫描好。

对于记录数比较小的表，全表扫描并不会对性能产生太大的影响，有时候反而会提高性能。但是随着数据量的增加，全表扫描会越来越慢，因此应当尽可能的避免全表扫描。

2. MySQL如何避免全表扫描

有一些方式可以帮助优化器避免使用全表扫描，如下：

1. 在where条件或者join的连接字段上添加合适的索引，大多数全表扫描是由于忘了加索引导致。
2. ANALYZE TABLE tbl_name，更新索引分布统计信息，帮助优化器更准确地评估执行成本，产生更优的执行计划。
3. FORCE INDEX，让MySQL优化器强制走某个索引。
4. 设置参数--max-seeks-for-key=1000 或者 SET max_seeks_for_key=1000，这个值设置的越小，MySQL优化器越倾向于走索引，而不是全表扫描。

参考资料：

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