Home Database Mysql Tutorial MySQL行子查询语句有关问题

MySQL行子查询语句有关问题

Jun 07, 2016 pm 04:16 PM
mysql Inquire statement

MySQL行子查询语句问题 大家好,在项目中碰到如下问题: hibernate 映射表的时候使用联合主键 例子如下: 商品表:Item,主键为联合主键ItemId 代码如下 public class ItemId{ @column Long itemId; @column Long userId;}@Table(...)public class Item{ @Id

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

public class ItemId{

   @column

   Long itemId;

   @column

   Long userId;

}

 

@Table(...)

public class Item{

   @Id

   ItemId id;

   @column

   Date createDate;

   @column

   boolean deleted;

   ...

}

Copy after login


三个手动创建索引如下:

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

数据库表为InnerDB

更新一条语句示例如下:

1

2

3

4

5

6

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();

}

Copy after login


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

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


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

1

2

3

4

5

6

7

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();

}

Copy after login

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);
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

PHP's big data structure processing skills

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

How to optimize MySQL query performance in PHP?

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

How to use MySQL backup and restore in PHP?

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into a MySQL table using PHP?

What are the application scenarios of Java enumeration types in databases? What are the application scenarios of Java enumeration types in databases? May 05, 2024 am 09:06 AM

What are the application scenarios of Java enumeration types in databases?

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

How to fix mysql_native_password not loaded errors on MySQL 8.4

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

How to use MySQL stored procedures in PHP?

Performance optimization strategies for PHP array paging Performance optimization strategies for PHP array paging May 02, 2024 am 09:27 AM

Performance optimization strategies for PHP array paging

See all articles