不知道有没有人碰到过这样恶心的问题:两张表连接查询并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)是常见的操作,但当它们结合在一起时,可能会导致性能下降。这个问题在标题和描述中已经阐述得很清楚,主要涉及到如何高效地...
二、MySQL优化策略 1. **查询重构**:通过分析慢查询日志,找出低效查询并进行重构,比如减少子查询,合并多条SQL,使用EXPLAIN分析查询执行计划。 2. **硬件升级**:增加内存容量,使用更快的SSD硬盘,或者分布式...
"MYSQL 查询效率优化技巧" 在数据库应用中,MySQL 查询效率对程序的执行速度有很大的影响。有效的处理优化数据库是非常有用的,尤其是大量数据需要处理的时候。以下是十个 SQL 语句优化技巧来提升 MYSQL 查询效率:...
MySQL 性能优化 - 查询优化技术 MySQL 性能优化是数据库管理员和开发者最关心的问题之一,如何分析和优化 SQL 查询以提高性能是 MySQL 性能优化的核心技术之一。在本资源中,我们将介绍 MySQL 性能优化的基本概念、...
MySQL查询优化是数据库管理中的关键环节,它直接影响到应用程序的性能和用户体验。在这个主题中,我们将深入探讨SQL诊断调优的原则、原理以及实现优化的思路。 首先,理解SQL查询优化的重要性至关重要。在高并发和...
MySQL的执行优化主要涉及查询计划的选择、索引的使用以及SQL语句的优化。在数据库操作中,SQL语句的执行效率直接影响到系统的响应时间。优化执行过程包括避免全表扫描,尽可能利用索引来加速查询,以及减少不必要的...
MySQL索引原理及慢查询优化是数据库管理中的重要主题,尤其是在高并发、大数据量的互联网环境中,优化查询性能对于系统的整体效能至关重要。MySQL作为广泛使用的开源关系型数据库,其索引机制和查询优化技巧是开发者...
MySQL性能优化是一个涵盖广泛的主题,涉及多个层面,包括SQL语句优化、索引优化、数据库表结构优化、系统级配置优化以及服务器硬件优化。以下是对这些方面进行详细说明: 1. **SQL语句优化** - **慢查询日志**:...
MySQL 数据库查询优化方案 MySQL 数据库查询优化是指对数据库中的查询语句进行优化,以提高查询效率和数据库性能。以下是 MySQL 数据库查询优化的一些重要知识点: 一、索引相关 索引是 MySQL 数据库查询优化的...
综上所述,MySQL优化是一个全面的过程,包括SQL查询、存储引擎、架构设计、服务器配置、日志管理和工具使用等多个方面。通过综合考虑,我们可以有效地提升MySQL的性能,满足业务需求。记得在优化过程中,始终关注...
- **sort_buffer_size**:查询排序时的缓冲区大小,应考虑到并发连接数,避免总分配内存过大。 在进行MySQL配置优化时,必须考虑到服务器的硬件能力和预期的工作负载。不同的参数设置会直接影响到查询性能、内存...
MySQL优化是数据库管理中至关重要的环节,它涉及到提升查询性能、降低资源消耗,从而提高整个系统的响应速度和服务质量。MySQL自带的慢查询日志(slow query log)是进行优化的重要工具之一,它记录了执行时间超过预设...
5. 结果集查看:查询结果会以清晰的表格形式显示,可以进行排序、过滤、格式化等操作,便于分析和理解。 6. 备份与恢复:工具可能包含备份数据库到文件和从备份文件恢复数据库的功能,这对于防止数据丢失至关重要。...
1. **MySQL优化**: - **查询优化**:通过编写高效的SQL语句,减少全表扫描,避免子查询,使用JOIN代替多个单表查询,以及合理使用索引,可以显著提高查询速度。 - **索引优化**:创建合适的索引能加快数据检索...
MySQL索引优化是数据库性能提升的关键技术之一,尤其在处理大量数据时,高效索引能够显著加快查询速度,降低服务器负载。本课件主要聚焦于MySQL数据库的索引原理、优化策略以及相关存储过程和触发器的应用。 首先,...
实例讲解 MYSQL 数据库的查询优化技术 在数据库系统中,查询操作占据着最大的比重,而查询操作所基于的 SELECT 语句在 SQL 语句中又是代价最大的语句。如果不使用优化技术,查询时间可能会很长,甚至需要数十分钟或...
5. query_cache_size:查询缓存大小,可加快重复查询的速度,但需权衡内存使用和缓存维护开销。 6. record_buffer_size、read_rnd_buffer_size、sort_buffer_size等缓冲区大小,影响数据读取和排序性能。 7. join_...
MySQL性能优化是数据库管理员和开发者的必修课,它涵盖了多个方面,包括查询优化、索引策略、存储引擎选择、数据库架构设计等。以下是对"MySQL性能优化的21个最佳实践"的详细阐述: 1. **查询优化**:编写高效的SQL...