mysql分页列表使用LEFT JOIN时的优化方法

最新推荐文章于 2024-03-26 15:21:41 发布
原创 最新推荐文章于 2024-03-26 15:21:41 发布 · 1.7k 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql优化 #left join优化

本文介绍了一种在MySQL中进行分页查询时的优化方法，特别是当查询涉及多个表的LEFT JOIN操作时。通过调整JOIN顺序和减少不必要的JOIN，可以显著提高查询效率，将页面刷新时间从1100ms降低到50ms。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

mysql分页列表使用LEFT JOIN时的优化方法

分页列表查询一般格式

  • 取分页数据
SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
LEFT JOIN table3 t3 ON t3.id = t1.id
LEFT JOIN table4 t4 ON t4.id = t1.id
WHERE t1.id = 1 AND t2. ...
LIMIT 0,30
  • 取总数
SELECT COUNT(0)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
LEFT JOIN table3 t3 ON t3.id = t1.id
LEFT JOIN table4 t4 ON t4.id = t1.id
WHERE t1.id = 1 AND t2. ...
缺点
  • LEFT JOIN消耗性能
  • 全部LEFT JOIN后再取分页
  • 取总数时候不必要的LEFT JOIN影响性能
优化
  • WHERE 条件语句用不到的LEFT JOIN的表，放到取分页后面
  • 取总数时候不必要的LEFT JOIN不要
  • 取分页数据
SELECT *
FROM (
    SELECT *
    FROM table1 t1
    LEFT JOIN table2 t2 ON t2.id = t1.id
    LEFT JOIN table3 t3 ON t3.id = t1.id
    WHERE t1.id = 1 AND t2. ...
    LIMIT 0,30
    )t
LEFT JOIN table4 t4 ON t4.id = t1.id
  • 取总数
SELECT COUNT(0)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
LEFT JOIN table2 t3 ON t3.id = t1.id
WHERE t1.id = 1 AND t2. ...

具体实际应用

网站分页列表
优化前
  • 数据量：9741
  • 每页显示30条数据
  • 平均每页刷新耗时：1100ms
  • 列表数据
