为什么这么查询不使用索引??

[火星大能猫]

最初的查询语句

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 表别名貌似没法走强制索引了

[火星大能猫]

引用 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来解决啊

[致命的西瓜]

引用 5 楼 wukaiping870123 的回复:

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

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

[LongRui888]

引用 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来解决啊[/quote] 你可以考虑写成这样： select from t left join xx union all select from t ......

[ACMAIN_CHM]

以文本方式贴出 explain select ... show index from .. 以供分析。

[致命的西瓜]

你指的是哪个表的索引，执行计划贴出来看看

[LongRui888]

实际上还是由于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 .. 一般会把数据先存到一个临时表中，然后再关联，那样索引就用不上了。

[火星大能猫]

引用 1 楼 wangjian0228 的回复:

你指的是哪个表的索引，执行计划贴出来看看

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

[LongRui888]

另外，还有一个问题，就是你的语句中用了 查询提示 query hint，FORCE INDEX(stuID)，就是强制使用索引。 实际上一般情况下，是否要使用索引，也是由优化器决定的，优化器通过一个 基于cost的模型，来评估，用索引的开销是多少，不用索引的开销是多少，然后取其中cost最小的，他认为这个执行计划就是相对最优化的，于是会用这个计划来运行sql。 但是，这里有问题？ 就是优化器是基于什么信息，来判断cost大还是小，实际上这个设计到了 表的、列的统计信息。 比如，表 有多少条数据，每条数据的平均字节数，数据页中数据的密度，列的选择性等。 但是不管怎么说，以上的信息都是平均值，或者是统计的值，并不是绝对准确的，所以，有些时候，就算你更新了统计信息，优化器还是不会做出正确的决策，这个时候你没办法只能用 query hint来指导优化器，你要用哪个计划。

[火星大能猫]

感谢 结贴感谢 结贴