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

最新推荐文章于 2024-04-30 06:21:15 发布
最新推荐文章于 2024-04-30 06:21:15 发布
阅读量1.3k 收藏 2
点赞数
分类专栏： 数据库 文章标签： mysql优化 left join优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_29825055/article/details/90236066
版权

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
liuhaojavax
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MYSQL分页limit速度太慢的优化方法
12-16
在MySQL中，`LIMIT`子句常用于实现分页功能，但当表数据量非常大时，使用`LIMIT`进行分页可能会导致性能急剧下降。这是因为`LIMIT`语句需要扫描大量的行，尤其是在`OFFSET`值较大的情况下。针对这个问题，我们可以...
从一个MySQL left join优化的例子加深对查询计划的理解
PHP程序员 - muzihao2014
04-30 963
今天遇到一个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中的模糊查询
yanguo110的博客
08-03 2581
原文链接：http://www.cnblogs.com/cyttina/p/3894428.html 1.  参数中直接加入%% 　　param.setUsername("%CD%");       param.setPassword("%11%"); select id,sex,age,username,password from person where true
MySQL面试：left join我要怎优化？
最新发布
2401_84103616的博客
04-30 927
引领完成Docker的安装、部署、管理和扩展，让其经历从测试到生产的整个开发生命周期，深入了解Docker适用于什么场景。并且这本Docker的学习权威指南介绍了其组件的基础知识，然后用Docker构建容器和服务来完成各种任务：利用Docker为新项目建立测试环境，演示如何使用持续集成的工作流集成Docker，如何构建应用程序服务和平台，如何使用Docker的API，如何扩展Docker。
业务数据LEFT JOIN 多表查询慢--优化操作
李逍遥
12-09 7346
业务数据LEFT JOIN 多表查询慢--优化操作
mysql的左右连接 分页_SQL 左连接(left join) 排序 分页 中遇到的未按理想状态排序分页的解决方案...
weixin_32205057的博客
01-28 767
SELECT a.id AS "id", a.code AS "code", a.name AS "name", a.type AS "type", a.importance_degree AS "importanceDegree", a.tech_state AS "techState", a.customerid AS "customerid", a.departmentid AS "depa...
Mysql索引优化实战（分页、JOIN、Count）
张三博客
03-21 1303
表示从表 employees 中取出从 10001 行开始的 10 行记录, 看着只有10条记录其实是先查询出10010条记录 然后抛弃前10000条记录 数据量越大效率越低。把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
浅谈mysql中多表不关联查询的实现方法
12-16
在MySQL数据库中，查询操作是日常开发中必不可少的一部分。...理解何时使用UNION与JOIN，以及如何优化不关联查询，对于提升MySQL数据库的性能至关重要。在实际开发中，应根据具体需求和数据量来选择最适合的查询方式。
mysql处理海量数据时的一些优化查询速度方法
09-09
MySQL在处理海量数据时，查询速度的优化是关键，尤其是当数据量达到百万级甚至更高时，普通SQL查询效率的降低会严重影响系统性能。以下是一些优化查询速度的方法： 1. **避免使用不等于（!= 或 ）操作符**：在`...
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一对多关联查询分页错误问题的解决方法
09-09
在实际使用时，需要确保传入的参数正确，例如`page`和`limit`，并确保在调用查询方法时计算出正确的分页参数。这种方法能够确保在进行一对多关联查询时，分页操作不仅作用于主表，同时也适用于关联的子表，从而避免...
mysql中LEFT JOIN分页时的优化方法
weixin_39388918的博客
08-20 1717
引言 查询的时候使用LEFT JOIN，配合分页要怎么优化？ 正文 常见的 分页SQL： 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 ...
记录一次分页优化
u010185947的专栏
07-19 383
oracle+mybatis+pageHelper分页优化。
Mybatis一对多多对一MySQL LEFT JOIN分页方案
悟达居士
05-02 5641
项目中遇到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多张表连表查询并分页
qq_44747508的博客
08-17 1947
SELECT t1.*,t2.refund_id,t3.reason FROM hq_order AS t1 LEFT JOIN hq_refund AS t2 ON t1.order_id=t2.order_id LEFT JOIN hq_refund_log AS t3 ON t2.refund_id=t3.refund_id WHERE t1.order_status=4 LIMIT 0,10 mysql语句介绍： 连接hq_order、hq_refund、hq_refund_log三张表 查询hq
Springboot集成MybatisPlus
liuy__257
06-28 123
MybatisPlus使用 导入maven依赖 <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId>
mysql left join 多对多 分页记录数不对的问题
lik_lik的博客
05-26 2175
一对多时的参考 情景阐述 event、group、work 三者的关系如下ER图，它们的主要属性为name， 在物理模型上表现为：event、group、work、event_group 表；以下是DDL DROP TABLE IF EXISTS `event` ; CREATE TABLE `event` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT '', `work_id` int
Excel、Mybatis、Maven项目笔记
m0_51440011的博客
10-25 138
SpringBoot学习心得
mysql left join 优化
08-28
### 回答1： MySQL的左连接优化可以通过以下几种方式实现： 1. 索引优化：在左连接的两个表中，对于连接条件的字段建立索引，可以大大提高查询效率。 2. 子查询优化：将左连接中的子查询转换为临时表，可以避免重复计算，提高查询效率。 3. 聚合函数优化：在左连接中使用聚合函数时，应该尽量减少聚合函数的使用次数，可以通过子查询或者临时表的方式实现。 4. 分页优化：在使用左连接进行分页查询时，应该尽量减少查询的数据量，可以通过限制查询结果的数量或者使用缓存的方式实现。 5. 数据库结构优化：在设计数据库结构时，应该尽量避免使用过多的左连接，可以通过拆分表或者使用其他关联方式实现。 ### 回答2： MySQL中的LEFT JOIN可以用于从左表中选择所有的行，并且根据指定的条件将与之匹配的右表中的行组合起来。在优化LEFT JOIN时，可以采取以下几个步骤： 1. 确定JOIN条件：首先需要确保LEFT JOIN的JOIN条件是正确的，通常是通过关联左表和右表的字段来进行匹配。在确定JOIN条件时，可以考虑创建合适的索引来加快查询速度。 2. 考虑使用合适的索引：在LEFT JOIN中，如果可以通过创建索引来加快查询速度，那么应该优先考虑这一方法。一般来说，可以在左表和右表的关联字段上创建索引，这样可以加快匹配过程。 3. 控制返回的列数：在LEFT JOIN中，返回的列数可能会比较多，如果不需要返回所有列，可以只选择需要的列，这样可以减少数据传输的开销，提高查询效率。 4. 注意数据量的大小：如果左表和右表中的数据量非常大，那么在执行LEFT JOIN时可能会导致性能下降。可以考虑对数据进行分区或者进行适当的数据剪裁，以减少JOIN操作的数据量。 5. 使用子查询代替LEFT JOIN：有时候可以使用子查询来代替LEFT JOIN操作，这样可能可以提高查询性能。但是需要注意，使用子查询也可能导致其他性能问题，需要仔细评估和测试。 总结起来，优化LEFT JOIN的关键是确定正确的JOIN条件、使用合适的索引、控制返回的列数和注意数据量的大小。通过合理的优化策略，可以提高LEFT JOIN的查询性能。 ### 回答3： MySQL的LEFT JOIN可以通过优化来提高查询性能。下面是一些优化策略： 1. 确保合适的索引：为LEFT JOIN使用合适的索引是提高性能的关键。在连接条件和被连接表上创建索引可以加速查询。比如，如果连接条件是在被连接表上的字段上，可以为该字段创建索引。 2. 限制输出列：只选择需要的列，避免选择所有列。这样可以减少磁盘IO和网络传输的数据量，提高查询效率。 3. 使用条件过滤：在LEFT JOIN之前先使用WHERE子句进行条件过滤，可以减少需要连接的行数，优化查询性能。 4. 利用子查询：可以先创建一个子查询，将需要连接的数据集合缩小，再进行LEFT JOIN操作。这样可以减少需要连接的行数，提高查询效率。 5. 使用表别名：为表使用别名，可以简化查询语句，提高可读性。 6. 避免使用SELECT *：只选择需要的列，避免使用SELECT *语句，可以减少磁盘IO和网络传输的数据量，提高查询效率。 7. 可以考虑使用内连接：内连接可以通过使用INNER JOIN替代LEFT JOIN来避免不必要的行连接，提高查询性能。 8. 分析查询计划：使用EXPLAIN语句可以分析查询计划，了解查询语句的执行过程，找出可能的性能瓶颈，并对查询进行优化。 通过以上优化策略，可以提高MySQL的LEFT JOIN查询性能，使查询更加高效。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

liuhaojavax

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

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

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

打赏作者

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

抵扣说明：

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

余额充值