mysql - 如何使mysql多列索引以快速执行

Question

我有一个查询要应用索引。查询是

select * from table where (id1='12' AND id2='17') OR (id1='17' AND id2='12');

现在我尝试使用 (id1,id2) 应用索引，但解释查询显示“type”为“ALL”当我尝试 (id2,id1) 或 id1 和 id2 时，响应仍然相同。那么如何在 id1 和 id2 上建立索引来处理上述查询。

mysql> explain select * from bothTable where (id1=12 and id2=17) OR (id1=17 and id2=12);
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | bothTable | ALL  | NULL          | NULL | NULL    | NULL |   67 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> create index index_id1 ON bothTable (id1,id2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from bothTable where (id1=12 and id2=17) OR (id1=17 and id2=12);
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | bothTable | ALL  | index_id1     | NULL | NULL    | NULL |   67 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

使用 union 它给我以下输出:

+----+--------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type  | table            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------+------------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY      | bothTable | ALL  | index_id1     | NULL | NULL    | NULL |   67 | Using where |
|  2 | UNION        | bothTable | ALL  | index_id1     | NULL | NULL    | NULL |   67 | Using where |
| NULL | UNION RESULT | <union1,2>       | ALL  | NULL          | NULL | NULL    | NULL | NULL |             |
+----+--------------+------------------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.00 sec)

Answer 1

create index id1_id2 on table(id1, id2);

此外，如果您的 id1 和 id2 列是整数列，请不要在整数周围放置 ''，而是尝试以下操作:

select * from table where (id1=12 AND id2=17) OR (id1=17 AND id2=12);

示例:

mysql> explain select * from ff where (id1 = '12' and id2 = '17') or (id1 = '17' and id2 = '12');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ff    | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> create index id1_id2 on ff(id1,id2);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from ff where (id1 = 12 and id2 = 17) or (id1 = 17 and id2 = 12);
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | ff    | index | id1_id2       | id1_id2 | 10      | NULL |    7 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

编辑由于这似乎仍然不起作用，请尝试一下:

explain 
select * from ff where (id1 = 12 and id2 = 17)
union
select * from ff where (id1 = 17 and id2 = 12);