Mysql性能：如何在数据库中存储文件树？

Question

我正在尝试将文件存储在数据库中并查询文件夹的数量和大小。我的表如下。

CREATE TABLE `files` (
`permission` varchar(10) DEFAULT NULL,
`size` int(11) NOT NULL DEFAULT '0' COMMENT 'filesize',
`date` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`time` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`path` varchar(1024) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
KEY `path` (`path`(10)),
KEY `path_2` (`path`(4)),
KEY `size` (`size`)
) ENGINE=MyISAM

我的文件夹就像“???/一些字符串”。我大约有 38,333,316 条记录。 简单的查询大约需要22秒。

mysql> select count(*),sum(size)/(1024.0*1024.0*1024.0) from files 
where path like '5___%' and size != 2048;
+----------+----------------------------------+
| count(*) | sum(size)/(1024.0*1024.0*1024.0) |
+----------+----------------------------------+
|  7900746 |                        9019.6535 |
+----------+----------------------------------+
1 row in set (22.89 sec)

mysql> explain select count(*),sum(size)/(1024.0*1024.0*1024.0) 
       from files where path like '5___%' and size != 2048;
--+-----------+-----+----+-------------- -+----+-------+----+--------+-----------
id|select_type|table|type|possible_keys   |key |key_len|ref |rows    | Extra     
--+-----------+-----+----+----------------+----+-------+----+--------+-----------
 1| SIMPLE    |files| ALL|path,path_2,size|NULL|NULL   |NULL|38333316|Using where
--+-----------+-----+----+----------------+----+-------+----+--------+-----------
1 row in set (0.23 sec)

有没有办法优化查询，我可以以某种方式加快速度吗？ 通常我会问一些类似的问题：

path like '5___%' and size != 2048
path like '3___%' and size != 2048
path like '45__%' and size != 2048

谢谢阿曼。

Answer 1

尝试这样的事情：

CREATE TABLE `directories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `path` varchar(1024) NOT NULL,
  `permissions` varchar(10) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `path` (`path`(4))
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `files` (
  `directory_id` int(10) unsigned NOT NULL,
  `filename` varchar(1024) NOT NULL,
  `permissions` varchar(10) NOT NULL,
  `timestamp` datetime NOT NULL,
  `size` bigint(20) unsigned NOT NULL,
  KEY `size` (`size`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

SELECT COUNT(*), SUM(size)/1073741824 FROM files WHERE directory_id IN(SELECT group_concat(id SEPARATOR ',') FROM directories WHERE path LIKE '5___%');