|
通过执行计划explain分析如下查询语句
- mysql> explain select msg_id from circlemessage_idx_0 where to_id = 113487 and circle_id=10019063 and msg_id>=6273803462253938690 and from_id != 113487 order by msg_id asc limit 30;
- +----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
- | 1 | SIMPLE | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16 | NULL | 349780 | Using where |
- +----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
- 1 row in set (0.00 sec)
- mysql> explain select msg_id from circlemessage_idx_0 where to_id = 113487 and circle_id=10019063 and from_id != 113487 order by msg_id asc limit 30;
- +----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
- | 1 | SIMPLE | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16 | NULL | 30 | Using where |
- +----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
- 1 row in set (0.00 sec)
问题分析
通过上面两个执行计划可以发现当没有msg_id >= xxx这个查询条件的时候，检索的rows要少很多，并且两者查询的时候都用到了索引，而且用到的还只是主键索引。那说明索引应该是不合理的，没有发挥最大作用。 （编辑：南平站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
