Shulou(Shulou.com)06/01 Report--

cause

The developer reports that the data size of a table is already 130G, which has an impact on the physical storage space, and it is not easy to make database ddl changes. After consulting the development of relevant business logic, in the e-commerce business system, there will be a corresponding order logistics information after each order transaction, so it is necessary to design a logistics-related table to store the logistics node information of the order, and the table uses text fields to store logistics information.

Approximate table structure:

CREATE TABLE `goods_order_ express` (

`id`int (10) unsigned NOT NULL AUTO_INCREMENT

`express_ id` int (10) unsigned NOT NULL

`message` varchar (200) NOT NULL

`status` varchar (20) NOT NULL

`state`tinyint (3) unsigned NOT NULL

`data`text NOT NULL

`created_ time`int (10) unsigned NOT NULL

PRIMARY KEY (`id`)

KEY `idx_ expid` (`express_ id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

Business analysis

Every time a courier arrives at a transit station or an event such as delivery or reception occurs, the api of the express company will generate information in the following format (remove business-related sensitive data)

[{"time": "2016-03-16 11:16:20", "ftime": "2016-03-16 11:16:20", "context": "Chengdu TD customer one Company in Chengdu, Sichuan Province has issued, next station Chengdu Transit Center", "areaCode": "areaName": "," status ":" on the way "}, {" time ":" 2016-03-16 11:11:03 "," ftime ":" 2016-03-16 11:11:03 " "context": "Sichuan Chengdu TD customer Company has packed", "areaCode": "areaName": "," status ":" on the way "}, {" time ":" 2016-03-16 11:08:09 "," ftime ":" 2016-03-16 11:08:09 "," context ":" Sichuan Chengdu TD customer Company has collected "," areaCode ":" areaName ":", "status": "pickup"]

The json string 411 characters, the development of business programs to periodically rotate to call the relevant api information, and the above json string data insert or update to the data field of goods_order_express. And the table has never been deleted from the beginning to the present, accumulating all the data from the initial to the present. With the explosive growth of the company's business, the watch will be bigger and grow faster in the future. The disk space of the database server is insufficient, and the table structure change is difficult to operate.

How to optimize?

1 can you reduce the amount of data written?

And business analysis, we can't discard the new data. However, every piece of logistics information actually has a life cycle, and its life cycle can be completed from delivery to receipt, that is, the data can no longer be displayed, and we basically do not view a logistics information that has been received. So you can archive historical data, such as backing up data from 90 days ago to hbase and deleting it from the MySQL database, thus keeping the size of the table within a reasonable range.

2 reduce the size of data field data

A reduce the json string data and retain the valid data

Time and ftime are the same, and developers confirm that ftime has no functional use, and areaCode areaName has no logical meaning in our logistics display system.

So simplify the json data as follows

[{"time": "2016-03-16 11:16:20", "context": "Chengdu TD customer one Company has issued, next stop Chengdu Transit Center", "status": "on the way"}, {"time": "2016-03-16 11:11:03", "context": "Sichuan Chengdu TD customer one Company has packed", "status": "on the way"}, {"time": "2016-03-16 11:08:09" "context": "A company of TD customers in Chengdu, Sichuan Province has collected", "status": "pickup"}]

After simplification, the number of characters occupied is reduced from 411 to 237, a reduction of 47% of the data.

B evaluate the number of logistics nodes

I believe everyone has the experience of online shopping. In general, express delivery contains about 15-20 node information.

{"time": "2016-03-16 11:16:20", "context": "Chengdu transfer Center, next stop", "status": "on the way"} occupy 85, we evaluate according to 100 characters, logistics information up to 20-100000 characters, the use of varchar (2048) should be able to meet the normal demand.

C some people may say that there are always exceptions, so let's analyze from this exception what if a logistics has 30 or 40 nodes?

From Shenzhen to Mohe in Heilongjiang or Urumqi in Xinjiang to Hangzhou, it is estimated that there will be more node information in Shanghai. For more than 20 nodes, we will not pay attention to the information of the 10th, 11, 14, 15 nodes. What is your focus on express delivery? Does the merchant deliver the goods? Is the express company looking for parts? Whether the express delivery arrives at the last kilometer of the destination. At this point, we can shrink more than 25 / 30 nodes, remove the intermediate non-core node information, and meet the design of our varchar (2048) without affecting the user experience.

3 sub-library sub-table

This is a plan that we have no choice but to do. Now, although all kinds of middleware are relatively mature, such as cobar,oneproxy, mycat and other reliable software, for a start-up company, we still lack the corresponding distributed database management tools, how to change 1024 tables? In fact, this is also a relatively difficult problem.

Summary

After a series of analysis and optimization, we finally converted the text field to varchar (2048), which is currently running well online. Reviewing the above optimization process is based on an in-depth understanding of business logic and logistics-related knowledge and more analysis of user behavior, which does not require advanced database knowledge. But in fact, developers often simply and rudely accept the functional design concept of pd, regardless of the impact on the underlying infrastructure. In fact, we only need to take one more step forward, we can do better, but this step may be a small step for good programmers and a big step for some people.

Here is a question: how to treat and solve the technical debt caused by the rapid iteration of development?

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.