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

I. background of the programme

Order system exists in various industries, such as e-commerce orders, bank flow, operator phone bills and so on. It is a very extensive and general system. For this kind of system, a classic practice has been formed in the past decade. However, with the development of the Internet and enterprises' attention to data, there are more and more orders that need to be stored and persisted. The importance of data and the expansion of data scale have brought new challenges.

Demand scenario

An e-commerce platform A needs to persist the order data generated by all platforms. At the same time, based on all the order data, the system needs to provide diversified query services for a variety of roles: consumers, shopkeepers and platforms. Consumers can query their own historical orders, merchants can count the best-selling products, and the platform can also analyze user behavior, platform transaction scale and so on. The main query methods include multi-dimensional retrieval of orders, as well as analysis and statistics of order data, such as:

For consumers: [a consumer] * [nearly 1 year] * [sell computers] order inquiry

For salesperson: [B salesperson] * [nearly 1 month] sales order

.

Technical point

The technical points that usually need to be considered technically in order scenarios mainly include the following aspects:

Query ability: need to have rich query types, such as multi-dimension, scope, fuzzy query, etc., as well as sorting, statistics and other functions.

Data volume: while storing massive data, it meets the requirements of strong consistency, high availability, low cost and so on.

Service performance: to cope with high concurrency and high concurrency while ensuring low latency

The realization of multi-dimensional and real-time query function is the core function of the order management solution.

Cdn.com/01df85ba45158cf32b5254759dd6a128847d2984.gif ">

II. Evolution of the plan

E-commerce usually adopts the traditional solution of MySQL to deal with the order scenario. With the powerful query ability of relational database, users can directly realize multi-dimensional query and data statistics of order data through SQL statements. The so-called data expansion is divided into horizontal and vertical, horizontal is the continuous iterative introduction of new field dimensions, vertical is the total amount of data stored. In the face of these two kinds of order data expansion, the single MySql solution is becoming more and more difficult. The combination scheme of SQL + NoSQL (hereinafter referred to as: combination scheme) arises at the historic moment, with the help of the respective advantages of the two databases to solve the respective needs of different scenarios. However, the combination scheme also brings new problems, which not only sacrifices the space cost, but also increases the development workload and operation and maintenance complexity. Incur additional overhead in ensuring data consistency.

Let's take a look at the following general scenarios:

General scheme 1. MySql sub-library and sub-table scheme

MySql itself has powerful data query and analysis functions, and creates an order system based on MyQql, which can deal with multi-dimensional query and statistical scenarios of order data. With the increase of order data, users will adopt sub-database, sub-table scheme to deal with, through this pseudo-distributed scheme to solve the problems caused by data expansion. However, once the data reaches the bottleneck, it is necessary to re-create a larger sub-database + full-scale migration of data, and the trouble will continue to arise. The problem caused by data iteration and expansion is difficult to overcome by MySql. Only rely on MySql's traditional order plan to highlight the shortcomings.

1. Data vertical (data scale) expansion: using the scheme of sub-database and sub-table, MySql needs to estimate the size of sub-database when deploying. Once the amount of data reaches the upper limit, redeploy and migrate all the data.

2. Horizontal expansion of data (field dimension): schema needs to be pre-defined, and the iterative addition of new fields is complex. When the dimension reaches a certain amount, it will affect the performance of the database.

2. MySql+HBase scheme

The scheme of introducing double data arises at the historic moment. Through the scheme of sharing and storing real-time data and historical data, the problem of data expansion can be solved to a certain extent. The scheme classifies the data into two parts of storage: real-time data and historical data. At the same time, through the data synchronization service, the expired data is synchronized to the historical data.

1. Real-time order data (for example, orders in the past 3 months): real-time orders are stored in MySql database. The speed of the total expansion of real-time orders is limited, and the ability of multi-dimensional query and analysis of real-time data is guaranteed.

