千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

【MySQL】 性能优化之 延迟关联

发表于：2024-11-14 作者：千家信息网编辑
千家信息网最后更新 2024年11月14日，【背景】某业务数据库load 报警异常，cpu usr 达到30-40 ，居高不下。使用工具查看数据库正在执行的sql ，排在前面的大部分是：SELECT id, cu_id, name, info,
千家信息网最后更新 2024年11月14日【MySQL】 性能优化之 延迟关联【背景】
某业务数据库load 报警异常，cpu usr 达到30-40 ，居高不下。使用工具查看数据库正在执行的sql ，排在前面的大部分是：

  1. SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;
表的数据量大致有36w左右，该sql是一个非常典型的排序+分页查询：order by col limit N,OFFSET M , MySQL 执行此类sql时需要先扫描到N行，然后再去取 M行。对于此类大数据量的排序操作，取前面少数几行数据会很快，但是越靠后，sql的性能就会越差，因为N越大，MySQL 需要扫描不需要的数据然后在丢掉，这样耗费大量的时间。

【分析】
针对limit 优化有很多种方式，
1 前端加缓存，减少落到库的查询操作
2 优化SQL
3 使用书签方式 ，记录上次查询最新/大的id值，向后追溯 M行记录。
4 使用Sphinx 搜索优化。
对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作，何谓"延迟关联" ：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

【解决】
根据延迟关联的思路，修改SQL 如下：
 优化前

点击(此处)折叠或打开

  1. root@xxx 12:33:48>explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =\'0\' AND end_time >=\'2014-05-29\' ORDER BY id asc LIMIT 149420 ,20;
  2. +----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----------------------------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----------------------------+
  5. | 1 | SIMPLE | relation | range | ind_endtime | ind_endtime | 9 | NULL | 349622 | Using where; Using filesort |
  6. +----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----------------------------+
  7. 1 row in set (0.00 sec)
其执行时间：

优化后：

点击(此处)折叠或打开

  1. SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id

  1. root@xxx 12:33:43>explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;
  2. +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
  5. | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 20 | |
  6. | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | |
  7. | 2 | DERIVED | relation | index | ind_endtime | PRIMARY | 8 | NULL | 733552 | |
  8. +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
  9. 3 rows in set (0.36 sec)
执行时间：


优化后 执行时间 为原来的1/3 。

如果您觉得从这篇文章受益，可以赞助 北在南方 一瓶饮料 ^_^
很赞哦！
数据 关联 时间 查询 延迟 方式 排序 数据库 性能 居高不下 业务 书签 典型 前端 大部分 工具 很快 思路 方法 正在 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 软件开发和测试工资水平 投影秀服务器控制软件下载 数据库会卡吗 图书馆加强网络安全 网络安全相关的论坛 我的世界服务器初始内存越来越大 德科软件开发工程师工作经验 软件开发怎么做安全措施 如何用数据库计算一个单元格 网络安全审核意见书图片 网络安全官网地址 linux运维监控服务器 网络安全班会心得体会范文 机关单位网络安全调研报告 关于网络安全六年级绘画 金山区品牌软件开发报价方案 记忆科技面试软件开发 数据库awr报告位置 orcle 修改数据库 创客照妖镜下载数据库来源 软件开发行业的地域分布 web中怎么配数据库 软件开发工具actools 阿里数据库开发手册 简单网站服务器多少钱 重点网络安全漏洞摘报 金山区移动网络技术服务 十堰有软件开发么 正定兜鞍网络技术有限公司 剪映草稿会上传服务器吗

相关文章

0