Mysql的联合索引-最左匹配的隐藏规则

发表于 分类于 阅读次数： 阅读次数： 本文字数： 840 阅读时长 ≈ 3 分钟

联合最左匹配原则

  1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

  2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

  3. 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

  4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

  5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

  6. 匹配某列的前缀索引，此时可以用到索引，但是如果通配符不是只出现在末尾，
    则无法使用索引。

一组和原则相悖的实验

创建一个表，字段：

1
2
3
4
5
6
7
8
CREATE TABLE `abc` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` varchar(64) DEFAULT NULL,
  `b` varchar(64) DEFAULT NULL,
  `c` varchar(64) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `idx_abc` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB  ;

正常的查询

1
2
3
4
5
6
7
explain select * from abc t where t.a='a' and t.b='b' and t.c='c'; // 三列索引
explain select * from abc t where t.a='a'  and t.b='b'; // 2列
explain select * from abc t where t.a='a'  and t.c='c';//1列
explain select * from abc t where t.a='a'; //1列
explain select * from abc t where t.b='b'; // 0列
explain select * from abc t where t.c='c'; // 0列
explain select * from abc t where t.a='a'  and t.c='c'and t.b>'b'; // 2列

以上查询均可按照正常的最左索引进行。

不符合原则的查询

1
explain select * from abc t where  t.b='b' and t.c='c';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t index idx_abc 201 476 Using where; Using index

结论很奇怪：按最左原则，b和c的查询，由于没有用到a列，所以不应该命中索引。

神奇的地方

1
2
3
4
ALTER TABLE `test`.`abc` 
ADD COLUMN `d` varchar(255) NULL AFTER `c`;
commit;
explain select * from abc t where  t.b='b' and t.c='c';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ALL 476 Using where

这轮索引没有命中，就因为多了一个字段吗？

猜想

  1. 当表中所有字段均在索引范围内，无论如何查询，都会试图用索引去提速。
  2. B+Tree在构建的时候，对全部字段有特殊处理。