2. Historical order data (for example, orders from 3 months ago): historical order data are stored in HBase, with the help of HBase, a distributed NoSql database, to effectively deal with the expansion of order data. It also ensures the persistence of historical order data.

However, this scheme sacrifices the use value of historical order data to users, merchants and platforms, and assumes that the demand frequency of historical data is very low. But once there is a demand, it needs a full table scan, the query speed is slow, and the cost of IO is very high. On the other hand, maintaining data synchronization brings some problems, such as data consistency, soaring cost of synchronous operation and maintenance, and so on.

3. MySql+ Elasticsearch scheme

The combination solution also has MySql+ Elasticsearch, which also stores the data in two parts, which can solve the problem of order index dimension growth to some extent. Users maintain their own data synchronization service to ensure the consistency of the two parts of data.

1. Full data: the full amount of order data is stored in the MySql database, and the data outside the order ID is stored as a field. The full amount of data is used as persistent storage, and it is also used for anti-checking of non-indexed fields.

2. Query data: only the fields that need to be retrieved are stored in Elasticsearch (based on Lucene distributed index database). With the help of the indexing ability of Elasticsearch, order data that can cope with dimension expansion is provided, and then MySql is checked to obtain complete order information if necessary.

This scheme can cope with the problems caused by the expansion of data dimensions, but with the continuous expansion of orders, the problem of poor scalability of MySql has been exposed again. At the same time, the cost of development and operation and maintenance of the scheme of data synchronization to Elasticsearch is very high, and there are also some disadvantages in the selection of the scheme.

Capability analysis MySqlHBaseElasticsearchTableStore storage mode row storage column storage + index storage scalability stand-alone, poor scalability, horizontal expansion (automatic) horizontal expansion consistency strong consistency, timing consistency

Strong consistency, temporal consistency search weak whether to support the amount of data ~ 1T ~ 100 million rows ~ 10 PB,~ trillion rows ~ 1 PB,~ 100 billion rows ~ 10 PB,~ trillion rows table storage (TableStore) scheme

If we use the multiple indexing (SearchIndex) scheme developed by Table Storage (TableStore), the order system problem of hundreds of millions of magnitude can be solved perfectly. TableStore has the characteristics of ready-to-use, charge by quantity and so on. Multivariate index is created at any time, which is a high-quality solution for mass e-commerce order unit data management.

As a fully hosted and distributed NoSQL data storage service provided by Aliyun, TableStore has the functions of [massive data storage], [hot spot data automatic slicing], [massive data multi-dimensional retrieval] and so on, which naturally solves the challenge of order data explosion.

At the same time, SearchIndex function not only ensures the high availability of user data, but also provides data multi-dimensional search, statistics and other capabilities. Create a variety of indexes for a variety of scenes to achieve a variety of mode retrieval. Users can create and activate indexes only when they need them. TableStore ensures the consistency of data synchronization, which greatly reduces the workload of users' scheme design, service operation and maintenance, code development and so on.

An overview of the page of order system based on table storage

The sample is embedded in the form storage console, and users can log in to the console to experience the system (for new users who are stored in the form, they need to click to activate the service, which is free of charge. The order data is stored in the public instance, and the experience does not consume user storage, traffic, or Cu).

Note: this sample provides [hundreds of millions of magnitude] order data. Official website console address: project sample

Second, construction preparation

If you have a good experience with hundreds of millions of order systems and want to start the process of building your own system, you can simply follow the steps below:

1. Activate form storage

The form storage service is activated through the console, and the form storage is ready-to-use (postpaid). The pay-by-quantity method has provided users with a free quota for functional testing. The form stores the console of the official website and the description of free quota.

2. Create an instance

Create a table storage instance through the console and select the Region that supports multiple indexes. (at present, the SearchIndex function has not yet been commercialized. Beijing, Shanghai, Shenzhen and Hangzhou will be opened temporarily, and gradually later.)

