为子字符串查询优化Mysql表索引 [英] Optimizing Mysql Table Indexing for Substring Queries

问题描述

我有一个MySQL索引问题给你们。

我有一个非常大的表（〜100Million Records）在MySQL中包含文件的信息。我做的大多数查询都涉及文件路径列上的子字符串操作。

这是表ddl：

  CREATE TABLE`filesystem_data`。 `$ tablename`（
`file_id` INT（14）NOT NULL AUTO_INCREMENT PRIMARY KEY，
`file_name` VARCHAR（256）NOT NULL，
`file_share_name` VARCHAR 
`file_path` VARCHAR（900）NOT NULL，
`file_size` BIGINT（14）NOT NULL，
`file_tier` TINYINT（1）UNSIGNED NULL，
`file_last_access` DATETIME NOT NULL，
`file_last_change` DATETIME NOT NULL，
`file_creation` DATETIME NOT NULL，
`file_extension` VARCHAR（50）NULL，
 INDEX（`file_path`，`file_share_name `）
）ENGINE = MYISAM 
}; 
  

例如，有一行有一个file_path，如：

 '\\Server100\share2\Home\Zenshai\My Documents \'
  

我将用类似

的方式提取用户名称（本例中的Zenshai）

  SELECT substring_index（substring_index（fp.file_path，'\\'，6），'\\'， -  1）as Username 
 FROM（SELECT'\\ \\Server100\\share2\\Home\\Zenshai\\My Documents \\'as file_path）fp 
  

这有点丑陋，但这不是我现在的关注。

我想要的建议是什么样的索引（如果有的话）可以帮助加快这个表上的这些类型的查询。欢迎任何其他建议。

感谢。

PS。虽然表非常大，但是有足够的空间用于索引。

解决方案

p>

您可以添加一个名为 USERNAME 的列，将其填入 INSERT / UPDATE 触发器与在 SELECT 中使用的表达式进行触发，并在此列上进行搜索。

P. 只要好奇，您的服务器上真的有 100 mln + 文件？

I have a MySQL indexing question for you guys.

I've got a very large table (~100Million Records) in MySQL that contains information about files. Most of the Queries I do on it involve substring operations on the file path column.

Here's the table ddl:

CREATE TABLE `filesystem_data`.`$tablename` (
                `file_id` INT( 14 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
                `file_name` VARCHAR( 256 ) NOT NULL ,
                `file_share_name` VARCHAR ( 100 ) NOT NULL,
                `file_path` VARCHAR( 900 ) NOT NULL ,
                `file_size` BIGINT( 14 ) NOT NULL ,
                `file_tier` TINYINT(1) UNSIGNED NULL, 
                `file_last_access` DATETIME NOT NULL ,
                `file_last_change` DATETIME NOT NULL ,
                `file_creation` DATETIME NOT NULL ,
                `file_extension` VARCHAR( 50 ) NULL ,
                INDEX ( `file_path`, `file_share_name` ) 
                ) ENGINE = MYISAM 
             };

So for example ill have a row with a file_path like:

'\\Server100\share2\Home\Zenshai\My Documents\'

And I'll extract the User's name (Zenshai in this example) with something like

SELECT substring_index(substring_index(fp.file_path,'\\',6),'\\',-1) as Username
FROM (SELECT '\\\\Server100\\share2\\Home\\Zenshai\\My Documents\\' as file_path) fp

It gets a bit ugly, but that's not really my concern right now.

What I'd like some advice on is what kind of index (if any at all) can help speed up these types of queries on this table. Any other suggestions are welcome too.

Thanks.

PS. Although the table gets very large there is enough space for indexes.

解决方案

You cannot use indices with your current table design.

You may add a column called USERNAME, fill it in the INSERT/UPDATE trigger with the expression you use in SELECT, and search on this column.

P. S. Just curious, you really have 100 mln+ files on your server?

这篇关于为子字符串查询优化Mysql表索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！