explain的误解

发现MySQL的explain还是很有迷惑性的。

看下面两个sql的explain，(i,j)是tt的主键

mysql> explain select * from tt where i between 3 and 5 and j = 4\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: tt
        type: range
possible_keys: PRIMARY
         key: PRIMARY
     key_len: 8
         ref: NULL
        rows: 8
       Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> explain select * from tt where i > 2 and i < 6 and j = 4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tt
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8
        Extra: Using where; Using index
1 row in set (0.00 sec)

看上去如果使用了between，那么MySQL可以用的索引的两列。但仔细的研究了一下，发现实际并不是这样。between 3 and 5可以写为(i>3 or i=3) and (i<5 or i=5)，是这两个等号使用了两列，而大于和小于的判断仍然是index扫描，只用了一列。

再看另一个

mysql> explain select * from tt where i in (3,4,5) and j=4\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: tt
        type: range
possible_keys: PRIMARY
         key: PRIMARY
     key_len: 8
         ref: NULL
        rows: 3
       Extra: Using where; Using index
1 row in set (0.00 sec)

这个才是真正用了两列，可以看到这里估计的行数是3，而刚才两个是8。用 show status 可以看得更清楚一些：

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
6 rows in set (0.00 sec)

mysql> select * from tt where i in (3,4,5) and j=4;
+---+---+
| i | j |
+---+---+
| 3 | 4 |
+---+---+
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 4     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
6 rows in set (0.00 sec)

mysql> select * from tt where i between 3 and 5 and j = 4;
+---+---+
| i | j |
+---+---+
| 3 | 4 |
+---+---+
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 6     |
| Handler_read_next     | 10    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
6 rows in set (0.00 sec)

后一个sql中有9次read_next_key，说明做了索引扫描。