What is the technical architecture of mysql e-commerce platform?-Mysql Tutorial-php.cn

1. E-commerce platform standardization suite

A. Mall system

1. Settings: site settings; account synchronization; upload settings ; SEO settings; message notifications; payment methods; permission settings; delivery areas;

2. Products: category management; brand management; product management; picture space;

3. Store: store management ; Store level; Store classification; Second-level domain name;

4. Membership: member management; points management; pre-deposit; sharing binding settings; buyer dynamics;

5. Transaction: order Management; refund management; consultation management; reporting management; evaluation management; investment and demolition management;

6. Website: article classification; article management; system articles; page navigation; advertising management; homepage management; recommendation position;

7. Operation: basic settings; group purchase management; gift redemption; event management;

8. Statistics: member statistics; store statistics; sales analysis; product analysis; marketing analysis;

B. Circle (BBS)

Circle setting; member title setting; circle classification management; circle management; circle topic management; circle member management; circle report management;

C.CMS (article management system)

CMS management; home page management; article management; article classification; pictorial classification; topic management; tag management; comment management;

D. Mobile terminal

Home page settings; category picture settings; download settings;

2. Technical architecture of the e-commerce platform

A. Application server

1. Two major categories: front-end server (mainly completes user response), back-end server (mainly completes data processing)

2.Nginx performs well in memory allocation and uses multi-threads to process requests, allowing memory resources to be shared between multiple threads, thus greatly reducing memory usage. In addition, we adopt a segmented memory allocation strategy to allocate and release memory in time according to demand, so the overall memory usage is very small and can support a large number of concurrent connections.

B. Load balancing

1.F5 (F5 BIG-IP), the official name is local traffic manager, can do layer 4-7 load balancing.

2.LVS (Linux Virtual Server), for network applications with large business volumes (such as news services, online banking, e-commerce, etc.). The combination of LVS and Keepalived has strong load resistance, simple configuration, and stable operation. This method is applicable to a wide range of applications.

⑴Three working modes of LVS:

①VS/NAT (Virtual Server Via NAT), network address translation technology, consists of a load balancing server and several real servers on the backend to form a server cluster. Advantages: Only one IP address needs to be configured on the scheduling server, and the server group can use private IP addresses. Disadvantages: Limited scalability.

②VS/TUN (Virtual Server via IP Tunneling), connection scheduling and management are the same as in VS/NAT, but the message forwarding method is different. Rewritten sentence: The advantage of this solution is that it can greatly increase the number of load scheduling servers, thereby building a high-performance super server. A server that supports the "IP Tunneling" or "IP Encapsulation" protocols is required.

③VS/DR (Virtual Server via Direct Routing), the scheduler dynamically selects a server based on the load of each server, without modifying or encapsulating IP packets, but instead converts the MAC address of the data frame to Instead, select the MAC address of the server, and then send the modified data frame to the LAN of the server group. It is required that the load scheduler and the actual server have a network card connected to the same physical network segment. The server network device does not respond to ARP, or can redirect the packet to the local Socket port.

⑵LVS scheduling algorithm

Polling scheduling; weighted round-robin scheduling; minimum connection scheduling; locality-based minimum connection; locality-based minimum connection to be copied; target address hash Scheduling; source address hash scheduling;

3.Nginx: Backend servers can be scheduled by polling, IP_HASH, URL_HASH, weight and other methods, and health checks are also supported. It has little dependence on the network and works on layer 7.

4.HAProxy: It can supplement some shortcomings of Nginx, such as session retention, cookie guidance, etc.; supports URL detection; in terms of efficiency, it is better than Nginx; it can load balance MySQL read operations;

C. Cache

1. Two parts: file cache (static content), data cache

2. Client cache: Header(“Cache-control :must-revalidate”);Header(“Expires:”.gmdate(“Did M Y H:i:s”,time() (60*60*24*30)));//Expired in 30 days php

3.CDN acceleration

4. Static file cache: Varnish/Squid

5. Data cache: memcache, redis

D. Data storage

1. Relational database: MySQL, Oracle, SQL Server