SELECT 
    w.*,
    d.`name` AS deptName,
    IFNULL(de.`name`,'') AS superviseName,
    CASE WHEN w.school_id IS NULL THEN d.`name` ELSE s.name END AS schoolName
    FROM website w
    LEFT JOIN sys_dept d ON d.id = w.dept_id
    LEFT JOIN sys_dept de ON de.id = w.supervise_id
    LEFT JOIN sys_dept scan ON scan.id = w.scan_id
    LEFT JOIN sys_dept verify ON verify.id = w.verify_id
    LEFT JOIN sys_dept_info i ON i.dept_id = w.dept_id
    LEFT JOIN dictionary_item item ON i.area_id = item.id
    LEFT JOIN school s ON s.id = w.school_id
    WHERE w.is_del = 0
    <if test="deptId != null and deptId != ''">
        AND d.id = #{deptId}
    </if>
    <if test="name != null and name != ''">
        AND (UPPER(w.name) LIKE UPPER(CONCAT('%',CONCAT(#{name},'%')))
        OR UPPER(w.py) LIKE UPPER(CONCAT('%',CONCAT(#{name},'%'))))
    </if>
    <if test="url != null and url != ''">
        AND UPPER(w.url) LIKE UPPER(CONCAT('%',CONCAT(#{url},'%')))
    </if>
    <if test="linkman != null and linkman != ''">
        AND UPPER(w.contact_name) LIKE UPPER(CONCAT('%',CONCAT(#{linkman},'%')))
    </if>
    ORDER BY w.create_date DESC
    <if test="pageSize != null ">
        LIMIT #{offset}, #{pageSize}
    </if>
  • 网站数量
SELECT 
    COUNT(0)
    FROM website w
    LEFT JOIN sys_dept d ON d.id = w.dept_id
    LEFT JOIN sys_dept de ON de.id = w.supervise_id
    LEFT JOIN sys_dept scan ON scan.id = w.scan_id
    LEFT JOIN sys_dept verify ON verify.id = w.verify_id
    LEFT JOIN sys_dept_info i ON i.dept_id = w.dept_id
    LEFT JOIN dictionary_item item ON i.area_id = item.id
    LEFT JOIN school s ON s.id = w.school_id
    WHERE w.is_del = 0
    <if test="deptId != null and deptId != ''">
        AND d.id = #{deptId}
    </if>
    <if test="name != null and name != ''">
        AND (UPPER(w.name) LIKE UPPER(CONCAT('%',CONCAT(#{name},'%')))
        OR UPPER(w.py) LIKE UPPER(CONCAT('%',CONCAT(#{name},'%'))))
    </if>
    <if test="url != null and url != ''">
        AND UPPER(w.url) LIKE UPPER(CONCAT('%',CONCAT(#{url},'%')))
    </if>
    <if test="linkman != null and linkman != ''">
        AND UPPER(w.contact_name) LIKE UPPER(CONCAT('%',CONCAT(#{linkman},'%')))
    </if>
优化
  • 数据量：9741
  • 每页显示300条数据
  • 平均每页刷新耗时：120ms
  • 每页显示30条数据
  • 平均每页刷新耗时：50ms
  • 列表数据
SELECT
    t.*,
    item.name                 AS areaName,
    IFNULL(scan.`name`, '')   AS scanName,
    IFNULL(verify.`name`, '') AS verifyName,
    de.`name`                 AS superviseName
    FROM (
        SELECT
            w.id,
            w.name,
            w.url,
            w.dept_id,
            w.scan_id,
            w.supervise_id,
            w.verify_id,
            CASE WHEN w.school_id IS NULL THEN d.`name` ELSE s.name END  AS schoolName,
            d.`name`                                                     AS deptName
        FROM website w
        LEFT JOIN sys_dept d ON d.id = w.dept_id
        LEFT JOIN school s ON s.id = w.school_id
        WHERE w.is_del = 0
        <if test="deptId != null and deptId != ''">
            AND d.id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            AND (UPPER(d.name) LIKE UPPER(CONCAT('%',CONCAT(#{deptName},'%')))
            OR UPPER(d.py) LIKE UPPER(CONCAT('%',CONCAT(#{deptName},'%')))
            OR UPPER(s.name) LIKE UPPER(CONCAT('%',CONCAT(#{deptName},'%'))))
        </if>
        <if test="name != null and name != ''">
            AND (UPPER(w.name) LIKE UPPER(CONCAT('%',CONCAT(#{name},'%')))
            OR UPPER(w.py) LIKE UPPER(CONCAT('%',CONCAT(#{name},'%'))))
        </if>
        <if test="url != null and url != ''">
            AND UPPER(w.url) LIKE UPPER(CONCAT('%',CONCAT(#{url},'%')))
        </if>
        <if test="linkman != null and linkman != ''">
            AND UPPER(w.contact_name) LIKE UPPER(CONCAT('%',CONCAT(#{linkman},'%')))
        </if>
        ORDER BY w.create_date DESC
        <if test="pageSize != null ">
            LIMIT #{offset}, #{pageSize}
        </if>
    ) t
    LEFT JOIN sys_dept_info i ON i.dept_id = t.dept_id
    LEFT JOIN dictionary_item item ON i.area_id = item.id
    LEFT JOIN sys_dept de ON de.id = t.supervise_id
    LEFT JOIN sys_dept scan ON scan.id = t.scan_id
    LEFT JOIN sys_dept verify ON verify.id = t.verify_id
  • 网站数量
SELECT
    COUNT(0)
    FROM (
        SELECT
            w.id,
            w.name,
            w.url,
            w.dept_id,
            w.scan_id,
            w.supervise_id,
            w.verify_id,
            CASE WHEN w.school_id IS NULL THEN d.`name` ELSE s.name END  AS schoolName,
            d.`name`                                                     AS deptName
        FROM website w
        LEFT JOIN sys_dept d ON d.id = w.dept_id
        LEFT JOIN school s ON s.id = w.school_id
        WHERE w.is_del = 0
        <if test="deptId != null and deptId != ''">
            AND d.id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            AND (UPPER(d.name) LIKE UPPER(CONCAT('%',CONCAT(#{deptName},'%')))
            OR UPPER(d.py) LIKE UPPER(CONCAT('%',CONCAT(#{deptName},'%')))
            OR UPPER(s.name) LIKE UPPER(CONCAT('%',CONCAT(#{deptName},'%'))))
        </if>
        <if test="name != null and name != ''">
            AND (UPPER(w.name) LIKE UPPER(CONCAT('%',CONCAT(#{name},'%')))
            OR UPPER(w.py) LIKE UPPER(CONCAT('%',CONCAT(#{name},'%'))))
        </if>
        <if test="url != null and url != ''">
            AND UPPER(w.url) LIKE UPPER(CONCAT('%',CONCAT(#{url},'%')))
        </if>
        <if test="linkman != null and linkman != ''">
            AND UPPER(w.contact_name) LIKE UPPER(CONCAT('%',CONCAT(#{linkman},'%')))
        </if>
        ORDER BY w.create_date DESC
        <if test="pageSize != null ">
            LIMIT #{offset}, #{pageSize}
        </if>
    ) t
left join limit offset 分页查询问题
悟已往之不谏，知来者之可追
04-13 462
在开始讨论LEFT JOIN的使用方法之前，让我们先简要回顾一下LEFT JOIN的概念。LEFT JOIN是一种用于将左表和右表连接起来的操作。它会返回左表中的所有记录，并且对于每条左表记录，如果在右表中找到符合条件的记录，就将其连接起来。如果没有匹配的记录，则右表的相应字段将被设置为NULL。
Mybatis一对多多对一MySQL LEFT JOIN分页方案
悟达居士
05-02 6448
项目中遇到Mybatis需要写一对多多对一的翻页，用LEFT JOIN之后再用LIMIT记录数是不足的。因为LEFT JOIN后记录数大于主表的记录数了。所以LIMIT出来的记录是少的，不足一页。后来我使用了用户变量的方式，解决了这个问题。上代码： select t.id, t2.* from table1 t left join table2 t2 = t1.id = t2.t_id li...
MySQL面试：left join我要怎优化？
最新发布
2401_83412087的博客
03-26 878
这个过程是先遍历表t1，然后根据表t1中取出的每行数据中的a值，去表t2中查询满足条件的记录。扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。把表t1的数据读入线程内存join_buffer中，这里我们是把整个表t1放入内存中。这里，我们两个表都是做的全表扫描，所以不管是哪个表做驱动表都是执行消耗都是一样的。取出表t2中满足条件的行，跟t1的数据组成一行，作为结果集的一部分。，现在起闭关修炼半个月，争取早日上岸！
从一个MySQL left join优化的例子加深对查询计划的理解
PHP程序员 - muzihao2014
04-30 1197
今天遇到一个left join优化的问题，搞了一下午，中间查了不少资料，对MySQL的查询计划还有查询优化有了更进一步的了解，做一个简单的记录：  select c.* from hotel_info_original c  left join hotel_info_collection h  on c.hotel_type=h.hotel_type and c.hotel_
mysql left join 一种优化思路
feiyu07的博客
11-22 392
现在有两个大表进行left join select count(*) from `test_a` left join `test_b` on `test_a`.`task_id` = `test_b`.`id` where `test_a`.`status` in (1, 11) and `test_b`.`city_id` = '10'; 第一步：分析一下哪个表是小表，由于lef...
mysql left join优化
学渣的博客
12-06 299
原理 MySQL内部采用了一种叫做 nested loop join的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据 优化方案： 如果可以的话...
MYSQL分页limit速度太慢的优化方法
12-16
在MySQL中，`LIMIT`子句常用于实现分页功能，但当表数据量非常大时，使用`LIMIT`进行分页可能会导致性能急剧下降。这是因为`LIMIT`语句需要扫描大量的行，尤其是在`OFFSET`值较大的情况下。针对这个问题，我们可以...
mysql 开发技巧之JOIN 更新和数据查重/去重
01-19
1 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN(MySQL 不支持)、CROSS JOIN 这是在网上找到的非常好的一篇博文，图解 join 语句： CODING HORROR-A Visual Explanation of SQL Joins 下图可以很清楚的明白，join ...
浅谈mysql中多表不关联查询的实现方法
12-16
在MySQL数据库中，查询操作是日常开发中必不可少的一部分。...理解何时使用UNION与JOIN，以及如何优化不关联查询，对于提升MySQL数据库的性能至关重要。在实际开发中，应根据具体需求和数据量来选择最适合的查询方式。
mysql left join 优化
诸葛子房的博客
03-28 268
mysql left join 优化 参考 https://www.cnblogs.com/zedosu/p/6555981.html https://blog.youkuaiyun.com/qq_36028348/article/details/88864691 posted @ 2019-03-28 11:06 诸葛子房 阅读(...) 评论(...) 编辑 收藏 ...
MySQL left join优化
热门推荐
老王的IT博客
09-17 1万+
left join 优化
MySQL - left join 优化
A_bad_horse的专栏
05-23 277
反例 SELECT A.卡号,B.手机号码 FROM A LEFT JOIN B ON A.客户号=B.客户号 WHERE B.联系方式='2' 以A表作为主表，并且左关联B表。 A表所有的卡号一定会显示出来 ， B表的筛选条件放到最外层， 相当于将A表关联B表又做了一遍筛选，结果就是"6223123456781001, 18810123456"。 正例 SELECT A.卡号,B.手机号码 FROM A LEFT JOIN ( SELECT * FROM B B.联系方..
mysql :left join语句优化
PHPer小成
06-21 1288
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。 在表中存在至少一个匹配时，INNER JOIN 关键字返回行。 a表： a1,a2 字段 b表： b1,b2 字段 c表： c1,c2 字段 假设有以下left join查询： select * fro
MYSQL left join 优化与解决办法
pengwupeng2008的专栏
08-03 2122
有时候left join 非常慢    a、主要是左边数据大，可以先想办法把左边数据减少    b、连接字段加入索引    c、不适用left join 直接在基础表查询条件中，改为 NOT EXISTS 语句
MySQL下LeftJoin的性能优化
F2004的专栏
04-21 7560
今天遇到了一个问题，有一个Select语句执行超慢，在加了Index之后依然超慢。 数据库是MySQL，表a中有数据4000条，表b中有数据14000条 Select语句为select count(*) from a left join b on a.id=b.id  （语句1） 执行时间为30秒 如果将Select语句改为select count(*) from b left join
mysql left join效率_mysql 如何优化left join
lz610756247的专栏
12-31 7215
今天遇到一个left join优化的问题，搞了一下午，中间查了不少资料，对MySQL的查询计划还有查询优化有了更进一步的了解，做一个简单的记录： select c.* from hotel_info_original c left join hotel_info_collection h on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id where h.hotel_id is null 这个sql是用来查询出c表中有h表中无的记录，所以
Mysql 优化LEFT JOIN语句
m0_47944994的博客
04-25 2055
Mysql 优化LEFT JOIN语句
Mysql查询语句优化
十指波课堂的博客
08-02 255
前言上一篇文章 《MySQL索引原理机器优化》讲了索引的一些原理以及优化方案,这一次学习对查询的优化,毕竟快速的查找到数据才是我们的最终目的.分析查询想要对一条查询语句进行优化,首先要对其进行分析,MySQL提供了这个机制, 可以通过explain sql或者desc sql的语法去获取MySQL对某一条语句的执行计划(MySQL优化之后的),explain的用法这里就不再赘述了,在另外一篇文章中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

liuhaojavax

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值