例子
SELECT customer_id,title,content FROM `product_comment`
WHERE audit_status=1 AND product_id=199726
LIMIT 0,5
这里的索引有auditstatus和productid,可以建立联合索引。但是哪个放左边就要计算区分度。
计算方法
SELECT COUNT(DISTINCT audit_status)/COUNT(*) AS audit_rate,
COUNT(DISTINCT product_id)/COUNT(*) AS product_rate
FROM product_comment;
区分度越高越好,放左边。结论 product_rate>audit_rate
建立联合索引
CREATE INDEX idx_productID_auditStatus ON product_comment(product_id,audit_status)
业务场景:删除评论表中对同一订单同一商品的重复评论,只保留最早的一条。
SELECT order_id,product_id,COUNT(*) FROM product_comment GROUP BY order_id,product_id HAVING COUNT(*)>1;
CREATE TABLE bak_product_comment_161022 LIKE product_comment;
INSERT INTO bak_product_comment_161022 SELECT * FROM product_comment;
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(*)>=2
) 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元以下的人数。
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) AS '<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`
set global show_query_log_file = /sql_log/show_log.log
set global log_queries_not_using_indexes = on; -- 未使用索引的SQL记录日志
set global long_query_time=0.001; -- 抓取执行超过多少时间的SQL(秒)
set global low_query_log=on; -- 启动
mysqldumpslow
工具,例如: mysqldumpslow slow-mysql.log