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

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads.

MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

Building an SQL database involves 10 steps: selecting DBMS; installing DBMS; creating a database; creating a table; inserting data; retrieving data; updating data; deleting data; managing users; backing up the database.
