5.7版本 in , not in , and , or 索引使用情况对比

1
2
3
4
5
6
7
8

CREATE TABLE `big_keys` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `pinyin` varchar(300) NOT NULL DEFAULT '',
  `addtime` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `big_keys` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL DEFAULT '', `pinyin` varchar(300) NOT NULL DEFAULT '', `addtime` int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`,`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> explain select * from  big_keys where id  in (10000,10001,10002) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: big_keys
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.12 sec)

mysql> explain select * from big_keys where id in (10000,10001,10002) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: big_keys partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.12 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> explain select * from  big_keys where id not in (10000,10001,10002) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: big_keys
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1070071
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.07 sec)

mysql> explain select * from big_keys where id not in (10000,10001,10002) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: big_keys partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 1070071 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.07 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> explain select * from big_keys where id=10000 or id=10001 or id=10002 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: big_keys
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from big_keys where id=10000 or id=10001 or id=10002 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: big_keys partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> explain select * from big_keys where id=10000 and id=10001 and id=10002 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from big_keys where id=10000 and id=10001 and id=10002 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Impossible WHERE 1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8

CREATE TABLE `big_keys2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `pinyin` varchar(300) NOT NULL DEFAULT '',
  `addtime` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8;

CREATE TABLE `big_keys2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL DEFAULT '', `pinyin` varchar(300) NOT NULL DEFAULT '', `addtime` int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`,`id`) ) ENGINE=innodb DEFAULT CHARSET=utf8;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> explain select * from  big_keys2 where id  in (10000,10001,10002) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: big_keys2
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.11 sec)

mysql> explain select * from big_keys2 where id in (10000,10001,10002) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: big_keys2 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.11 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> explain select * from  big_keys2 where id not in (10000,10001,10002) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: big_keys2
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 528795
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.10 sec)

mysql> explain select * from big_keys2 where id not in (10000,10001,10002) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: big_keys2 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 528795 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.10 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> explain select * from big_keys2 where id=10000 or id=10001 or id=10002 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: big_keys2
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from big_keys2 where id=10000 or id=10001 or id=10002 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: big_keys2 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> explain select * from big_keys2 where id=10000 and id=10001 and id=10002 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from big_keys2 where id=10000 and id=10001 and id=10002 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Impossible WHERE 1 row in set, 1 warning (0.00 sec)