一个sql需要优化
select outc.* from B0 bo , O0 outc , E0 ev where outc.id = bo.outId
and ev.id = outc.eId and (ev.tId = 11 or ev.id=1153) order by eId, typeid
观察他的执行计划
+----+-------------+-------+-------------+----------------------------------+----------------------------------+---------+--------------------------+------+---------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+----------------------------------+----------------------------------+---------+--------------------------+------+---------------------------------------------------------------------------------------------+
| 1 | SIMPLE | ev | index_merge | PRIMARY,idx_E_tournamentId_0 | idx_Event_tournamentId_0,PRIMARY | 9,8 | NULL | 2 | Using union(idx_Event_tournamentId_0,PRIMARY); Using where; Using temporary; Using filesort |
| 1 | SIMPLE | outc | ref | PRIMARY,idx_O_eventId_0 | idx_O_e_0 | 8 | aa.ev.id | 14 | |
| 1 | SIMPLE | bo | ref | idx_BO_outcome_0 | idx_BO_outcome_0 | 8 | aa.outc.id | 1 | Using index |
+----+-------------+-------+-------------+----------------------------------+----------------------------------+---------+--------------------------+------+---------------------------------------------------------------------------------------------+
3 rows in set发现他也是走了索引的 (执行速度也快的)
只是从上面的执行计划看, 第1行index_merge , ref为空 ,返回记录仅仅2行
开发人员说这个sql一次执行15遍(循环执行 所以需要优化 )我将这个改为
select * from(select outc.* from B0 bo , O0 outc , E0 ev where outc.id = bo.outId
and ev.id = outc.eId and ev.tId = 11
union
select outc.* from B0 bo , O0 outc , E0 ev where outc.id = bo.outId
and ev.id = outc.eId and ev.id=1153) aa
order by eId, typeid
执行计划这个完全走索引,ref几乎似乎理想的关联表行数现在无法说服一定要改为自己的sql?
select outc.* from B0 bo , O0 outc , E0 ev where outc.id = bo.outId
and ev.id = outc.eId and (ev.tId = 11 or ev.id=1153) order by eId, typeid
观察他的执行计划
+----+-------------+-------+-------------+----------------------------------+----------------------------------+---------+--------------------------+------+---------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+----------------------------------+----------------------------------+---------+--------------------------+------+---------------------------------------------------------------------------------------------+
| 1 | SIMPLE | ev | index_merge | PRIMARY,idx_E_tournamentId_0 | idx_Event_tournamentId_0,PRIMARY | 9,8 | NULL | 2 | Using union(idx_Event_tournamentId_0,PRIMARY); Using where; Using temporary; Using filesort |
| 1 | SIMPLE | outc | ref | PRIMARY,idx_O_eventId_0 | idx_O_e_0 | 8 | aa.ev.id | 14 | |
| 1 | SIMPLE | bo | ref | idx_BO_outcome_0 | idx_BO_outcome_0 | 8 | aa.outc.id | 1 | Using index |
+----+-------------+-------+-------------+----------------------------------+----------------------------------+---------+--------------------------+------+---------------------------------------------------------------------------------------------+
3 rows in set发现他也是走了索引的 (执行速度也快的)
只是从上面的执行计划看, 第1行index_merge , ref为空 ,返回记录仅仅2行
开发人员说这个sql一次执行15遍(循环执行 所以需要优化 )我将这个改为
select * from(select outc.* from B0 bo , O0 outc , E0 ev where outc.id = bo.outId
and ev.id = outc.eId and ev.tId = 11
union
select outc.* from B0 bo , O0 outc , E0 ev where outc.id = bo.outId
and ev.id = outc.eId and ev.id=1153) aa
order by eId, typeid
执行计划这个完全走索引,ref几乎似乎理想的关联表行数现在无法说服一定要改为自己的sql?
解决方案 »
- MySql的show profile 显示Page_faults_major很大,意味什么?
- c程序连接mysql,内存占用问题
- 新手问题,root用户创建数据库为什么不行?
- 联合查询的问题,第一次为真就不查询第二次,如果为否需要查询第二次。
- 还原mysql数据库出错 存储空间不足 不能完成此命令??急
- 请教ERROR:‘WinMySQLadmin.Ink’指向的驱动器或网络连接不可用
- 请教高手:MySQL++在C++ Builder中不能运行的问题。
- 整了一天了,越来越晕。event定时每周日23点45分执行一个任务到底怎么做啊
- Mysql 服务启动报错 1067,尝试了很多方法,依然无法解决,求技术大牛指点。
- mysql本地连接报错
- navicat能控制MySQL开关吗
- 求一条sql语句……急用!
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
Using union(idx_Event_tournamentId_0,PRIMARY); Using where; Using temporary; Using filesort 意思没有利用到索引, 这个应该做点优化。
所以我将原来的sql 换成union的模式
楼上同意吗?
这个应该可以啊。因为只产生了两条记录,在这个情景下是可以用的。并且也不错。
很多文章提示有Using temporary这个 必须做优化那么请分析我的sql是否更好
select * from( select outc.* from B0 bo , O0 outc , E0 ev where outc.id = bo.outId
and ev.id = outc.eId and ev.tId = 11
union
select outc.* from B0 bo , O0 outc , E0 ev where outc.id = bo.outId
and ev.id = outc.eId and ev.id=1153) aa
order by eId, typeid
他的执行计划走索引 而且 没有 Using temporary
他的执行计划只有 Using filesort
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
| 1 | SIMPLE | t7974 | range | PRIMARY | PRIMARY | 4 | NULL |
2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
1 row in set (0.00 sec)mysql> explain select * from t7974 where seqnum=56 union select * from t7974 whe
re seqnum=3465;
+----+--------------+------------+-------+---------------+---------+---------+--
-----+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | r
ef | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+--
-----+------+-------+
| 1 | PRIMARY | t7974 | const | PRIMARY | PRIMARY | 4 | c
onst | 1 | |
| 2 | UNION | t7974 | const | PRIMARY | PRIMARY | 4 | c
onst | 1 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL |
NULL | NULL | |
+----+--------------+------------+-------+---------------+---------+---------+--
-----+------+-------+
3 rows in set (0.00 sec)
其中seqnum是主键
可见union之后时间复杂度为常量 而or的话是不走索引借此发挥一下不Using temporary;很明显你的查询行数没超过
max_allowed_packet
效率高
不知道我说的对不
select * from t7974 where seqnum=56 or seqnum=3465 order by a, b
看你如何加上后面的 order by a, b
索引?