千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2024-11-12 作者：千家信息网编辑
千家信息网最后更新 2024年11月12日，【背景】某业务数据库load 报警异常，cpu usr 达到30-40 ，居高不下。使用工具查看数据库正在执行的sql ，排在前面的大部分是：SELECT id, cu_id, name, info,
千家信息网最后更新 2024年11月12日【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安全错误 数据库的锁怎样保障安全 软件开发红米笔记本可以用吗 网络安全密钥不匹配怎么找到 软件开发修改完善记录 应届生国企软件开发面试问题 学校网络安全简报范文 大渡口区一站式软件开发流程要求 安全评价数据库系统主要内容 网络安全的三种防御策略 常规网络安全管理 城步乡镇网络安全自查报告 亚普路卡北京网络技术有限公司 wamp 服务器 苏州企业软件开发平台 综合布线网络安全 斯凯网络安全专业 服务器开机显示什么 vm虚拟机怎么进入服务器 门店管理软件开发 北京清华同方大数据库 四川惠普服务器维修费用 文山计算机网络技术专科学校 轮回服没有服务器咋回事 软件开发设计怎么学 yum服务器本地 周鸿祎软件开发成本 信息学院网络技术是什么 网关服务器是什么 数据库ndb和net有什么区别 git服务器 的备份 比特币是以什么网络技术

相关文章

0