MySql经常用,但是通常就是ORM中间件来CRUD操作,很少关注索引问题,可能是我近些年很少用MySQL做大型的系统了。
现在一面试就遇到所有领域的问题来都来一个问一遍,MySQL的复合索引其实也就一知半解。

面试官的问类似于select语句里where a>100 and b=3 and c=1 能否命中创建的(a, b, c)这种索引,还有a>100能否命中这个复合索引。

我想当然就说,a>100 and b=3 and c=1 只能命令(a, b, c)这种索引, a>100不能命中。唉呀,人家笑了。我说我可能说的不对,我下来试试。好吧查资料,建表,亲自实操一把。

先来找下mysql的联合索引命中规则

以下来自https://blog.csdn.net/u013276277/article/details/78150411

示例如下。首先创建表:
CREATE TABLE E (e1 INT, e2 VARCHAR(9), e3 INT, PRIMARY KEY(e1, e3));
这样就建立了一个联合索引:e1,e3

触发联合索引是有条件的:
1、使用联合索引的全部索引键,可触发索引的使用。
例如:SELECT E.* FROM E WHERE E.e1=1 AND E.e3=2

2、使用联合索引的前缀部分索引键,如“key_part_1 <op>常量”,可触发索引的使用。
例如:SELECT E.* FROM E WHERE E.e1=1

3、使用部分索引键,但不是联合索引的前缀部分,如“key_part_2 <op>常量”,不可触发索引的使用。
例如:SELECT E.* FROM E WHERE E.e3=1

4、使用联合索引的全部索引键,但索引键不是AND操作,不可触发索引的使用。
例如:SELECT E.* FROM E WHERE E.e3=2 OR E.e1=1

自己试下才甘心

建表

CREATE TABLE `Person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `age` smallint(5) unsigned DEFAULT '10',
  `sex` tinyint(1) unsigned DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `childrens` int(10) unsigned DEFAULT '0',
  `name` varchar(30) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `aindex` (`age`,`sex`,`city`)
)

插入数据

insert into Person(name, age, sex, city, childrens) values('jack', 30, 1, 'xian', 3);
insert into Person(name, age, sex, city, childrens) values('lily', 20, 0, 'shanghai', 2);
insert into Person(name, age, sex, city, childrens) values('jackson', 23, 1, 'xian', 0);
...

试下全部索引键and操作

explain select * from Person where age=30 and sex=0 and city='xian'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: ref
possible_keys: aindex
          key: aindex
      key_len: 68
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

命令索引

试下全部索引键,有and, 有or

explain select * from Person where age=30 and sex=0 or city='xian'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: ALL
possible_keys: aindex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 12.57
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

未命中索引 ,看来有OR是不行的。

试下where中只有一个条件,且是第一个索引

explain select * from Person where age=30\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: ref
possible_keys: aindex
          key: aindex
      key_len: 3
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

命中索引

再试下where中只有一个条件,但是是第二个索引键

explain select * from Person where sex=0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

没有命中索引, 所以最左原则讲的第二个索引之后是无法命中的

再试下where有两个条件,and前两个索引

explain select * from Person where age=30 and sex=0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: ref
possible_keys: aindex
          key: aindex
      key_len: 5
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

命中索引

上面的两个索引键,顺序颠倒下

explain select * from Person where sex=0 and age=30\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: ref
possible_keys: aindex
          key: aindex
      key_len: 5
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

也是命中了索引,有点奇怪,网上有的地方说复合索引遵从最左原则,(a, b, c), 只能是 a, (a, b), (a, b, c) ,这里试了和顺序没有关系

三个索引键顺序打乱 and 也是一样命中

explain select * from Person where sex=0 and city='xian' and age=30\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: ref
possible_keys: aindex
          key: aindex
      key_len: 68
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

取两个索引键and下也是一样命中

explain select * from Person where city='xian' and age=30\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: ref
possible_keys: aindex
          key: aindex
      key_len: 3
          ref: const
         rows: 2
     filtered: 11.11
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

再来几个有意思的查询

条件中是大于,不是等于还能命中吗

explain select * from Person where age>20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: ALL
possible_keys: aindex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 77.78
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

居然没有命中, 但是大于改成小于呢

explain select * from Person where age<20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person
   partitions: NULL
         type: range
possible_keys: aindex
          key: aindex
      key_len: 3
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

这里命中了,看来小于和等于可以用来命中,大于不能,这里有意思的是explain返回的type是 range,即索引范围查询。 另外,多个索引键组合小于条件也可以乱序,试了也是可以命中的。

综上:

  1. 复合索引(a, b, c) 命中索引条件可以是 a,(a and b), (a and b and c),
    组合顺序可以任意,如(a and b), (b and c), (a and c), (c and a and b)
  2. 使用部分索引键命中,只能是a, 不能是b,也不能是c,这种情况考虑单个索引
  3. 大于或大于等于条件不能命中, 小于条件和小于等于条件是可以命中的。

PS.

  1. 现在的面试基本上都是拿一个问题决定面试者的生死,唉...
  2. 能否命中索引关注 explain 响应中的key值

标签: mysql, 索引

添加新评论