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

联合最左匹配原则

  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. 匹配某列的前缀索引,此时可以用到索引,但是如果通配符不是只出现在末尾,
    则无法使用索引。

一组和原则相悖的实验

创建一个表,字段:

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  ;

正常的查询

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列

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

不符合原则的查询

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列,所以不应该命中索引。

神奇的地方

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在构建的时候,对全部字段有特殊处理。

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

上一篇:shardingsphere,shardingjdbc按年月时间分库,定时创建库表


下一篇:PHP+Redis+MySQL商品秒杀与超卖!