为什么这么查询不使用索引??-CSDN论坛

⋅大数据量情况下UnionALL分表查询分页效率极低 ⋅TransactionScope在MySQL中如何使用? ⋅为什么这么查询不使用索引?? 更多帖子 关注私信空间博客 火星大能猫 本版专家分：20 结帖率 98.91%

最初的查询语句





SELECT

	-- T1.*, 

	T2.STUID,

	T2.APPID,

	T2.APPNAME,

	T2.BEGINDATE,

	T2.ENDDATE

FROM

	t_stu T1

LEFT JOIN (

	SELECT

		StuID,AppID,AppName,BeginDate,EndDate

	FROM

		t_olzj

	UNION ALL

		SELECT

			StuID,AppID,AppName,BeginDate,EndDate

		FROM

			t_olgz

		UNION ALL

			SELECT

				StuID,AppID,AppName,BeginDate,EndDate

			FROM

				t_olxj 

) AS T2 ON T1.ID = T2.StuID

LIMIT 0,

 100

t_ol是分表
查询效率非常低,
比如t_olgz 含有20多万条数据
t_stu才几百条数据
查询居然要6秒
于是测试
发现问题原因是没走索引



EXPLAIN

SELECT

	-- T1.*, 

	T2.STUID,

	T2.APPID,

	T2.APPNAME,

	T2.BEGINDATE,

	T2.ENDDATE

FROM

	t_stu T1 FORCE index (id)

LEFT JOIN 

			t_olgz 

 AS T2 ON T1.ID = T2.StuID

;

如果直接这么查询,效率非常高0.5秒

但是如果这样查询
就不走索引了
效率6秒左右

EXPLAIN

SELECT

	-- T1.*, 

	T2.STUID,

	T2.APPID,

	T2.APPNAME,

	T2.BEGINDATE,

	T2.ENDDATE

FROM

	fx_student T1 FORCE index (id)

LEFT JOIN 

(select * FROM

			t_olgz FORCE INDEX(stuID)

 )

t2  ON T1.ID = T2.StuID

LIMIT 0,

 100;

请问这个查询如何优化才能走索引
加as 表别名貌似没法走强制索引了

0 2016-11-04 11:14:55

回复数 9 只看楼主引用举报楼主

⋅竟然拿到了小花，第一朵，感谢

⋅python pip使用问题

⋅python 环境玩坏了

更多帖子

关注私信空间博客

致命的西瓜

本版专家分：4574

GitHub 绑定GitHub第三方账户获取

红花 2016年11月其他数据库开发大版内专家分月排行榜第一

黄花 2016年12月其他数据库开发大版内专家分月排行榜第二: 你指的是哪个表的索引，执行计划贴出来看看

0 2016-11-04 11:29:50

只看TA 引用举报 #1 得分 0

⋅不想长大啊的留言板

⋅ 不购买Exadata一体机，ORACLE 12C中是否支持列式存储功能

⋅【2016论坛之星】投我一票

更多帖子

关注私信空间博客

不想长大啊

本版专家分：15986

版主

金牌 2014年2月总版技术专家分月排行榜第一

银牌 2014年1月总版技术专家分月排行榜第二
2013年12月总版技术专家分月排行榜第二

优秀版主 2016年10月优秀小版主

微软MVP 2014年4月荣获微软MVP称号: 实际上还是由于mysql的优化器的问题导致的。

在mysql中写sql，有一个重要的原则，就是各种子查询，如 :

xx in (select xx from tb)
left join (select xx from tb) t on ..
select (select xx from tb),... from tb
not exists(select xx from tb)

都建议你直接写成：
left join tb t on t.xx = yy.xx

因为mysql对于这种join形式的sql，能进行比较好的优化，而那种括号的left join (select xx from tb) t on .. 一般会把数据先存到一个临时表中，然后再关联，那样索引就用不上了。

0 2016-11-04 12:05:20

只看TA 引用举报 #2 得分 10

⋅大数据量情况下UnionALL分表查询分页效率极低 ⋅TransactionScope在MySQL中如何使用? ⋅为什么这么查询不使用索引?? 更多帖子 关注私信空间博客 火星大能猫 本版专家分：20: 引用 1 楼 wangjian0228 的回复:
你指的是哪个表的索引，执行计划贴出来看看

(select * FROM
t_olgz FORCE INDEX(stuID)
)
left join的右表

0 2016-11-04 12:05:30

只看TA 引用举报 #3 得分 0

⋅不想长大啊的留言板

⋅ 不购买Exadata一体机，ORACLE 12C中是否支持列式存储功能

⋅【2016论坛之星】投我一票

更多帖子

关注私信空间博客

不想长大啊

本版专家分：15986

版主

金牌 2014年2月总版技术专家分月排行榜第一

银牌 2014年1月总版技术专家分月排行榜第二
2013年12月总版技术专家分月排行榜第二

优秀版主 2016年10月优秀小版主

