联合索引与=，in，between

CREATE TABLE `0010_mailinfo_1` (
  `mid` BINARY(24) NOT NULL,
  `ufid` BIGINT(20) UNSIGNED NOT NULL,
  `flags` BIGINT(20) UNSIGNED NOT NULL,
  `ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `size` INTEGER(10) UNSIGNED NOT NULL,
  `attnum` TINYINT(3) UNSIGNED NOT NULL,
  `priority` TINYINT(3) UNSIGNED NOT NULL,
  `optime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `subject` VARCHAR(512) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `mailfrom` VARCHAR(100) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `mailto` VARCHAR(320) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `email` VARCHAR(96) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`ufid`, `ctime`, `mid`),
  KEY `idxmid` (`mid`)
)ENGINE=InnoDB
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

mysql> explain select hex(mid),size, subject, ufid from 0010_mailinfo_1 where ufid in
    ->  (50089, 50090, 50091, 50092, 50093)
    -> and ctime = '2009-03-23 15:26:18' and  attnum != 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: 0010_mailinfo_1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 12
          ref: NULL
         rows: 5
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select hex(mid),size, subject, ufid from 0010_mailinfo_1 where ufid
    -> between  50089 and 50093  and ctime = '2009-03-23 15:26:18' and  attnum != 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: 0010_mailinfo_1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 12
          ref: NULL
         rows: 78132
        Extra: Using where
1 row in set (0.00 sec)

http://www.mysqlperformanceblog.com/2006/08/10/using-union-to-implement-loose-index-scan-to-mysql/

MySQL Will not use indexes in all cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with "=".

ufid 是联合索引的一部分，在使用 between 时不能很好使用这第一个字段，正如上面这个文章中说的那样。

其实，除了 = 以外，用 in 也是可以的，正如上面文章的评论中说的。这应该算是松散索引扫描的一个例子。
还有下面的文章：

http://www.jianzhaoyang.com/database/mysql_order_by_implement

http://www.jianzhaoyang.com/database/mysql_group_by_implement

http://www.jianzhaoyang.com/database/mysql_join_buffer_nested_loop_implement

http://www.mysqlperformanceblog.com/2006/05/09/descending-indexing-and-loose-index-scan/

http://hi.baidu.com/hexie007/blog/item/1f85b9ee9b084e2e2df53494.html

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

转载于:http://blog.itpub.net/16989307/viewspace-720640/