我想我知道这个问题是怎么产生的了,但是有新的问题来了。
前提条件:两表数据固定。
测试步骤:两表都做analyze,之后执行SQL(explain select t.topic_id, t.topic_title, t.create_time from grp_topic as t left join grp_group as g ON t.group_id = g.group_id where (g.city_id = 0 or g.city_id = 1) and t.is_del = 0 order by t.create_time desc limit 10
。
测试步骤反复多次。
问题来了,执行的都是同一SQL,却出现了两种不同的执行计划。
+----+-------------+-------+-------+-----------------+----------+---------+--------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+----------+---------+--------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | g | range | PRIMARY,city_id | city_id | 1 | NULL | 37 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | t | ref | group_id | group_id | 4 | XXXXXXX.g.group_id | 290 | Using where |
+----+-------------+-------+-------+-----------------+----------+---------+--------------------+------+-----------------------------------------------------------+
+----+-------------+-------+--------+-----------------+-------------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------+-------------+---------+--------------------+------+-------------+
| 1 | SIMPLE | t | index | group_id | create_time | 4 | NULL | 10 | Using where |
| 1 | SIMPLE | g | eq_ref | PRIMARY,city_id | PRIMARY | 4 | XXXXXXX.t.group_id | 1 | Using where |
+----+-------------+-------+--------+-----------------+-------------+---------+--------------------+------+-------------+