2. Memory database: Redis, MongoDB (document type)

3. Distributed database: HBase

4. MySQL scalable solution: MySQL Cluster; DRBD hard disk network mirroring; MySQL Replication (recommended); MySQL data segmentation;

5. Data segmentation: through a specific algorithm , the data stored in the same database (table) is dispersed and stored in multiple databases (tables) to achieve the effect of dispersing the load of a single device.

6. Vertical segmentation: Split into different databases (hosts) according to different tables. It is suitable for systems with low coupling between businesses, little mutual influence, and clear business logic. Its rules are simple and easy to implement.

Horizontal table splitting: According to the logical relationship in the data table, the data is split into multiple tables through a certain algorithm. The splitting rule itself is more complicated than splitting based on table names, and subsequent data maintenance is also more complicated, but it is better for reducing system pressure and is a recommended processing method under high-concurrency big data.

E.File storage

Shared storage: NFS

File storage: HDFS, FastDFS

F.Message Queue

ActiveMQ; Gearman; MemcacheQ; RabbitMQ; HTTPSQS; Taobao MetaQ; NSQ, etc. In addition, the message queue based on Memcache/Redis is also easy to deploy, easy to maintain and easy to expand.

G. Search design

lucene, sphinx and domestic xunsearch

3. Design and implementation of mall suite

A. Member module

1. Module composition: After registration, the default is the buyer. To become a seller, you need to submit an application for settlement after registration and pass the review. The login port for buyers and sellers exists independently. As the foundation of the website, it basically involves all modules of the website.

2. Design ideas:

① Design requirements:

The interface is simple and convenient: registration and login are simple and convenient, a mobile phone number or email password
Collect more member information: Member center collects
Differentiated management: member hierarchy and management system
Increase membership stickiness
Members’ guidance
High Cohesion and low coupling: Buyer and seller centers are divided into independent modules
for data analysis

②Data table Design

Master-slave coordination: master table and slave table
Reasonable use of redundancy: for example, the user name is also saved in the store table
Clear structure: for example, the user table is separated from the merchant table

③Module design

Buyer membership functional requirements: registration and login; buyer membership level; information management; account security; other related functions
Expert account functional requirements: merchants open stores ;Permission management; Creation of sub-accounts; Store information management; Store decoration; Store classification; Store consumption; Other related functions

3. Development and use

Reasonable stratification of members; use of word-of-mouth marketing; understanding of the life cycle of members; member care; reasonable and comprehensive analysis of member data;

B. Product module

1. Several Small modules: product classification; brand; type; specifications and specification values; attributes and attribute values; commodities;

2. Module composition:

① Product classification: add, edit, delete , import and export, rarely modified, cached files

②Brand: Add (added by platform and added by merchants, merchant additions need to be reviewed), edit, delete

③Specifications and specification values: Platform For additions, deletions and modifications, stores can only add specification values according to specifications

④ Types and attributes: operated by the platform

⑤ Products: additions, deletions and modifications are made by the store. The platform can be reviewed and deleted.

3. Design ideas:

① Product-related data table design

The product classification table and the type table have a one-to-many relationship, and the product classification table Types are associated with attributes, specifications, and brands. It is one-to-many with the product table
The attribute series table includes the attribute table and the attribute value table, which is a one-to-many relationship. The attribute table and the type table are many-to-one. The relationship between the attribute value table and the product table and the attribute and product relationship table is a many-to-many relationship.
The specification series table includes the specification table and the specification value table, which is a A to-many relationship. The specification table and type table use the type and specification relationship table as a bridge to form a many-to-many relationship. The relationship between the specification table, specification value table and product table is a many-to-many relationship.
The brand table uses the type and brand relationship table as a bridge, and is many-to-many with the type table. The brand table and product table are one-to-many.
The product series table includes a product table, a product public table and a product picture table. The product table and the product public table are many-to-one, and the product picture table is many-to-many

②Design ideas related to platform management of products

Platform management The staff needs to first complete the settings of product classification, brand, type, specifications, and attributes

③The design ideas for merchants to publish products

