美高梅官方网站3045-mgm6608美高梅app下载
数据库-从500万条数据中询问,求优化一条SQL语句

数据库-从500万条数据中询问,求优化一条SQL语句

作者:美高梅官方网站3045    来源:未知    发布时间:2019-11-25 10:36    浏览量:

最近在写SQL语句时,对选择IN 还是Exists 犹豫不决,于是把两种方法的SQL都写出来对比一下执行效率,发现IN的查询效率比Exists高了很多,于是想当然的认为IN的效率比Exists好,但本着寻根究底的原则,我想知道这个结论是否适用所有场景,以及为什么会出现这个结果。网上查了一下相关资料,大体可以归纳为:外部表小,内部表大时,适用Exists;外部表大,内部表小时,适用IN。那我就困惑了,因为我的SQL语句里面,外表只有1W级别的数据,内表有30W级别的数据,按网上的说法应该是Exists的效率会比IN高的,但我的结果刚好相反!!“没有调查就没有发言权”!于是我开始研究IN 和Exists的实际执行过程,从实践的角度出发,在根本上去寻找原因,于是有了这篇博文分享。

从500万条数据中查询,求优化一条SQL语句
表结构如下,里面有500W数据。我没有权限修改这个表,所以请大家给我优化下SQL查询
[code="sql"]CREATE TABLE IF NOT EXISTS jdp_tb_trade (
tid bigint NOT NULL,
status varchar DEFAULT NULL,
type varchar DEFAULT NULL,
seller_nick varchar DEFAULT NULL,
buyer_nick varchar DEFAULT NULL,
created datetime DEFAULT NULL,
modified datetime DEFAULT NULL,
jdp_hashcode varchar DEFAULT NULL,
jdp_response mediumtext,
jdp_created datetime DEFAULT NULL,
jdp_modified datetime DEFAULT NULL,
PRIMARY KEY (tid),
KEY ind_jdp_tb_trade_seller_nick_jdp_modified (seller_nick,jdp_modified),
KEY ind_jdp_tb_trade_jdp_modified (jdp_modified),
KEY ind_jdp_tb_trade_seller_nick_modified (seller_nick,modified),
KEY ind_jdp_tb_trade_modified (modified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;[/code]

使用Spring Security实现方法权限管理

我的实验数据包括两张表:t_author表 和 t_poetry表。对应表的数据量:

符合以下条件的有3W条数据
[code="sql"]SELECT COUNT AS tp_count FROM jdp_tb_trade WHERE ( seller_nick IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七') ) AND ( (jdp_modified > '2007-11-30 09:52:39') AND (jdp_modified <= '2014-04-21 22:31:13') ) LIMIT 1[/code]

1、技术目标

t_author表,13355条记录;t_poetry表,289917条记录。

我要分页查询出这3W条数据,由于MYSQL分页越往后查询越慢,所以我用了关联查询。该查询在前面2页需要3到4分钟才能返回结果。后面的每页4秒左右就返回了。实在搞不明白为什么。麻烦大家帮忙优化下。每页必须要在10秒内完成。
[code="sql"]SELECT t1.jdp_modified,t1.jdp_response FROM jdp_tb_trade t1, ( SELECT tid FROM jdp_tb_trade WHERE ( seller_nick IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七') ) AND ( (jdp_modified > '2007-11-30 09:52:39') AND (jdp_modified <= '2014-04-21 22:31:13') ) ORDER BY jdp_modified desc LIMIT 0,200 ) t2 WHERE t1.tid=t2.tid[/code]

了解并创建Security框架所需数据表

CREATE TABLE t_poetry (id bigint NOT NULL AUTO_INCREMENT,poetry_id bigint NOT NULL COMMENT '诗词id',poetry_name varchar NOT NULL COMMENT '诗词名称', author_id bigint NOT NULL COMMENT '作者id'PRIMARY KEY ,UNIQUE KEY pid_idx (poetry_id) USING BTREE,KEY aid_idx (author_id) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=291270 DEFAULT CHARSET=utf8mb4

CREATE TABLE t_author (id int NOT NULL AUTO_INCREMENT,author_id bigint NOT NULL,author_name varchar NOT NULL,dynasty varchar NOT NULL,poetry_num int NOT NULL DEFAULT '0'PRIMARY KEY ,UNIQUE KEY authorid_idx (author_id) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=13339 DEFAULT CHARSET=utf8mb4

为项目添加Spring Security框架

执行计划分析 IN 执行过程

掌握Security框架配置

sql示例:select * from tabA where tabA.x in (select x from tabB where y>0 );

应用Security框架为项目的CRUD操作绑定权限

其执行计划:执行tabB表的子查询,得到结果集B,可以使用到tabB表的索引y;执行tabA表的查询,查询条件是tabA.x在结果集B里面,可以使用到tabA表的索引x。

2、权限管理需求描述

Exists执行过程

为系统中的每个操作定义权限,如定义4个权限:

sql示例:select from tabA where exists (select from tabB where y>0);

1)超级权限,可以使用所有操作

逐行针对tabA表的记录,去关联tabB表,判断tabB表的子查询是否有返回数据,5.5之后的版本使用Block Nested Loop如果子查询有返回数据,则将tabA当前记录返回到结果集。tabA相当于取全表数据遍历,tabB可以使用到索引。

2)添加影片权限

实验过程

3)修改影片权限

实验针对相同结果集的IN和Exists 的SQL语句进行分析。包含IN的SQL语句:

 4)删除影片权限

select from t_author ta where author_id in (select author_id from t_poetry tp where tp.poetry_id>3650 );

为系统设置管理员帐号、密码

包含Exists的SQL语句:

为系统创建权限组,每个权限组可以配置多个操作权限,如创建2个权限组:

select from t_author ta where exists (select * from t_poetry tp where tp.poetry_id>3650 and tp.author_id=ta.author_id);

1)"Administrator"权限组,具有超级权限

第一次实验数据情况

 2)"影片维护"权限组,具有添加影片、修改影片权限

t_author表,13355条记录;t_poetry表,子查询筛选结果集 where poetry_id>293650 ,121条记录;

可将管理员加入权限组,管理员登录后具备权限组所对应操作权限

使用exists耗时0.94S, 使用in耗时0.03S,IN 效率高于Exists

管理员可不属于某权限组,可为管理员直接分配权限

对t_poetry表的子查询结果集很小,且两者在t_poetry表都能使用索引,对t_poetry子查询的消耗基本一致。两者区别在于,使用 in 时,t_author表能使用索引:

3、使用准备

使用exists时,t_author表全表扫描:

3.1)在数据库中创建6张表

