主页 / dba / 问题 / 43
Accepted
Maniero
Asked: 2011-01-04 14:18:52 +0800 CST

如何在 MySQL 中创建条件索引？

  • 772

如何创建索引来过滤 MySQL 中表的特定范围或子集？AFAIK 无法直接创建，但我认为可以模拟此功能。

示例：我想为NAME列创建索引，仅用于行STATUS = 'ACTIVE'

此功能在 SQL Server中称为过滤索引，在 Postgres中称为部分索引。

mysql index

6 个回答

  1. Best Answer

    MySQL 目前不支持条件索引。

    要实现您的要求（不是您应该这样做；）），您可以开始创建一个辅助表：

    CREATE TABLE  `my_schema`.`auxiliary_table` (
   `id` int unsigned NOT NULL,
   `name` varchar(250), /* specify the same way as in your main table */
   PRIMARY KEY (`id`),
   KEY `name` (`name`)
);

    然后在主表中添加三个触发器：

    delimiter //

CREATE TRIGGER example_insert AFTER INSERT ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   END IF;
END;//

CREATE TRIGGER example_update AFTER UPDATE ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   ELSE
      DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
   END IF;
END;//

CREATE TRIGGER example_delete AFTER DELETE ON main_table
FOR EACH ROW
BEGIN
   DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
END;//

delimiter ;

    我们需要delimiter //是因为我们想;在触发器内部使用。

    这样，辅助表将准确包含对应于主表行的 ID，这些行包含字符串“ACTIVE”，由触发器更新。

    要在 a 上使用它select，您可以使用通常的join：

    SELECT main_table.* FROM auxiliary_table LEFT JOIN main_table
   ON auxiliary_table.id = main_table.id
   ORDER BY auxiliary_table.name;

    如果主表已经包含数据，或者如果您进行了一些以不寻常方式更改数据的外部操作（例如：在 MySQL 之外），您可以使用以下方法修复辅助表：

    INSERT INTO auxiliary_table SET
   id = main_table.id,
   name = main_table.name,
   WHERE main_table.status="ACTIVE";

    关于性能，您可能会有较慢的插入、更新和删除。仅当您真正处理所需条件为正的少数情况时，这才有意义。即使这样，可能只有测试你才能看到节省的空间是否真的证明了这种方法的合理性（以及你是否真的节省了任何空间）。

    • 12

  2. 如果我正确理解了这个问题，我认为您要做的就是在 NAME 和 STATUS 列上创建一个索引。这将有效地让您查询 NAME='SMITH' 和 STATUS='ACTIVE'

    • 7

  3. 您不能进行条件索引，但对于您的示例，您可以在 ( name, status) 上添加多列索引。

    即使它会索引这些列中的所有数据，它仍然会帮助您找到状态为“活动”的名称。

    • 5

  4. 您可以通过在两个表之间拆分数据、在需要所有数据时使用视图来合并两个表以及仅索引该列中的一个表来实现这一点 - 但我认为这会导致需要查询的性能问题运行整个表，除非查询计划器比我认为的更聪明。本质上，您将手动对表进行分区（并将索引仅应用于其中一个分区）。

    不幸的是，内置的表分区功能无法帮助您完成任务，因为您无法将索引应用于单个分区。

    您可以维护一个带有索引的额外列，并且仅当您希望索引所基于的条件为真时，该列中才有一个值，但这可能是劳动密集型的，并且在以下方面具有有限（或负）价值查询效率和节省空间。

    • 4

  5. MySQL 现在有虚拟列，可用于索引。

    • 0

  6. 我知道这个问题已经相当老了，但最好的方法是只以正确的顺序创建具有两列的索引。如果您先将状态放在索引中，然后将名称放在索引中，您将创建一个索引，该索引将“分组”所有“活动”名称。这样，当您查询表时，优化器将准确地知道索引中的何处开始查找您要查询的名称。

    • 0

相关问题