mysql 5.7 多条件 in_5分钟了解MySQL5.7对in用法有什么黑科技

MySQL5.7对in用法有什么黑科技

构建测试环境

Part1:创建测试数据库

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

[root@HE1 ~]# mysql -uroot -p

Enterpassword:

Welcometo the MySQL monitor.  Commandsend with ;or \g.

Your MySQLconnection idis 5

Server version: 5.7.15-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracleand/or its affiliates.All rights reserved.

Oracleis a registered trademarkof Oracle Corporationand/or its

affiliates. Other names may be trademarksof their respective

owners.

Type'help;' or '\h' for help. Type'\c' to clear thecurrent input statement.

mysql>select version();

+------------+

| version()  |

+------------+

| 5.7.15-log |

+------------+

1 rowin set (0.00 sec)

[root@HE3 ~]# /usr/local/mariadb/bin/mysql -uroot -S /tmp/mariadb.sock

Welcometo the MariaDB monitor.  Commandsend with ;or \g.

Your MariaDBconnection idis 3

Server version: 10.1.16-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Aband others.

Type'help;' or '\h' for help. Type'\c' to clear thecurrent input statement.

MariaDB [helei]>select version();

+-----------------+

| version()       |

+-----------------+

| 10.1.16-MariaDB |

+-----------------+

1 rowin set (0.00 sec)

如何构建MySQL5.7测试环境和MariaDB10.1测试环境本文不做赘述，如有需要可移步：

一分钟完成MySQL5.7安装

MariaDB10.1自动化部署

Part2:构建测试表

1

2

3

4

5

6

7

8

9

10

11

CREATE TABLE helei (

idINT (10) UNSIGNEDNOT NULL AUTO_INCREMENT,

c1INT (10)NOT NULL DEFAULT '0',

c2INT (10) UNSIGNEDDEFAULT NULL,

c5INT (10) UNSIGNEDNOT NULL DEFAULT '0',

c3TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

c4VARCHAR (200)NOT NULL DEFAULT '',

PRIMARY KEY (id),

KEY idx_c1 (c1),

KEY idx_c2 (c2)

) ENGINE = INNODB;

对比测试结果

Part1:MyriaDB10.1.16

1

2

3

4

5

6

7

MariaDB [helei]> explainselect *from heleiwhere (id,c1)in ((100,2684),(101,3566));

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id   | select_type |table | type | possible_keys |key  | key_len | ref  |rows | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | helei |ALL  |NULL          |NULL |NULL    |NULL | 5198 | Usingwhere |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 rowin set (0.00 sec)

可以看到，MariaDB对这条SQL总共扫描了5198行，且没有用到任何的索引。

Part2:MySQL5.7.15

1

2

3

4

5

6

7

mysql> explainselect *from heleiwhere (id,c1)in ((100,2684),(101,3566));

+----+-------------+-------+------------+-------+----------------+---------+---------+------+------+----------+-------------+

| id | select_type |table | partitions | type  | possible_keys  |key     | key_len | ref  |rows | filtered | Extra       |

+----+-------------+-------+------------+-------+----------------+---------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | helei |NULL       | range |PRIMARY,idx_c1 |PRIMARY | 4       |NULL |    2 |    20.00 | Usingwhere |

+----+-------------+-------+------------+-------+----------------+---------+---------+------+------+----------+-------------+

1 rowin set, 1 warning (0.29 sec)

可以看到，MySQL5.7版本已经用到了索引，一共扫描条数为2行。这是因为该语句已经被MySQL5.7的内部优化器改写为如下图所示：

——总结——

可以看出，MariaDB版本内部优化器无法改写，因此对于这类查询只能全表扫描。经测试，MySQL5.6和MariaDB10.0/10.1都无法改写。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。

结束语