发现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，说明做了索引扫描。
作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：https://jackxiang.com/post/1330/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！
PHPUnit 需要测试memcache类的方法
php谨防超时我用CURl,3秒超时示例！评论列表
