如何在MySQL数据库的单列索引空间中对多列进行索引

Question

这是我在面试过程中遇到的一个问题。

假设您将得到包含列的数据库： “ROW_ID”， “payload_data” 和 “TIME_STAMP”。但是，您一次只能保留一列的索引。

如何为“row_id”和“time_stamp”保留索引？

Answer 1

我觉得这个问题应该是“智能”和预期的答案是：

让row_id表的主键和time_stamp创建索引。

这样表格只有一个附加索引。无论如何，主键都是需要和创建的。

总的来说，表格有两个索引，在我看来，这是一个低质量的面试问题。

Answer 2

这显然是一个人为的要求，因为没有理由避免多列索引。

一种解决方案是创建一个虚拟列，将row_id与time_stamp连接起来，并对虚拟列进行索引。

CREATE TABLE `artificial` (
    `row_id` int NOT NULL, 
    `time_stamp` timestamp NULL DEFAULT NULL, 
    `payload_data` text, 
    `row_id_time_stamp` varchar(20) 
     GENERATED ALWAYS AS (concat(`row_id`,':',`time_stamp`)) VIRTUAL, 
    KEY `row_id_time_stamp` (`row_id_time_stamp`) 
) 

但是该索引不如多列索引那么灵活。如果您在两列上进行搜索，它都可以工作，但如果您想使用=来搜索特定的row_id值，它将不起作用。

换句话说，这种情况下可以使用多列索引，但不是CONCAT指数：

... WHERE row_id = '1234' 

要使用CONCAT指标，你要么必须寻找一种模式，或级联为两列值：

... WHERE row_id_time_stamp LIKE '1234:%' 
... WHERE row_id_time_stamp = '1234:2017-10-12 22:27:00' 

另一种可能的解释是，如果row_id是主键，然后在time_stamp二次索引隐式地包括，因为InnoDB实现二级索引的方式row_id。所以这可能是一个测试MySQL/InnoDB内部的问题。