o &nbsp,&nbsp&nbsp,&nbsp
针对MySQL在单表海量记录等场景下，本文由业界广泛关注的MySQL问题的经验分享整理而成。
关于伯乐头条
专注于IT互联网，分享业界最新动态。
新浪微博：
推荐微信号
（加好友请注明来意）
- 好的话题、有启发的回复、值得信赖的圈子
- 分享和发现有价值的内容与观点
- 为IT单身男女服务的征婚传播平台
- 优秀的工具资源导航
- 翻译传播优秀的外文文章
- 国内外的精选博客文章
- UI,网页，交互和用户体验
- 专注iOS技术分享
- 专注Android技术分享
- JavaScript, HTML5, CSS
- 专注Java技术分享
- 专注Python技术分享
& 2017 伯乐在线千万表级联查询优化-Oracle数据库
当前位置：&>&&>& &
千万表级联查询优化
千万表级联查询优化
|　来源:网络 | 关键字：
需求背景：某大型物流公司CRM提供可视化营销功能，一线员工可以在界面中直接按条件搜索到本门店下属各个用户：潜在客户，大客户，散户等，并显?
需求背景：某大型物流公司CRM提供可视化营销功能，一线员工可以在界面中直接按条件搜索到本门店下属各个用户：潜在客户，大客户，散户等，并显示用户的各种数据：基本信息，来源信息，花费金额，花费频率，回访信息，统计报表等数据；涉及到表 ：用户表基本信息表，用户附加信息表，订单表，用户来源表表基数：用户表1000万，附加信息3000万，订单表5000万，用户来源表1000万正式进入查询阶段：第一阶段sql查询：在数据库中添加相应索引，通过条件减少sql执行时，搜索范围优化结果：12秒完成一次搜索第二阶段数据表结构优化：相应的表添加分区，以部门为分区单位进行range分区，优化结果：5秒完成一次搜索压测结果：30并发下，数据库CPU消耗达到85%成本增加：多张表分区的维护成本：分区带来在线重定义工作，分区后全局索引重建，分区索引重建等工作量第三阶段数据表结构优化： 在1，2基础上，增加统计数据部分的物化视图，中间表优化结果：1.2秒完成一次搜索，压测结果：通过50并发（300次请求每秒）压测，数据库CPU消耗在70%以内，其他指标正常成本增加： 中间表的维护，增量更新脚本，全量恢复脚本；物化视图的维护成本，增量更新脚本一个半月终于将这么看似庞大且操蛋的需求完成了，过程中基本天天泡在DBA哪里，优化sql，优化数据表，任何一个又可能的过程都不会放过，险些转行做了DBA；当时感觉是一个很诺囊桓龀晒窍衷诳蠢矗芏嘈枰此贾Γ。。总结一下：对于千万级别的数据而已，说大不大说小不小，一般这个级别SQL本身的查询优化以及很乏力了，需要依靠数据表结构 数据库层的一些优化才能够到达预期的目标，而且会因为这些的增加，增加很多其他的维护成本：多出来很多中间表，中间表的维护脚本，增量更新的脚步等等，有时候也要考虑一下这些成本的增加是否值得或者适合，或者可以考虑一下新型的技术来解决比如spark/strom等新型的数据处理技术，但是这样成本会更高一点点，需要全面的权衡
网友评论仅供其表达个人看法，并不表明网易立场。君，已阅读到文档的结尾了呢~~
ORACLE数据库性能优化优化,性能,帮助,数据库,数据库性能,性能优化,反馈意见
扫扫二维码，随身浏览文档
手机或平板扫扫即可继续访问
ORACLE数据库性能优化
举报该文档为侵权文档。
举报该文档含有违规或不良信息。
反馈该文档无法正常浏览。
举报该文档为重复文档。
推荐理由：
将文档分享至：
分享完整地址
文档地址：
粘贴到BBS或博客
flash地址：
支持嵌入FLASH地址的网站使用
html代码：
&embed src='/DocinViewer-4.swf' width='100%' height='600' type=application/x-shockwave-flash ALLOWFULLSCREEN='true' ALLOWSCRIPTACCESS='always'&&/embed&
450px*300px480px*400px650px*490px
支持嵌入HTML代码的网站使用
您的内容已经提交成功
您所提交的内容需要审核后才能发布，请您等待！
3秒自动关闭窗口写在前面的话：
不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程；
不要求每个人一定知道线上（现在或未来）哪张表数据量大，哪张表数据量小；
但把mysql客户端（如SQLyog，如HeidiSQL）放在桌面上，时不时拿出来 explain 一把，这是一种美德！在实例讲解之前，我们先回顾一下联表查询的基础知识。——联表查询的基础知识——引子：为什么第一个查询using temporary，第二个查询不用临时表呢？下面两个查询，它们只差了一个order by，效果却迥然不同。第一个查询：EXPLAIN extendedSELECT ads.idFROM ads, city WHERE
city.city_id = 8005
AND ads.status = 'online'
AND city.ads_id=ads.idORDER BY ads.id desc执行计划为：
select_type
possible_keys
-----------
--------------
--------------------
-------------------------------
ads_id,city_id
U Using filesort
city.ads_id
Using where
第二个查询：EXPLAIN extendedSELECT ads.idFROM ads,city WHERE
city.city_id =8005
AND ads.status = 'online'
AND city.ads_id=ads.idORDER BY city.ads_id desc执行计划里没有了using temporary：
select_type
possible_keys
-----------
--------------
--------------------
---------------------------
ads_id,city_id
U Using filesort
city.ads_id
Using where
为什么？ DBA告诉我们：
MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。 EXPLAIN 结果中，第一行出现的表就是驱动表（Important!） 以上两个查询语句，驱动表都是 city，如上面的执行计划所示！ 对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（Important!）因此，order by ads.id desc 时，就要先 using temporary 了！ 驱动表的定义wwh999 在 2006年总结说，当进行多表连接查询时， [驱动表] 的定义为：1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；2）未指定联接条件时，行数少的表为[驱动表]（Important!）。 忠告：如果你搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断既然“未指定联接条件时，行数少的表为[驱动表]”了，而且你也对自己写出的复杂的 Nested Loop Join 不太有把握（如下面的实例所示），就别指定谁 left/right join 谁了，请交给 MySQL优化器 运行时决定吧。如果您对自己特别有信心，可以像火丁一样做优化。 小结果集驱动大结果集de.cel 在2012年总结说，不管是你，还是 MySQL，优化的目标是尽可能减少JOIN中Nested Loop的循环次数，以此保证：永远用小结果集驱动大结果集（Important!）！——实例讲解—— Nested Loop Join慢查SQL语句先了解一下 mb 表有 千万级记录，mbei 表要少得多。慢查实例如下：explainSELECT mb.id, ……FROMmb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid
ORDER BY mbei.apply_time DESClimit 0,10够复杂吧。Nested Loop Join 就是这样，以驱动表的结果集作为循环的基础数据，然后将结果集中的数据作为过滤条件一条条地到下一个表中查询数据，最后合并结果；此时还有第三个表，则将前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此反复。这条语句的执行计划如下：
select_type
possible_keys
-----------
--------------
--------------
-------------------
--------------------------------------------
U U Using filesort
Using index
由于动用了“LEFT JOIN”，所以攻城狮已经指定了驱动表，虽然这张驱动表的结果集记录数达到百万级！..如何优化？..优化第一步：LEFT JOIN改为JOIN干嘛要 left join 啊？直接 join！explainSELECT mb.id…… FROM mb JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid
ORDER BY mbei.apply_time DESClimit 0,10立竿见影，驱动表立刻变为小表 mbei 了， Using temporary 消失了，影响行数少多了：
select_type
possible_keys
-----------
--------------
----------------------------
--------------
Using filesort
PRIMARY,userid
mbei.mb_id
Using index
优化第一步之分支1：根据驱动表的字段排序，好吗？left join不变。干嘛要根据非驱动表的字段排序呢？我们前面说过“对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序！”的。explainSELECT mb.id…… FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid
ORDER BY mb.id DESClimit 0,10也满足业务场景，做到了rows最小：
select_type
possible_keys
-----------
--------------
--------------
-------------------
-----------
Using index
Using index 优化第二步：去除所有JOIN，让MySQL自行决定！写这么多密密麻麻的 left join/inner join 很开心吗？explainSELECT mb.id…… FROM mb,mbei,u
mb.id=mbei.mb_id
and mb.uid=u.user_idorder by mbei.apply_time desclimit 0,10立竿见影，驱动表一样是小表 mbei：
select_type
possible_keys
-----------
--------------
----------------------------
--------------
Using filesort
PRIMARY,userid
mbei.mb_id
Using index
最后的总结：强调再强调：不要过于相信你的运气！不要相信你的开发环境里SQL的执行速度！请拿起 explain 武器，如果你看到以下现象，请优化：出现了Using temporary；rows过多，或者几乎是全表的记录数；key 是 (NULL)；possible_keys 出现过多（待选）索引。 记住，explain 是一种美德！
参考资源：1）wwh999，2006，进行多表查时的排序问题,其多表查询时的原理论证！ ；2）de.cel，2012，MySQL中的Join 原理及优化思路 ；3）火丁，2013，MySQL优化的奇技淫巧之STRAIGHT_JOIN； 赠图一枚：
最新教程周点击榜
微信扫一扫