Set specification values; product pictures; picture space; inventory alarm; association Board type;

④ Design ideas for user retrieval of products

Use full-text search

3. Code implementation

When deleting product categories, it is necessary to clean up the interaction with product categories Associated data;

C. Promotion module

1. Module composition:

①Common promotion methods:

Discount Promotion
Group buying: Promote the sales of e-commerce websites, directly increase the number of registered members of e-commerce websites, and promote customers to try to buy, discover Problems existing in the platform and expand the brand exposure and popularity of e-commerce websites;
Preferential promotions: buy one get one free, buy get one gift, buy get points, buy get voucher;
Matching sales: customers When browsing a product, recommend other products to him. This product can be sold together with other products, and the total price will be reduced accordingly.
Limited time limited promotion
Lottery promotion
# #Interactive promotion: polite praise, polite invitation
Added-value promotion: free shipping, additional services

2 .Design ideas

①Business design principles

Attract attention
Persuasion function
Feedback information
Stimulate sales

②Module design example ( Group purchase module)

Package management: The platform provides the store
Group purchase management: The platform conducts review and can be removed from the shelves at any time
"Coming soon": Add
Search settings: product category, price range, etc.
Detailed information: Pictures, prices, descriptions are eye-catching, and the status is clearly visible
Group purchase orders: unified into the order module

3. Development and use

Development principles: simple and easy to understand, eye-catching, flexible combination, data statistics

Note: benefit others and yourself, Used together, not cumbersome, attractive enough, realistic, and a promise worth a thousand dollars

D. Shopping cart module

#1. Module composition: adding, deleting, editing, and collecting goods Function

2. Design idea

①Design requirements:

Persistence saving: cookie saving before logging in, database saving after logging in, order successful Then clear the purchased products
Support adding multiple types of products
Support adding multiple store products
Easy to operate
Data integrity: promotion information, product subtotal, store subtotal, etc.
Data accuracy: key information (inventory, price, status, etc.)

②Data table design

The core fields of table relationships are indispensable: involving members, stores, product tables, etc.
Necessary redundant fields: product prices, names, pictures, stores Name etc
Data accuracy: Query the database to get the latest valid data when processing key nodes

③Shopping cart Model design

Add, delete, modify, and query operations
Entry encapsulation: Operations such as cookies or databases are externally represented as an entrance, with parameters Form diversion
Data statistics
Data integrity and accuracy

E. Delivery module

1. Module composition: The platform needs to initialize some basic information, such as national or local administrative areas, major express companies, etc.; merchants It is necessary to set up a courier company; the freight template not only supports different freight rates in different regions, but also avoids merchants from repeatedly setting the freight for goods, reducing workload; when buyers place an order, they must set the receipt information, and the system will calculate the freight accordingly.

2. Design ideas

①Design requirements

Built-in administrative area
Built-in Delivery company
Set cash on delivery area
Freight rate template
Receiving address
Logistics tracking

3. Function implementation

① Delivery area: One is the standard administrative area setting; the other is the cash on delivery area Settings; all regional data when loading the delivery region page are completed by the server. When loading the page, put the county ID that supports cash on delivery into the JS array. When editing the region, whether the upper-level region is selected and the quantity The changes are completed by client JS

②Distribution company: at least include company name, website, company code, etc.

③Receiving address: N can be saved and a default delivery address can be set

F. Order module

1. Design idea

①Order status

Refers to the general term for the monetary elements involved in the order, including at least the unit price of the product, total price of the product, total order amount, discount amount, shipping fee, voucher face value, refund amount, etc.

③Order number

Jianyi can fully consider related elements such as time, random numbers, merchant IDs, member IDs, and self-increasing IDs. The purpose of the design is to ensure that the probability of repeating order numbers is minimized under high concurrency

④Inventory

Order occupied inventory
Unsellable inventory
Locked inventory: on sale During the event
Virtual inventory

⑤Consolidated payment

You can merge orders from different merchants Unified payment

⑥Role permissions

