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

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/758322/viewspace-680921/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/758322/viewspace-680921/