请教一个分区表查询排序的问题

Question

表索引情况如下：mysql> show index from smw_ids;
+---------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| smw_ids |          0 | PRIMARY     |            1 | smw_id        | A         |     1154326 |     NULL | NULL   |      | BTREE      |         |
| smw_ids |          0 | PRIMARY     |            2 | smw_namespace | A         |     1154326 |     NULL | NULL   |      | BTREE      |         |
| smw_ids |          1 | smw_title   |            1 | smw_title     | A         |     1154326 |       50 | NULL   |      | BTREE      |         |
| smw_ids |          1 | smw_sortkey |            1 | smw_sortkey   | A         |     1154326 |       50 | NULL   |      | BTREE      |         |
+---------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.05 sec)
 
mysql> explain partitions SELECT smw_namespace, smw_title FROM `smw_ids` WHERE smw_namespace=1180 ORDER BY smw_sortkey ASC LIMIT 5 ;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | smw_ids | p39        | ALL  | NULL          | NULL | NULL    | NULL | 243654 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)
 
mysql> 从EXPLAIN结果来看，这个查询是先通过分区得到所有的记录，然后在对这些记录进行排序，排好前5条得到结果集输出。我的理解是在smw_sortkey上面有索引，那么它应该是已经排好序的。在这里为什么不用这个索引呢？

Answer 1

上面的这个查询在110W+的记录情况下，需要跑0.35S左右，我查看PROFILING看到的就是SORT RESULT这里耗时。在另外一个没有进行分区的表里，同样的语句，它会使用smw_sortkey这个索引，然后0.00S就得到了结果了。
下面是另外一个表的索引和和EXPLAIN的情况。mysql> explain SELECT smw_namespace, smw_title FROM `smw_ids` WHERE smw_namespace=1180 ORDER BY smw_sortkey ASC LIMIT 5;
+----+-------------+---------+-------+---------------+-------------+---------+------+---------+-------------+
| id | select_type | table   | type  | possible_keys | key         | key_len | ref  | rows    | Extra       |
+----+-------------+---------+-------+---------------+-------------+---------+------+---------+-------------+
|  1 | SIMPLE      | smw_ids | index | NULL          | smw_sortkey | 257     | NULL | 1144741 | Using where | 
+----+-------------+---------+-------+---------------+-------------+---------+------+---------+-------------+
1 row in set (0.00 sec)
 
mysql> show index from smw_ids;
+---------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| smw_ids |          0 | PRIMARY     |            1 | smw_id        | A         |     1259391 |     NULL | NULL   |      | BTREE      |         | 
| smw_ids |          1 | smw_title   |            1 | smw_title     | A         |      629695 |     NULL | NULL   |      | BTREE      |         | 
| smw_ids |          1 | smw_title   |            2 | smw_namespace | A         |     1259391 |     NULL | NULL   |      | BTREE      |         | 
| smw_ids |          1 | smw_title   |            3 | smw_iw        | A         |     1259391 |     NULL | NULL   | YES  | BTREE      |         | 
| smw_ids |          1 | smw_sortkey |            1 | smw_sortkey   | A         |     1259391 |     NULL | NULL   |      | BTREE      |         | 
+---------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

Answer 2

看到你上面的索引,好像那个列上的索引是前缀索引,mysql的order by 好像是不能用前缀索引的,你改掉这个索引再试试,看看什么情况.没有数据测试不出来..

Answer 3

前缀索引是值:  KEY `smw_sortkey` (`smw_sortkey`(50)),注意50 
不是值多列索引中的最左索引,我没有数据,所以都是全表扫描,但是我force index 一下 如图:
mysql> explain partitions SELECT smw_namespace, smw_title FROM `smw_ids_1` force
 index (smw_sortkey) WHERE smw_namespace=1180 ORDER BY smw_sortkey ASC LIMIT 5
    -> ;
+----+-------------+-----------+------------+-------+---------------+-----------
--+---------+------+------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key
  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------------+-------+---------------+-----------
--+---------+------+------+-------------+
|  1 | SIMPLE      | smw_ids_1 | p39        | index | NULL          | smw_sortke
y | 257     | NULL |  141 | Using where |
+----+-------------+-----------+------------+-------+---------------+-----------
--+---------+------+------+-------------+
1 row in set (0.00 sec)

Answer 4

确实如你说的，我另外建了个全字段索引的，不用前缀了。可以用到这个索引咯。mysql> explain SELECT /* TWBrowse::getNaviTermByNs 66.249.71.12 */ smw_namespace, smw_title FROM `smw_ids` WHERE smw_namespace=1180  ORDER BY smw_sortkey DESC LIMIT 1 ;
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | smw_ids | index | NULL          | idx_smw_ids | 257     | NULL |    1 | Using where |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)
 
mysql> 
前缀索引不能对ORDER BY排序起作用，你是哪本书上看到的？《高性能MYSQL》吗？

Answer 5

《高性能MYSQL》原话：MYSQL不能在order by 或者group by查询中使用前缀索引，也不能把他们用做覆盖索引。第86页。

Answer 6

你的索引是 （smw_id，smw_namespace）
而你的查询条件是 
smw_namespace=1180
ORDER BY smw_sortkey这样smw_namespace=1180条件常规情况下无法利用索引。

Answer 7

我的理解是有分区存在的前提下，优化器会先定位到分区。到这个分区下，再决定使用哪个索引。我前面没有用到索引，所以ORDER BY的时候使用了文件排序，导致0.4S的时间。原因是因为排序字段上的索引是前缀索引（50），MYSQL优化器不能对ORDER BY使用前缀索引。后来我把这个索引改成全长度，不用前缀，结果就使用到这个索引了。