union all 如何优化 我在本地模拟不出你说的情况数据10w是指每个表的记录吧,unoin结果大概多少记录呢? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 其实Union里每句语句的执行和单句执行效率是一样的就多了最后结果集union的过程,记录多,字段多的话可能耗时就多了,但总觉得你的结果夸张了点 无法提速,估计你用了ORDER BY来排序。 这么多记录需要重新排序会花比较长时间。 建议你直接贴出你的语句。 +----+--------------+--------------+-------------+------------------------------------------+----------------------------------+---------+---------------+------+-----------------------------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |+----+--------------+--------------+-------------+------------------------------------------+----------------------------------+---------+---------------+------+-----------------------------------------------------------------------------+| 1 | PRIMARY | b | index_merge | PRIMARY,auditing_state,flag,freeze_state | flag,auditing_state,freeze_state | 5,5,5 | NULL | 548| Using intersect(flag,auditing_state,freeze_state); Using where; Using index || 1 | PRIMARY | a | ref | Idx_purvey_product,auditing_state | Idx_purvey_product | 9 | chemmade.b.id | 30| Using where || 2 | UNION | b | index_merge | PRIMARY,auditing_state,flag,freeze_state | flag,auditing_state,freeze_state | 5,5,5 | NULL | 548| Using intersect(flag,auditing_state,freeze_state); Using where; Using index || 2 | UNION | a | ref | user_id | user_id | 5 | chemmade.b.id | 4| Using where || 3 | UNION | b | index_merge | PRIMARY,auditing_state,flag,freeze_state | flag,auditing_state,freeze_state | 5,5,5 | NULL | 548| Using intersect(flag,auditing_state,freeze_state); Using where; Using index || 3 | UNION | a | ref | user_id | user_id | 9 | chemmade.b.id | 2| Using where || NULL | UNION RESULT | <union1,2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort|+----+--------------+--------------+-------------+------------------------------------------+----------------------------------+---------+---------------+------+-----------------------------------------------------------------------------+7 rows in set (0.06 sec) 不使用order大概要5s左右,SQL只是简单的联合查询 你要select出来10w行记录做什么? 没有条件过滤吗 比如分页什么的只要几十行 有分页,但是也要select出结果排序后才能分页吧 你的SQL语句是什么?索引情况? 执行计划已贴出,SQL就是简单的联合查询,单条SQL没有速度问题 详细说明你的要求是什么?分页?你的SQL语句是什么,有什么要求,看看能否优化一下SQL语句 你只要排序,则UNION ALL后的效率会很差。因为MYSQL需要自己进行排序无法利用索引。 union 空表 帮忙优化 SQL 语句 此sql如何添加索引 效率最高? 求个快法~~~ 请教MySQL怎样实现双向同步? MySql下两个表联合查询的sql语句,大家帮个忙 可以在sql或过程中调用执行外部命令吗? MYSQL -5.6.10 WIN64 环境 下面 UTF-8问题 多条件查询,帮忙写一个语句 mysql中如何写 用正则表达式验证11位手机号的sql语句 类似QQ好友关系数据库应该如何设计 问个弱弱的问题
就多了最后结果集union的过程,记录多,字段多的话可能耗时就多了,但总觉得你的结果夸张了点
+----+--------------+--------------+-------------+------------------------------
------------+----------------------------------+---------+---------------+------
+-----------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows
| Extra |
+----+--------------+--------------+-------------+------------------------------
------------+----------------------------------+---------+---------------+------
+-----------------------------------------------------------------------------+
| 1 | PRIMARY | b | index_merge | PRIMARY,auditing_state,flag,f
reeze_state | flag,auditing_state,freeze_state | 5,5,5 | NULL | 548
| Using intersect(flag,auditing_state,freeze_state); Using where; Using index || 1 | PRIMARY | a | ref | Idx_purvey_product,auditing_s
tate | Idx_purvey_product | 9 | chemmade.b.id | 30
| Using where || 2 | UNION | b | index_merge | PRIMARY,auditing_state,flag,f
reeze_state | flag,auditing_state,freeze_state | 5,5,5 | NULL | 548
| Using intersect(flag,auditing_state,freeze_state); Using where; Using index || 2 | UNION | a | ref | user_id
| user_id | 5 | chemmade.b.id | 4
| Using where || 3 | UNION | b | index_merge | PRIMARY,auditing_state,flag,f
reeze_state | flag,auditing_state,freeze_state | 5,5,5 | NULL | 548
| Using intersect(flag,auditing_state,freeze_state); Using where; Using index || 3 | UNION | a | ref | user_id
| user_id | 9 | chemmade.b.id | 2
| Using where || NULL | UNION RESULT | <union1,2,3> | ALL | NULL
| NULL | NULL | NULL | NUL
L | Using filesort
|
+----+--------------+--------------+-------------+------------------------------
------------+----------------------------------+---------+---------------+------
+-----------------------------------------------------------------------------+
7 rows in set (0.06 sec)
索引情况?
你的SQL语句是什么,有什么要求,看看能否优化一下SQL语句