MySQL:定义正确的索引

Question

我有这张表:

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| ID                  | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| CODE                | varchar(255) | NO   |     | NULL    |                |
| REVISION            | varchar(255) | NO   |     | NULL    |                |
| NAME                | varchar(255) | NO   |     | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------+

现在，我的应用程序将执行这些查询(从可能性较大到可能性较小):

1. 从 ID = 的项目中选择 *
2. 从代码 = 的项目中选择 *按修订说明排序
3. 从代码 = 的项目中选择 *和修订 = ?
4. 从项目中选择*，代码如下？或者名字？按代码 ASC、修订 DESC 排序

对于这种情况，最佳的索引组合是什么？

我在 Windows 8.1 x64 上使用 MySQL 5.6.14 (InnoDB)。

<小时/>

更新(仍在测试):

到目前为止最好的方法是使用单独的索引:IDX_CODE (CODE)、IDX_NAME (NAME)。

mysql> describe select id, code, revision from UNIT where code like 'M0170SIGM1%' or name like 'M0170SIGM1%' ORDER BY code ASC, revision DESC;
+----+-------------+-------+-------------+-------------------+-------------------+---------+------+------+------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys     | key               | key_len | ref  | rows | Extra                                                            |
+----+-------------+-------+-------------+-------------------+-------------------+---------+------+------+------------------------------------------------------------------+
|  1 | SIMPLE      | UNIT  | index_merge | IDX_CODE,IDX_NAME | IDX_CODE,IDX_NAME | 767,767 | NULL |   31 | Using sort_union(IDX_CODE,IDX_NAME); Using where; Using filesort |
+----+-------------+-------+-------------+-------------------+-------------------+---------+------+------+------------------------------------------------------------------+

无法让 MySQL 在双向排序中使用索引。

Answer 1

主键负责第一个查询。

对于第二个和第三个:item(code, revision)。请注意，order by case asc 是不必要的，因为您只选择一个代码。

最后一个查询有问题。您可能想使用全文索引。如果 like 模式开头没有通配符，那么以下公式可能会更好:

select i.*
from (select i.* from item i where code like ? union
      select i.* from item i where name like ?
     ) i
order by code, revision;

但是，引擎可能会使用全表扫描然后排序来实现此查询，而不管索引如何。