Article From:https://segmentfault.com/q/1010000011712946
Question:
SELECT orders.GUIZI_NO AS cabNo, CAST(SUM(ordinPost.amount) AS DECIMAL(14,2)) AS orderMoney
FROM
( 
  SELECT order_water_code, amount
  FROM wallet_pay_post_record t
  WHERE t.create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND t.`status` = 1 
) ordinPost 
INNER JOIN guizi_order orders ON ordinPost.order_water_code = orders.TRADE_WATER_NO
GROUP BY orders.GUIZI_NO
HAVING orderMoney < '200.00'

wallet_pay_post_record This table has more than 100 million pieces of data, and the query statistics are slow. Asking create_time to add an index doesn’t seem to improve much speed either.

Implementation plan

Answer 0:

sqlOptimize logically
1 In the inner layer, the wallet_pay_post_record table is operated by group according to the order_water_code field to reduce the amount of data in INNER JOIN
2 If wallet_pay_post_record and guizi_order are many-to-one relationships, the amount & lt; 200 condition can be moved to the inner layer to deal with first.

  SELECT order_water_code, sum(amount) as amount
  FROM wallet_pay_post_record t
  WHERE t.create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND t.`status` = 1 
  and t.amount < 200
  group by order_water_code
  having amount < 200

Answer 1:

Single table 100 million, sub table should be.
Another single table 100 million, you just take the last 30 days of the record, 30 days, how many records? If you take up 100/10 of the total, the efficiency of indexing will be good.
If the index is fixed, you can compose the index (status, create_time), otherwise you can have a create_time single-column index.
Finally, orders.TRADE_WATER_NO must be indexed.

Answer 2:

Optimization suggestion: do not operate in SQL.

Answer 3:

Using space to change time, you can write multiple SQL statements separately.

Answer 4:

1.Modify DATE_SUB, use create_time> = ‘…’ and create_time < = ‘…’.
2.orderMoney < ‘200.00’ Change to orderMoney < 200.
It is better to send out explain information.

Answer 5:

Do not use database function.

Link of this Article: SQL optimization problem

Leave a Reply

Your email address will not be published. Required fields are marked *