mysql的Covering Index

關於Covering Index，看到為了這個而改寫查詢，一開始不太適應，因為oracle裡本來的做法就是這樣的，可能是mysql的優化器區別吧：

如果你想利用Covering Index，那麼就要注意SELECT方式，只SElECT必要的欄位，千萬別SELECT *，因為我們不太可能把所有的欄位一起做索引。

如何才能確認查詢使用了Covering Index呢？很簡單，使用explain即可！只要在Extra裡出現Using index就說明使用的是Covering Index。

mysql> explain
    ->  SELECT *
    ->  FROM rental
    ->  ORDER BY rental_date desc LIMIT 10000, 10;
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | rental | ALL  | NULL          | NULL | NULL    | NULL | 16342 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.01 sec)

mysql>   EXPLAIN SELECT *
    ->  FROM rental
    ->  JOIN (
    ->  SELECT rental_id
    ->  FROM rental
    ->  ORDER BY rental_date desc LIMIT 10000, 10
    ->   ) AS t1 ON (t1.rental_id=rental.rental_id) ;
+----+-------------+------------+--------+---------------+-------------+---------+--------------+-------+-------------+
| id | select_type | table      | type   | possible_keys | key         | key_len | ref          | rows  | Extra       |
+----+-------------+------------+--------+---------------+-------------+---------+--------------+-------+-------------+
|  1 | PRIMARY     | | ALL    | NULL          | NULL        | NULL    | NULL         |    10 |             |
|  1 | PRIMARY     | rental     | eq_ref | PRIMARY       | PRIMARY     | 4       | t1.rental_id |     1 |             |
|  2 | DERIVED     | rental     | index  | NULL          | rental_date | 13      | NULL         | 10010 | Using index |
+----+-------------+------------+--------+---------------+-------------+---------+--------------+-------+-------------+
3 rows in set (0.01 sec)

Let’s see why this can happen, and how to rewrite the query to work around the
problem. We begin with the following query:
mysql> EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY'
-> AND title like '%APOLLO%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: products
type: ref
possible_keys: ACTOR,IX_PROD_ACTOR
key: ACTOR
key_len: 52
rows: 10
Extra: Using where
The index can’t cover this query for two reasons:
• No index covers the query, because we selected all columns from the table and
no index covers all columns. There’s still a shortcut MySQL could theoretically
use, though: the WHERE clause mentions only columns the index covers, so
MySQL could use the index to find the actor and check whether the title
matches, and only then read the full row.
• MySQL can’t perform. the LIKE operation in the index. This is a limitation of the
low-level storage engine API, which allows only simple comparisons in index
operations. MySQL can perform. prefix-match LIKE patterns in the index because
it can convert them to simple comparisons, but the leading wildcard in the query
makes it impossible for the storage engine to evaluate the match. Thus, the
MySQL server itself will have to fetch and match on the row’s values, not the
index’s values.
There’s a way to work around both problems with a combination of clever indexing
and query rewriting. We can extend the index to cover (artist, title, prod_id) and
rewrite the query as follows:
mysql> EXPLAIN SELECT *
-> FROM products
-> JOIN (
-> SELECT prod_id
-> FROM products
-> WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'
-> ) AS t1 ON (t1.prod_id=products.prod_id)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
...omitted...
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: products
...omitted...
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: products
type: ref
possible_keys: ACTOR,ACTOR_2,IX_PROD_ACTOR
key: ACTOR_2
key_len: 52
ref:
rows: 11
Extra: Using where; Using index