连接性能:受主键选择或列顺序的影响？

Question

我有一个 20 列和 3000 万行的表格劳动。一列lab_id是外键，具有索引（不唯一）。另一个表映射有 10 列，具有主键 ID 和唯一的索引列lab_id。我想用地图中的内容更新一些劳动列：

UPDATE labor r
INNER JOIN map m ON r.lab_id = m.lab_id
SET r.col5 = m.col5
  , r.col6 = m.col6
  , r.col7 = m.col7
;

<代码>劳动力。lab_id＜/code＞可以作为第二列或最后一列放置。用于联接的索引列的位置是否会影响更新的性能？

如果删除 map.id并将map.lab_id定义为主键，性能是否会发生变化？

Answer 1

表中列的顺序没有可衡量的差异。

主键的选择在InnoDB中确实有所不同。该存储引擎使用表的主键作为所谓的聚集索引。表的内容存储在该聚集索引中，而不是DBMS表空间的其他部分中。因此，当查询引用主键时，查询规划器可以立即访问行中的其余数据。

相比之下，非主键，甚至是唯一的主键（在InnoDB中）检索主键，查询规划器使用该主键访问聚集索引。因此，通过主键的行引用只需要一次数据访问操作。总的来说。

如果您的 id 列是自动增量列，并且您希望继续以这种方式使用它，则可以将其放入唯一索引中，然后更改主键。与此类似的 DDL 语句可以解决问题。

sql prettyprint-override">ALTER TABLE map
     ADD UNIQUE INDEX id(id),
     DROP PRIMARY KEY,
     ADD PRIMARY KEY (lab_id);

这可能是一个很好的方法，因为您的表将继续工作，而无需更改您现有的软件。

这种DDL语句同时处理多个键。这是进行更改的最有效方法，因为它一次完成所有表的重组。这仍然需要时间。

至于性能。插入/更新性能变化不大：插入和更新仍然需要检查和更新两个唯一键。查询性能会变得更好。

(以上都不适用于MyISAM或AriaDB存储引擎。)