MariaDB社区
标题:
关于数据表索引的使用优化问题
[打印本页]
作者:
jerson
时间:
2008-5-4 13:03
标题:
关于数据表索引的使用优化问题
t表中5000条数据,有flag字段,等于1的2500条,等于0的2500条,在flag上建了索引,
当使用查询select * from t where flag=1 limit 10 的时候还是会扫描2000多行,应该怎么做可以使它就扫描10行啊?
作者:
kider
时间:
2008-5-5 09:52
好问题...
作者:
edwin_chen
时间:
2008-5-13 17:57
不知道楼主说的扫描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
时间:
2008-5-27 19:21
好
作者:
kider
时间:
2008-6-3 10:02
详细，高见，赞...
作者:
sflong
时间:
2008-9-1 16:57
那这个rows有什么用呢,只能通过查询时间来判断使用索引的好处了?
欢迎光临 MariaDB社区 (http://123.56.88.72/)
Powered by Discuz! X3.2