mysql中in的用法总结_5分钟了解MySQL5.7对in用法有什么黑科技

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

构建测试环境

Part1:创建测试数据库

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

Enterpassword:

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis5

Serverversion:5.7.15-logMySQLCommunityServer(GPL)

Copyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits

affiliates.Othernamesmaybetrademarksoftheirrespective

owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>selectversion();

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

|version()|

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

|5.7.15-log|

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

1rowinset(0.00sec)

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

WelcometotheMariaDBmonitor.Commandsendwith;or\g.

YourMariaDBconnectionidis3

Serverversion:10.1.16-MariaDBMariaDBServer

Copyright(c)2000,2016,Oracle,MariaDBCorporationAbandothers.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

MariaDB[helei]>selectversion();

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

|version()|

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

|10.1.16-MariaDB|

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

1rowinset(0.00sec)

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

一分钟完成MySQL5.7安装

http://suifu.blog.51cto.com/9167728/1855415

MariaDB10.1自动化部署

http://suifu.blog.51cto.com/9167728/1830575

Part2:构建测试表

CREATETABLEhelei(

idINT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,

c1INT(10)NOTNULLDEFAULT'0',

c2INT(10)UNSIGNEDDEFAULTNULL,

c5INT(10)UNSIGNEDNOTNULLDEFAULT'0',

c3TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,

c4VARCHAR(200)NOTNULLDEFAULT'',

PRIMARYKEY(id),

KEYidx_c1(c1),

KEYidx_c2(c2)

)ENGINE=INNODB;

对比测试结果

Part1:MyriaDB10.1.16

MariaDB[helei]>explainselect*fromheleiwhere(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|

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

1rowinset(0.00sec)

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

Part2:MySQL5.7.15

mysql>explainselect*fromheleiwhere(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|

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

1rowinset,1warning(0.29sec)

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

总结：

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