不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低。
情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id。
下面给出建表语句:
create table t_team ( id int primary key, tname varchar(100) ); create table t_people ( id int primary key, pname varchar(100), team_id int, foreign key (team_id) references t_team(id) );
下面我要连接两张表查询出前10个people,按tname排序。
于是,一个SQL语句诞生了:
select * from t_people p left join t_team t on p.team_id=t.id order by p.pname limit 10; [语句①]
这个是我第一反应写的SQL,通俗易懂,也是大多数人的第一反应。然后来测试一下这个语句的执行时间。首先要准备数据。我用存储过程在t_team表中生成1000条数据,在t_people表中生成100000条数据。(存储过程在本文最后)
执行上面那条SQL语句,执行了好几次,耗时在3秒左右。
再换两个语句对比一下:
1.把order by子句去掉:
select * from t_people p left join t_team t on p.team_id=t.id limit10; [语句②]
耗时0.00秒,忽略不计。
2.还是使用order by,但是把连接t_team表去掉:
select * from t_people p order by p.pname limit 10; [语句③]
耗时0.15秒左右。
对比发现[语句①]的效率巨低。
为什么效率这么低呢。[语句②]和[语句③]执行都很快,[语句①]不过是二者的结合。如果先执行[语句③]得到排序好的10条people结果后,再连接查询出各个people的team,效率不会这么低。那么只有一个解释:MySQL先执行连接查询,再进行排序。
解决方法:如果想提高效率,就要修改SQL语句,让MySQL先排序取前10条再连接查询。
SQL语句:
select * from (select * from t_people p order by p.pname limit 10) p left join t_team t on p.team_id=t.id limit 10; [语句④]
[语句④]和[语句①]功能一样,虽然有子查询,虽然看起来很别扭,但是效率提高了很多,它的执行时间只要0.16秒左右,比之前的[语句①] (耗时3秒) 提高了20倍。
这两个表的结构很简单,如果遇到复杂的表结构…我在实际开发中就碰到了这样的问题,使用[语句①]的方式耗时80多秒,但使用[语句④]只需1秒以内。
最后给出造数据的存储过程:
CREATE PROCEDURE createdata() BEGIN DECLARE i INT; START TRANSACTION; SET i=0; WHILE i<1000 DO INSERT INTO t_team VALUES(i+1,CONCAT('team',i+1)); SET i=i+1; END WHILE; SET i=0; WHILE i<100000 DO INSERT INTO t_people VALUES(i+1,CONCAT('people',i+1),i%1000+1); SET i=i+1; END WHILE; COMMIT; END
作者:叉叉哥 转载请注明出处:http://blog.csdn.net/xiao__gui/article/details/8616224
相关推荐
在MySQL查询优化中,连接查询和排序是两个关键的环节,尤其当它们结合在一起时,可能会引发性能问题。本文将探讨一个具体的例子,即在连接查询中加入`ORDER BY`子句导致效率骤降的问题,以及如何通过优化SQL语句来...
在MySQL查询优化中,连接查询(join)与排序(order by)和限制返回结果的数量(limit)是常见的操作,但当它们结合在一起时,可能会导致性能下降。这个问题在标题和描述中已经阐述得很清楚,主要涉及到如何高效地...
使用`mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/htzw-slow.log`可以查看包含左连接且按时间排序的前10个慢查询。使用`| more`命令可以避免因结果过多而导致的屏幕信息滚动过快问题。 总结来说,...
二、MySQL优化策略 1. **查询重构**:通过分析慢查询日志,找出低效查询并进行重构,比如减少子查询,合并多条SQL,使用EXPLAIN分析查询执行计划。 2. **硬件升级**:增加内存容量,使用更快的SSD硬盘,或者分布式...
6. **MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍** 7. **mysql的中文数据按拼音排序的2个方法** 8. **MySQL中按照多字段排序及问题解决** 9. **mysql 关键词相关度排序方法详细示例分析** ...
"MYSQL 查询效率优化技巧" 在数据库应用中,MySQL 查询效率对程序的执行速度有很大的影响。有效的处理优化数据库是非常有用的,尤其是大量数据需要处理的时候。以下是十个 SQL 语句优化技巧来提升 MYSQL 查询效率:...
MySQL 性能优化 - 查询优化技术 MySQL 性能优化是数据库管理员和开发者最关心的问题之一,如何分析和优化 SQL 查询以提高性能是 MySQL 性能优化的核心技术之一。在本资源中,我们将介绍 MySQL 性能优化的基本概念、...
### 高效MySQL查询加速指南:索引策略、查询优化、性能调优 在数据库管理和开发领域中,MySQL因其灵活性和高效性而受到广泛欢迎。然而,在面对大规模数据集时,即使是性能强大的MySQL也可能遇到查询速度慢的问题。...
MySQL查询优化是数据库管理中的关键环节,它直接影响到应用程序的性能和用户体验。在这个主题中,我们将深入探讨SQL诊断调优的原则、原理以及实现优化的思路。 首先,理解SQL查询优化的重要性至关重要。在高并发和...
### MySQL查询优化浅析 #### 一、查询优化概述 查询优化是数据库管理系统(DBMS)中的一个重要组成部分,其主要目标是在接收到一个SQL查询后,寻找最高效的执行计划,以尽可能快的速度返回查询结果。这一过程涉及到...
5. **query_cache_size**:查询缓存大小。适当设置此值可以提高查询性能,但过大的缓存可能会导致更多的内存占用。 6. **read_rnd_buffer_size**:读取随机数据时的缓冲区大小。 7. **sort_buffer_size**:排序操作...
在处理MySQL海量数据查询优化时,我们需要关注的策略包括但不限于以下几点: 1. 优化索引使用:避免全表扫描至关重要。为此,应当在查询条件(WHERE)和排序(ORDER BY)涉及的列上创建索引。索引有助于数据库管理...
### MySQL查询流程分析 在深入理解MySQL查询流程...通过合理地利用查询优化技术、权限管理和缓存机制等功能,可以显著提升MySQL系统的整体性能和用户体验。希望本文能为大家在学习和使用MySQL的过程中提供一定的帮助。
MySQL的执行优化主要涉及查询计划的选择、索引的使用以及SQL语句的优化。在数据库操作中,SQL语句的执行效率直接影响到系统的响应时间。优化执行过程包括避免全表扫描,尽可能利用索引来加速查询,以及减少不必要的...
3. **系统配置优化**:调整MySQL服务器的参数,如增大缓冲区大小、设置合理的连接超时时间、优化排序缓存等,可以更好地利用硬件资源,提升系统整体性能。 4. **硬件优化**:硬件升级,如使用更快的磁盘、增加内存...
MySQL性能优化是一个涵盖广泛的主题,涉及多个层面,包括SQL语句优化、索引优化、数据库表结构优化、系统级配置优化以及服务器硬件优化。以下是对这些方面进行详细说明: 1. **SQL语句优化** - **慢查询日志**:...
MySQL索引原理及慢查询优化是数据库管理中的重要主题,尤其是在高并发、大数据量的互联网环境中,优化查询性能对于系统的整体效能至关重要。MySQL作为广泛使用的开源关系型数据库,其索引机制和查询优化技巧是开发者...
MySQL 数据库查询优化方案 MySQL 数据库查询优化是指对数据库中的查询语句进行优化,以提高查询效率和数据库性能。以下是 MySQL 数据库查询优化的一些重要知识点: 一、索引相关 索引是 MySQL 数据库查询优化的...