mysql优化之(use temporary、use filesort)

本文介绍MySQL查询过程中出现的usetemporary和usefilesort的原因及其解决办法,并解析Extra字段中各项指示的意义,帮助读者优化数据库查询效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

目录

 

一、use temporary

二、use filesort

三、extra的其它字段说明

 

一、use temporary
      为了解决查询,MySQL需要创建一个临时表来容纳结果。

use temporary产生条件:

 如果GROUP BY 的列没有索引,产生临时表.
 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.
 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.
    如果group by 的列没有索引,必产生内部临时表,
   如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表
 

解决方式:根据如上use temporary产生条件对应的解决,mysql也不会再使用use temporary。

 

二、use filesort
     MySQL需要额外的一次传递,以对查询出的数据进行排序。文件排序(文件可能在磁盘,也可能在内存)。

需要注意的是:由于 Using filesort是使用算法在 内存中进行排序,MySQL对于排序的记录的大小也是有做限制:max_length_for_sort_data,默认为1024
show variables like '%max_length_for_sort_data%';

+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024 |
+--------------------------+-------+
经过笔者测试,如果排序查询的数据两大于这个默认值的话,还是会使用Using filesort。

解决方式:当排序查询的数据量在默认值1024的范围内时,在排序的字段上加上索引可以提升MySQL查询的速度,mysql也不会再使用use filesort。

 

三、extra的其它字段说明
useing  index: 是指用到了索引覆盖,效率非常高

using where 是指光靠索引定位不了,还得where判断一下

Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。

Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。

range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。

Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。

Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。

Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。

Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
————————————————
版权声明:本文为优快云博主「Lei_Da_Gou」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.youkuaiyun.com/Lei_Da_Gou/article/details/89448282

<think>好的,我现在需要帮助用户分析和优化MySQL的多表联合查询性能。首先,我得理清楚用户的问题。用户想知道分析方法和最佳实践,所以我要从这两个方面入手。 首先,分析性能的方法。根据引用[3],MySQL不会告诉用户获取了多少行来构建结果集,但用户可以通过EXPLAIN命令来查看执行计划。执行计划中的rows列很重要,它显示了预估需要扫描的行数。如果这个值很大,但返回的行数很少,说明存在效率问题。比如,如果全表扫描但只返回少量数据,可能需要索引优化。 然后,优化方法。覆盖索引是一个关键点,引用[3]提到覆盖索引可以直接从索引中获取数据,避免回表,减少IO。接着是索引优化,特别是关联字段的索引,比如外键字段必须有索引,否则可能会导致全表扫描。另外,避免使用SELECT *,只选择需要的列,减少数据传输量。 用户可能还涉及到分库分表的情况,比如引用[2]提到不同的数据格式和数据库存储,可能需要考虑数据冗余或者汇总表来优化查询。比如物化视图或者汇总表,减少联查的复杂度。 还有,子查询优化。有时候子查询会导致性能问题,可以尝试转换为JOIN操作,或者使用EXISTS代替IN。此外,查询重写也很重要,比如将复杂的查询拆分成多个简单步骤,利用临时表来存储中间结果。 另外,硬件和配置优化,比如调整join_buffer_size,但需要根据具体情况来调整,不能盲目增加。分页优化,避免使用大偏移量的LIMIT,改用WHERE条件过滤。 可能还需要考虑数据库引擎的选择,比如引用[1]提到ClickHouse在OLAP领域的优势,但用户的问题是关于MySQL的,所以可能需要结合业务场景,如果联查复杂且数据量大,是否可以考虑其他数据库或者分析型引擎。 最后,用户可能需要实际案例来理解这些优化方法,比如具体如何分析执行计划,如何创建覆盖索引,或者如何重写查询语句。同时,生成的相关问题需要考虑用户可能的后续疑问,比如索引失效的情况,如何监控性能,或者分库分表的策略。</think>### MySQL多表联查性能优化分析与实践 #### 一、性能分析方法 1. **执行计划分析** 使用`EXPLAIN`命令查看执行计划,重点关注: - `type`字段判断访问类型(最好达到`ref`或`eq_ref`) - `rows`字段估算扫描行数,若该值远大于实际返回行数,说明存在无效扫描[^3] - `Extra`字段提示是否使用临时表(`Using temporary`)或文件排序(`Using filesort`) 2. **慢查询日志分析** 启用`slow_query_log`记录执行时间超过阈值的查询,通过`mysqldumpslow`工具分析高频低效联查。 3. **数据访问量对比** 对比`EXPLAIN`预估行数与实际返回行数,若扫描行数远高于返回行数,说明存在优化空间。 #### 二、优化实践方案 1. **索引优化** - **关联字段必建索引**:确保`JOIN`条件字段(尤其是外键字段)有索引 ```sql ALTER TABLE orders ADD INDEX idx_customer_id(customer_id); ``` - **覆盖索引优化**:包含查询所需全部字段的复合索引可避免回表操作[^3] ```sql CREATE INDEX idx_covering ON products(name, price, category_id); ``` 2. **查询重写优化** - **子查询转JOIN**:将相关子查询改写为`JOIN`操作 ```sql -- 优化前 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); -- 优化后 SELECT users.* FROM users JOIN orders ON users.id = orders.user_id; ``` - **分阶段执行**:拆分复杂联查为多个简单查询,使用临时表存储中间结果 ```sql CREATE TEMPORARY TABLE temp_orders SELECT product_id FROM orders WHERE create_time > '2023-01-01'; SELECT products.* FROM products JOIN temp_orders ON products.id = temp_orders.product_id; ``` 3. **数据结构优化** - **冗余设计**:对高频访问的关联字段进行冗余存储(需权衡数据一致性) - **汇总表预计算**:针对统计类查询创建定期更新的汇总表[^3] ```sql CREATE TABLE sales_summary ( product_id INT PRIMARY KEY, total_sales DECIMAL(10,2), update_time TIMESTAMP ); ``` 4. **执行配置优化** - 调整`join_buffer_size`(默认256KB),建议不超过1GB - 设置`optimizer_switch='batched_key_access=on'`启用BKA连接算法 5. **分页优化技巧** ```sql -- 低效写法 SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id LIMIT 1000000, 20; -- 高效写法 SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE orders.id > 1000000 ORDER BY orders.id LIMIT 20; ``` #### 三、典型案例分析 **场景**:订单系统联查客户信息表,响应时间超过2秒 **优化步骤**: 1. `EXPLAIN`显示`customers`表全表扫描 2. 为`customers.email`字段添加索引 3. 改写查询为覆盖索引扫描: ```sql SELECT customers.email, orders.total FROM orders USE INDEX(idx_customer_id) JOIN customers USE INDEX(idx_email) ON orders.customer_id = customers.id WHERE customers.email LIKE 'VIP%@domain.com'; ``` 4. 响应时间降至200ms内
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值