1. How do I paginate comments

That's how it's usually written

 SELECT customer_id,title,content FROM product_comment WHERE audit_status = 1 AND product_id =199726 LIMIT 0,15;;

Let's look at its execution plan

You can see possible_keys、key、key_len Values are NULL, This article SQL stay product_comment There are no available indexes on the table , Take out 9593 The row filtering degree is 1%

1. Index , Optimize comment paging queries

According to our index specification can be considered in where Index on condition

where The condition has two fields , We can calculate the difference between the two columns in the table with the following statement

Calculates the degree of field data discrimination , Index

SELECT COUNT(DISTINCT audit_status)/COUNT(*) AS audit_rate,COUNT(DISTINCT product_id)/COUNT(*) AS product_rate FROM product_comment;

The closer the ratio is 1, The better the degree of differentiation , We should put the well-differentiated columns on the left side of the union index

Now that we've indexed it , Now let's look at the execution plan

You can see that the federated index is used in the query , Only one piece of data is queried , We can return the data we need , The filtration level is 100 percent , We've done our first optimization

 Database access overhead = Indexes IO + Index the data of a table to which all record results correspond IO

shortcoming

such SQL The disadvantage of statement queries is that , Turn the page , Like thousands of pages later , It's going to get less and less efficient , The query time will also get longer and longer , Especially when the table data is large

Applicable scenario

It is useful if the result set of the table is small , Let's say below 10,000 lines , Or query conditions are very complex , For example, multiple different query judgments are involved , Or table association

2. Further optimize comment paging queries ,SQL Statements to rewrite

After the rewrite SQL sentence :

SELECT t.customer_id,t.title,t.content
FROM (
SELECT customer_id FROM product_comment WHERE product_id =199726 AND audit_status = 1 LIMIT 0,15
)a JOIN product_comment t
ON a.customer_id = t.comment_id;

Before rewriting SQL And rewrite it SQL The result set of the query is the same , But it's more efficient than before SQL

Use the premise

