mysql组合索引与单列索引

心已赠人 2021-06-24 15:58 716阅读 0赞

1、组合索引能够避免回表查询：

假设有一张订单表(orders)，包含order\_id和product\_id二个字段。一共有31条数据。符合下面语句的数据有5条。执行下面的sql语句：

select product_id 
    from orders
    where order_id in (123, 312, 223, 132, 224);

这条语句要mysql去根据order\_id进行搜索，然后返回匹配记录中的product\_id。所以组合索引应该按照以下的顺序创建：

create index orderid_productid on orders(order_id, product_id)
    mysql> explain select product_id from orders where order_id in (123, 312, 223, 132, 224) \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: orders
             type: range
    possible_keys: orderid_productid
              key: orderid_productid
          key_len: 5
              ref: NULL
             rows: 5
            Extra: Using where; Using index
    1 row in set (0.00 sec)

可以看到，这个组合索引被用到了,扫描的范围也很小，只有5行。

为什么要创建组合索引呢？这么简单的情况直接创建一个order\_id的索引不就行了吗？如果只有一个order\_id索引，没什么问题，会用到这个索引，然后mysql要去磁盘上的表里面取到product\_id。

但是用组合索引的话，mysql可以完全从索引中取到product\_id，速度自然会快。

2、组合索最左匹配原则：

组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。如果有一个组合索引(col\_a,col\_b,col\_c)

下面的情况都会用到这个索引：

col_a = "some value";
    col_a = "some value" and col_b = "some value";
    col_a = "some value" and col_b = "some value" and col_c = "some value";
    col_b = "some value" and col_a = "some value" and col_c = "some value";

对于最后一条语句，mysql会自动优化成第三条的样子~~。

下面的情况就不会用到索引：

col_b = "aaaaaa";
    col_b = "aaaa" and col_c = "cccccc";

3、组合索引与单列索引区别：

如果我们的查询where条件只有一个，我们完全可以用单列索引，这样的查询速度较快，索引也比较瘦身。如果我们的业务场景是需要经常查询多个组合列，不要试图分别基于单个列建立多个单列索引（因为虽然有多个单列索引，但是MySQL只能用到其中的那个它认为似乎最有效率的单列索引）。这是因为当SQL语句所查询的列，全部都出现在复合索引中时，此时由于只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多。

实例：如下查询

SELECT ｀uid｀ FROM people WHERE lname｀='Liu'  AND ｀fname｀='Zhiqun' AND ｀age｀=26

1）我们不想全表扫描，建立一个单列索引：

ALTER TABLE people ADD INDEX lname (lname);  
将lname列建索引，这样就把范围限制在lname='Liu'的结果集1上，之后扫描结果集1，产生满足fname='Zhiqun'的结果集2，再扫描结果集2，找到 age=26的结果集3，即最终结果。  
  
由 于建立了lname列的索引，与执行表的完全扫描相比，效率提高了很多，但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引，再创建fname或者age 列的索引，但是，不论在哪个列上创建索引搜索效率仍旧相似。

2）组合索引：

ALTER TABLE people ADD INDEX lname\_fname\_age (lame,fname,age);  
为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。

注：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。