Query and optimization of 10 million orders in mysql at a time, and summary of mysql orders at a time

Query and optimization of 10 million orders in mysql at a time, and summary of mysql orders at a time

　

The company's order system has a large daily order volume, with a large amount of table data exceeding 10 million. Company SQL optimization is not doing well, it can be said that it is not done, so the query is very slow.

 

Question

Excerpt a sentence from a functionSQL EXPLAINView execution plan

EXPLAIN + SQL view SQL Execution Plan

An index is useless, and the affected rows are close to 20 million. It is no wonder it will slow down.

 It is estimated that several sheets of A4 paper are printed in the original SQL statement. I will make a simplified version.

SELECT  COUNT(t.w_order_id) lineCount,  SUM(ROUND(t.feel_total_money / 100, 2)) AS lineTotalFee,  SUM(ROUND(t.feel_fact_money / 100, 2)) AS lineFactFeeFROM  w_orders_his tWHERE 1=1AND DATE_FORMAT(t.create_time, '%Y-%m-%d') >= STR_TO_DATE(#{beginTime},'%Y-%m-%d') AND DATE_FORMAT(t.create_time, '%Y-%m-%d') <= STR_TO_DATE(#{endTime},'%Y-%m-%d')AND t.pay_state = #{payState}AND t.store_id LIKE '%#{storeId}%'limit 0,10

This SQL statement requires that orders of the specified time and condition be retrieved from the 20 million table for total amount summary.

To optimize SQL statements, you need to optimize the SQL statements based on the company's business and technical architecture.SQLThere are differences in optimization.

 

Optimization Point 1:

AND DATE_FORMAT(t.create_time, '%Y-%m-%d') >= STR_TO_DATE(#{beginTime},'%Y-%m-%d') AND DATE_FORMAT(t.create_time, '%Y-%m-%d') <= STR_TO_DATE(#{endTime},'%Y-%m-%d')

We know that SQL definitely needs to reduce the use of functions, like DATE_FORMAT (t. create_time, '% Y-% m-% D') is absolutely forbidden. If the database has 1 million data records, it will execute 1 million functions, which greatly affects efficiency. The STR_TO_DATE (# {beginTime}, '% Y-% m-% D') function on the right will be executed once, but the function is not recommended. So remove the function and use it directly.>=, <=OrBETWEEN ANDThe speed will be much faster, but some database design time fields only have date and no time, so we need to splice the time after the date, for example:"" + "00:00:00".

A better way is to use the timestamp, store the timestamp in the database, AND then compare it with the timestamp, such as BETWEEN 'start timestamp 'AND 'end timestamp'

 

Optimization Point 2:

AND t. store_id LIKE '% # {storeId} %'

This sentence is usedLIKEIn addition, the pre-and post-match will cause index failure, which is generally avoided and should be changedAND t. store_id LIKE '# {storeId} %'

 

Optimization Point 3:

Generally, an index is used to query a record based on the primary key and unique index, and the query speed is extremely fast even for hundreds of millions of data records. However, this SQL statement needs to be used to query data statistics.COUNTAndSUMTherefore, you can create a joint index.

Note that the key index (a, B, c) supports combination of a, B, B, and c, but does not support combination of B, c. When the leftmost field is a constant reference, the index is very effective.

Therefore, place the required fields on the left.Key index (create_time, w_order_id, feel_total_money, feel_fact_money, payState, storeId)

Result

The optimization was a few minutes ago, but now it is in milliseconds. In fact, there are not many changes, so we can avoid making good use of statements.EXPLAINQuerySQLEfficiency.

If you have time, I will give you something else.SQLExample of Optimization

　　　　　　

Which of the following can be optimized?

• The JOIN condition must be an index, preferably a unique index. Otherwise, once a large number of data is inserted
• Uniion on is generally prohibited unless the number of records before and after union on is very small.
• Disable OR
• You can check the total number by using COUNT (*). MYSQL will automatically optimize it without the need for COUNT (ID ).
• The database field is not null. The smaller the field type INT> VARCHAR, the better.
• SELECT * is forbidden. You need to determine the fields used.
• Generally, it is not used in SQL for numerical calculation.
• SQL is concise and clear

　　　　　　

Reference

EXPLAINType (from top to bottom, performance from poor to good)

• All full table Query
• Full index Scan
• Range index range scan
• Ref uses a non-unique or unique index prefix scan to return records with the same value
• Eq_ref uses a unique index and returns only one record
• Const, the system table can only match one row at most, and query based on the unique index or primary key
• If null is not used to access a table or index, you can directly obtain the result.

 

Five major MYSQL Engines

• ISAM: Fast Reading without occupying memory and storage resources. It does not support transactions and cannot be fault-tolerant.
• MyISAM: Read block, more extensions.
• HEAP: temporary tables residing in the memory, which is faster than ISAM and MyISAM. Data is unstable, and data is lost if it is shut down and not saved.
• InnoDB: supports transaction and Foreign keys, and the speed is not as high as the previous engine block.
• Berkley (BDB): it supports transaction and Foreign keys and is not as fast as the previous engine block.

Generally, the transaction is set to InnoDB, and the other is set to MyISAM.