Buyer: order cancellation, deletion (put in recycle bin), refund, return, goods receipt, evaluation, etc.
Merchant: order review, closing, shipping, after-sales processing, etc.
Platform: order cancellation, change of payment status, Deletion, arbitration, etc.

⑦Table design

Order main table: stores main and commonly used order information, such as order number, Amount, freight, status, etc.
Auxiliary table: auxiliary information, such as shipping information, invoice information, consignee information, promotion information, etc.
Order product table: some product list information in the order
Payment order table: designed for combined payments , save a payment order number, and use a payment order number for N order table records
Order log table: record the operation log when the order content changes, including the operator, Operation time, operation content, etc.

2. Place an order

The system will do a lot of processing work when generating an order, such as processing receipt information and invoices Information, promotional information, shipping costs, vouchers, payment orders, orders, logs, etc.

G. Payment interface

1. There are two ways to access payment results: one is synchronous, with jump notification through the browser; the other is asynchronous , that is, server backend execution.

2. Design requirements: security; data integrity (transaction processing); scalability;

3. Database design: at least include the name and identification code of the payment method, and the identification code must be consistent with The directory names of some parts of the payment interface API program are consistent. In addition, the serialized payment interface configuration information and payment interface status must be saved;

H. Chargeback module

1. Design ideas

①When there is a new refund or return application but the order has not been completed (when the receipt is confirmed), in order to prevent disputes, the order status must be locked

②Return: Based on the refund process, the steps of buyer shipping and merchant receiving are added.

If the merchant does not agree to refund or return the product, the buyer can apply again or complain to the platform about the merchant and submit relevant evidence for arbitration by the system administrator.

④You can use a table and use a field to identify whether it is a refund or a return

⑤The reason for refund or return is entered by the system administrator in the background and selected by the buyer when submitting the application.

2. Development skills

① You must first set the rules, clarify your ideas, and communicate and resolve any unclear points in the logic in a timely manner.

② Try to make full use of the code, and be sure to perform server-side data verification.

I. Settlement module

Settlement is the bill settlement between the platform and the merchant. It is settled regularly. After the payment is made, the system will wait for the merchant to confirm the bill. If it is correct, , after the merchant confirms, it enters the system review process. After the system review, it is submitted to the financial department for payment operations; after the payment is completed, the payment-related information is entered in the background, and the bill settlement is completed.

1. Design ideas

①Data table design: bill table, including date, total order amount, total shipping fee, total refund amount, total commission amount, refunded commission amount, store fee, Fields such as the amount due and settlement status; the bill summary table is a statistical summary of the settlement information of all merchants every month;

②Settlement process design: when the account is issued, the system automatically calculates the settlement account for this month

[Execution Timing] Automatic and manual;

[Settlement Object] Completed orders or chargebacks for transactions that occurred last month;

[Calculation Formula] Order amount, commission amount (Commission = actual selling price of the product * purchase quantity - discount allocation amount), return order amount, refund commission, store promotion fee;

③Amount payable by the platform = order amount - commission amount - return order amount refund commission - Store promotion expenses;

④Reconciliation: The platform provides the information, confirms it after checking and submits it to the platform for review. After completing the review, go through the financial process to make the payment. After the payment is completed, enter the relevant payment information and submit to complete the settlement process.

J. Statistical module

1. Let data analysis intervene in operations: make operational decisions intelligently based on data; use data as the goal to effectively execute operational plans ; Based on data, optimize the business process;

2. Module composition:

①Views (PV), the total number of times the browser loads web pages;

②Visitors Number (UV), use cookies to determine absolutely unified visitors;

③Conversion rate, refers to the ratio of the number of customers who actually consume and the total number of customers who come to the website. Transaction conversion rate = number of transaction customers/total number of visitors;

④Average visit depth refers to the number of pages of your website that users browse during one visit, which is the ratio of PV and UV;

How to improve access depth?

Reasonable typesetting and layout of the website;
Content of the website;
Reasonable navigation and appropriate internal link anchor text;

⑤Per capita residence time on the website, average website residence time = total website residence time/number of sessions (visits)