Use this SQL There is a premise , The primary key of the product review table is customer_id , And there are coverage indexes ( That's the joint index that we just created )

Optimization principle

The primary key is extracted using the overwrite index according to the filter condition comment_id, And then we can sort it , The number of rows to extract the data we need , Then the comment table is sorted by primary key to fetch the other fields ,

The data overhead in this way is the index IO + Results after index paging (15 Row data ) The table of IO,

advantage

Than before I rewrote it SQL stay IO We'll save a lot , The advantage of this rewriting approach is that the resources and time spent on each page turn are essentially the same , It doesn't turn the page , The efficiency of the worse

Application scenarios :

When querying and sorting fields ( namely where Clause and order by The fields covered by the clause ), Used where there is a corresponding coverage index

This is also true if the result set of the query is large

Two . How to delete duplicate data

requirement

Delete duplicate comments on the same item for the same order from the comment table , Keep only the earliest one

Step one

Check for duplicate comments on the same item for the same order , If there is , Follow up

Query statement :

SELECT order_id,product_id,COUNT(*) FROM product_comment
GROUP BY order_id,product_id HAVING COUNT(*) > 1;

Step two

Backup product_comment surface ( Avoid the case of accidental deletion )

Backup statement :

 CREATE TABLE bak_product_comment_190108 AS SELECT * FROM product_comment;

If you are prompted :

Error code :1786 Statement violates GTID consistency:CREATE TABLE ... SELECT.

Use the following statement instead

 CREATE TABLE bak_product_comment_190108 AS LIKE product_comment;
INSERT INTO bak_product_comment_190108 SELECT * FROM product_comment;
 Error code :1786
Statement violates GTID consistency:CREATE TABLE ... SELECT.

The reason for the error

This is because in the 5.6 And above version inside , Open the enforce_gtid_consistency=true Functionally induced ,MySQL The official explanation is when enabled enforce_gtid_consistency Functional time ,MySQL Only be allowed to secure transactions , And can be logged SQL Statement executed , image create table … select and create temporarytable sentence , And update both transaction and non-transaction tables SQL Statements or transactions are not allowed to execute .

terms of settlement

Method 1

modify :

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = off;

In profile :

ENFORCE_GTID_CONSISTENCY = off;

Method 2 :

create table xxx as select Will split into two parts .
create table xxxx like data_mgr;
insert into xxxx select *from data_mgr;

If the table is large , Then use mysql dump The way to export to files for backup

Step three

Delete duplicate comments on the same order

Delete statements :

DELETE a FROM product_comment a
JOIN(
SELECT order_id,product_id,MIN(comment_id) AS comment_id
FROM product_comment
GROUP BY order_id,product_id
HAVING COUNT(*) > 1
) b on a.order_id = b.order_id AND a.product_id = b.product_id
AND a.comment_id > b.comment_id;

3、 ... and . How to conduct inter-partition statistics

requirement

The total amount of consumption is greater than 1000 yuan ,800 To 1000 yuan ,500 To 800 yuan , as well as 500 The number of people under yuan

SQL sentence

SELECT
COUNT(CASE WHEN IFNULL(total_money,0) >= 1000 THEN a.customer_id END) AS ' Greater than 1000'
,COUNT(CASE WHEN IFNULL(total_money,0) >= 800 AND IFNULL(total_money,0)<1000
THEN a.customer_id END) AS '800~1000'
,COUNT(CASE WHEN IFNULL(total_money,0) >= 500 AND IFNULL(total_money,0)<800
THEN a.customer_id END) AS '500~800'
,COUNT(CASE WHEN IFNULL(total_money,0) < 500 THEN a.customer_id END) ' Less than 500'
FROM mc_userdb.customer_login a
LEFT JOIN
(
SELECT customer_id,SUM(order_money) AS total_money
FROM mc_orderdb.order_master
GROUP BY customer_id
) b
ON a.customer_id = b.customer_id

Check to see if the result is correct



The sum is 10010, Indicates that the query results are correct

(5) Common business in the e-commerce scenario SQL More related articles

  1. UI Design tutorial sharing : Common ways of expression in e-commerce web page design

    1. Hand painted illustration   scene . Characters and creative paintings with stories Will give people the feeling of dream looming , Make a deep impression , Suitable for making activity page and promoting own brand 2. Simplicity Less than three colors , The background is mainly low brightness color , In the age of information explosion , We ...

  2. Java Advanced topics ( 15、 ... and ) Study multithreading from the perspective of e-commerce system ( Next )

    Preface ​ This chapter continues to comb : Thread related basic theory and tools . Performance tuning of multithreaded programs and the use of multithreading in e-commerce scenarios . Multithreading J·U·C ThreadLocal Concept ​ ThreadLocal Class is not used to solve ...

  3. Java Advanced topics ( 13、 ... and ) Study multithreading from the perspective of e-commerce system ( On )

    Preface ​ This chapter mainly shares , Application of multithread concurrency in e-commerce system . Mainly from the following aspects : Thread related basic theory and tools . Performance tuning of multithreaded programs and the use of multithreading in e-commerce scenarios . Multithreading J·U·C Thread pool Concept Looking back on thread creation ...

  4. Grasp these two points , Seize the next e-commerce outlet |2016 The latest Chinese e-commerce App ranking &amp; research report

    preface Online retailers , It has grown up with the continuous development of China's Internet economy . TaoBao . In terms of transaction volume and influence, Jingdong's e-businesses are among the most successful in China . Among the most topical Internet companies . Although China's economy has slowed down in recent years , But the growth rate of China's consumer market is still expected to ...

  5. Cloudera Hadoop 4 Practical courses (Hadoop 2.0、 Cluster interface management 、 E-commerce online inquiry + Log offline analysis )

    Course syllabus and content introduction : Each class is about 35 minute , No less than 40 speak Chapter one (11 speak ) · Distributed and traditional stand-alone mode ·Hadoop Background and how it works ·Mapreduce Working principle analysis · Second generation MR--YARN Principle analysis ·Cl ...

  6. Java Fresh e-commerce platform - Coupon system architecture design and source analysis

    Java Fresh e-commerce platform - Coupon system architecture design and source analysis E-commerce backstage : Example interpretation promotion system E-commerce background system includes commodity management system . Purchasing system . Storage system . Order system . Promotion system . The system of safeguarding rights . Financial system . Member system . Authority system, etc , Systems ...

  7. zz Jingdong e-commerce recommendation system practice

    It's true that Today, I'd like to share with you my experience in the practice of JD e-commerce recommendation system , It mainly includes : brief introduction Sorting module Real time updates Recall and first round sequencing The experiment platform brief introduction When it comes to recommendation systems , The most classic is collaborative filtering , The figure above is an example of collaborative filtering . Collaborative filtering mainly ...

  8. Architecture design | Based on the e-commerce transaction process , The illustration TCC Transaction commit in segments

    In this paper, the source code :GitHub· Click here || GitEE· Click here One . A brief introduction to the scenario 1. Scene description Distributed transactions are very common in business systems , The most classic scenario is the trading business in the e-commerce architecture , Pictured : The client service through the request order , perform ...

  9. How to use it step by step DDD Design an e-commerce website ( Four )—— Sell goods to customers

    Read the directory Preface How to sell The use of domain services Back to reality Conclusion One . Preface In the first part we talked about “ Sell goods to customers ” The preliminary design of products and users in . Now put the rest of “ sell ” This action is done by . Here's a reminder , Under normal circumstances , Every step of our business ...

Random recommendation

  1. SecureCRT Set in \n Change lines for carriage return , and \r\n Act in unison

    Check the option in the red box on the way

  2. JavaScript Electronic clock + count down

    JavaScript Time class       Get time, minutes, seconds :          getHours()          getMinutes();          getSeconds();       obtain ...

  3. eliminate ComponentOne(C1StudioNet_2013v2) Registration tips for

      In the future, if you encounter any tips , stay License Add to file :C1.Win.C1Command.C1OutBar, C1.Win.C1Command.4, Version=4.0.20132.19568,  ...

  4. AC Automaton correction

    #include<iostream> #include<cstdio> #include<cmath> #include<algorithm> #inc ...

  5. cmd Order newspaper 4048 error

    resolvent : win10 System : Shortcut key win+x, Find the command prompt ( Administrators ), Open and download the corresponding dependency package . win7/8: Open up and start , Enter the command prompt , Find the command prompt for administrator privileges , Open and download the corresponding dependency package . Tips : Such as ...

  6. python Namespace and scope

    python Namespace and scope   A namespace is the relationship between a name and an object , Think of a namespace as a dictionary , The key is the name , Value is the object . The namespace does not share names . A name in a namespace can put any python Object as value , In different ...

  7. 《Linux Kernel design and implementation 》 Reading notes 3

    Chapter 18 transfer try 18.1 Get ready to start 1. preparation : One bug A hiding place bug The kernel version of Knowledge and luck about kernel code 2. perform foo Will cause the program to immediately dump the core information (dump core). ...

  8. TFT LCD Detailed explanation of display principle

    < What is liquid crystal > We generally think that objects have three states : solid state . liquid state . Gaseous state , Actually, it's just for water , There are some organic compounds and And there's a state between the solid and the liquid It's the liquid crystal state , Here's the picture ( One ):                 ...

  9. [ turn ]POJ3624 Charm Bracelet( A typical 01 knapsack problem )

    source :https://www.cnblogs.com/jinglecjy/p/5674796.html Topic link :http://bailian.openjudge.cn/practice/4131/ ...

  10. in the light of phpStudy Web server intrusion

    There's an alarm on the client server today , Find out why , Found... In the root directory wk.php E:\phpStudy\MySQL\bin\mysqld.exe, Version: (MySQL Community Server ...