MySql 对列的一部分进行索引

Question

我需要搜索一个中等大小的MySql表(大约1500万条记录)。我的查询搜索以另一个值结尾的值，例如:

SELECT * FROM {tableName} WHERE {column} LIKE '%{value}'

• {value} 的长度始终为 7 个字符。
• {column} 有时长度为 8 个字符(否则为 7 个字符)。

有没有办法提高我的搜索性能？显然索引不是一个选项。

我可以在另一列上以相反的顺序保存 {column} 值并为该列建立索引，但我希望避免这种解决方案。

Answer 1

{value} is always 7 characters length

您的数据未规范化。解决这个问题就是解决问题的方法。其他任何东西都是黑客。话虽如此，我承认修复过去由假人造成的损坏并不总是切合实际的。

但是，最合适的黑客攻击取决于您未告诉我们的大量信息。

• 运行查询的频率
• 复合数据的格式是什么

but im looking to avoid this solution.

为什么？这是解决问题的合理方法。唯一的缺点是您需要维护新属性 - 鉴于此数据域出现在多个不同属性中(另一个规范化违规)，这意味着以单独的方式实现索引更有意义， EAV relation但您只需在原始表上添加触发器即可使用现有代码库保持同步。我能想到的每个解决方案都可能需要类似的修复。

这是一个帮助您入门的简化示例(没有多个属性):

CREATE TABLE lookup (
   table_name VARCHAR(18) NOT NULL,
   record_id INT NOT NULL, /* or whatever */ 
   suffix VARCHAR(7),
   PRIMARY KEY (table_name, record_id),
   INDEX (suffix, table_name, record_id)
);

CREATE TRIGGER insert_suffix AFTER INSERT ON yourtable
   FOR EACH ROW
   REPLACE INTO lookup (table_name, record_id, suffix)
   VALUES ('yourtable', NEW.id
   , SUBSTR(NEW.attribute, NEW.id, RIGHT(NEW.attribute, 7
  );  

CREATE TRIGGER insert_suffix AFTER UPDATE ON yourtable
   FOR EACH ROW
   REPLACE INTO lookup (table_name, record_id, suffix)
   VALUES ('yourtable', NEW.id
   , RIGHT(NEW.attribute, 7)
  ); 

  CREATE TRIGGER insert_suffix AFTER DELETE ON yourtable
   FOR EACH ROW
   DELETE FROM lookup WHERE table_name='yourtable' AND record_id=OLD.id
  ;