⑥Page bounce rate, refers to the visitor reaching the target page , after arriving and leaving without continuing to visit other pages of the website, it is called a Bounce! That is, a skip. The formula for calculating the bounce rate is: the number of visits that leave the page divided by the total number of visits to the page

⑦Number of items ordered

⑧Amount of items ordered

Average transactions The amount refers to the average amount of goods purchased by each member of the website within a certain period of time, also known as the unit price. The unit price per customer can be expressed as the average consumption amount of each customer. The calculation formula is the total sales divided by the total number of customers, or the total sales divided by the total number of transactions.

⑩Repeat purchase rate refers to the consumer’s purchase of the product Or the number of repeat purchases of a service. One is the number of times all customers who have purchased the product have repeatedly purchased the product as an independent unit; the other algorithm is the proportion of the total number of repeat purchases per unit time; the first one is recommended;

3. Design ideas

①The design principles of the data itself

must have an overall concept: overall sales information (order amount, order volume, average customer price) ; Overall product information (number of products ordered, average price of products, number of new products, total number of products, top 30 product sales within 7 days); overall customer information (total number of members, number of new members, number of members placing orders); overall store information Information (total number of stores, total number of new stores, top 30 store sales within 7 days)
Statistics should be detailed: down to a certain member, a certain point in time, a certain Regions, etc.
Regular data analysis: year-on-year comparison between the Nth month of this year and the Nth month of last year (year-on-year development rate=number of this period/number of the same period last year*100%; year-on-year Growth rate = (number in this period - number in the same period last year) / number in the same period last year * 100%); chain-on-quarter comparison is the comparison between the reporting period and the previous statistical period (month-on-month development speed = (number in this period / number in the previous period) * 100%; Month-on-month growth rate = (number of this period - number of previous period) / number of previous period * 100%)
Pay attention to daily operational data: average number of pages visited by customers, average stay time, bounce out Rate, number of times a product has been collected, etc.
Data should be real-time

②Business level design principles

In-depth analysis of the business: E-commerce website data analysis is based on industry analysis->store analysis->brand analysis->analysis of products and members, from large to small.
Intuitive and easy to understand: use line charts to express changing trends; use bar charts for simple comparisons; use funnel charts to display process conversion rates; it is best to use pie charts or donut charts to express proportions ; Indicate the distribution in the area using statistical maps;

③ Design principles of module design: portability; scalability; simplicity and intuitiveness; use of cache ;

④Data table design principles

⑤Establish more cache data tables; concise fields reduce complex judgments; necessary redundant fields reduce joint queries (member name, product name, store name, etc.);

4. Development and use

①Data operations should pay attention to the following issues:

②People issues: The emphasis on data should start from the leadership Starting;

③Practical application: actually using the data;

④Don’t just seek the best: when choosing a data mining algorithm, we must figure out whether it is suitable for the problem we want to solve ;

⑤Seeking truth: Extract effective information as much as possible when mining;

⑥Repeatability: It takes a while to dig again;

⑦Data accumulation: Data analysis requires a certain amount of accumulation Only with a large amount of data can the results obtained through data mining be convincing;

⑧Quick response: After the data is generated, the results can be obtained quickly to maximize the effect;

K. Pre-deposit

1. Members have three main operations for pre-deposit: recharge, withdrawal and shopping

2. Design ideas

①Design requirements: security sex; data integrity;

②Data table design:

Recharge table records members’ recharge information. The main fields are recharge form, member information, recharge amount, Recharge time, recharge status, etc. If there is an administrator operation, the identity of the administrator should also be recorded
Withdrawal table, record the member’s withdrawal information, the main fields are withdrawal order number, member information, Withdrawal amount, beneficiary bank information, application status and platform payment information (payment time, operator, etc.)
Log table records all operations when changing pre-deposits , including detailed records of changes in the available amount and frozen amount, the main fields include operator information, operation type (order, withdrawal, recharge, refund, etc.), available pre-deposit, frozen pre-deposit, operation time, remarks wait

The above is the detailed content of What is the technical architecture of mysql e-commerce platform?. For more information, please follow other related articles on the PHP Chinese website!