项目中遇到这样一个sql语句,select * from table where id=92876 order by match_weight desc limit 10,此时效率极低,将降序改为升序效率更低。当语句改为limit 50或者去掉order by和where子句之一时效率很高。不得其解,望大神指教
...全文
6045013打赏收藏
MySQL的where、order by、limit结合使用问题
项目中遇到这样一个sql语句,select * from table where id=92876 order by match_weight desc limit 10,此时效率极低,将降序改为升序效率更低。当语句改为limit 50或者去掉order by和where子句之一时效率很高。不得其解,望大神指教
查看这两个语句的查询计划,发现优化之后使用索引不同
SIMPLE relative_memo_to_open_source_projects index find_by_osp_id match_weight_sort 5 14550 Using where
SIMPLE relative_memo_to_open_source_projects ref find_by_osp_id find_by_osp_id 4 const 3608 Using where; Using filesort
通过使用USE INDEX (index1, index2 ...)可以限制查询使用的索引范围,同样还有IGNORE INDEX (index1, index2 ...)可以不使用某些索引,通过使用FORCE INDEX (index1, index2...)可以强制使用索引,因此在这里可以加上USE INDEX(find_by_osp_id)来限制使用索引提高查询效率,修改后语句为
SELECT * FROM relative_memo_to_open_source_projects USE INDEX (find_by_osp_id) WHERE osp_id = 92876 ORDER BY match_weight LIMIT 5
查询计划为
SIMPLE relative_memo_to_open_source_projects ref find_by_osp_id find_by_osp_id 4 const 3608 Using where; Using filesort
执行时间为0.1s左右
where 或者 order by 看具体的执行计划,可能会先 order 也可以是先 where
limit 肯定是最后。[/quote]
竟然还有 先order by再 where了。。
这是真的吗?
哪种情况?
顺序不应该是这样的吗。没你说的那种吧。
from... where...group by... having.... select ... order by... limit
[/quote]当你从一个排好序的序列是,找出其中的数值时,是先排序还是先筛选? 比如让你从字黄中找出所有 Y开头的字母。
where word like 'Z%' order by word
MYSQL会根据已有的索引,直接进行筛选,而不会再进行排序。
[/quote]
你说的这种情况是 也是先where,mysql也没有先排序啊。
只是mysql发现word有索引,where之后不需要排序而已。(本身就是排好的,直接输出就好)
where 或者 order by 看具体的执行计划,可能会先 order 也可以是先 where
limit 肯定是最后。[/quote]
竟然还有 先order by再 where了。。
这是真的吗?
哪种情况?
顺序不应该是这样的吗。没你说的那种吧。
from... where...group by... having.... select ... order by... limit
[/quote]当你从一个排好序的序列是,找出其中的数值时,是先排序还是先筛选? 比如让你从字黄中找出所有 Y开头的字母。
where word like 'Z%' order by word
MYSQL会根据已有的索引,直接进行筛选,而不会再进行排序。
where 或者 order by 看具体的执行计划,可能会先 order 也可以是先 where
limit 肯定是最后。[/quote]
竟然还有 先order by再 where了。。
这是真的吗?
哪种情况?
顺序不应该是这样的吗。没你说的那种吧。
from... where...group by... having.... select ... order by... limit
“create index xx on tablexx (id,match_weight desc); ” ,非常有道理 。
索引使用注意问题 :
(1)以下需要注意不使用索引
<>, NOT IN, LIKE %_开头
<> 可以用 a>1 or a<3 代替,NOT IN 可以用NOT exists代替
(2)在使用max() min()时最好加上索引.
(3)单索引要创建在确实需要的地方.
多列索引 有最佳左前缀特性 所以尽量左边的字段是最常用的.(4)索引不会包括有NULL值,就是有NULL,索引就失效(5)使用短索引,一个字段字太多,可以建立部分索引,只取前十个字.节约空间.
(6)不要在列上运算.例如:where MD5("password") = "myz"
(7)在mysql5.5(好像是这个版本,记不清了)之前,一条SQL语句中如果存在多个索引,Mysql自动使用一个自己认为比较高效的索引(仅能使用一个索引)