mysqlmysql如何优化in条件大列表查询

哦，是的，MySQL的IN状态确实很让人头疼，尤其是当涉及到大列表时。
2 02 2 年我在上海，经历过一次。
这个IN里有上百个城市名，查询速度慢得要命。
当时我很困惑，但在查看了信息后，我意识到我必须这样做。

我们先来看看性能如何。

使用 EXPLAIN 命令。
你必须能够使用它。
例如，当我检查用户表中的城市时，IN 包含“纽约”、“伦敦”等。
我执行 EXPLAIN SELECT FROM users WHERE city IN ('New York', 'London', ...)。
查看类型栏。
如果是ALL，就意味着全表扫描，肯定不行。
可能的键列为空，表示没有可用的索引。
此时，行列中的数字较大，意味着需要检查很多行，导致性能较差。
我当时做的查询返回了数万行，这显然很慢。

还有慢查询日志也很重要。
如果 EXPLAIN 查询执行时间超过您设置的阈值，例如例如。
1 秒，这会被记录在日志中。
通过分析日志，可以找出哪些IN查询需要优化。

如何选择JOIN和IN
一般来说，JOIN的性能比IN好。
特别是当涉及到 JOIN 时，当 user_id 和 temp_users.id 列都有索引时，JOIN 通常会更快。
例如，在我前面的示例中，要从“Orders”表中检查属于用户“temp_users”的订单，IN 为：
SELECT FROMorders WHERE user_id IN (SELECT id FROM temp_users);
JOIN 为：
SELECT o. FROM Orders o JOIN temp_users t ON o.user_id = t.id;
例如，如果temp_users表不大，只有几百条记录，并且orders表的user_id列有索引，那么JOIN通常会更快，因为JOIN可以利用索引进行快速匹配，而不是像IN那样单独比较列表中的每个值。
然而，JOIN 并不适合所有情况。
例如，如果temp_users表很大或者user_id列没有索引，JOIN可能会导致全表扫描，而IN可能会更好。
这取决于您的具体情况。
您必须使用 EXPLAIN 来分析查询计划。

如何处理大列表
如果IN列表太大，可以将其分割。
例如，我将数百个城市名称分成几个小列表，每个列表分组 1 ,000 个，然后使用 UNION ALL 将它们连接起来。
像这样：
SELECT FROM table WHERE id IN (1 ,2 ,...,1 000) 联合所有 SELECT FROM 表 WHERE id IN (1 001 ,1 002 ,...,2 000) 联合所有 ...
这样就可以控制每个小列表的大小了。
几百到一千个最好避免单个查询太大。

还有临时表+JOIN的使用。
首先将IN列表值保存在临时表中，然后将它们连接到原始表中。
步骤是先创建临时表，如：
CREATE TEMPORARY TABLE temp_ids(id INT PRIMARY KEY); INSERT INTO temp_ids VALUES (1 ),(2 ),...,(1 0000);
然后在查询中使用 JOIN：
SELECT t. FROM table t JOIN temp_idst ON t.id = ti.id;
临时表的好处是只在当前会话中有效，当会话结束时自动删除结束，这在处理非常大的列表时非常方便。

其他建议
最直接的方式当然是添加索引。
例如，如果您经常搜索城市，请在“城市”列中添加索引。
增加了索引，查询速度肯定会快很多。

另外，尽量不要将查询变成全表扫描。
使用 EXPLAIN 显示类型列。
如果是ALL，则说明全表都扫描完了，需要优化。
例如，您可以自定义查询语句或使用 JOIN 代替。

如果实在受不了这种大列表查询，也可以考虑调整数据库结构。
例如，拆分表、分区或使用 Elasticsearch 等专门的数据处理工具。
不过这个比较复杂，要看具体情况。

如何在mysql中优化DISTINCT去重查询

哦，给大家说一下我当时遇到的悬念，比如MySQLDistinct的优化。

几年前我做了一个项目。
随着用户数量的增加，通过用户 ID 删除重复文件的查询将停止。
查看记录后发现是全表扫描，最后用临时表+文件排序的方式解决了。
那场面太恐怖了。

