SQL -内连接表上的复合索引？

Question

我正在尝试创建一个或多个额外的索引来加快下面的查询速度。所有的键都是主键(除了表A上的id )，所以它们已经有一个默认的btree索引与之关联。表A上的id也已经有一个与之关联的索引，因为它是一个MUL键，这意味着它是非惟一索引的一部分。

Select A.id
From TableA A
      Inner join TableB B
      On A.address = B.address
      And A.code = B.code
Group by A.id
Having count(distinct B.user) = 1;

以下是上述表格的当前索引：

mysql> show index from TableA;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TableA |          0 | PRIMARY  |            1 | address      | A         |           8 |     NULL | NULL   |      | BTREE      |         |
| TableA |          0 | PRIMARY  |            2 | code         | A         |          24 |     NULL | NULL   |      | BTREE      |         |
| TableA |         1  | id       |            1 | id           | A         |           8 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


mysql> show index from TableB;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TableB    |          0 | PRIMARY  |            1 | user        | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| TableB    |          0 | PRIMARY  |            2 | address     | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| TableB    |          0 | PRIMARY  |            3 | code        | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| TableB    |          1 | address  |            1 | address     | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| TableB    |          1 | address  |            2 | code        | A         |           9 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

查询解释说明如下：

    +----+-------------+-------+--------+---------------+---------+---------+---------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                                   | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | F     | index  | address       | address | 514     | NULL                                  |    9 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | A     | eq_ref | PRIMARY       | PRIMARY | 514     | db.B.address,db.B.code                |    1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------------+------+----------------------------------------------+

我不明白我应该做什么。地址和代码的复合索引是提高上述查询速度的唯一方法吗？

或者id的聚集索引(因为查询使用group by)更好？或者我可以两者都使用？

Answer 1

截至2016年底，MySQL不能在单个查询中利用任何给定表的多个索引。因此，添加新的单列索引无济于事。我想你知道这一点。

如果不尝试一下，很难知道复合索引会有多好。也就是说，我建议你在TableB上试试这个索引。(address, code, user)

为什么？该查询查找code和address的特定值，然后汇总user。试试看。

您也可以在TableA上尝试复合索引。应该是(id, address, code)。这从id开始，因为您在TableA上没有任何WHERE过滤器，并且可以通过以id顺序扫描表来优化GROUP BY子句。但请先尝试TableB索引。

http://use-the-index-luke.com/是一个很好的参考资料。