1. not in 和 <> 的优化-->left join

SELECT customer_id, first_name, last_name, email FROM customerWHERE customer_id NOT IN (SELECT customer_id FROM payment);--优化后SELECT a.customer_id, a.first_name, a.last_name, a.emailFROM customer aLEFT JOIN payment b ON a.customer_id=b.customer_idWHERE b.customer_id IS NULL;

2. 大表的数据批改要分批解决

比方1000万行记录在表中要删除, 或者更新100万行记录;

优化计划: 一次只删除/删除5000条, 而后sleep几秒(暂停几秒)-->给主从同步缓冲工夫;

3. 汇总查问的优化 select count(*)-> 减少汇总表

减少一个汇总表, 每天更新一次汇总值;

而后每次查问时, 汇总表+实时表(time>date(now()))即可;

SELECT COUNT(*) FROM product_comment WHERE product_id=999;--减少汇总表 product_comment_cntCREATE TABLE product_comment_cnt(product_id INT, cnt INT);--查问时:SELECT SUM(cnt) FROM(    SELECT cnt FROM product_comment_cnt WHERE product_id = 999    UNION ALL    SELECT count(*) FROM product_comment WHERE product_id = 999 AND timestr>DATE(now()));--mysql> select DATE(now());+-------------+| DATE(now()) |+-------------+| 2020-09-12  |+-------------+1 row in set (0.01 sec)