Mysql優化（出自官方文件） - 第五篇

目錄

• Mysql優化（出自官方文件） - 第五篇
  • 1 GROUP BY Optimization
  • 2 DISTINCT Optimization
  • 3 LIMIT Query Optimization
  • 4 Function Call Optimization
  • 5 Avoiding Full Table Scans

Mysql優化（出自官方文件） - 第五篇

1 GROUP BY Optimization

通常來講，實現group by的方式是建立一個臨時表，然後按照group by的列插入到臨時表中，在進行後續處理，但是如果group by的列均來自於同一個index（唯一或者二級索引），那麼Mysql會使用index來進行group by處理。關於索引的使用方式，主要有兩種：

1. Loose Index Scan：將group by操作和所有的range一起來進行操作。
2. Tight Index Scan：首先進行range scan，然後在對獲取到的結果進行分組。

• Loose Index Scan

  定義：不需要掃描group by的所有列來滿足where條件，只需要考慮索引中的一部分來滿足要求。Loose Index Scan需要滿足下面的條件：

  • 只針對一個表
  • group by的列必須是索引leftmost的列（如果沒有group by，distrinct也可以，且distrinct的列也必須為leftmost），比如，在一個表(t1,t2,t3,t4)上有索引(t1,t2,t3)，此時group by的列為(t1,t2)或者(t1)，均適用於Loose Index Scan，如果是(t1,t3,t4)，那麼將無法使用該優化技術。
  • 只支援MIN,MAX聚合函式，並且作用的物件都必須為同一個列，該列必須在索引裡面且在group by語句中。
  • select中不在group by的列比較物件必須為常量，包括MIN和MAX
  • 索引中的列必須完全被索引，比如：一個c1 varchar(20)，索引不能為c1(10)，必須為整個長度。

  在EXPLAIN輸出裡面，如果採用了這種優化技術，那麼會顯示Using index for group-by

  下面的語句均使用這種優化技術（假設t1表(t1,t2,t3,t4)，有索引(t1,t2,t3)）：

  SELECT c1, c2 FROM t1 GROUP BY c1, c2;
  SELECT DISTINCT c1, c2 FROM t1;
  SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
  SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
  SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
  SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
  SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

  下面的語句不適用這種情況：

  • 聚合函式只支援MIN和MAX：

  SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

  • group by的列必須為leftmost:

    SELECT c1, c2 FROM t1 GROUP BY c2, c3;

  • select中剩餘的列（沒有被group by包含的）必須和一個常量進行比較，下面的c3不滿足：

    SELECT c1, c3 FROM t1 GROUP BY c1, c2;

  除了MIN和MAX外，Loose Index Scan也可以作用於其他形式的聚合函式，但是有如下限制：

  • AVG, SUM和COUNT均可以支援，但是AVG, SUM必須只有一個引數，COUNT可以有多個引數
  • 不能有GROUP BY 和 DISTINCT語句
  • 文章開頭的限制依舊適用於這種情況。

  假設t1表(t1,t2,t3,t4)有索引(t1,t2,t3)，下面的語句也可以使用與Loose Index Scan：

  SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
  SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

• Tight Index Scan
  當Loose Index Scan無法適用時，此時如果適用於Tight Index Scan，Mysql依舊不會去建立一個臨時表，Tight Index Scan的定義為：如果有where條件，那麼會根據索引範圍直接進行掃描（index Scan），反之，會進行一個Full Index Scan。對於下面的語句，不適用於Loose Index Scan，但是依舊可以採用Tight Index Scan來進行分組：

  依舊假設t1表(t1,t2,t3,t4)有索引(t1,t2,t3)：

  • GROUP BY的列不是leftmost，但是進行Full Index Scan後，在對其進行過濾。

  SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
  SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

2 DISTINCT Optimization

由於DISTINCT和GROUP BY是可以相互轉換的，因此，適用於GROUP BY的情況也同樣適用於DISTINCT，比如下面的語句是等價的：

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;

3 LIMIT Query Optimization

大多數時候，Mysql也會對LIMIT row_count語句（沒有HAVING）進行優化，大致總結如下：

• 當LIMIT只有小部分行時，Mysql會選擇用full table scan，而不是使用索引

