分布式文件共享服务，分享给我、我的共享、事件列表、回调通知等页面出现了查询非常慢的情况，客户反馈，表数据百万级别，查询需要一百多秒，无法忍受。顾需要我们优化 sql，添加索引。
慢 sql
在我们的常稳环境模拟客户的数据量，真实压测数据：
![[Pasted image 20250825101044.png]]
10 rows in set (57.60 sec)
mysql> ALTER TABLE event_correlation ADD INDEX idx_did_deleted_at (did, deleted_at);
Query OK, 0 rows affected (54.94 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE event_chain ADD INDEX idx_status_block_height_event_id (status, block_height DESC, event_id);
Query OK, 0 rows affected (1 min 2.84 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE event_correlation ADD INDEX idx_did_deleted_at_event_id (did, deleted_at, event_id);
Query OK, 0 rows affected (1 min 36.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE event_chain ADD INDEX idx_status_block_height_event_id (status DESC, block_height DESC, event_id);
Query OK, 0 rows affected (1 min 1.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看执行计划
+----+-------------+-------------------+------------+--------+--------------------------------------------------------------------------------------+---------------------------+---------+---------------------------------+---------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+--------+--------------------------------------------------------------------------------------+---------------------------+---------+---------------------------------+---------+----------+---------------------------------+
| 1 | SIMPLE | event_correlation | NULL | ref | idx_event_correlation_did,idx_event_correlation_event_id,idx_did_deleted_at_event_id | idx_event_correlation_did | 514 | const | 2181983 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | ec | NULL | eq_ref | uni_event_chain_event_id | uni_event_chain_event_id | 1026 | dtdh.event_correlation.event_id | 1 | 100.00 | NULL |
+----+-------------+-------------------+------------+--------+--------------------------------------------------------------------------------------+---------------------------+---------+---------------------------------+---------+----------+---------------------------------+
2 rows in set, 1 warning (0.01 sec)
重点关注：Using temporary; Using filesort 这是导致慢的原因，如果仅仅是两百多万数据中取 10 条，也很快。
通过测试，在为表结构添加合适的索引（为 order by 字段添加索引）后，数据查询快起来了。
select count(*) .... where xxx = xxx 这种最慢，如果数据非常集中的话，会导致计算 count(*) 非常慢。
重新设计查询接口
缓存 or has more 方式 + 近似计算 total
小表驱动大表
数据查询时，遵循 小表驱动大表，因为 join 时都是循环查询的。