为什么这里不使用 s 上的索引进行排序？
设置：
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)
我们可以看到它使用文件排序而不是使用 s 上的索引，当选择的结果集很大时，这会很慢。如何优化？
Setup:
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)
And then run this:
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)
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
来自：MySQL 5.1参考手册：MySQL如何使用索引
From: MySQL 5.1 Reference Manual: How MySQL Uses Indexes
id 上的索引用于标识要返回的行。根据您使用的 MySQL 版本，它可能只允许每个表使用一个索引，并且优化器已确定使用索引来过滤行而不是排序更有效。
The index on id is being used to identify the rows to return. Depending on the version of MySQL you are using, it may only allow the use of one index per table, and the optimizer has determined it is more efficient to use the index for filtering the rows rather than for ordering.
在“id”列上创建聚集索引。
聚集索引意味着物理排序。这样我猜测调用此查询时不会有文件排序。
但一张表只能有一个聚集索引。因此，如果您有另一列作为表的主键，则可能无法在列“id”上创建聚集索引。
因为主键默认是聚集的。
Create a clustered index on the column 'id'.
Clustered index means a physical sort. That way I am guessing there wont be a filesort, when this query is invoked.
But a table can have only one clustered index. Hence , if you have another column that is a primary key for the table, you may not be able to create a clustered index on column 'id'.
As primary keys by default are clustered.
您使用的是哪个版本的 MySQL？直到版本 5，MySQL 才可以为每个表使用多个索引。
要使用的索引的选择还取决于结果集的大小。由于结果中仅返回两条记录，因此它可能不会使用索引。对于如此小的结果集，MySQL 似乎并不介意手动排序。
然而，如果这对您来说是一个常见的查询，那么您可以做的真正帮助 MySQL 的事情是添加一个复合索引（'id'，'s'）。基本上，这几乎就像您创建另一个小表，该表始终按 id 然后 s 排序，因此不需要文件排序，并且只需要一个索引，而不是两个。
What version of MySQL are you on? Not until version 5 could MySQL use more than one index per table.
The choice of the indexes to use also depends on the size of the result set. With only two records returned in the result, it may not use the index anyway. For such small result sets, MySQL doesn't seem to mind sorting things manually.
However, what you could do to really help MySQL out, if this is a common query for you, is to add a compound index ('id', 's'). Basically, it's almost like your creating another little table that is always sorted by id then s, so no filesort would be required, and it would only need the one index, not two.
您遇到的问题是由于您在 sql 语句中放置了 Order by 子句。这导致 MySql 跳过使用任何索引并对 S 进行完整排序。解释语句显示 MySql 有 i_test_id 一个可能的索引可供选择，并且键字段显示它已被选择，但它必须对 s 也进行排序。优化器选择不使用 i_test_s 作为可能的索引，因为它在性能方面的成本更高。您可以通过以磁盘空间为代价构建复合索引来解决此问题，也可以使用联合来以不同的方式构建查询。不过还没有在你的例子中尝试过。
The problem you are experiencing is coming from the fact that you are putting an Order by clause in your sql statement. This is causing MySql to skip using any of the indexes and doing a full sort on S. The explain statement is showing that MySql has the i_test_id a possible index to choose from and the key field is showing that it has been chosen, but it must perform a sort on s as well. The optimizer has chosen to not use i_test_s as a possible index because it would be more costly in term of performance. You can go around this issue by building componsite indexes at the expense of disk space, or you can structure your query differently using Unions instead. Haven't tried it in your example though.