在子查询结果集较小时,查询耗时主要表现在对t_author表的遍历上。

t_admin        管理员帐号表

第二次实验数据情况

t_role权限表

t_author表,13355条记录;t_poetry表,子查询筛选结果集 where poetry_id>3650 ,287838条记录;

t_group        权限组表

使用exists耗时0.12S, 使用in耗时0.48S,Exists效率高于 IN

t_group_role权限组对应权限表

两者的索引使用情况跟第一次实验是一致的,唯一区别是子查询筛选结果集的大小不同,但实验结果已经跟第一次的不同了。这种情况下子查询结果集很大,我们看看mysql的查询计划:使用in时,由于子查询结果集很大,对t_author和t_poetry表都接近于全表扫描,此时对t_author表的遍历耗时差异对整体效率影响可以忽略,执行计划里多了一行,在接近全表扫描的情况下,mysql优化器选择了auto_key来遍历t_author表:

t_group_user管理员所属权限组表

使用exists时,数据量的变化没有带来执行计划的改变,但由于子查询结果集很大,5.5以后的MySQL版本在exists匹配查询结果时使用的是Block Nested-Loop(Block嵌套循环,引入join buffer,类似于缓存功能)开始对查询效率产生显着影响,尤其针对子查询结果集很大的情况下能显着改善查询匹配效率:

t_user_role管理员对应权限表

根据上述两个实验及实验结果,我们可以较清晰的理解IN 和Exists的执行过程,并归纳出IN 和Exists的适用场景:

建表SQL语句如下:

