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都无法改写。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。