Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to use streaming queries and compare ordinary queries for MySQL performance testing", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "how to use streaming queries and compare ordinary queries for MySQL performance testing" bar!

I. Preface

When the program accesses the MySQL database, when the amount of data queried is very large, the database driver loads all the loaded data into memory, which may lead to memory overflow (OOM).

In fact, streaming query is provided in MySQL database, which allows qualified data to be loaded into memory in batches and partially, which can effectively avoid OOM.

Second, JDBC implements streaming query.

Streaming queries can be achieved by setting the setFetchSize method of JDBC's PreparedStatement/Statement to Integer.MIN_VALUE or using the method Statement.enableStreamingResults (). When the ResultSet.next () method is executed, it will be returned one by one through the database connection, so that it will not take up a lot of client memory.

Public int execute (String sql, boolean isStreamQuery) throws SQLException {Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; int count = 0; try {/ / get database connection conn = getConnection (); if (isStreamQuery) {/ / set streaming query parameter stmt = conn.prepareStatement (sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY) Stmt.setFetchSize (Integer.MIN_VALUE);} else {/ / ordinary query stmt = conn.prepareStatement (sql);} / / execute the query to get the result rs = stmt.executeQuery (); / / traverse the result while (rs.next ()) {System.out.println (rs.getString (1)) Count++;}} catch (SQLException e) {e.printStackTrace ();} finally {close (stmt, rs, conn);} return count;}

PS: in the above example, the parameter isStreamQuery is used to switch between streaming query and normal query, which is used for the following test comparison.

Third, performance testing

A test table my_test is created for testing, with a total of 27w pieces of data, which are tested using the following four test cases:

General query with large amount of data (27w articles)

Streaming query with large amount of data (27w articles)

General query with small amount of data (10 items)

Small data streaming query (10 items)

3.1. Test large amount of data common query @ Testpublic void testCommonBigData () throws SQLException {String sql = "select * from my_test"; testExecute (sql, false);} 3.1.1. Query time

27w data takes 38 seconds

3.1.2. Memory footprint

Use nearly 1 GB of memory

3.2. Test streaming query @ Testpublic void testStreamBigData () throws SQLException {String sql = "select * from my_test"; testExecute (sql, true);} 3.2.1. Query time

27w data takes 37 seconds

3.2.2. Memory footprint

Because it is acquired in batches, there is a fluctuation of 30-270m.

3.3. Test small amount of data common query @ Testpublic void testCommonSmallData () throws SQLException {String sql = "select * from my_test limit 100000, 10"; testExecute (sql, false);} 3.3.1. Query time

It takes 1 second to use 10 pieces of data

3.4. Test small data streaming query @ Testpublic void testStreamSmallData () throws SQLException {String sql = "select * from my_test limit 100000, 10"; testExecute (sql, true);} 3.4.1. Query time

It takes 1 second to use 10 pieces of data

Thank you for your reading, the above is the content of "how to use streaming queries and compare ordinary queries for MySQL performance testing". After the study of this article, I believe you have a deeper understanding of how to use streaming queries and compare ordinary queries for MySQL performance testing, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.