在网上看到两个查询语句,引擎INNODB,版本Mysql 5.0 .
Common Query 1:
SELECT * FROM task_condition_0
WHERE uid <= 110402840 AND (Tc_ID<79777 or UID<1104028400) limit 6;
Better Query 2:
SELECT A2.* FROM task_condition_0 A1 INNER JOIN task_condition_0 A2 ON A1.TC_id=A2.TC_id
WHERE A1.uid <= 110402840 AND (A1.Tc_ID<79777 or A1.UID<1104028400) limit 6;
经过优化器得到一样结果的另外一种写法:
SELECT A1.* FROM task_condition_0 A1 INNER JOIN (SELECT TC_ID FROM task_condition_0 WHERE uid <= 110402840 AND (Tc_ID<79777 or UID<1104028400) ORDER BY UID,TC_ID limit 6) A2
ON A1.TC_id=A2.TC_id ;
EXPLAIN 1
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+------------------------+----------------+---------+------+-------+-------------+
| 1 | SIMPLE | task_condition_0 | range | PRIMARY,index_uid_tcid | index_uid_tcid | 11 | NULL | 20390 | Using where |
EXPLAIN 2
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------- ---+----------------+---------+--------------+-------+--------------------------+
| 1 | SIMPLE | A1 | range | PRIMARY,index_uid_tcid | index_uid_tcid | 11 | NULL | 20390 | Using where; Using index |
| 1 | SIMPLE | A2 | eq_ref | PRIMARY | PRIMARY | 4 | tmp.A1.TC_ID | 1 | |
实际上以上两个都只扫描rows=6.
查看逻辑读Innodb_buffer_pool_read_requests
1 221736008-221735923=85
2 221736074-221736008=66
明显我们看到的第二个语句比第一个语句效率要高。
因为什么呢 ?
SQL1使用Second index排序--->取到所有行--->进行limit--->最后获得所需的数据行
SQL2 使用Second index排序--->只取到主键值,不需要取所有行--->进行limit--->通过与全表进行主键关联。来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12309491/viewspace-700666/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12309491/viewspace-700666/