• Mysql掃描到LIMIT限定的行數後就會停止掃描，如果有order by就會停止排序，如果有distinct也會停止掃描，此時，Mysql可能只對表的大部分行進行了排序，所以會導致一個結果：帶LIMIT和不帶LIMIT的order by的結果有機率不一致，該點下面會進行解釋。

• Mysql預設不會儲存已經查詢出來的結果，使用SQL_CALC_FOUND_ROWS，這樣子可以通過 SELECT FOUND_ROWS()來重複獲取計算出來的結果。

• 如果需要使用臨時表，Mysql也會利用row_count來計算所需要的空間。

• 如果使用了LIMIT，那麼優化器也有可能避免使用filesort，而是使用記憶體方式進行排序。

關於為什麼帶LIMIT和不帶LIMIT可能結果不一樣的原因：

當order by的列有多個重複列的時候，如果帶LIMIT，Mysql會進行部分排序，如果部分排序後發現數目已經滿足LIMIT的row_count，那麼就會停止排序，並將結果直接返回，而這個時候，有些資料還沒有得到排序，所以這就導致了全排序（不帶LIMIT）和帶LIMIT的結果不一致，主要體現在其他列上面，來看下面的例子：

order by的是category列，表的資料如下:

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

限制LIMIT 5的結果可能為：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

注意結果的第三行和第四行，和初始是不一樣的，因為此時Mysql可能只進行了部分排序，所以有機率出現這樣的結果。Mysql在實現排序的時候，只會保證order by列的順序，並不會保證其他列的順序。

4 Function Call Optimization

在Mysql裡面，Function被分為兩種：確定性函式和非確定性函式。

確定性函式：給定一個值，多次呼叫會確定性的返回另外一個值

非確定性函式：給定一個值，多次呼叫可能返回不同的值，比如：RAND(), UUID()

假設有下面的表：

CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));

對於下面的兩種查詢：

SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);

解釋如下：

對於第一條語句：由於POW產生一個固定的結果，所以Mysql會將其視為一個常量進行優化，有可能使用索引查詢來快速找到對應記錄。

對於第二條語句：由於RAND會產生不同的結果，所以Mysql會對錶t的每一行進行where判斷，相應的，就會進行全表掃描，因為需要RAND來產生不同的結果。

綜上，如果貿然使用非確定性的函式，對Mysql效能可能會產生隱性的不良影響，可能帶來的影響有下面幾種情況：

• 非確定性函式由於無法產生固定的結果，所以優化器就沒辦法對其優化，比如：如果是常量，可以使用index lookup，但是由於非確定性函式的存在，就只能用table scan的方式了。
• InnoDB中，非確定性函式可能會導致鎖由單行鎖升級為range-key lock。
• 在複製操作中，update中有非確定性函式可能是不安全的。

如果不得不用確定性函式，可以使用下面的方式進行優化：

• 將RAND的結果首先賦值給一個常量，然後查詢語句中直接使用該常量，這樣，Mysql就會把where語句後面的值視為常量來進行優化，比如此時可以使用index lookup。

  SET @keyval = FLOOR(1 + RAND() * 49);
  UPDATE t SET col_a = some_expr WHERE id = @keyval;

• 將非確定性函式的值放在derived table裡面，然後在where語句裡面直接使用derived table裡面的值，Mysql也可以使用相應的優化措施。

  UPDATE /*+ NO_MERGE(dt) */ t, (SELECT FLOOR(1 + RAND() * 49) AS r) AS dt
  SET col_a = some_expr WHERE id = dt.r;

• 有的時候，如果可以確定部分條件，那麼用and連線起來非確定性函式，這樣子可以導致非確定性函式的執行次數大大減少，比如下面的語句。

  SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

5 Avoiding Full Table Scans

在EXPLAIN的輸出結果裡面，如果column列顯示為ALL，那麼說明Mysql本次在進行全表掃描，為了避免全表掃描，而是讓Mysql使用索引，可用下面的方式：

• 使用ANALYZE TABLE tbl_name來更新表索引的分佈性。

• 使用FORCE INDEX命令告訴Mysql table scan的效率要遠低於使用索引。

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

• 在啟動Mysql的時候，mysqld命令加上 --max-seeks-for-key=1000 或者說使用 SET max_seeks_for_key=1000， 該值設定的越低，那麼Mysql就更傾向於使用索引。