后来我慢慢摸索，发现了几个效果不错的方法：
第一；为从重复中删除的字段构建联合索引。
例如，我的查询是 SELECT DISTINCT user_id FROM Orders。
为user_id创建索引并完成构建后；我用EXPLAIN看了一下，发现执行计划中类型已经变成了ref而不是ALL。
这意味着直接通过索引去除重复项，而不需要扫描整个表，从而节省了大量的工作。

其次，缩小数据范围。
这一点尤其重要。
有时你知道有些数据不需要，但你仍然想编辑它；这绝对是慢的。
例如，我有一个查询；从订单中选择不同的 user_id WHERE status='pending';因为大多数情况都被称为“已完成”或“已取消”；这个问题确实可以改进；因为只有几集正在“等待”。
所以我这样做 SELECT DISTINCT order FROM user_id WHERE status='pending' AND user_id IS NOT NULL。
这样，数据量突然变小，检索速度更快。

第三，使用 GROUP BY 而不是 DIFFERENCE。
我也尝试过这个，有时确实有效。
例如，我有一个查询；选择不同的用户 ID；按 order_id 订购。
执行此查询 SELECT user_id; GROUP BY user_id FROM order_id；订单号；可以改成 更改后发现执行计划中仍然引用了该类型并且Extra没有使用临时和文件类型。
这意味着直接通过索引消除重复，无需临时表和文件排序，从而显着提高性能。

Stokes，减少 SELECT 字段的数量。
这也是非常重要的。
有时您显然只需要 user_id，但要求添加用户名和电子邮件等大字段肯定很慢。
例如，我有一个问题；选择不同的用户 ID；用户名 FROM 命令。
我可以从 SELECT DISTINCT user_id 更改此查询，因为我不需要用户名。

第五，使用封面指示器以避免图表背面。
我也尝试过这个，非常棒。
例如，我有一个查询； ORDER BY DISTINCT user_id FROM WHERE status='completed';我可以为status和user_id建立联合索引；从添加索引更改表 idx_status_user_id (status, user_id);建好索引后，用EXPLAIN看了一下，发现执行计划中依然引用了该类型，而Extra没有临时使用，也没有文件类型使用。
这意味着直接通过索引删除复制，而无需发送回表，从而显着提高性能。

最后通过EXPLAIN分析执行计划。
我也经常用这个。
问题是否标记为“否”我使用 EXPLAIN 来查看是否使用了临时表和文件排序。
例如，我有一个查询； ORDER BY DISTINCT user_id FROM WHERE status='completed';我使用 EXPLAIN SELECT DISTINCT user_id AND ORDERS WHERE status='completed';视图执行计划中的类型为ref，Extra发现临时使用且没有文件类型在使用。
我知道这个问题已经优化了。

总的来说，优化DISTINCT查询的关键是尽量减少扫描的数据量；索引结构合理应用；就是通过分析执行计划来避免临时表操作，进行针对性的优化。
我希望这些经验可以帮助到你。

mysqlmysql如何优化distinct多列查询

结论：纳入综合指标是核心。
解释审查实施计划。
内存参数只是辅助性的。

包括综合索引：
包含 DISTINCT 的所有列。

首先列出 WHERE 条件。

示例：SELECT DISTINCT col1 , col2 FROM my_table WHERE col3 ='val'，构建索引（col3 , col1 , col2 ）。

解释看看是否使用索引。

说明：
使用索引是最好的。

使用临时或文件排序是最糟糕的。

分组依据和不同：
DISTINCT 是纯粹的重复删除。

GROUP BY 通常与聚合函数相关。

查看EXPLAIN的性能，大多数情况都是相似的。

内存规格：
tmp_table_size：内存临时表的上限。

max_heap_table_size：MEMORY表的上限。

sort_buffer_size：排序内存。

根据具体情况调整，如SET SESSION tmp_table_size=1 2 8 M。

别再挑剔了。
这就是优化点。