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
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
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 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 ）
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,
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
Delete duplicate comments on the same item for the same order from the comment table , Keep only the earliest 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;
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
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
Delete duplicate comments on the same order
Delete statements ：
DELETE a FROM product_comment a
SELECT order_id,product_id,MIN(comment_id) AS comment_id
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
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
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
SELECT customer_id,SUM(order_money) AS total_money
GROUP BY customer_id
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
- 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 ...
- 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 ...
- 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 ...
- Grasp these two points , Seize the next e-commerce outlet ｜2016 The latest Chinese e-commerce App ranking & 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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
- 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, ...
- AC Automaton correction
#include<iostream> #include<cstdio> #include<cmath> #include<algorithm> #inc ...
- 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 ...
- 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 ...
- 《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). ...
- 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 ): ...
- [ 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/ ...
- 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 ...