Hello everyone, I encountered the following problem in the project: the example of using the combined primary key when the hibernate ing table is as follows: Product table: Item, the code for the primary key as the Union primary key ItemId is as follows: publicclassItemId {@ columnLongitemId; @ columnLonguserId;} @ Table (...) publicclassItem {@ Id
Hello everyone, I encountered the following problem in the project: the example of using the combined primary key when the hibernate ing table is as follows: Product table: Item, the code for the primary key as the Union primary key ItemId is as follows: public class ItemId {@ column Long itemId; @ column Long userId;} @ Table (...) public class Item {@ Id
MySQL row subquery statements
Hello everyone, I encountered the following problems in the project:
Use the Union primary key when hibernate maps tables.
Example:
Item table: Item. The primary key is the associated primary key ItemId.
The Code is as follows:
public class ItemId{ @column Long itemId; @column Long userId;}@Table(...)public class Item{ @Id ItemId id; @column Date createDate; @column boolean deleted; ...}
The three manually created indexes are as follows:
ItemId primary key index
UserId primary key index
ItemId and userId join Indexes
The database table is InnerDB.
An example of updating a statement is as follows:
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();}
Use the above update statement hibernate to generate the following SQL statement
Update item set delete =? Where (itemId, roleId) = (?, ?)
It is very time-consuming to query this SQL statement in the background. When the data volume of the item table reaches the million-level, it requires 2 ~ 3 seconds
Through the detailed parameters returned by the query, we can see that Mysql has performed a full table query and locked the table, and no index is used.
Then, to solve the online problem, make the following changes:
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();}
The statement generated by hibernate is as follows:
Update item set delete =? Where itemId =? And roleId =?
The efficiency immediately went up. The index was used, and the update only took milliseconds.
Therefore, there are two problems:
1. Why does hibernate generate
Update item set delete =? Where (itemId, roleId) = (?, ?)
Instead
Update item set delete =? Where itemId =? And roleId =?
2. Why are the efficiency of the preceding two SQL statements in mysql different?
Why?
Note:
The Mysql official documents describe row subqueries as follows:
References the discussion of this point is a scalar or column subquery, that is, a subquery that returns a single value or a column value. A row subquery is a subquery variable that can return a single row. Therefore, more than one column value can be returned. The following are two examples:
SELECT * FROM t1 WHERE (1, 2) = (SELECT column1, column2 FROM t2 );
SELECT * FROM t1 where row (1, 2) = (SELECT column1, column2 FROM t2 );
If column1 = 1 and column2 = 2 in a row of table t2, the query result is TRUE.
Expressions (1, 2) and ROW (1, 2) are sometimes called ROW constructors. The two are equivalent, and they are also valid in other contexts. For example, the following two statements are semantically equivalent (but currently only the second statement can be optimized ):
SELECT * FROM t1 WHERE (column1, column2) = (1, 1 );
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
The row constructor is usually used to compare with subqueries that can return two or more columns. For example, the following query can reply to the request, "Search for all rows that also exist in Table t2 in Table t1 ":
SELECT column1, column2, column3
FROM t1
WHERE (column1, column2, column3) IN
(SELECT column1, column2, column3 FROM t2 );
