What are the points for attention in Mysql large table query

This article mainly introduces Mysql big table query points for attention, the article is very detailed, has a certain reference value, interested friends must read it!

When executing a query, Mysql defaults to load all the results into memory before returning them (this mode can be understood as Oracle's ALL_ROWS optimization mode). If the table data is too large, it will cause memory overflow.

1. When mysql console connects to the database:

Add the-Q option, mysql-h hostname-u root-p-Q

two。 When jdbc connects to the database:

Add useCursorFetch=true to the connection string

In the created statement, add setFetchSize, as shown in

Stmt = conn.createStatement (java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY)

Stmt.setFetchSize (Integer.MIN_VALUE)

Note: The Integer.MIN_VALUE is used by the MySQL driver as a signal to switch to streaming result set mode. It is not used as a value.

See the documentation, under "Resultset". In summary:

By default, ResultSets are completely retrieved and stored in memory. You can tell the driver to stream the results back one row at a time by setting stmt.setFetchSize (Integer.MIN_VALUE); (in combination with a forward-only, read-only result set).

The above is all the contents of the article "what are the points for attention in the query of MySQL large table". Thank you for your reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!