何为松散索引扫描? 何为紧凑索引扫描?

Question

CREATE TABLE `t3` (
  `id` int(11) NOT NULL DEFAULT '0',
  `u` char(100) DEFAULT NULL,
  `v` char(100) DEFAULT NULL,
  `w` char(100) DEFAULT NULL,
  `x` char(100) DEFAULT NULL,
  `y` char(100) DEFAULT NULL,
  `z` char(100) DEFAULT NULL,
  `iu` int(11) DEFAULT NULL,
  `iv` int(11) DEFAULT NULL,
  `iw` int(11) DEFAULT NULL,
  `ix` int(11) DEFAULT NULL,
  `iy` int(11) DEFAULT NULL,
  `iz` int(11) DEFAULT NULL,
  UNIQUE KEY `i_id` (`id`) USING BTREE,
  KEY `i_u_v_w` (`u`,`v`,`w`) USING BTREE,
  KEY `i_iu_iv_iw` (`iu`,`iv`,`iw`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8==================================================================我直接测试发现, 只有select iu, max(iv) 或 min(iv) from t3 group by iu; 方出现 Using index for group-by(松散索引扫描)其他组合, 甚至select iu, max(iv) 或 min(iv) from t3 group by iu, iv; 均提示using index
问题1, 为什么去掉max后就不是Using index for group-by, 而是using index问题2, 到底什么是紧凑索引扫描, 什么是松散索引扫描?
在mysql官方的在线手册有http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#group-by-optimization有相关描述, 
但是我根据它提供的范例完全不能搞明白什么是松散索引扫描?
这个问题我已经在网上找了一周了, 无奈大多数文章均为抄袭, 还是没搞明白, 希望大家能帮助我, 谢谢

Answer 1

感觉为什么mysql官方提供的手册读起来都那么绕口呢, 感觉好像有语病一样? 真是搞不明白

Answer 2

select iu, max(iv) from t3 group by iu, iv;等同于
select iu, iv, max(iv) from t3 group by iu, iv;
 这个自然无法 Loose Index Scan
根据表中的索引 KEY `i_iu_iv_iw` (`iu`,`iv`,`iw`) USING BTREE
只有
select `iu`, max(iv) from t3 group by iu;
select `iu`,`iv`, max(iw) from t3 group by `iu`,`iv`;
才会使用 Loose Index Scan对另外一个索引KEY `i_u_v_w` (`u`,`v`,`w`) USING BTREE,也相同。

Answer 3

首先你要明白什么场景下会用到松散索引扫描？相对松散扫描有紧凑扫描。为什么你用了max函数就会用到松散扫描呢？请看使用松散索引扫描的条件。
当mysql 在group by 时 发现不能满足紧凑扫描时，尝试松散扫描(这相对于紧凑效率会率高，具体我尚未测试过)，如果连松散都不能用，那么可能会用到临时表或者文件排序。要利用到松散索引扫描实现GROUP BY，需要至少满足以下几个条件：
◆ GROUP BY 条件字段必须在同一个索引中最前面的连续位置；
◆ 在使用GROUP BY 的同时，只能使用MAX 和MIN 这两个聚合函数；
◆ 如果引用到了该索引中GROUP BY 条件之外的字段条件的时候，必须以常量形式存在；现在明白了吧！！！！11

Answer 4

select `iu`, max(iv) from t3 group by iu; 请问"松散"一词在这里是如何体现的呢

Answer 5

describe select iu, iv, min(iw) from t3 group by iu;
describe select iu, iv, min(iw) from t3 group by iu, iv;
describe select iu, iv, min(iw) from t3 group by iu, iv, iw;均提示using index

Answer 6

给出你的 show index from t2;explain select iu, iv, min(iw) from t3 group by iu, iv;以供分析。

Answer 7

*************************** 1. row ***************************
       Table: t3
  Non_unique: 0
    Key_name: i_id
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 200040
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: t3
  Non_unique: 1
    Key_name: i_u_v_w
Seq_in_index: 1
 Column_name: u
   Collation: A
 Cardinality: 200040
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: t3
  Non_unique: 1
    Key_name: i_u_v_w
Seq_in_index: 2
 Column_name: v
   Collation: A
 Cardinality: 200040
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
*************************** 4. row ***************************
       Table: t3
  Non_unique: 1
    Key_name: i_u_v_w
Seq_in_index: 3
 Column_name: w
   Collation: A
 Cardinality: 200040
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
*************************** 5. row ***************************
       Table: t3
  Non_unique: 1
    Key_name: i_iu_iv_iw
Seq_in_index: 1
 Column_name: iu
   Collation: A
 Cardinality: 25005
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
*************************** 6. row ***************************
       Table: t3
  Non_unique: 1
    Key_name: i_iu_iv_iw
Seq_in_index: 2
 Column_name: iv
   Collation: A
 Cardinality: 200040
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
*************************** 7. row ***************************
       Table: t3
  Non_unique: 1
    Key_name: i_iu_iv_iw
Seq_in_index: 3
 Column_name: iw
   Collation: A
 Cardinality: 200040
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
7 rows in set (0.02 sec)ERROR:
No query specifiedmysql> explain select iu, iv, min(iw) from t3 group by iu, iv\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: index
possible_keys: NULL
          key: i_iu_iv_iw
      key_len: 15
          ref: NULL
         rows: 200040
        Extra: Using index
1 row in set (0.00 sec)ERROR:
No query specified谢谢

Answer 8

注意你的索引
i_iu_iv_iw 1 iu A 25005
i_iu_iv_iw 2 iv A 200040
i_iu_iv_iw 3 iw A 200040你表中一共200040条记录，当你的 iu,iv 就已经没有重复的记录了。也就是说select iu, iv, min(iw) from t3 group by iu, iv等同于 select iu, iv, iw from t3 那为什么还要去Loose Index Scan
另外贴 show index, explain 不要使用 /G, 别人还要再变成行来分析。

Answer 9

好的, 我下次会注意这个问题, 但是还有个困惑始终没明白什么是松散索引扫描是怎么个松散法, 紧凑索引又是怎么回事, 英文不好, mysql那个手册看着很迷糊,