未加星标

5分钟了解MySQL5.7对in用法有什么黑科技

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

5分钟了解MySQL5.7对in用法有什么黑科技
mysql5.7对in用法有什么黑科技 构建测试环境

Part1: 创建测试数据库

[[email protected] ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.15-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select version(); +------------+ | version() | +------------+ | 5.7.15-log | +------------+ 1 row in set (0.00 sec) [[email protected] ~]# /usr/local/mariadb/bin/mysql -uroot -S /tmp/mariadb.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.1.16-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [helei]> select version(); +-----------------+ | version() | +-----------------+ | 10.1.16-MariaDB | +-----------------+ 1 row in set (0.00 sec)

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

一分钟完成MySQL5.7安装

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

MariaDB10.1自动化部署

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

Part2: 构建测试表

CREATE TABLE helei ( id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, c1 INT (10) NOT NULL DEFAULT '0', c2 INT (10) UNSIGNED DEFAULT NULL, c5 INT (10) UNSIGNED NOT NULL DEFAULT '0', c3 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c4 VARCHAR (200) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY idx_c1 (c1), KEY idx_c2 (c2) ) ENGINE = INNODB; 对比测试结果

Part1: MyriaDB10.1.16

MariaDB [helei]> explain select * from helei where (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 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)

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

Part2: MySQL5.7.15

mysql> explain select * from helei where (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 | Using where | +----+-------------+-------+------------+-------+----------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.29 sec)

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


5分钟了解MySQL5.7对in用法有什么黑科技

总结

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

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQLAURIMTIRYCUUT数据AUT
分页:12
转载请注明
本文标题:5分钟了解MySQL5.7对in用法有什么黑科技
本站链接:http://www.codesec.net/view/481366.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(41)