(select ctime,... The from ` ni000_kline ` where ctime>=1514466000 and ctime<1545980340)
UNION all
(select ctime,... The from ` SR000_kline ` where ctime>=1514466000 and ctime<1545980340)
The order by ctime asc

Demand is that there are two tables data structures have been deposited to a futures varieties of k line data, now need to extract the data scale for 2018 years to write files, look
"

CodePudding user response:

Ctime is index

CodePudding user response:

Around 110 w two tables of data integrated, the data query result is 19 w

CodePudding user response:

Select * from tt where id in

(
(select id (this is the primary key) from ` ni000_kline ` where ctime>=1514466000 and ctime<1545980340)
UNION all
(select id (this is the primary key) from ` SR000_kline ` where ctime>=1514466000 and ctime<1545980340)
)



Check out how large result sets returned:
Innodb data is stored in the primary key index, and so a full table scan was actually scanning table's primary key index directly, so the whole scan to each line can be directly in the result set, and then returned to the client,
In fact, the server does not need a complete result set, take the data and send data process is like this:
1, in a line, wrote net_bufffer, the memory size is defined by the cords net_buffer_length, by default, 16 k
2, repeated access lines, until net_buffer write full, send call network interface,
3, if send successful, empty net_buffer, then continue to take the next line, and write net_buffer,
4, if send function returns eagain or wsaewouldblock, said local network stack with the socket send buffer, to wait for, until the network stack to write again, to continue to send,

From the process can see
1, in the process of send a query, occupy the largest mysql internal memory is net_buffer_length so big, will not be up to 20 g (need to the size of the data)
2, the socket send buffer is not likely to reach 20 g (defined by default/proc/sys/net/core/wmem_default), if the socket send buffer write full, will suspend read data process,
Meaning that mysql is reading while hair, means that if the client receives slowly can lead to a mysql server due to the results, but did not report this transaction execution time longer,

If you want to reduce in sending to the client in this state, will net_buffer_length parameter is set to a larger value is an optional scheme,
Query the status of the change is this has nothing to do (omit) :
1, after the mysql query execution stage, first of all, the state set to sending data;
2, and then send the results of column related information (meta data) to the client
3, continue to execute a statement process
4, the execution is completed, the status is set to an empty string,
That is sending the data does not necessarily mean is sending data, and may be at any stage in the process of actuators,
Only when a thread to wait for the client receiving results state, sending will not be visible until the to the client, and if the show sending data mean just being executed,
The innodb_buffer_pool_size general advice is set to 60% or 80% of the available physical memory,
Inndob memory management is the Least Recently Used algorithm (further Recently, informs, LRU) algorithm, the algorithm core is to eliminate the longest unused data,