关于mysql的join

举报
仙士可 发表于 2023/06/21 17:09:03 2023/06/21
【摘要】 在qq群,经常听到 "最好不要用join","join用了网站会很卡"类似与这样的言论,那么事实上是这样吗?本来本人是想用理论来说服大家的,但是可能有些人不信理论,只信某些"大神"的凭空言论,所以本人喜欢拿事实说话,再加上理论进行说明测试环境:3张表:create table if not exists article_category_list( categoryId int unsign...

在qq群,经常听到 "最好不要用join","join用了网站会很卡"类似与这样的言论,那么事实上是这样吗?

本来本人是想用理论来说服大家的,但是可能有些人不信理论,只信某些"大神"的凭空言论,所以本人喜欢拿事实说话,再加上理论进行说明

测试环境:

3张表:

create table if not exists article_category_list
(
 categoryId int unsigned auto_increment comment '分类id'
  primary key,
 categoryName varchar(64) not null comment '分类名称',
 pid int unsigned not null comment '父级分类id',
 sort smallint(6) null,
 state tinyint null,
 addTime int null,
 note varchar(255) null
)
charset=utf8mb4;

create table if not exists user_list
(
 userId int unsigned auto_increment
  primary key,
 userName varchar(30) default '默认用户' not null comment '用户名',
 phone varchar(11) not null comment '手机',
 sex tinyint unsigned default 0 not null comment '性别 0 未知  1 男  2女',
 userPassword varchar(255) not null comment '密码',
 userSession varchar(32) null comment '会话信息',
 lastLoginIp varchar(20) null comment '最后一次登录ip',
 lastLoginTime int unsigned null comment '最后一次登录时间',
 addTime int(10) not null comment '用户添加时间',
 constraint uq_phone
  unique (phone)
)
comment '用户' charset=utf8mb4;

create table if not exists article_list
(
 articleId int unsigned auto_increment comment '文章id'
  primary key,
 categoryId int not null comment '分类id',
 categoryName varchar(64) not null comment '分类名称',
 userId int null,
 title varchar(64) not null comment '标题',
 imgUrl varchar(255) not null,
 description varchar(255) null comment '简介',
 author varchar(32) not null comment '作者',
 content text null comment '内容',
 addTime int unsigned not null comment '新增时间',
 updateTime int unsigned null comment '更新时间',
 state tinyint(2) unsigned not null comment '状态 1正常,2隐藏',
 note varchar(255) null
)
charset=utf8mb4;

create index userId
 on article_list (userId);
create index categoryId
 on article_list (categoryId);
复制

mysql为 5.7环境

数据量为: article_category_list:100,user_list:80万,article_list:90万,

本文使用 easyswoole orm组件进行测试.

一对一大数据测试:

通过随机获取10.0个文章数据,同时join获取用户id:

<?php
/**
 * Created by PhpStorm.
 * User: tioncico
 * Date: 20-8-13
 * Time: 下午10:46
 */
include "./vendor/autoload.php";
\EasySwoole\EasySwoole\Core::getInstance()->initialize()->globalInitialize();

go(function () {
    $startTime = microtime_float();
    $count = 0;
    for ($i = 0; $i < 500; $i++) {
        $sql = "select * from article_list as a inner join user_list as b on a.userId=b.userId limit 1000";
        $query = new \EasySwoole\Mysqli\QueryBuilder();
        $query->raw($sql);
        $data = \EasySwoole\ORM\DbManager::getInstance()->getConnection()->defer()->query($query);
        $count += count($data->getResult());
    }

    $endTime = microtime_float();
    echo "join查询数据量:{$count}\n";
    echo $endTime - $startTime . "秒" . PHP_EOL;
    \EasySwoole\Component\Timer::getInstance()->clearAll();
});

/*
*
*返回当前 Unix 时间戳和微秒数(用秒的小数表示)浮点数表示，常用来计算代码段执行时间
*/
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
复制

同样的逻辑,改为分次查询:

<?php
/**
 * Created by PhpStorm.
 * User: tioncico
 * Date: 20-8-13
 * Time: 下午10:46
 */
include "./vendor/autoload.php";
\EasySwoole\EasySwoole\Core::getInstance()->initialize()->globalInitialize();

go(function () {
    $startTime = microtime_float();
    $count = 0;
    for ($i = 0; $i < 500; $i++) {
        $sql = "select * from article_list limit 1000";
        $query = new \EasySwoole\Mysqli\QueryBuilder();
        $query->raw($sql);
        $data = \EasySwoole\ORM\DbManager::getInstance()->getConnection()->defer()->query($query);
        $count += count($data->getResult());
        $articleData = $data->getResult();
        $userIds = [];
        foreach ($articleData as $article) {
            if (empty($article['userId'])) {
                continue;
            }
            $userIds[] = "'{$article['userId']}'";
        }
        $sql = "select * from user_list where `userId` in (" . implode(',', $userIds) . ')';
        $query = new \EasySwoole\Mysqli\QueryBuilder();
        $query->raw($sql);
        \EasySwoole\ORM\DbManager::getInstance()->getConnection()->defer()->query($query);
        //如果需要获取join一样的数据,那就需要对数据进行二次处理,以下代码先注释
//        $userData = $data->getResult();
//        $userList = [];
//        foreach ($userData as $user) {
//            $userList[$user['userId']] = $user;
//        }
//        foreach ($articleData as $key=>$article){
//            if (!isset($userList[$article['userId']])){
//                continue;
//            }
//            $articleData[$key] = array_merge($article,$userList[$article['userId']]);
//            $articleData[$key]['userInfo'] = $userList[$article['userId']];//这是第二种写法
//        }

    }

    $endTime = microtime_float();
    echo "分开查询数据量:{$count}\n";
    echo $endTime - $startTime . "秒" . PHP_EOL;
    \EasySwoole\Component\Timer::getInstance()->clearAll();
});


/*
*
*返回当前 Unix 时间戳和微秒数(用秒的小数表示)浮点数表示，常用来计算代码段执行时间
*/
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
复制

测试结果如下:

[root@tioncico-server homeTest]# php test.php 
join查询数据量:500000
6.7306478023529秒
[root@tioncico-server homeTest]# php test.php 
join查询数据量:500000
6.0196189880371秒
[root@tioncico-server homeTest]# php test.php 
join查询数据量:500000
6.0013608932495秒
[root@tioncico-server homeTest]# php test.php 
join查询数据量:500000
5.9181699752808秒
[root@tioncico-server homeTest]# php test8.php 
分开查询数据量:500000
4.5411529541016秒
[root@tioncico-server homeTest]# php test8.php 
分开查询数据量:500000
4.5213208198547秒
[root@tioncico-server homeTest]# php test8.php 
分开查询数据量:500000
5.7194919586182秒
[root@tioncico-server homeTest]# php test8.php 
分开查询数据量:500000
4.231143951416秒
[root@tioncico-server homeTest]# php test8.php 
分开查询数据量:500000
5.1239991188049秒
[root@tioncico-server homeTest]# php test8.php 
分开查询,组装join的数据,数据量:500000
6.9564120769501秒
[root@tioncico-server homeTest]# php test8.php 
分开查询,组装join的数据,数据量:500000
6.7047250270844秒
[root@tioncico-server homeTest]# php test8.php 
分开查询,组装join的数据,数据量:500000
6.7332708835602秒
[root@tioncico-server homeTest]# php test8.php 
分开查询,组装join的数据,数据量:500000
6.772262096405秒
[root@tioncico-server homeTest]# php test8.php 
分开查询,组装join的数据,数据量:500000
6.7405600547791秒
[root@tioncico-server homeTest]#
复制

可以看出,当获取1000条数据时,join查询大致为6秒左右,分开查询为4.5秒左右,比join快了25%,但是由于分开查询的数据并不是可以直接使用的数据,还需要做数据拼接,这个时候消耗到了6.7秒,比join查询还更慢了0.7秒

一对多小数据测试

为了测试的严谨性,我们每次查出10个user,并且直接join获取所有发布的文章数据.

也就是说,10个user,每个关联n条文章数据.

join方式

<?php
/**
 * Created by PhpStorm.
 * User: tioncico
 * Date: 20-8-13
 * Time: 下午10:46
 */
include "./vendor/autoload.php";
\EasySwoole\EasySwoole\Core::getInstance()->initialize()->globalInitialize();

go(function () {
    $startTime = microtime_float();
    $count = 0;
    for ($i = 0; $i < 5000; $i++) {
        $userIds = [];
        for ($y=0;$y<10;$y++){
            $userIds[] = mt_rand(1,800000);
        }
        $sql = "select * from user_list as a inner join article_list as b on a.userId=b.userId where a.userId in ( ".implode(',',$userIds)." )";
        $query = new \EasySwoole\Mysqli\QueryBuilder();
        $query->raw($sql);
        $data = \EasySwoole\ORM\DbManager::getInstance()->getConnection()->defer()->query($query);
        $count += count($data->getResult());
    }

    $endTime = microtime_float();
    echo "join查询数据量:{$count}\n";
    echo $endTime - $startTime . "秒" . PHP_EOL;
    \EasySwoole\Component\Timer::getInstance()->clearAll();
});


/*
*
*返回当前 Unix 时间戳和微秒数(用秒的小数表示)浮点数表示，常用来计算代码段执行时间
*/
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
复制

分开查询方式:

<?php
/**
 * Created by PhpStorm.
 * User: tioncico
 * Date: 20-8-13
 * Time: 下午10:46
 */
include "./vendor/autoload.php";
\EasySwoole\EasySwoole\Core::getInstance()->initialize()->globalInitialize();

go(function () {
    $startTime = microtime_float();
    $count = 0;
    for ($i = 0; $i < 5000; $i++) {
        $userIds = [];
        for ($y=0;$y<10;$y++){
            $userIds[] = mt_rand(1,800000);
        }
        //先查询user
        $sql = "select * from user_list where userId in ( ".implode(',',$userIds)." )";
        $query = new \EasySwoole\Mysqli\QueryBuilder();
        $query->raw($sql);
        $data = \EasySwoole\ORM\DbManager::getInstance()->getConnection()->defer()->query($query);
        //再查询article_list
        $sql = "select * from  article_list where userId in ( ".implode(',',$userIds)." )";
        $query = new \EasySwoole\Mysqli\QueryBuilder();
        $query->raw($sql);
        $data = \EasySwoole\ORM\DbManager::getInstance()->getConnection()->defer()->query($query);

        $count += count($data->getResult());
    }

    $endTime = microtime_float();
    echo "join查询数据量:{$count}\n";
    echo $endTime - $startTime . "秒" . PHP_EOL;
    \EasySwoole\Component\Timer::getInstance()->clearAll();
});


/*
*
*返回当前 Unix 时间戳和微秒数(用秒的小数表示)浮点数表示，常用来计算代码段执行时间
*/
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
复制

测试结果:

[tioncico@tioncico-server homeTest]$ php test10.php 
join查询数据量:4469
4.4179630279541秒
[tioncico@tioncico-server homeTest]$ php test10.php 
join查询数据量:4467
2.8955171108246秒
[tioncico@tioncico-server homeTest]$ php test10.php 
join查询数据量:4506
2.9106030464172秒
[tioncico@tioncico-server homeTest]$ php test10.php 
join查询数据量:4443
2.7280490398407秒
[tioncico@tioncico-server homeTest]$ php test10.php 
join查询数据量:4386
2.7762699127197秒
[tioncico@tioncico-server homeTest]$ php test10.php 
join查询数据量:4430
2.8715240955353秒
[tioncico@tioncico-server homeTest]$ php test11.php 
分开查询数据量:4537
4.8987159729004秒
[tioncico@tioncico-server homeTest]$ php test11.php 
分开查询数据量:4586
4.4783749580383秒
[tioncico@tioncico-server homeTest]$ php test11.php 
分开查询数据量:4547
5.2708139419556秒
[tioncico@tioncico-server homeTest]$ php test11.php 
分开查询数据量:4583
4.5162749290466秒
[tioncico@tioncico-server homeTest]$ php test11.php 
分开查询数据量:4477
7.7116341590881秒
复制

可以看出,join查询时,时间为2.7-4秒之间徘徊,而分开查询时,却变成了4-7秒内徘徊,这个数据还是没有做拼接数据的情况

1对1小数据测试

我们进行第三个案例测试,每次获取10条文章数据,并且关联文章分类表:

<?php
/**
 * Created by PhpStorm.
 * User: tioncico
 * Date: 20-8-13
 * Time: 下午10:46
 */
include "./vendor/autoload.php";
\EasySwoole\EasySwoole\Core::getInstance()->initialize()->globalInitialize();

go(function () {
    $startTime = microtime_float();
    $count = 0;
    for ($i = 0; $i < 2000; $i++) {
        $ids = [];
        for ($y=0;$y<10;$y++){
            $ids[] = mt_rand(1,800000);
        }
        $sql = "select * from article_list as a inner join article_category_list as b on a.categoryId=b.categoryId where a.categoryId in ( ".implode(',',$ids)." )";
        $query = new \EasySwoole\Mysqli\QueryBuilder();
        $query->raw($sql);
        $data = \EasySwoole\ORM\DbManager::getInstance()->getConnection()->defer()->query($query);
        $count += count($data->getResult());
    }

    $endTime = microtime_float();
    echo "join查询数据量:{$count}\n";
    echo $endTime - $startTime . "秒" . PHP_EOL;
    \EasySwoole\Component\Timer::getInstance()->clearAll();
});


/*
*
*返回当前 Unix 时间戳和微秒数(用秒的小数表示)浮点数表示，常用来计算代码段执行时间
*/
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
复制

分开查询代码:

<?php
/**
 * Created by PhpStorm.
 * User: tioncico
 * Date: 20-8-13
 * Time: 下午10:46
 */
include "./vendor/autoload.php";
\EasySwoole\EasySwoole\Core::getInstance()->initialize()->globalInitialize();

go(function () {
    $startTime = microtime_float();
    $count = 0;
    //我直接在这查询出所有的categoryId
    $sql = "select * from  article_category_list";
    $query = new \EasySwoole\Mysqli\QueryBuilder();
    $query->raw($sql);
    $data = \EasySwoole\ORM\DbManager::getInstance()->getConnection()->defer()->query($query);
    $categoryList = [];
    foreach ($data->getResult() as $datum){
        $categoryList[$datum['categoryId']] = $datum;
    }
    for ($i = 0; $i < 2000; $i++) {
        $ids = [];
        for ($y=0;$y<10;$y++){
            $ids[] = mt_rand(1,800000);
        }
        //查询article_list
        $sql = "select * from  article_list where articleId in ( ".implode(',',$ids)." )";
        $query = new \EasySwoole\Mysqli\QueryBuilder();
        $query->raw($sql);
        $data = \EasySwoole\ORM\DbManager::getInstance()->getConnection()->defer()->query($query);

        $count += count($data->getResult());
    }

    $endTime = microtime_float();
    echo "分开查询数据量:{$count}\n";
    echo $endTime - $startTime . "秒" . PHP_EOL;
    \EasySwoole\Component\Timer::getInstance()->clearAll();
});


/*
*
*返回当前 Unix 时间戳和微秒数(用秒的小数表示)浮点数表示，常用来计算代码段执行时间
*/
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
复制

测试结果:

[tioncico@tioncico-server homeTest]$ php test12.php 
join查询数据量:36182
4.1274499893188秒
[tioncico@tioncico-server homeTest]$ php test12.php 
join查询数据量:9023
1.3489010334015秒
[tioncico@tioncico-server homeTest]$ php test12.php 
join查询数据量:18254
1.5686419010162秒
[tioncico@tioncico-server homeTest]$ php test12.php 
join查询数据量:9109
1.3906681537628秒
[tioncico@tioncico-server homeTest]$ php test13.php 
分开查询数据量:20000
1.3932311534882秒
[tioncico@tioncico-server homeTest]$ php test13.php 
分开查询数据量:19999
3.3708009719849秒
[tioncico@tioncico-server homeTest]$ php test13.php 
分开查询数据量:20000
1.4796049594879秒
[tioncico@tioncico-server homeTest]$ php test13.php 
分开查询数据量:19998
1.6495499610901秒
[tioncico@tioncico-server homeTest]$ php test13.php 
分开查询数据量:20000
1.4959900379181秒
[tioncico@tioncico-server homeTest]$ php test13.php 
分开查询数据量:19998
1.5449161529541秒
复制

可以看出,使用join查询,跟分开查询(这里的分开查询做了优化,因为只有100条消息,所以只需要一次性查出100条即可)

join查询在1.4-4秒之间,而分开查询也在1.5-3.4秒之间,也没有更快

原理解析:

在mysql 查询关联中,有以下几种关联形式:

1:1对1关联,每次查询数据关联数据都只有一条数据

2:1对多关联,每次查询数据关联数据为1条以上

同时,根据关联主次关系,以及数据量的大小,可以区分各种情况:

1:A,B 1对1关联,B数据总量为100,A数据总量90万 ,每次查少量数据/每次查大量数据

2:A,B 1对1关联,B数据总量80万,A数据总量90万 ,每次查少量数据/每次查大量数据

3:A,B 1对多关联,B数据总量90万,A数据总量80万,每次查少量数据/每次查大量数据

在关联需求中,影响速度的因素为:

1:查询数据量,数据量越大,传输带宽越大,则越慢,本文使用的是内网环境,可极大避免此问题

2:查询索引,命中索引并使用索引之后速度则快,否则表数据越大越慢,本文所有查询条件均使用了索引,所以可以忽略索引问题

3:查询次数,每次查询,意味着mysql都需要进行一次sql命令解析->sql查询->数据传回,查询次数越少则越快

4:数据组装,当使用join,order by,group by等sql语句时,会使得mysql查询完数据之后还需要对数据进行拼装再返回,数据越大,逻辑越复杂,则查询越慢

既然已经有了3点因素,那我们来看第一个案例:

每次查询1000条数据,1对1关联, 主要是数据量大

关系到了 第1点,第4点的说明,由于分开查询节省了数据组装流程,所以加快了查询速度,所以比join方式查询更快

如果使用php进行数据组装,速度则跟join方案几乎一致

注:本身mysql原生查询,应该是游标式while循环获取,本文使用的foreach其实在原生查询中,可以省略好几个步骤,应该是分开查询更快

第二个案例:

每次查询10条,1对多关联

该案例

由于查询数据量并不大,

user_list表字段数据并不多(如果字段数多,也会影响),一条数据大头在article_list中

同时因为分开查询,将增加一次查询次数,

关系到了第3点的说明

所以导致分开查询比join查询更慢了差不多一倍.

第三个案例:

每次查询10条,1对1小数据关联

这里的分开查询优化了每次查询,小数据只查询了一次,但是速度依旧跟join差不多

总结

通过上面的说明,或许你已经对join以及分开查询的各种应用场景有所了解了,对于性能方面也有一定的掌握了,所以,说一下几个知识点:

1:join查询会消耗性能,但是消耗的是组装数据的性能(数据量越大,越复杂时越明显)

2:join查询速度与分开查询几乎一致.(只要有索引,就非常快)

3:分开查询会多执行一条sql,意味着查询速度将会更慢

4:可以通过预先缓存方式,节省掉join小数据的组装数据开销以及带宽开销

那为什么有大佬不建议使用join呢?

1:join如果逻辑太多,代码将非常难懂

2:join如果太多,对于这条sql 的索引优化将会变得更难

3:join的sql复杂,可读性差,同时由于sql复杂,很难被mysql缓存

4:分开查询的sql简单易懂,同时查询后将会有sql缓存,提高下次查询速度

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL SQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入