关于数据表索引的使用优化问题

jerson

t表中5000条数据,有flag字段,等于1的2500条,等于0的2500条,在flag上建了索引,
当使用查询select * from t where flag=1 limit 10 的时候还是会扫描2000多行,应该怎么做可以使它就扫描10行啊?

kider

好问题...

edwin_chen

不知道楼主说的扫描2000多行，是否是指explain中的rows列的值。

如果是的话，我觉得和“等于1的2500条”没有什么直接关系。我作了个例子，如下：
mysql> select count(*) from DEVICEINFO;
+----------+
| count(*) |
+----------+
|     5000 |
+----------+
1 row in set (0.00 sec)
这个表中有5000条记录
-------------------------------------------------------------------------------------
mysql> select count(*) from DEVICEINFO WHERE STATUS=1;
+----------+
| count(*) |
+----------+
|     5000 |
+----------+
1 row in set (0.02 sec)
其中status都是等于1
---------------------------------------------------------------------------------------
mysql> select * from DEVICEINFO WHERE STATUS=1 LIMIT 10;
我执行如下查询仅需要0.01秒
---------------------------------------------------------------------------------------
看一下它的explain
mysql> explain select * from DEVICEINFO WHERE STATUS=1 LIMIT 10;
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | DEVICEINFO | ref  | status_Index  | status_Index | 2       | const | 2505 |       |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)
扫描了2505行，实际上匹配的记录数是5000行。因此说明这只是一个大概的值。而不是真实扫描的记录数。
----------------------------------------------------------------------------------------
mysql> explain select * from DEVICEINFO WHERE STATUS=1 LIMIT 100;
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | DEVICEINFO | ref  | status_Index  | status_Index | 2       | const | 2505 |       |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from DEVICEINFO WHERE STATUS=1 LIMIT 5000;
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | DEVICEINFO | ref  | status_Index  | status_Index | 2       | const | 2505 |       |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)

我把limit改为100或者5000，同样还是2505，因此进一步证明这个值跟扫描多少记录无关。
---------------------------------------------------------------------------------------------------------------------------
进一步测试，用update语句把其中的2500条记录的status改为0，再测试

mysql> explain select * from DEVICEINFO WHERE STATUS=1 LIMIT 5000;
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | DEVICEINFO | ref  | status_Index  | status_Index | 2       | const | 2531 |       |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from DEVICEINFO WHERE STATUS=1 LIMIT 10;
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | DEVICEINFO | ref  | status_Index  | status_Index | 2       | const | 2526 |       |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.01 sec)
mysql> explain select * from DEVICEINFO WHERE STATUS=1 LIMIT 100;
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | DEVICEINFO | ref  | status_Index  | status_Index | 2       | const | 2526 |       |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from DEVICEINFO WHERE STATUS=1 LIMIT 5000;
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | DEVICEINFO | ref  | status_Index  | status_Index | 2       | const | 2526 |       |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)

可以看到没有什么特定的规律。因此结论：这个rows值与实际扫描记录数无关。


另外像这样的语句，仅需要0.01秒的执行时间，有必要再优化吗？

[ 本帖最后由 edwin_chen 于 2008-5-13 17:59 编辑 ]

mysqlkumao

好

kider

详细，高见，赞...

sflong

那这个rows有什么用呢,只能通过查询时间来判断使用索引的好处了?