After the instance is created, submit a ticket to apply for the multi-index function for testing (it is turned on by default after commercialization, and there is no charge for use).

Request address: submit a ticket, and select * * form Storage * *-> * * consultation on Product Features and Features * *-> * * create a ticket * *. The application content is as follows:

Problem description: please fill in [apply for SearchIndex Invitational Test]

Confidential information: please fill in [region + instance name], for example: Shanghai + myInstanceName

3. Download SDK

New functions have been added to the use of SDK with multiple index (SearchIndex), official website address, temporary java, go and node.js SDK.

Java-SDK com.aliyun.openservices tablestore 4.7.4gomurSDK $go get github.com/aliyun/aliyun-tablestore-go-sdk4, table design

The order system is not just a data sheet of an order, it should include: consumer table, salesperson table, product table, supplier table, transaction order table, payment order form and so on. In this example, the pork loin uses the four most basic tables (consumer table, salesperson table, product table, transaction order table), with only the order table as an example:

Table name: order_contract

Column name data type index type field description _ id (primary key column) String

MD5 (oId) avoid hot oIdStringKEYWORD order number pNameStringTEXT product name, TEXT type index can be fuzzy query, but can not sort totalPricedoubleDOUBLE order total price orderTimelongLONG order time (timestamp). Start to build (core code) 1. Create a data table

Four forms: order form, consumer form, salesperson form, product form

You only need to maintain one instance and create it as follows: create and manage the data table through the console (you can also create it directly through SDK):

2. Create a data table index

TableStore automatically synchronizes full and incremental index data: users can create and manage SearchIndex through the console (they can also be created through SDK):

3. Data import

Insert part of the test data (100 million pieces of data are inserted in the console sample, and users can insert a small amount of test data through the console)

Order No. (md5) (main key) Consumer number Consumer name salesperson number Product name Product name Brand Product Type order time payment time payment status Product Unit Price Total price o0000000000c49f5fd5aba33159accae0d3ecd749a7c0019 Chu 10 p0003004vivo x21vivo mobile phones 2018-07-17 21:00:00

No 2498.9924997.98 Consumer number (main key) Consumer name Consumer points Registration time c0001 Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer number (main key) Consumer name Consumer integral Registration time c0001 Consumer name Consumer integral Registration time c0001 Consumer name Consumer integral Registration time c0001 Consumer name Consumer integral Registration time c0001 Consumer name Consumer integral Registration time c0001 Consumer name Consumer integral Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer points Registration time c0001 Consumer name Consumer

There are two types of data reads:

Primary key read

Primary key column acquisition based on native table storage: getRow, getRange, batchGetRow, etc. Primary key reading is used for indexing (automatic) reverse query, and users can also provide a page for a single query of the primary key (order md5), which is extremely fast on the order of 100 million. Single primary key query does not support multi-dimensional retrieval.

Index read

Query:search interface based on the new SearchIndex function. Users are free to design multi-dimensional conditional combination queries for index fields. Different query conditions and different sorting methods are constructed by setting and selecting different query parameters; at present, it supports: precise query, range query, prefix query, matching query, wildcard query, phrase matching query, participle string query, and Boolean and or combination.

For example, [orders for consumer c0001 with consumption above 99.99] are combined as follows:

List mustQueries = new ArrayList (); TermQuery termQuery = new TermQuery (); termQuery.setFieldName ("cId"); termQuery.setTerm (ColumnValue.fromString ("c0001")); mustQueries.add (termQuery); RangeQuery rangeQuery = new RangeQuery (); rangeQuery.setFieldName ("totalPrice"); rangeQuery.setFrom (ColumnValue.fromDouble (99.99)); mustQueries.add (rangeQuery); BoolQuery boolQuery = new BoolQuery (); boolQuery.setMustQueries (mustQueries); IV. Welcome to join us.

In this way, the core code of the system has been completed, is it very simple to build an order system based on table storage?

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.