(5) 电商场景下的常见业务SQL处理

用户1214487

发布于 2022-03-26 14:36:23

7150

发布于 2022-03-26 14:36:23

文章被收录于专栏：Python

1. 如何对评论进行分页展示

一般情况下都是这样写

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

我们来看看它的执行计划

可以看到possible_keys、key、key_len的值均为NULL，说明这条SQL在product_comment 表上是没有可用的索引的，取出9593行过滤度为1%

1. 建立索引，优化评论分页查询

根据我们索引规范可以考虑在where条件上建立索引 where条件有两个字段，我们可以通过以下语句计算一下两列数据在表中的区分度

计算字段数据区分度，建立索引

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

比值越接近1，代表区分度越好，我们应该把区分度好的列放到联合索引的左侧

我们现在建立索引后，再来看看执行计划

可以看到查询时运用到了联合索引，只查询出一条数据，就能返回我们需要的数据了，过滤程度是百分之百，我们完成了第一步优化

数据库的访问开销 = 索引 IO + 索引全部记录结果所对应的一个表数据的 IO

缺点

这种SQL语句查询的缺点是，越往后翻页，比如几千页之后，效率会越来越差，查询时间也会越来越长，尤其表数据量大的时候更是如此

适用场景

它的适用场景是表的结果集很小，比如一万行以下时，或查询条件非常复杂，比如涉及到多个不同的查询判断，或是表关联时使用

2. 进一步优化评论分页查询，SQL语句改写

改写后的SQL语句：

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;

改写前的SQL和改写后的SQL查询出来的结果集是一样的，但是效率要高于改写前的SQL

使用前提

使用这个SQL有一个前提是，商品评论表的主键是customer_id ，且是有覆盖索引（也就是刚刚我们建立的联合索引）

优化原理

先根据过滤条件利用覆盖索引取出主键的comment_id，然后再进行排序，取出我们所需要的数据的行数，然后再和评论表通过主键进行排序来取出其他的字段，这种方式的数据开销是索引 IO +索引分页后的结果（15行数据）的表的IO，

优点

比改写前的SQL在IO上要节省很多，这种改写方式的优点是在每次翻页的所消耗的资源和时间基本是相同的，不会越往后翻页，效率越差

应用场景：

当查询和排序字段（即where子句和order by子句所涉及的字段），有对应的覆盖索引的情况下使用并且查询的结果集很大的情况下也是适用于这种情况的

二. 如何删除重复数据

要求

删除评论表中对同一订单同一商品的重复评论，只保留最早的一条

步骤一

查看是否存在对于同一订单同一商品的重复评论，如果存在，进行后续步骤

查询语句：

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

步骤二

备份product_comment表（避免误删除的情况）

备份语句：

 CREATE  TABLE bak_product_comment_190108 AS  SELECT * FROM product_comment;

如果提示：

错误代码：1786 Statement violates GTID consistency:CREATE TABLE ... SELECT.

则换用下面的语句

    CREATE  TABLE bak_product_comment_190108 AS  LIKE  product_comment;
    INSERT INTO bak_product_comment_190108  SELECT * FROM product_comment；

错误代码：1786
Statement violates GTID consistency:CREATE TABLE ... SELECT.

错误原因

这是因为在5.6及以上的版本内，开启了 enforce_gtid_consistency=true 功能导致的，MySQL官方解释说当启用 enforce_gtid_consistency 功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。

解决办法

方法一

修改：

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = off;

配置文件中：

ENFORCE_GTID_CONSISTENCY = off;

方法二：

create table xxx as select 的方式会拆分成两部分。

create table xxxx like data_mgr;
insert into xxxx select *from data_mgr;

如果表数据量比较大，则使用mysql dump的方式导出成文件进行备份

步骤三

删除同一订单的重复评论

删除语句：

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;

三. 如何进行分区间统计

要求

统计消费总金额大于1000元的，800到1000元的，500到800元的，以及500元以下的人数

SQL语句

SELECT 
COUNT(CASE WHEN IFNULL(total_money,0) >= 1000 THEN a.customer_id END) AS '大于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)  '小于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