MySQL行子查询语句问题

大家好,在项目中碰到如下问题:
hibernate 映射表的时候使用联合主键
例子如下:
商品表:Item,主键为联合主键ItemId
代码如下


public class ItemId{
@column
Long itemId;
@column
Long userId;
}

@Table(...)
public class Item{
@Id
ItemId id;
@column
Date createDate;
@column
boolean deleted;
...
}


三个手动创建索引如下:

itemId 主键索引
userId 主键索引
itemId, userId 联合索引

数据库表为InnerDB

更新一条语句示例如下:

public void softDeleteItem(long itemId, long userId){
session.createQuery("update Item set deleted = :deleted WHERE id=:id")
.setParameter("deleted", 1)
.setParameter("id", new ItemId(itemId, userId))
.executeQuery();
}


通过上面的更新语句hibernate产生如下SQL语句
[b]update item set delete=? where (itemId, roleId) = (?, ?)[/b]

在后台查询这条sql语句非常耗时,在item表数据量达到百万级级的时候好使就需要2~3秒
通过查询返回的详细参数可以看到Mysql做全表查询了,而且锁表了,并没有使用索引。


之后为了解决线上问题,就做出更改如下

public void softDeleteItem(long itemId, long userId){
session.createQuery("update Item set deleted = :deleted WHERE id.itemId=:itemId AND id.userId = :userId")
.setParameter("deleted", 1)
.setParameter("itemId", itemId)
.setParameter("userId", userId)
.executeQuery();
}

hibernate生成的语句如下:
[b]update item set delete=? where itemId = ? and roleId=?[/b]

效率立即上去了,使用了索引,更新只需要毫秒级的时间。

故,两个问题:

1. hibernate为什么会生成
update item set delete=? where (itemId, roleId) = (?, ?)
而不是
update item set delete=? where itemId = ? and roleId = ?

2. 上面两条sql语句在mysql执行的效率为什么不一样 ?

何解?


备注:
Mysql官方文档对行子查询的说明如下:
[quote]对于本点的讨论属于标量或列子查询,即返回一个单一值或一列值的子查询。行子查询是一个能返回一个单一行的子查询变量,因此可以返回一个以上的列值。以下是两个例子:
SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);
如果在表t2的一个行中,column1=1并且column2=2,则查询结果均为TRUE。

表达式(1,2)和ROW(1,2)有时被称为行构造符。两者是等同的,在其它的语境中,也是合法的。例如,以下两个语句在语义上是等同的(但是目前只有第二个语句可以被优化):

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。例如,以下查询可以答复请求,“在表t1中查找同时也存在于表t2中的所有的行”:

SELECT column1,column2,column3
FROM t1
WHERE (column1,column2,column3) IN
(SELECT column1,column2,column3 FROM t2);[/quote]
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值