Streaming query and cursor query methods in MySQL (summary sharing)
This article brings you relevant knowledge about mysql. It mainly introduces the streaming query and cursor query methods in MySQL. It has a good reference value and I hope it will be helpful to everyone. .
Recommended learning: mysql video tutorial
1. Business scenario
Now the business system needs to start from the MySQL database Read 500w data rows for processing
- Migrate data
- Export data
- Batch processing data
Second, list the following three A processing method
- Regular query: read 500w data into JVM memory at one time, or read in pages
- Streaming query: read one piece at a time and load it into JVM memory. Business processing
- Cursor query: Like streaming, control how many pieces of data are read at a time through the fetchSize parameter
2.1 General query
By default, the complete retrieval result set is stored in memory. In most cases, this is the most efficient way to operate and is easier to implement.
Assuming that the data volume of a single table is 5 million, no one will load it into the memory at one time, and paging is generally used.
Here, the test demo is just to monitor the JVM, so paging is not used and the data is loaded into the memory at one time
@Test
public void generalQuery() throws Exception {
// 1核2G：查询一百条记录：47ms
// 1核2G：查询一千条记录：2050 ms
// 1核2G：查询一万条记录：26589 ms
// 1核2G：查询五万条记录：135966 ms
String sql = "select * from wh_b_inventory limit 10000";
ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery(sql);
int count = 0;
while (rs.next()) {
count++;
}
System.out.println(count);
}JVM monitoring
We will reduce the memory size -Xms70m -Xmx70m
During the entire query process, the heap memory usage gradually increases, and eventually leads to OOM:
java.lang.OutOfMemoryError: GC overhead limit exceeded
1. Frequently triggering GC
2. There is a hidden danger of OOM
2.2 Streaming query
One thing to note about streaming queries: all rows in the result set must be read (or closed) before any other queries can be issued on the connection, otherwise an exception will be thrown and its query will exclusively occupy the connection.
From the test results, streaming query does not improve the query speed
@Test
public void streamQuery() throws Exception {
// 1核2G：查询一百条记录：138ms
// 1核2G：查询一千条记录：2304 ms
// 1核2G：查询一万条记录：26536 ms
// 1核2G：查询五万条记录：135931 ms
String sql = "select * from wh_b_inventory limit 50000";
statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = statement.executeQuery(sql);
int count = 0;
while (rs.next()) {
count++;
}
System.out.println(count);
}JVM monitoring
We will reduce the heap memory to -Xms70m -Xmx70m
We found that even though the heap memory was only 70m, OOM still did not occur
2.3 Cursor query
Note:
1. Need to splice parameters in the database connection information useCursorFetch=true
2. Secondly, set the number of data read by Statement each time, such as reading 1000 at a time
Judging from the test results, cursor query has shortened the query speed to a certain extent## We will reduce the heap memory - Xms70m -Xmx70m We found that in a single-threaded situation, cursor query and streaming query can avoid OOM very well, and cursor query can optimize query speed. ##3.1 RowDataStatic First, it will check whether there is data in its internal buffer that has not been returned. If there is, return the next row. The default RowDataStatic reads all The data is transferred to the client memory, which is our JVM; JDBC Client -> Client Socket -> MySQL -> Retrieve data return -> MySQL Kernel Socket Buffer -> Network -> Client Socket Buffer -> JDBC Client General query will load all the data queried into the JVM and then process it. If the amount of query data is too large, it will continue to experience GC, and then there will be a memory overflow The server is ready to return from the first piece of data When the data is loaded into the buffer, the data is loaded into the kernel buffer of the client machine through the TCP link. The inputStream.read() method of JDBC will be awakened to read the data. The only difference is that the stream is turned on. When reading, only one package size of data is read from the kernel each time, and only one row of data is returned. If one package cannot assemble one row of data, another package will be read. When the cursor is turned on, when the server returns data, it will return the data according to the size of fetchSize, and the client will return the data every time when receiving the data. Change the buffer data and read all the data. If the data has 100 million data, if FetchSize is set to 1000, 100,000 round-trip communications will be performed; Because MySQL does not know when the client has finished consuming the data , and its own corresponding table may have DML write operations. At this time, MySQL needs to create a temporary space to store the data that needs to be taken away. So when you enable useCursorFetch to read a large table, you will see several phenomena on MySQL: Concurrent calls: Jmete 10 threads concurrent calls in 1 second Streaming query memory performance report is as follows Concurrent calls are also OK for memory usage and do not exist Stacked increase The cursor query memory performance report is as follows 1. Both cursor query and streaming query can avoid OOM in a single thread; 2. Cursor query is faster than streaming query in terms of query speed. Compared with ordinary query, streaming query cannot shorten the query. Time; 3. In concurrent scenarios, the trend of streaming query heap memory is more stable, and there is no additive increase. Recommended learning: mysql video tutorial The above is the detailed content of Streaming query and cursor query methods in MySQL (summary sharing). For more information, please follow other related articles on the PHP Chinese website!@Test
public void cursorQuery() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
// 注意这里需要拼接参数，否则就是普通查询
conn = DriverManager.getConnection("jdbc:mysql://101.34.50.82:3306/mysql-demo?useCursorFetch=true", "root", "123456");
start = System.currentTimeMillis();
// 1核2G：查询一百条记录：52 ms
// 1核2G：查询一千条记录：1095 ms
// 1核2G：查询一万条记录：17432 ms
// 1核2G：查询五万条记录：90244 ms
String sql = "select * from wh_b_inventory limit 50000";
((JDBC4Connection) conn).setUseCursorFetch(true);
statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(1000);
ResultSet rs = statement.executeQuery(sql);
int count = 0;
while (rs.next()) {
count++;
}
System.out.println(count);
}
3. RowDataThe logic of ResultSet.next() is to implement the class ResultSetImpl and get it from RowData every time The data for the next row. RowData is an interface, and the implementation relationship diagram is as follows
If all reading is completed, trigger a new request to MySQL Server to read the fetchSize quantity result
4.1 generalQuery General query
4.2 streamQuery streaming query
4.3 cursorQuery cursor query
5. Concurrency scenarios
6. Summary
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
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
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: An Introduction to the World's Most Popular Database
Apr 12, 2025 am 12:18 AM
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's Place: Databases and Programming
Apr 13, 2025 am 12:18 AM
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
How to connect to the database of apache
Apr 13, 2025 pm 01:03 PM
Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.
Why Use MySQL? Benefits and Advantages
Apr 12, 2025 am 12:17 AM
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.
How to start mysql by docker
Apr 15, 2025 pm 12:09 PM
The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
Laravel Introduction Example
Apr 18, 2025 pm 12:45 PM
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
How to install mysql in centos7
Apr 14, 2025 pm 08:30 PM
The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting
