在 MySQL 中为多列创建索引以进行查询优化

发布于04月14日

我在MySQL中有一个这样的表，有大量行.

TABLE `Table_1` (
  `InternalId` int(10) NOT NULL AUTO_INCREMENT,
  `Id` varchar(50) NOT NULL,
  `Test_Column` varchar(100)  NULL,
  `Month` int(2) NOT NULL,
  `Year` int(2) NOT NULL,
  PRIMARY KEY (`InternalId`),
  KEY `IX_Id` (`Id`)
) ENGINE=InnoDB;

我需要定期查询表中月/年已过go 且Test_列不为NULL的行，并使用以下查询对它们执行一些操作:

Select * From Table_1 where Test_Column is NULL AND (Year < @currentYear OR (Year = @currentYear AND Month = @currentMonth))

为这些列创建索引的最佳方法是什么？我是否只需要在所有三列上创建一个索引，即年、月、测试列，以及测试列为空的事实是否值得考虑？

推荐答案

这是我试过的.在MySQL 8.0.28中测试:

alter table table_1 
  add column test_column_is_null bool as (test_column is null),
  add column date date as (date(concat(`Year`, '-', `Month`, '-01'))),
  add index (test_column_is_not_null, date);

然后我查看了解释报告以了解您的问题:

EXPLAIN 
SELECT * FROM Table_1 WHERE test_column_is_null = 1 
  AND date < DATE_FORMAT(NOW(), '%Y-%m-01')\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Table_1
   partitions: NULL
         type: range
possible_keys: test_column_is_null
          key: test_column_is_null
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

这表明它使用了我添加的索引，key_len表明它使用了索引的两列(1字节表示布尔值，3字节表示日期，还有一些字节表示可空).