本文介绍了为什么在这里的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)
我们可以看到它使用文件排序，而不是使用上的索引，这将是缓慢的，当所选择的结果集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?
推荐答案
这篇关于为什么在这里的s索引不用于排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！