为什么在这里的s索引不用于排序? [英] Why is the index on s not used for sort here?

问题描述

设置:

mysql> create table test(id integer unsigned,s varchar(30));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test(id,s) value(1,'s');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id,s) value(1,'tsr');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id,s) value(1,'ts3r');
Query OK, 1 row affected (0.00 sec)

mysql> create index i_test_id on test(id);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create index i_test_s on test(s);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>  insert into test(id,s) value(21,'ts3r');
Query OK, 1 row affected (0.00 sec)

然后运行:

mysql> explain select * from test where id in (1) order by s desc;
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | test  | ref  | i_test_id     | i_test_id | 5       | const |    2 | Using where; Using filesort |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------------+
1 row in set (0.02 sec)

我们可以看到它使用文件排序，而不是使用上的索引，这将是缓慢的，当所选择的结果集big.How优化它的?

We can see it uses filesort instead of using the index on s,which will be slow when the selected result set is big.How to optimize it?

推荐答案

有时，即使索引可用，MySQL也不使用索引.发生这种情况的一种情况是，优化器估计使用索引将需要MySQL访问表中很大比例的行.

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table.

来自: MySQL 5.1参考手册:MySQL如何使用索引

这篇关于为什么在这里的s索引不用于排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！