`
zhengyutao
  • 浏览: 36671 次
  • 性别: Icon_minigender_1
  • 来自: 黑龙江
最近访客 更多访客>>
社区版块
存档分类
最新评论

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语句
update item set delete=? where (itemId, roleId) = (?, ?)

在后台查询这条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生成的语句如下:
update item set delete=? where itemId = ? and roleId=?

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

故,两个问题:

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

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

何解?


备注:
Mysql官方文档对行子查询的说明如下:
引用
对于本点的讨论属于标量或列子查询,即返回一个单一值或一列值的子查询。行子查询是一个能返回一个单一行的子查询变量,因此可以返回一个以上的列值。以下是两个例子:
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);

分享到:
评论

相关推荐

    mysql所有的查询语句详解

    MySQL是世界上最受欢迎的关系型数据库管理系统之一,尤其在Web开发中,与Java服务器页面(JSP)结合使用时,它的查询语句是数据交互的核心。在本篇中,我们将深入探讨MySQL在JSP中的所有主要查询语句,以及如何在...

    mysql子查询的用法

    MySQL子查询是数据库查询中的一个重要概念,它允许在SQL语句内部嵌套其他查询,用于检索、比较或操作数据。子查询可以理解为一个独立的查询结果,它被用作外部查询的一部分,提供了灵活的数据处理能力。在本篇文章中...

    MySQL锁类型以及子查询锁表问题、解锁1

    在实际应用中,可能会遇到死锁问题,特别是在涉及子查询的更新语句中。如描述中所示,如果一个事务在更新时对子查询中的表进行锁定,可能导致其他事务尝试获取已被锁定的资源,从而引发死锁。MySQL在检测到死锁时会...

    MySQL查询插入语句

    - **子查询插入**:从另一个表中选择数据进行插入。 ```csharp BackBZID = Convert.ToInt32(TPMySqlHelper.GetSingle("INSERT INTO TP_BZ_ZB (ZB_ALID, ZB_SFWC, ZB_BT, ZB_CKDN, ZB_BZCode, ZB_ZWPJ, ZB_WBKH, ZB...

    Oracle Sql语句转换成Mysql Sql语句

    3. **连接查询**:Oracle的JOIN语法允许在ON条件中使用子查询,而在MySQL中,子查询通常需要移到FROM或WHERE子句中。 4. **游标**:Oracle SQL支持游标,MySQL则不直接支持,需要使用存储过程或临时表来实现类似...

    MySQL学习-查询语句汇总

    ### MySQL学习-查询语句汇总 #### 一、获取表中某一列的最大值 在MySQL中,`MAX()`函数被广泛应用于查询特定列的最大值。例如,在本案例中,我们有一个名为`shop`的表,它包含三个字段:`article`(项目编号)、`...

    mysql子查询与连表查询的效率比较及优化

    **子查询(Subquery)**:子查询是指在一个查询语句中嵌套另一个查询语句的方式。子查询可以出现在SELECT、FROM、WHERE等子句中。例如,在WHERE子句中的子查询可以用来过滤主查询的结果集。 **连表查询(Join Query...

    25.6 MySQL 子查询

    MySQL 子查询是一种在SQL查询语句中嵌套其他查询的方法,它允许我们在一个查询中使用另一个查询的结果。子查询可以作为SELECT语句的一部分,也可以出现在FROM或WHERE子句中,甚至可以在HAVING子句中使用。它们为...

    mysql数据库基础查询语句全套学习

    本教程主要针对初学者,旨在提供一套完整的MySQL基础查询语句学习资料。通过尚硅谷的课程,我们将逐步掌握如何使用SQL(Structured Query Language)来操作数据库。 一、SQL基础 SQL是用于管理关系数据库的语言,...

    Effective MySQL之SQL语句最优化.pdf

    10. SQL语句优化的技术手段:技术手段包括但不限于使用子查询优化、使用JOIN代替子查询、避免SELECT *、使用更有效的查询方法(如IN代替OR)、利用数据库提供的存储过程和函数减少网络往返次数等。 11. 经验与实践...

    MySql高级Sql查询

    2. **子查询**:子查询是在一个查询语句内部嵌套另一个查询,它能处理复杂的逻辑条件。例如,你可以用子查询来找出某个字段的最大值,然后在主查询中找到所有与此最大值相等的记录。 3. **聚合函数**:如COUNT(), ...

    设计高效合理的MySQL查询语句

    在设计高效合理的MySQL查询语句时,关注查询性能至关重要,因为查询操作在数据库操作中占据了主要部分,而SELECT语句的执行成本最高。随着数据量的增加,全表扫描会导致查询时间显著增长,可能需要数十分钟甚至数...

    mysql查询语句汇总.zip

    9. **子查询**:在查询语句中嵌套另一个查询,如 `SELECT * FROM 表1 WHERE 列1 = (SELECT 列2 FROM 表2 WHERE 条件);` 10. **DISTINCT关键字**:用于去除重复行,如 `SELECT DISTINCT 列名 FROM 表名;` 11. **...

    mysql面试题-查询语句

    MySQL是世界上最受欢迎的关系型数据库管理系统之一,其查询语句是SQL语言的核心部分,对于任何希望在IT行业,尤其是数据库管理领域工作的人来说,掌握MySQL查询语句至关重要。面试中,MySQL查询语句通常会涉及到以下...

    mysql查询语句汇总

    MySQL查询语句是数据库管理中不可或缺的部分,它用于检索、更新、删除和操作数据库中的数据。本汇总将全面介绍MySQL中的基本查询语法以及高级查询技巧。 1. **基础查询** - **SELECT语句**: 用于从表中选择数据。...

    mysql和oracle和sql语句

    例如,Oracle的`SELECT`语句可以使用子查询、连接查询以及窗口函数进行复杂的分析。 SQL Server是微软开发的一款数据库管理系统,它在语法上也有一些自己的特点。比如,SQL Server中的`TOP`关键字用于限制返回的...

    MySQL查询语句汇总.zip

    MySQL查询语句是数据库管理中不可或缺的部分,它用于从MySQL数据库中检索数据。这份"MySQL查询语句汇总.zip"文件提供了全面的学习资料,包括相关的文档说明,非常适合对MySQL查询语句进行深入学习。 首先,我们要...

    mysql各种语句多表查询+ mysql视图 mysql数据库管理+ select语句+ 创建

    在本教程中,我们将深入探讨MySQL中的多种查询语句,包括多表查询、视图、数据库管理、SELECT语句以及创建表等核心概念。 首先,让我们从多表查询开始。在实际应用中,往往需要从多个相关表中获取信息,这就需要...

    mysql子查询精炼版.pdf

    MySQL子查询是数据库查询中的重要概念,用于在一个查询语句中嵌套另一个查询语句,以获取更复杂的数据。在上述文档中,主要讲解了单行子查询、多行子查询以及相关子查询和不相关子查询的用法。 1. **单行子查询**:...

Global site tag (gtag.js) - Google Analytics