Mysql runs on several important points about how to run mysql that processes millions of data in batches.

Mysql runs on several important points about how to run mysql that processes millions of data in batches.
Several important knowledge points for processing millions of data in large batches:1. Set the memory configuration for php running ini_set ("memory_limit", "1200 M ");

　　The following configurations are available in php. ini:

; Maximum input variable nesting level
Http://php.net/max-input-nesting-level
; Max_input_nesting_level = 64

; How many GET/POST/COOKIE input variables may be accepted
; Max_input_vars = 1000

; Maximum amount of memory a script may consume (128 MB)
Http://php.net/memory-limit
Memory_limit = 100 M

There will be a default configuration, resulting in an error when running to a certain number of times, unable to run.

Ii. Set the maximum script execution time set_time_limit (int $seconds)

Detailed Method

 

3. Do not set the cache result mysql_unbuffered_query

It has great advantages in processing large volumes of data,

(PHP 4> = 4.0.6, PHP 5)

Mysql_unbuffered_query-a row that sends an SQL query to MySQL and does not obtain or cache the result.

Description: resource mysql_unbuffered_query (string $query[, Resource $link_identifier])

Mysql_unbuffered_query () sends an SQL query to MySQLqueryBut it does not automatically obtain and cache the result set like mysql_query. On the one hand, this will save a considerable amount of memory in processing large result sets. On the other hand, you can perform operations on the result set immediately after obtaining the first line, instead of waiting until the entire SQL statement is executed. When multiple databases are connected, you must specify an optional parameter.link_identifier.

Note:

The benefit of mysql_unbuffered_query () Is that mysql_unbuffered_query () and mysql_data_seek () cannot be used on the result set returned by mysql_unbuffered_query (). In addition, before sending a new SQL query to MySQL, you must extract the result rows generated by all non-cached SQL queries.

See mysql_query ()

 

4. cancelling variable cache unset ($ x)

At the end of the loop, use the unset method to release the memory occupied by the variable.

 

 

Code example (using pdo ):

$ Pdo = new PDO ("mysql: host = 127.0.0.1; dbname = aa", "root", "root"); $ pdo-> setAttribute (PDO: MYSQL_ATTR_USE_BUFFERED_QUERY, false); ini_set ("memory_limit", "1200 M"); set_time_limit (0); $ SQL = 'select url, num from cas_key limit 100000 '; $ rs = $ pdo-> query ($ SQL); while ($ value = $ rs-> fetch () {unset ($ xxx ); unset ($ xxx);} var_dump (memory_get_usage (); // view the memory usage method

 

 

 

If you have more optimization methods, please kindly advise!