您的位置:新葡亰496net > 网络数据库 > 连接查询排序浅谈,MySQL查询优化

连接查询排序浅谈,MySQL查询优化

发布时间:2019-08-09 19:25编辑:网络数据库浏览(106)

      编程萌新,因为遇到这么个SQL 查询的问题:在一张表A里有如下字段:学生姓名、学科名、学科成绩。写一条SQL 语句查出各科平均成绩并按学生姓名分组,按如下格式显示:学生姓名|语文|数学|英语。一开始遇到的时候挺懵的,有印象但是却忘了怎么写了,接下来就是查阅资料并亲自上手测试是否可行~

    多表联结查询效率问题一直是我们开发人员讨论的一个问题,下面我把我在使用多表查询时的一些测试也分享给各位朋友,希望对大家所有帮助。

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

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

    一、问题:

      第一次代码修修改改如下,此时还是在只用一张表来查询(无法实现上述目标结果,贴图只是为了说明下思考路线):

    最近做网站的时候遇到一个问题,我需要输出B表和C表中的两个字段,不过纠结的是,程序所得到的参数是A表中的id,而A表与B表及C表关联的是另一个字段。如此一来,我就需要从3个表中读取数据,刚开始我想用SQL的多表联结查询,可是又担心效率太低。如果我先读出A表中与B、C关联的字段的值,再运行另一条SQL语句使用该值读取B、C表中的值效率是否会高一些?思索良久,最终决定写一个程序来测试。

    下面给出建表语句:

    下面给出建表语句:

    数据库是MS SQLServer2000,要把SQLServer2000里的一张表的数据导入MySQL5,其中SQLServer2000表的字段以简体中文命名(强烈建议不要以中文做为字段名)。其实操作就是对SQLServer查询记录,插入到MySQL里。选择的脚本语言是PHP,PHP打开MSSQL和MySQL扩展,对这两个数据库操作都是很容易的问题。
    问题就出现在SQLServer中表的字段名是中文,写好的查询语句在SQLServe里测试是通过有记录返回,用PHP的MSSQL扩展查询就是报错。
    上网查了资料,网上相关的信息不太多,很多网友都认为是PHP的MSSQL扩展不支持SQL语句中有中文。查了一下资料,PHP的MSSQL是支持SQL中有中文的语句。出现报错问题大多是因为编码的问题,编码数据库和编码与查询语句编码不统一,查询语句到SQLServer里中文部分就成了乱码,造成查询失败。

        

    以下代码以Discuz!X2.5的数据库为例,查询tid为1的主题发表者的username。

    复制代码 代码如下:

    复制代码 代码如下:

    二、解决方法:

     1 select UName,
     2 (select AVG(Grades) from UserInfo where GName = '语文') as '语文',
     3 (select AVG(Grades) from UserInfo where GName = '数学') as '数学',
     4 (select AVG(Grades) from UserInfo where GName = '英语') as '英语' 
     5 from UserInfo
     6 where UName = '小明'
     7 group by UName
     8 /*having GName = '语文' and GName = '数学' and GName = '英语'*/
     9 order by AVG(Grades)
    10 go
    

     代码如下

    create table t_team
    (
    id int primary key,
    tname varchar(100)
    );

    create table t_team
    (
    id int primary key,
    tname varchar(100)
    );

    知道了原因,接下来分析解决,确认是编码不统一的问题。解决分以下几步:
    1、确认SQLServer 数据库的编码,我的数据编码是GBK。
    2、确认当前PHP脚本文件的编码,我的编码是UTF-8。
    3、转换SQL查询语句的的编码。
    补充:有的网友提到要把PHP的脚本文件编码转成和数据库编码一致,其实这一步大可不必,只要确认你的SQL语句和数据库的编码一致就可以,这里建议不必转换的原因是如果你的PHP脚本文件里包含其它PHP脚本,那也得对所有include或require的脚本文件编码转换,不然PHP脚本编码不统一很容易出错,若互相关联的文件很多,这也是一件很麻烦的问题而且把事情复杂化了。

     

    复制代码

    create table t_people
    (
    id int primary key,
    pname varchar(100),
    team_id int,
    foreign key (team_id) references t_team(id)
    );

    create table t_people
    (
    id int primary key,
    pname varchar(100),
    team_id int,
    foreign key (team_id) references t_team(id)
    );

    三、方案:
    写一个转换函数,在把SQL操作前把SQL语句编码转换。下面贴出我的范例代码:

      然后查阅资料也没有头绪,就想只用一张表是否真的可行,要不用两张表试一下?接下来就是在原表A(实际为如下代码中 UserInfo表)基础上插入一个 学生id 的字段,然后新建一张学生表(T_User),拥有两个字段:id、姓名,学生表的id匹配表A里的学生id,查询语句通过连接实现(因为是思考一个问题,就不考虑内外或者交叉连接的问题了),代码如下:

    <?php
        $start = time();
        for($i = 1; $i < 10000; $i ){
         $writerInfo = DB::fetch_first("SELECT a.username FROM
         pre_common_连接查询排序浅谈,MySQL查询优化。member AS a, pre_forum_thread AS b
         WHERE b.tid='1' AND a.uid=b.authorid");
        }
        echo(time() - $start);
        ?>

    下面我要连接两张表查询出前10个people,按tname排序。

    下面我要连接两张表查询出前10个people,按tname排序。

    复制代码 代码如下:

    1 select UName,
    2 (select AVG(Grades) from UserInfo where GName = '语文' and UName = tu.name) as '语文',
    3 (select AVG(Grades) from UserInfo where GName = '数学' and UName = tu.name) as '数学',
    4 (select AVG(Grades) from UserInfo where GName = '英语' and UName = tu.name) as '英语' 
    5  from UserInfo as ui join T_User as tu
    6  on ui.UName = tu.name
    7 group by ui.UName,tu.name
    8 go
    

    这段代码在执行SQL查询前先保存当前时间戳,然后执行一万次多表联结查询,然后用执行完毕后的时间戳减去开始前的时间戳,得到运行时间。
        然后将循环块中代码改为如下:

    于是,一个SQL语句诞生了:select * from t_people p left join t_team t onp.team_id=t.id order by p.pname limit 10; [语句①]

    于是,一个SQL语句诞生了:select * from t_people p left join t_team t onp.team_id=t.id order by p.pname limit 10; [语句①]

    //编码转换函数
    function utf8togb($s) {
    return iconv('utf-8', 'gbk//IGNORE', $s); // IGNORE 参数是遇到不成转换的字符时忽略
    }
    //建议把所有中文字段用英文别名替换,方便下面操作还有编码转换等问题
    $sql="SELECT [id], [栏目] as typeid, [正题] as title, [作者] as author, convert(text, [正文]) as body FROM [文章表];";
    $sql = utf8togb($sql);

     

     代码如下

    这个是我第一反应写的SQL,通俗易懂,也是大多数人的第一反应。

    这个是我第一反应写的SQL,通俗易懂,也是大多数人的第一反应。

    您可能感兴趣的文章:

    • Linux编译mssql扩展使用php连接sqlserver2008的使用步骤
    • Linux下安装PHP MSSQL扩展教程
    • CentOS 6.3下给PHP添加mssql扩展模块教程
    • PHP连接MSSQL方法汇总
    • php5.3不能连接mssql数据库的解决方法
    • php使用pdo连接mssql server数据库实例
    • PHP连接MSSQL2008/2005数据库(SQLSRV)配置实例
    • php连接mssql数据库的几种方法
    • php连接mssql的一些相关经验及注意事项
    • 关于php连接mssql:pdo odbc sql server
    • php 连接mssql数据库 初学php笔记
    • php adodb连接mssql解决乱码问题
    • PHP基于mssql扩展远程连接MSSQL的简单实现方法

      运行测试如下:

    复制代码

    然后来测试一下这个语句的执行时间。

    然后来测试一下这个语句的执行时间。

    图片 1

    $tid = DB::fetch_first("SELECT authorid FROM pre_forum_thread WHERE tid='1'");

    首先要准备数据。我用存储过程在t_team表中生成1000条数据,在t_people表中生成100000条数据。(存储过程在本文最后)

    首先要准备数据。我用存储过程在t_team表中生成1000条数据,在t_people表中生成100000条数据。(存储过程在本文最后)

      成功!研究并解决差不多花了1个多小时,可能资质不行,但是会继续努力。从简单入手,从萌新出发~

    $user = DB::fetch_first("SELECT username FROM pre_common_member WHERE uid='$tid[authorid]'");

    执行上面那条SQL语句,执行了好几次,耗时在3秒左右。

    执行上面那条SQL语句,执行了好几次,耗时在3秒左右。

    这次把SQL语句分为两次进行执行。
        为了让结果准确,两段代码分别执行了三次,结果第一段代码的执行时间分别为13、13和15秒,而第二段代码执行时间分别为23、21、22。
        可以看出,将多表联结的SQL分为多次执行,时间将比一次多表联结要长大概80%左右,结论是直接执行一行多表连接效率更高

    再换两个语句对比一下:   

    再换两个语句对比一下:   

    下面讲一些题外话了,不过也是关于多表联合查询的

    1.把order by子句去掉:select * from t_people p left join t_team t on p.team_id=t.id limit10; [语句②]

    1.把order by子句去掉:select * from t_people p left join t_team t on p.team_id=t.id limit10; [语句②]

    inner join,full outer join,left join,right jion
    内部连接 inner join 两表都满足的组合
    full outer 全连 两表相同的组合在一起,A表有,B表没有的数据(显示为null),同样B表有
    A表没有的显示为(null)
    A表 left join  B表 左连,以A表为基础,A表的全部数据,B表有的组合。没有的为null
    A表 right join B表 右连,以B表为基础,B表的全部数据,A表的有的组合。没有的为null

    耗时0.00秒,忽略不计。

    耗时0.00秒,忽略不计。

    查询分析器中执行:
    --建表table1,table2:
    create table table1(id int,name varchar(10))
    create table table2(id int,score int)
    insert into table1 select 1,'lee'
    insert into table1 select 2,'zhang'
    insert into table1 select 4,'wang'
    insert into table2 select 1,90
    insert into table2 select 2,100
    insert into table2 select 3,70

    2.还是使用order by,但是把连接t_team表去掉:select * from t_people p order by p.pname limit 10;  [语句③]

    2.还是使用order by,但是把连接t_team表去掉:select * from t_people p order by p.pname limit 10;  [语句③]

    如表

    耗时0.15秒左右。

    耗时0.15秒左右。

    table1|table2|

    idname|idscore|
    1lee|190|
    2zhang|2100|

    对比发现[语句①]的效率巨低。

    对比发现[语句①]的效率巨低。

    4wang|370|

    以下均在查询分析器中执行

    一、外连接
    1.概念:包括左向外联接、右向外联接或完整外部联接

    2.左连接:left join 或 left outer join
    (1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
    (2)sql语句

     代码如下

    复制代码

    select * from table1 left join table2 on table1.id=table2.id
    -------------结果-------------

    为什么效率这么低呢。[语句②]和[语句③]执行都很快,[语句①]不过是二者的结合。如果先执行[语句③]得到排序好的10条people结果后,再连接查询出各个people的team,效率不会这么低。那么只有一个解释:MySQL先执行连接查询,再进行排序。

    为什么效率这么低呢。[语句②]和[语句③]执行都很快,[语句①]不过是二者的结合。如果先执行[语句③]得到排序好的10条people结果后,再连接查询出各个people的team,效率不会这么低。那么只有一个解释:MySQL先执行连接查询,再进行排序。

    idnameidscore

    1lee190
    2zhang2100

    解决方法:如果想提高效率,就要修改SQL语句,让MySQL先排序取前10条再连接查询。

    解决方法:如果想提高效率,就要修改SQL语句,让MySQL先排序取前10条再连接查询。

    4wangNULLNULL

    注释:包含table1的所有子句,根据指定条件返回table2相应的字段,不符合的以null显示

    3.右连接:right join 或 right outer join
    (1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
    (2)sql语句

     代码如下

    复制代码

    select * from table1 right join table2 on table1.id=table2.id
    -------------结果-------------

    SQL语句:

    SQL语句:

    idnameidscore

    1lee190
    2zhang2100
    NULLNULL370


    注释:包含table2的所有子句,根据指定条件返回table1相应的字段,不符合的以null显示

    4.完整外部联接:full join 或 full outer join
    (1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
    (2)sql语句

     代码如下

    复制代码

    select * from table1 full join table2 on table1.id=table2.id
    -------------结果-------------

    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; [语句④]

    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; [语句④]

    idnameidscore

    1lee190
    2zhang2100
    4wangNULLNULL
    NULLNULL370


    注释:返回左右连接的和(见上左、右连接)

    二、内连接
    1.概念:内联接是用比较运算符比较要联接列的值的联接

    2.内连接:join 或 inner join

    3.sql语句

     代码如下

    复制代码

    select * from table1 join table2 on table1.id=table2.id
    -------------结果-------------

    [语句④]和[语句①]功能一样,虽然有子查询,虽然看起来很别扭,但是效率提高了很多,它的执行时间只要0.16秒左右,比之前的[语句①]提高了20倍。

    [语句④]和[语句①]功能一样,虽然有子查询,虽然看起来很别扭,但是效率提高了很多,它的执行时间只要0.16秒左右,比之前的[语句①]提高了20倍。

    idnameidscore

    1lee190
    2zhang2100


    注释:只返回符合条件的table1和table2的列

    4.等价(与下列执行效果相同)

     代码如下

    复制代码

    A:select a.*,b.* from table1 a,table2 b where a.id=b.id
    B:select * from table1 cross join table2 where table1.id=table2.id 

     (注:cross join后加条件只能用where,不能用on)

    三、交叉连接(完全)

    1.概念:没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。(table1和table2交叉连接产生3*3=9条记录)

    2.交叉连接:cross join (不带条件where...)

    3.sql语句

     代码如下

    复制代码

    select * from table1 cross join table2
    -------------结果-------------

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

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

    idnameidscore

    1lee190
    2zhang190
    4wang190
    1lee2100
    2zhang2100
    4wang2100
    1lee370
    2zhang370
    4wang370


    注释:返回3*3=9条记录,即笛卡尔积

    4.等价(与下列执行效果相同)

     代码如下

    复制代码

    A:select * from table1,table2

    ...

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

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

    复制代码 代码如下:

    复制代码 代码如下:

    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),i00 1);
     SET i=i 1;
    END WHILE;
    COMMIT;
    END

    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),i00 1);
     SET i=i 1;
    END WHILE;
    COMMIT;
    END

    转载自:

    转载自:

    下面给出建表语句: 复制代码 代码...

    您可能感兴趣的文章:

    • MySQL中基本的多表连接查询教程
    • 详解MySQL中的分组查询与连接查询语句
    • MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍
    • MySQL笔记之连接查询详解
    • mysql连接查询(左连接,右连接,内连接)
    • 详解MySql基本查询、连接查询、子查询、正则表达查询
    • MySQL查询优化:用子查询代替非主键连接查询实例介绍
    • MySQL中对表连接查询的简单优化教程
    • 经典mysql连接查询例题
    • MySQL连接查询实例详解

    本文由新葡亰496net发布于网络数据库,转载请注明出处:连接查询排序浅谈,MySQL查询优化

    关键词: