MySQL查询优化:连接查询排序limit(join、order by、limit语句)

MySQL查询优化:连接查询排序limit(join、order by、limit语句)

不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,
但是加上order by以后,语句的执行时间变的巨长,效率巨低。

www.bitsCN.com

情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个
team,people中有个字段team_id。

下面给出建表语句:

[sql]

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 onp.
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秒左右,比之前的[语句①]提高了20倍。

这两个表的结构很简单,如果遇到复杂的表结构…我在实际开发中就碰到了这样的
问题,使用[语句①]的方式耗时80多秒,但使用[语句④]只需1秒以内。

最后给出造数据的存储过程:

[sql]

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

时间: 2024-10-26 08:09:46

MySQL查询优化:连接查询排序limit(join、order by、limit语句)的相关文章

MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍_Mysql

不知道有没有人碰到过这样恶心的问题:两张表连接查询并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 (

MySQL · 捉虫动态 · 5.6中ORDER BY + LIMIT 错选执行计划

问题描述 create table t1(id int auto_increment primary key, a int, b int, c int, v varchar(1000), key iabc(a,b,c), key ic(c)) engine = innodb; insert into t1 select null,null,null,null,null; insert into t1 select null,null,null,null,null from t1; insert

Yii2实现跨mysql数据库关联查询排序功能代码

背景:在一个mysql服务器上(注意:两个数据库必须在同一个mysql服务器上)有两个数据库: memory (存储常规数据表) 中有一个 user 表(记录用户信息) memory_stat (存储统计数据表) 中有一个 user_stat (记录用户统计数据) 现在在 user 表生成的 GridView 列表中展示 user_stat 中的统计数据 只需要在User的model类中添加关联 public function getStat() { return $this->hasOne(U

连接查询-sql left join 的探讨

问题描述 sql left join 的探讨 select a.xx,b.xx,c.xx from table1 a left join table2 b on a.xxx = b.xxx left join table3 c on a.xxx = c.xxx select a.xx,b.xx,c.xx from table1 a left join table2 b on a.xxx = b.xxx left join table3 c on b.xxx = c.xxx select ab.*

MYSQL 多表查询、删除、更新一些sql语句

例  代码如下 复制代码 SELECT cat.`name` , class.`title` FROM `cat` , `class` WHERE cat.`id` = class.`cat` AND cat.`id` =2 LIMIT 0 , 30 DELETE cat, class FROM cat, class WHERE cat.`id`=class.`cat` AND cat.`id`=1 多表更新在 MySQL 3.23 中,你可以使用 LIMIT # 来确保只有给定的记录行数目被更

Mysql左连接sql left join 语句详解

mysql教程左连接sql left join 语句详解 mysql左连接查询是联合查询中的一种方式,就是说把两个相关的表通过这种方式联合在一起查询,从而更加方便调用数据,避免多层循环嵌套. 左连接查询所用关键字 left join ,书面解释如下:   左外连接(左连接):结果集既包括连接表的匹配行,也包括左连接表的所有行. 下面我解释一下,也就是说左链接查询的返回结果既包含了left join关键字左边表的全部资料,同时也包含了与左表匹配的右表中的符合条件的资源. 简单的说就是以左表为中心,

php基础之连接mysql数据库和查询数据_Mysql

连接数据库,有三种方法 1. 常规方式: $con=mysql_connect($dbhostip,$username,$userpassword) or die("Unable to connect to the MySQL!"); $db = mysql_select_db($dbdatabasename,$con); //执行语句 $qres=mysql_query("SELECT id,GoodsName FROM user"); //提取一条数据 11 $

什么是mysql左链接查询 如何使用?

MySQL左连接查询是连接查询中的一种方式,下面就为您介绍MySQL左连接查询中的一些问题谈论,如果您感兴趣的话,不妨一看. 我这里所说的主表是指在连接查询里MySQL以哪个表为主进行查询.比如说在MySQL左连接查询里,一般来说左表就是主表,但这只是经验之谈,很多时候经验主义是靠不住的,为了说明问题,先来个例子,建两个演示用的表categories和posts: CREATE TABLE IF NOT EXISTS `categories` (     `id` int(10) unsigne

mysql-MySQL内连接查询结果的显示顺序

问题描述 MySQL内连接查询结果的显示顺序 表fruits的字段f_id为primary key 表suppliers的字段s_id为primary key与fruits的s_id有相同的数据类型 然后使用这句查询语句: 1.select suppliers.s_id,s_name,f_name,f_price from suppliers inner join fruits on suppliers.s_id=fruits.s_id; 查询的都结果显示如下: 2.变换表的位置再查询: sel