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优化器越倾向于走索引,而不是全表扫描。

发表评论