SQL優化之避免全表掃描

在mysql5.6官方文檔中關于full table scan的介紹如下：

An operation that requires reading the entire contents of a table, rather than just selected portions using an index. Typically performed either with small lookup tables, or in data warehousing situations with large tables where all available data is aggregated and analyzed. How frequently these operations occur, and the sizes of the tables relative to available memory, have implications for the algorithms used in query optimization and managing the buffer pool.
The purpose of indexes is to allow lookups for specific values or ranges of values within a large table, thus avoiding full table scans when practical.           

大意：

Full table scan是一種需要讀取表全部資料的操作，常見于讀取小表的全部内容，或者在數倉中查詢大表所有資料用以分析或聚合。

索引的目的是允許在一個大表中查找特定的值或值的範圍，進而在實際操作時避免全表掃描。

一、發生全表掃描的條件

在表資料非常大時，全表掃描往往是災難性的。當explain輸出的type列的值為ALL時，表示這一步發生了全表掃描操作。一般出現以下條件時會發生全表掃描：

1、表資料量很小，走索引查詢比全表掃描更麻煩；這對于少于10行且行長度較短的表來說很常見；

2、SQL的on子句或者where子句涉及到的列上沒有索引；

3、将索引列與常量值進行比較，并且MySQL已經計算（基于索引樹）常量覆寫了表中太大的一部分，而表掃描會更快。換言之，即便字段選擇性高且有索引，但要取出的資料行太多，也不會走索引，這個比例一般是20%-30%；

4、對于低選擇性的索引列，一個鍵值可能會比對N多行資料，這個時候也不走索引而發生全表掃描。

二、優化全表掃描

對于小資料量的表，表掃描通常是合适的。對于大資料量的表，可以嘗試以下技術來避免優化器發生全表掃描：

1、通過“ANALYZE TABLE tb_name”語句更新表上的索引統計資訊；對于innodb表和myisam表來說，執行該語句時會加表級S鎖；

2、使用 “FORCE INDEX”語句強制走索引，告訴優化器走該索引更合适；

3、修改max_seeks_for_key參數值，例如：SET max_seeks_for_key=1000。該參數的含義：根據鍵查找行時，限制假定的最大查找次數；通過調低該參數值，以使優化器更喜歡索引而不是表掃描。