Sharing of classic cases of E-Commerce orders

Time:2022-5-10

1. When the amount of order data is large or the query statistics are complex enough, MySQL query has a bottleneck

Solution:

  • Step 1: database table design and index optimization (including strict control of field length, vertical table splitting, field redundancy, index coverage and Optimization)
  • Step 2: split query business, separate paging data query from statistical query, and reduce the impact of statistical query on business operation (generally, the business of query statistical results is not common)
  • Step 3: when the data scale is large, the slow query is mainly reflected in the query statistics. The result cache can be added to the slow interface of the system to alleviate the pressure on the database caused by slow SQL query
  • Step 4: adopt the MySQL + NoSQL architecture scheme to synchronize the data in MySQL to other media, such as es. Use other search engines to assist query. Practice scheme: Canal + Kafka + elasticsearch to realize order query

2. The problem of policy quantity deduction. The performance of redis distributed lock or MySQL for update is poor in the case of concurrency

Solution:

  • Step 1: the policy deduction adopts redis incrby increment to record the used quantity. Before deduction, use incrby to increase the amount to be deducted, and then compare it with the maximum limit of the strategy. If it meets the requirements, continue the deduction process and add a deduction daily record. Otherwise, roll back and use redis decrby to automatically reduce the quantity
  • Step 2: use the scheduled task to synchronize the value of redis to the actual used quantity of the policy (ensure the final consistency according to the actual business control frequency). Because the program increases first and then processes the subsequent logic, there must be exceptions. In case of exceptions, the value recorded by redis is greater than the actual used quantity
  • Step 3: when the quantity is synchronized with MySQL, if it is found that the redis value reaches the upper limit of usage, the policy deduction flow records will be aggregated, and then finally confirmed with the value in redis. Once it is found that there is still a surplus, the redis value will be restored and the remaining quantity will be returned.

    Sharing of classic cases of E-Commerce orders

    image.png

3. How does Kafka consumer prevent message loss?

Solution:

  • Establish a message processing failure retry mechanism and an early warning notification mechanism

    Sharing of classic cases of E-Commerce orders

    image.png

4. How to ensure the order of Kafka consumption news?

Solution:

  • Scheme 1: kafkaconsumer instance + multiple worker threads + one thread corresponds to a blocking queue consumption thread model (production side: Kafka can write certain types of messages to the same partition through the partitionkey, and a partition can only correspond to one consumption thread)
  • Scheme 2: add the message ID or message timestamp to the message content, verify the message every time it is consumed (redis can be used to cache the latest ID), and discard the delayed message directly.

5. Client jitter, fast operation, network communication or slow server response may cause repeated processing by the server and eventually generate duplicate data. For example, generate multiple configured policies

Solution:

  • Scheme 1: the database table establishes a unique index, and the database acts as the last line of Defense (some function operations lack uniqueness rules, so scheme 1 cannot achieve the expected effect)
  • Scheme 2: lock the combined key value of [MD5 (method path + input parameter)] (you can realize rapid project integration with AOP annotation). If the locking fails, an error message will be returned, and the internal execution method still needs to be verified according to the business rules

6. Optimization of paging query performance of large data list (the working principle of limit is to first read the first n records, then discard the first n records and read the last m desired ones, so the greater the N, the greater the offset, and the worse the performance)

Solution:

  • Scheme 1: use the indexed list or primary key to perform the order by operation, record the last returned primary key, and use the primary key for filtering in the next query; (applicable to search scenarios in page order)
  • Scheme 2: use sub query to realize paging (applicable to SQL query for inspection, support page skipping query, and the performance is not as good as scheme 1)
SQL code 1: average time 3.3 seconds select * from OMS_ order order by order_ code LIMIT 100000, 10
 
SQL code 2: average time: 0.2 seconds select * from OMS_ order WHERE order_ code >= (SELECT order_code FROM  ec_oms_order ORDER BY order_code LIMIT 100000 , 1) order by order_ code LIMIT 10
  • Ultimate solution: reduce unnecessary paging queries or limit the maximum page number that can be paged (it is recommended to control within 100 pages) or only provide sequential paging (such as sliding paging of mobile app)

7. Be good at using data cache to reduce the pressure of MySQL query. In addition to redis cache, the use of mybatis level-1 cache and ThreadLocal in the same transaction reduces database query operations

Solution:

  • Scheme 1: use redis cache to cache infrequently modified data, such as stores, commodities, dictionaries, etc. (how to ensure the data consistency between the database and redis?)
  • Scheme 2: enable the first level cache of mybatis. In the same transaction, the same SQL statement will first hit the first level cache to avoid direct query to the database and improve performance
  • Scheme 3: use ThreadLocal to realize thread data sharing, which can reduce parameter transmission and improve code cleanliness (ThreadLocal data should be cleaned up after use, and it is recommended to clean up before using the method)

8. How to deal with distributed transactions in multi system data interaction?

Solution:

  • Scheme 1: system a introduces the local message table or business table and adds a status field to record the call result of system B. system a operates in its own local transaction, inserts a piece of data into the message table (or update status), scans the message table regularly, triggers the system B interface to return the result and updates the message table (or update status). If system B fails to process, it will retry n times. If not, it will give up and trigger the alarm notification [premise: the receiver interface ensures idempotency, allows delay, and does not rely on the receiver’s response results to immediately carry out other business operations]
  • Scheme 2: reliable MQ. This scheme is the same as scheme 1. It only changes the scheduled task and message table into MQ consumption [difficulty: how to ensure that messages are not lost? It is also only applicable to the scenario where delay is allowed and other business operations are carried out immediately without relying on the response results of the receiver]
  • Scheme 3: TCC transaction【course】(the disadvantage is that the amount of code is doubled, which is somewhat invasive)
  • Scheme 4: Seata (at mode: read uncommitted isolation level (global lock to ensure isolation), and the transformation of old projects is at great risk. XA mode: a business mode that uses transaction resources (database, message service, etc.) to support XA protocol and uses the mechanism of XA protocol to manage branch transactions)

practical experience: 99% of the distributed interface calls do not do distributed transactions, but directly monitor (send e-mail and text messages), record logs (complete logs in case of errors), quickly locate, troubleshoot, find solutions and repair data afterwards.

9. Jackson, the underlying logic in the project, will convert the numeric null value to 0 (MS common web encapsulated logic), but in some scenarios, 0 has special significance and needs to maintain the original null value

Solution:

  • The custom serializer jsonserializer adds custom serialization to the fields that need to maintain null values to achieve flexible control without affecting the existing project configuration.

    Sharing of classic cases of E-Commerce orders

    image.png

    Sharing of classic cases of E-Commerce orders

    image.png

10. Query immediately after the new after-sales order is created. During the promotion period, there is a prompt that no data can be found. After investigation, the reason is that the master-slave database is delayed

Solution:

  • For the scenario that can be found immediately after writing, the way of forced reading of the main database is adopted

11. API interface signature verification (to prevent tampering and replay attacks)

  • Define public authentication parameters (parameters that all interfaces must have. Parameters can be stored in HTTP headers [high priority] or URL link parameters

    Sharing of classic cases of E-Commerce orders

    image.png
  • Signature generation method
    ① The service request method of all business interfaces is post, and the request parameter type is content type = Application / JSON
    ② The sign signature character generation rule is MD5 (secrect + client + cuid + format + time + version + requstbody) toJSONString() + secrect). toLowerCase(); [secrect secret key]

Specific implementation:

  • Client: generate a signature sign according to the above requirements and store it in the request headers together with the above parameters
  • Server: verification process

    Sharing of classic cases of E-Commerce orders

    image.png