微软MVP 2014年4月荣获微软MVP称号: 另外，还有一个问题，就是你的语句中用了查询提示 query hint，FORCE INDEX(stuID)，就是强制使用索引。

实际上一般情况下，是否要使用索引，也是由优化器决定的，优化器通过一个基于cost的模型，来评估，用索引的开销是多少，不用索引的开销是多少，然后取其中cost最小的，他认为这个执行计划就是相对最优化的，于是会用这个计划来运行sql。

但是，这里有问题？就是优化器是基于什么信息，来判断cost大还是小，实际上这个设计到了表的、列的统计信息。
比如，表有多少条数据，每条数据的平均字节数，数据页中数据的密度，列的选择性等。

但是不管怎么说，以上的信息都是平均值，或者是统计的值，并不是绝对准确的，所以，有些时候，就算你更新了统计信息，优化器还是不会做出正确的决策，这个时候你没办法只能用 query hint来指导优化器，你要用哪个计划。

0 2016-11-04 12:11:02

只看TA 引用举报 #4 得分 0

⋅大数据量情况下UnionALL分表查询分页效率极低 ⋅TransactionScope在MySQL中如何使用? ⋅为什么这么查询不使用索引?? 更多帖子 关注私信空间博客 火星大能猫 本版专家分：20: 引用 2 楼 yupeigu 的回复:
实际上还是由于mysql的优化器的问题导致的。

在mysql中写sql，有一个重要的原则，就是各种子查询，如 :

xx in (select xx from tb)
left join (select xx from tb) t on ..
select (select xx from tb),... from tb
not exists(select xx from tb)

都建议你直接写成：
left join tb t on t.xx = yy.xx

因为mysql对于这种join形式的sql，能进行比较好的优化，而那种括号的left join (select xx from tb) t on .. 一般会把数据先存到一个临时表中，然后再关联，那样索引就用不上了。

但是我需要left join 多张分表
分表就必须
union all来解决啊

0 2016-11-04 13:09:24

只看TA 引用举报 #5 得分 0

⋅竟然拿到了小花，第一朵，感谢

⋅python pip使用问题

⋅python 环境玩坏了

更多帖子

关注私信空间博客

致命的西瓜

本版专家分：4574

GitHub 绑定GitHub第三方账户获取

红花 2016年11月其他数据库开发大版内专家分月排行榜第一

黄花 2016年12月其他数据库开发大版内专家分月排行榜第二: 引用 5 楼 wukaiping870123 的回复:
但是我需要left join 多张分表
分表就必须
union all来解决啊

可以考虑拆开，单独join 后再union

0 2016-11-04 13:27:15

只看TA 引用举报 #6 得分 60

⋅不想长大啊的留言板

⋅ 不购买Exadata一体机，ORACLE 12C中是否支持列式存储功能

⋅【2016论坛之星】投我一票

更多帖子

关注私信空间博客

不想长大啊

本版专家分：15986

版主

金牌 2014年2月总版技术专家分月排行榜第一

银牌 2014年1月总版技术专家分月排行榜第二
2013年12月总版技术专家分月排行榜第二

优秀版主 2016年10月优秀小版主

微软MVP 2014年4月荣获微软MVP称号: 引用 5 楼 wukaiping870123 的回复:
Quote: 引用 2 楼 yupeigu 的回复:

实际上还是由于mysql的优化器的问题导致的。

在mysql中写sql，有一个重要的原则，就是各种子查询，如 :

xx in (select xx from tb)
left join (select xx from tb) t on ..
select (select xx from tb),... from tb
not exists(select xx from tb)

都建议你直接写成：
left join tb t on t.xx = yy.xx

因为mysql对于这种join形式的sql，能进行比较好的优化，而那种括号的left join (select xx from tb) t on .. 一般会把数据先存到一个临时表中，然后再关联，那样索引就用不上了。

但是我需要left join 多张分表
分表就必须
union all来解决啊

你可以考虑写成这样：

select
from t
left join xx

union all

select
from t
......

0 2016-11-04 15:45:19

只看TA 引用举报 #7 得分 20

⋅国内大家感觉程序员中有百分之多少不知道CSDN的？ ⋅无法看到某些私信 ⋅无法看到某些私信 更多帖子 关注私信空间博客 ACMAIN_CHM 本版专家分：471951 版主 名人 2012年荣获名人称号 榜眼 2010年总版技术专家分年内排行榜第二 探花 2009年总版技术专家分年内排行榜第三 进士 2013年总版技术专家分年内排行榜第十 2011年总版技术专家分年内排行榜第七: 以文本方式贴出
explain select ...
show index from ..
以供分析。

0 2016-11-04 22:33:46

只看TA 引用举报 #8 得分 10

火星大能猫 本版专家分：20: 感谢结贴感谢结贴

0 2016-11-09 14:37:54

只看TA 引用举报 #9 得分 0

为什么这么查询不使用索引?? [问题点数：100分，结帖人wukaiping870123]