mysql优化 - 关于 MySQL 分页查询的优化

Question

使用的 MySQL 官方示例表 sakila.film。

索引情况如下所示，均为 BTREE 索引：

需要对film 根据标题 title 排序后取某一页的数据，直接查询

EXPLAIN SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

可以看到，优化器执行了全表扫描，我想通过按照索引分页后回表的方式改写 SQL，所以重写 SQL 如下：

#按照索引分页后回表查询
EXPLAIN SELECT a.`film_id`,a.`description` FROM film a
INNER JOIN (SELECT film_id FROM film ORDER BY title LIMIT 50,5) b ON a.`film_id`=b.`film_id`;

但是在 id 为 2 的查询过程中，虽然 type 为 index，但是实际上仍然扫描了全表这是为什么呢？

Answer 1

事實上，你改寫成 INNER JOIN 後，性能已經改進了。

第一條查詢的 cost 為：

mysql> select film_id, description from film order by title limit 50,5;

mysql> show status like 'Last_query_cost';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 209.799000 |
+-----------------+------------+

第二條查詢的 cost 為：

mysql> select a.film_id, a.description from film a inner join (select film_id from film c order by title limit 50,5) b on a.film_id = b.film_id;

mysql> show status like 'Last_query_cost';                                                          +-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 0.000000 |
+-----------------+----------+

所以你的目的已經達成了。

改進的原因是，使用 INNER JOIN 後，覆蓋索引（covering index）發揮了作用，整個匹配過程只需要 film_id 和 title，而它們都有索引。 description 欄位只在配對完後才會去讀取，所以節省了大量 IO。

下面來簡單分析第二個查詢。

MySQL 執行JOIN 的演算法，本質上就是簡單的巢狀循環，最外層循環總是線性遍歷，不會用索引的（這裡是臨時表，也沒有索引），內層循環有索引則會使用索引。所以外層循環的大小，直接決定了 JOIN 的表現。

從explain 的結果來看，MySQL 把SELECT 子句得到的表b 放在了外層循環，這個表只有55 行記錄，所以MySQL 決定先從它開始，訪問類型為ALL，意即掃描全表，也就是55 行記錄。

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 55
        Extra: NULL

第二層循環是基於表 a，這裡只需要找 film_id，用上了主鍵索引，非常快。記住，配對之後才會有第三層循環。

*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: b.film_id
         rows: 1
        Extra: NULL

第三層循環是基於 SELECT 子句裡的表 film，匹配的是 title，也用上了索引，非常快。

*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: c
         type: index
possible_keys: NULL
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 989
        Extra: Using index

你問這裡為什麼還是掃描了全表？其實應該不是，如果要掃面全表，存取型別應該是 ALL 而不是 index。所以不用擔心。

你的這個最佳化寫法，剛好也出現在 High Performance MySQL 第六章，我剛好看到。

Answer 2

INNER JOIN用法就是把符合要求的元組取出來，一定要掃描全表。
id 為 2 的查詢過程中。 。 。為什麼沒有在後面加上and a.film_id=2；

Answer 3

你這樣寫有什麼用？