select * from ( select id from table1 union all select id from table2 union all select id from table3 union all select id from table4 union all select id from table5 union all select id from table6 union all select id from table7) as temp WHERE xx= xx; 我这样查询是不是本身就是不正确的?
ctrl + L 看下查询计划,你就知道瓶颈在哪里了,然后有针对性的去优化
是有点问题,你这个union 7个表,尽量让每个子查询走索引,应该会快些,可以看看执行计划, 瓶颈应该在每个子查询上,union all 本身不耗什么时间的
数据库的表 结构不能改动, 像 这种对表 进行 union all 是不是 已经在降低效率了?
改为先筛选后联合应该会快点吧?另外过滤条件比较固定的话,在涉及到的列上加个索引应该也是个好方法。select * from ( select id from table1 WHERE xx= xx; union all select id from table2 WHERE xx= xx; union all select id from table3 WHERE xx= xx; union all select id from table4 WHERE xx= xx; union all select id from table5 WHERE xx= xx; union all select id from table6 WHERE xx= xx; union all select id from table7 WHERE xx= xx; )
select count(1) from bean
改为先筛选后联合应该会快点吧?另外过滤条件比较固定的话,在涉及到的列上加个索引应该也是个好方法。select * from ( select id from table1 WHERE xx= xx; union all select id from table2 WHERE xx= xx; union all select id from table3 WHERE xx= xx; union all select id from table4 WHERE xx= xx; union all select id from table5 WHERE xx= xx; union all select id from table6 WHERE xx= xx; union all select id from table7 WHERE xx= xx; ) 把 所有的 条件放在 每个查询, 效率 确实提高不少
改为先筛选后联合应该会快点吧?另外过滤条件比较固定的话,在涉及到的列上加个索引应该也是个好方法。select * from ( select id from table1 WHERE xx= xx; union all select id from table2 WHERE xx= xx; union all select id from table3 WHERE xx= xx; union all select id from table4 WHERE xx= xx; union all select id from table5 WHERE xx= xx; union all select id from table6 WHERE xx= xx; union all select id from table7 WHERE xx= xx; ) 把 所有的 条件放在 每个查询, 效率 确实提高不少Count一样的道理,先12个count,然后再相加,速度应该快很多吧?
mysql 是有 记录数据的总行数, 但是那不是动态的, 例如我需要 一个条件的筛选过后的行数, 直接获取 就不准确了
EXPLAIN select COUNT(1) from ( select userid from tradeinfo where userid = 28 union all select userid from travelinfo where userid = 28 union all select userid from projectinfo where userid = 28 union all select userid from lawinfo where userid = 28 union all select userid from loaninfo where userid = 28 union all select userid from educationinfo where userid = 28 union all select userid from fundinfo where userid = 28 union all select userid from creditcarinfo where userid = 28 union all select userid from cardinfo where userid = 28 union all select userid from tidymoneyinfo where userid = 28 union all select userid from financialinfo where userid = 28 union all select userid from bankinfo where userid = 28 ) as temp 现在的执行效率是0.016s 影响效率的是, 先筛选在联合, 做count(*) 统计, SELECT 后面的字段越少 效率越高,就保留 条件字段,
COUNT(1)和COUNT(*)是一样的,你可以在里面写任何常量,如COUNT('CSDNDSB'),不会输出一列常量,不会去系统表里找所有列名。
COUNT(1)和COUNT(列名)意义不同,不能放在一起比较。唯一可比较的情况是列名是单主键表的主键列,即聚集非空单键值索引,这种情况下除非想要强制走聚集索引扫描,否则COUNT(1)优于COUNT(列名),因为前者允许计划选择最估索引,而且没有列输出。http://bbs.csdn.net/topics/390635419
select id from table1
union all
select id from table2
union all
select id from table3
union all
select id from table4
union all
select id from table5
union all
select id from table6
union all
select id from table7) as temp WHERE xx= xx;
我这样查询是不是本身就是不正确的?
是有点问题,你这个union 7个表,尽量让每个子查询走索引,应该会快些,可以看看执行计划, 瓶颈应该在每个子查询上,union all 本身不耗什么时间的
改为先筛选后联合应该会快点吧?另外过滤条件比较固定的话,在涉及到的列上加个索引应该也是个好方法。select * from (
select id from table1 WHERE xx= xx;
union all
select id from table2 WHERE xx= xx;
union all
select id from table3 WHERE xx= xx;
union all
select id from table4 WHERE xx= xx;
union all
select id from table5 WHERE xx= xx;
union all
select id from table6 WHERE xx= xx;
union all
select id from table7 WHERE xx= xx;
)
改为先筛选后联合应该会快点吧?另外过滤条件比较固定的话,在涉及到的列上加个索引应该也是个好方法。select * from (
select id from table1 WHERE xx= xx;
union all
select id from table2 WHERE xx= xx;
union all
select id from table3 WHERE xx= xx;
union all
select id from table4 WHERE xx= xx;
union all
select id from table5 WHERE xx= xx;
union all
select id from table6 WHERE xx= xx;
union all
select id from table7 WHERE xx= xx;
) 把 所有的 条件放在 每个查询, 效率 确实提高不少
查询到的数据为6W条, 连接12张 用count 查询时间为8s
另外看到你执行计划的字段名,貌似我们我们搞行业有关系啊,
另外看到你执行计划的字段名,貌似我们我们搞行业有关系啊,是都慢, 不管是查询还是,count 他们的效率目前都慢, 先处理 sql 优化, 然后在建立索引,现在各种写法,效率都不行!
另外看到你执行计划的字段名,貌似我们我们搞行业有关系啊,是都慢, 不管是查询还是,count 他们的效率目前都慢, 先处理 sql 优化, 然后在建立索引,现在各种写法,效率都不行!问题其实很明显啊,你这个执行计划里显示你的查询都没有走索引union all没什么问题,瓶颈不在这,子查询要走索引之后这点数据应该没问题
你可以把觉得有优化空间的sql发出来,执行计划也发下,让大家帮看看。
改为先筛选后联合应该会快点吧?另外过滤条件比较固定的话,在涉及到的列上加个索引应该也是个好方法。select * from (
select id from table1 WHERE xx= xx;
union all
select id from table2 WHERE xx= xx;
union all
select id from table3 WHERE xx= xx;
union all
select id from table4 WHERE xx= xx;
union all
select id from table5 WHERE xx= xx;
union all
select id from table6 WHERE xx= xx;
union all
select id from table7 WHERE xx= xx;
) 把 所有的 条件放在 每个查询, 效率 确实提高不少Count一样的道理,先12个count,然后再相加,速度应该快很多吧?
如果我没记错的话,mysql里的记录的是主键索引的估算记录数,用处是在执行计划优化的时候提供参考,他这个表里,目前似乎连主键索引都没有建,全部是全表扫
EXPLAIN select COUNT(1) from (
select userid from tradeinfo
where userid = 28
union all
select userid from travelinfo
where userid = 28
union all
select userid from projectinfo
where userid = 28
union all
select userid from lawinfo
where userid = 28
union all
select userid from loaninfo
where userid = 28
union all
select userid from educationinfo
where userid = 28
union all
select userid from fundinfo
where userid = 28
union all
select userid from creditcarinfo
where userid = 28
union all
select userid from cardinfo
where userid = 28
union all
select userid from tidymoneyinfo
where userid = 28
union all
select userid from financialinfo
where userid = 28
union all
select userid from bankinfo
where userid = 28
) as temp
现在的执行效率是0.016s
影响效率的是, 先筛选在联合, 做count(*) 统计, SELECT 后面的字段越少 效率越高,就保留 条件字段,
建议:
1、单个执行子查询,看每个耗时多少
2、最好是在应用层将数据汇总