SQL优化

Question

我正在尝试优化mysql上的查询。

以下是表的结构：

表文件：

id: int(8) primary key auto_increment
filename: varchar(1024) 

其目的是创建一个像文件浏览器一样的导航结构。

因此，用户单击第一个文件夹，并看到一个文件和文件夹列表，它可以在其中单击以与文件交互或单击另一个文件夹。

假设我知道第一个目录的名称是'/storage/‘。

下面是我使用的查询来列出'/storage/‘文件夹中的文件和文件夹：

-- List Files
SELECT FileName,ID
FROM FILE
WHERE SUBSTRING(FileName, 1, 9) = '/storage/'
  AND LOCATE('/', SUBSTRING(FileName, 9+1)) = 0
UNION
-- List folders
SELECT DISTINCT(SUBSTRING(FileName, 11+1, LOCATE('/', SUBSTRING(FileName, 9+1)))) AS FileName,
       0 AS ID
FROM FILE
WHERE 
  SUBSTRING(FileName, 1, 9) = '/storage/'
  AND LOCATE('/', SUBSTRING(FileName, 9+1)) > 0
ORDER BY ID, FileName

此查询正在运行，并列出了文件和文件夹：

FILENAME                    ID
subdirectory/               0
/storage/test.jpg           123

如何优化查询以使查询更快？

我已经在文件名和id列中添加了索引，但是在查询中使用mysql函数(如locate、substring等)时，似乎没有使用索引。

我的存储有20T，查询需要7秒才能完成。

编辑的

我添加了另一列'dirpath varchar‘，在其中我添加了文件所在的目录，并且我能够得到50%的替换。

我创建了两个触发器来填充该列：

CREATE TRIGGER TR_dirpath_INSERT_file
BEFORE INSERT
ON file FOR EACH ROW 
SET NEW.dirpath = SUBSTR(new.filename, 1, LENGTH(new.filename)-LENGTH(SUBSTRING_INDEX(new.filename, '/', -1)));    


CREATE TRIGGER TR_dirpath_UPDATE_file
BEFORE UPDATE
ON file FOR EACH ROW 
SET NEW.dirpath = SUBSTR(new.filename, 1, LENGTH(new.filename)-LENGTH(SUBSTRING_INDEX(new.filename, '/', -1)));

最后的SQL是：

-- List Files
SELECT FileName,ID
FROM FILE
WHERE dirpath = '/storage/'

UNION
-- List folders
SELECT DISTINCT(SUBSTRING(FileName, 11+1, LOCATE('/', SUBSTRING(FileName, 9+1)))) AS FileName,
       0 AS ID
FROM FILE
WHERE 
  SUBSTRING(FileName, 1, 9) = '/storage/'
  AND LOCATE('/', SUBSTRING(FileName, 9+1)) > 0
ORDER BY ID, FileName

现在，我必须找到一种方法来改进这个SQL的第二部分(列表文件夹)。

Answer 1

设计MySQL模式的一般规则--需要分割的单独部分。插入前先这样做。您已经看到了将路径与文件名分开是多么的混乱。

当我创建类似的数据库时，我选择了两个表:一个用于Directories，一个用于Files。Files没有路径的filename，加上对Directories中一行的引用(dir_id)。