【MySQL】效能最佳化之 覆蓋索引

yuntui發表於2016-11-03
MySql索引
   一個包含查詢所需的欄位的索引稱為 covering index 覆蓋索引。MySQL只需要透過索引就可以返回查詢所需要的資料，而不必在查到索引之後進行回表操作，減少IO，提供效率。
   當你對一個sql 使用explain statement 檢視一個sql的執行計劃時，在EXPLAIN的Extra列出現Using Index提示時，就說明該select查詢使用了覆蓋索引。

【使用場景】
生產過程中遇到的一個例子，且先不討論 count(欄位)還是 count(*)  
root@yang 06:38:34>select count(o.order_id) as cnt from `order` o  WHERE o.settle_type = 2   and o.order_status = 2  and o.buyer_id=1979459339672858;        
+------+
| cnt  |
+------+
| 7574 |
+------+
1 row in set (0.22 sec)

root@yang 06:36:38>explain select count(o.order_id) as cnt from `order` o  WHERE o.settle_type = 2   and o.order_status = 2  and o.buyer_id=1979459339672858; 
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type        | possible_keys         | key                   | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | o     | index_merge | buyer_id,order_status | buyer_id,order_status | 9,1     | NULL | 3852 | Using intersect(buyer_id,order_status); Using where |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

上述select語句的執行計劃使用了index_merge 索引聚合，整體sql執行花費0.22s 。根據查詢條件對錶結構進行如下調整：
root@yang 06:33:14>alter table `ordert` add key ind_od_bid_st_ty_oid(`buyer_id`,`order_status`,`settle_type`,`order_id`);
Query OK, 0 rows affected (3.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@yang 06:38:50>explain select count(o.order_id) as cnt from `ordert` o  WHERE o.settle_type = 2   and o.order_status = 2  and o.buyer_id=1979459339672858;
+----+-------------+-------+------+----------------------+----------------------+---------+-------------------+-------+--------------------------+
| id  | select_type | table | type | possible_keys        | key                  | key_len | ref               | rows  | Extra                    |
+----+-------------+-------+------+----------------------+----------------------+---------+-------------------+-------+--------------------------+
|  1 | SIMPLE      | o     | ref  | ind_od_bid_st_ty_oid | ind_od_bid_st_ty_oid | 11      | const,const,const | 15242 | Using where; Using index |
+----+-------------+-------+------+----------------------+----------------------+---------+-------------------+-------+--------------------------+
1 row in set (0.00 sec)
執行計劃使用了 using index --覆蓋索引而且執行時間由0.22s，下降到小於0.01s。
root@yang 06:39:06>select count(o.order_id) as cnt from `ordert` o  WHERE o.settle_type = 2   and o.order_status = 2  and o.buyer_id=1979459339672858;        
+------+
| cnt  |
+------+
| 7574 |
+------+
1 row in set (0.00 sec)

【覆蓋索引的限制】
遇到以下情況，執行計劃不會選擇覆蓋查詢：
1select選擇的欄位中含有不在索引中的欄位 ，也即索引沒有覆蓋全部的列。
2 where 條件中不能含有對索引進行like的操作。

root@odbsyunying 08:18:15>explain select count(*) as cnt from `ordert` o  WHERE o.settle_type = 2 
 > and o.order_status = 2  
 > and o.buyer_id like '1979459339672858' \G
*************************** 1. row ***************************
                id: 1
  select_type: SIMPLE
           table: o
           type: index
possible_keys: ind_od_bid_st_ty_oid
            key: ind_od_bid_st_ty_oid
      key_len: 19
             ref: NULL
            rows: 767466  ---覆蓋索引掃描
           Extra: Using where; Using index
1 row in set (0.00 sec)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30633755/viewspace-2127643/，如需轉載，請註明出處，否則將追究法律責任。

相關文章