1. 如何對評論進行分頁展示 一般情況下都是這樣寫 SELECT customer_id,title,content FROM product_comment WHERE audit_status = 1 AND product_id =199726 LIMIT 0,15;; 我們來看看它的執行計劃 ...
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
檢驗一下結果是否正確
總和是10010，說明查詢結果正確