IN查询在内部表和外部表上都可以使用到索引; Exists查询仅在内部表上可以使用到索引;当子查询结果集很大,而外部表较小的时候,Exists的Block Nested Loop的作用开始显现,并弥补外部表无法用到索引的缺陷,查询效率会优于IN。当子查询结果集较小,而外部表很大的时候,Exists的Block嵌套循环优化效果不明显,IN 的外表索引优势占主要作用,此时IN的查询效率会优于Exists。 网上的说法不准确。其实“表的规模”不是看内部表和外部表,而是外部表和子查询结果集。最后一点,也是最重要的一点:世间没有绝对的真理,掌握事物的本质,针对不同的场景进行实践验证才是最可靠有效的方法。 实验过程中发现的问题补充

Sql代码

仅对不同数据集情况下的上述exists语句分析时发现,数据集越大,消耗的时间反而变小,觉得很奇怪。具体查询条件为:

SET FOREIGN_KEY_CHECKS=0; 

where tp.poetry_id>3650,耗时0.13Swhere tp.poetry_id>293650,耗时0.46S

-- 创建管理员帐号表t_admin 

可能原因:条件值大,查询越靠后,需要遍历的记录越多,造成最终消耗越多的时间。这个解释有待进一步验证后再补充。

CREATE TABLE `t_admin` (  

`id`bigint(20) unsigned NOT NULL AUTO_INCREMENT,  

`passwd`varchar(12) NOT NULL DEFAULT '' COMMENT '用户密码',  

`nickname`varchar(20) NOT NULL DEFAULT '' COMMENT '用户名字',  

`phoneno`varchar(32) NOT NULL DEFAULT '' COMMENT '电话号码',  

PRIMARY KEY (`id`)  

) ENGINE=InnoDB AUTO_INCREMENT=6DEFAULT CHARSET=utf8; 

-- 添加3个管理帐号  

INSERT INTO `t_admin` VALUES ('1', 'admin', 'admin', '');  

INSERT INTO `t_admin` VALUES ('4', '123456', 'test', '');  

INSERT INTO `t_admin` VALUES ('5', '111111', '111111', ''); 

-- 创建权限表t_role 

CREATE TABLE `t_role` (  

`id`bigint(20) unsigned NOT NULL AUTO_INCREMENT,  

`role`varchar(40) NOT NULL DEFAULT '',  

`descpt`varchar(40) NOT NULL DEFAULT '' COMMENT '角色描述',  

`category`varchar(40) NOT NULL DEFAULT '' COMMENT '分类',  

PRIMARY KEY (`id`)  

) ENGINE=InnoDB AUTO_INCREMENT=60DEFAULT CHARSET=utf8; 

-- 加入4个操作权限 

INSERT INTO `t_role` VALUES ('1', 'ROLE_ADMIN', '系统管理员', '系统管理员');  

INSERT INTO `t_role` VALUES ('2', 'ROLE_UPDATE_FILM', '修改', '影片管理');  

INSERT INTO `t_role` VALUES ('3', 'ROLE_DELETE_FILM', '删除', '影片管理');  

INSERT INTO `t_role` VALUES ('4', 'ROLE_ADD_FILM', '添加', '影片管理'); 

-- 创建权限组表 

CREATE TABLE `t_group` (  

`id`bigint(20) unsigned NOT NULL AUTO_INCREMENT,  

`groupname`varchar(50) NOT NULL DEFAULT '',  

PRIMARY KEY (`id`)  

) ENGINE=InnoDB AUTO_INCREMENT=7DEFAULT CHARSET=utf8; 

-- 添加2个权限组 

INSERT INTO `t_group` VALUES ('1', 'Administrator');  

INSERT INTO `t_group` VALUES ('2', '影片维护'); 

-- 创建权限组对应权限表t_group_role 

CREATE TABLE `t_group_role` (  

`id`bigint(20) unsigned NOT NULL AUTO_INCREMENT,  

`groupid`bigint(20) unsigned NOT NULL,  

友情链接: 网站地图
Copyright © 2015-2019 http://www.zen-40.com. mgm美高梅有限公司 版权所有