에서 출발한 삽질기.
우선 현재 리눅스는 기본적으로 파일명은 255, 전체 경로 길이는 4K 가 최대길이의 default다.
https://serverfault.com/questions/9546/filename-length-limits-on-linux
단. 제한 해제가 가능한지는 모르겠다.
윈도우즈는 2016년 win10에서 그동안 제한 걸려있던 260 길이의 경로 제한을 풀어버렸다.
https://mspoweruser.com/ntfs-260-character-windows-10/
with recursive file_full_paths (id, path) as (
....
)
https://mariadb.com/kb/en/library/recursive-common-table-expressions-overview/
CREATE TABLE `file_system` (
`id` INT(11) UNSIGNED NOT NULL,
`path` VARCHAR(255) NULL DEFAULT NULL,
`parent` INT(11) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `parent_fk` (`parent`),
FULLTEXT INDEX `path_index` (`path`),
CONSTRAINT `parent_fk` FOREIGN KEY (`parent`) REFERENCES `file_system` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
delimiter //
CREATE PROCEDURE dorepeat(pstart INT, pend Int)
BEGIN
SET @x = pstart;
REPEAT
SET @x = @x + 1;
insert into file_system (id,parent,path)
values (@x,
#floor(RAND()*(@x-1)),
@x-1,
concat(
char(round(rand()*25)+97),
char(round(rand()*25)+97),
char(round(rand()*25)+97),
char(round(rand()*25)+97),
char(round(rand()*25)+97),
char(round(rand()*25)+97),
char(round(rand()*25)+97),
char(round(rand()*25)+97)));
UNTIL @x > pend
END REPEAT;
END
//
insert into file_system (id,parent,path) values (0,null,'C:/');
insert into file_system (id,parent,path) values (1,null,'D:/');
insert into file_system (id,parent,path) values (2,null,'F:/');
insert into file_system (id,parent,path) values(3,null,'');
CALL dorepeat1(4,10000);
with recursive file_full_paths (id, path) as (
select fs1.id, fs1.path from file_system fs1 where fs1.parent is null
union
SELECT fs.id as id,
CONCAT(fp.path, '/', fs.path) as path
FROM file_full_paths fp, file_system fs
WHERE fs.parent = fp.id and fs.parent is not null
)
select * from file_full_paths;
with recursive file_full_paths (id, path) as (
select fs1.id, fs1.path from file_system fs1 where fs1.parent is null
union
SELECT fs.id as id,
CONCAT(fp.path, '/', fs.path) as path
FROM file_full_paths fp, file_system fs
WHERE fs.parent = fp.id and fs.parent is not null
)
select * from file_full_paths;
/* Affected rows: 0 찾은 행: 29,958 경고: 0 지속 시간 1 쿼리: 0.453 sec. (+ 0.031 sec. network) */
with recursive file_full_paths (id, path) as (
select fs1.id, fs1.path from file_system fs1 where fs1.parent is null
union
SELECT fs.id as id,
CONCAT(fp.path, '/', fs.path) as path
FROM file_full_paths fp, file_system fs
WHERE fs.parent = fp.id and fs.parent is not null
)
select * from file_full_paths where path = 'C:';
/* Affected rows: 0 찾은 행: 1 경고: 0 지속 시간 1 쿼리: 0.437 sec. */
with recursive file_full_paths (id, path) as (
select fs1.id, fs1.path from file_system fs1 where fs1.parent is null
union
SELECT fs.id as id,
CONCAT(fp.path, '/', fs.path) as path
FROM file_full_paths fp, file_system fs
WHERE fs.parent = fp.id and fs.parent is not null
)
select * from file_full_paths where path like '%f%';
/* Affected rows: 0 찾은 행: 28,004 경고: 0 지속 시간 1 쿼리: 0.438 sec. (+ 0.031 sec. network) */
with recursive file_full_paths (id, path) as (
select fs1.id, fs1.path from file_system fs1 where fs1.parent is null
union
SELECT fs.id as id,
CONCAT(fp.path, '/', fs.path) as path
FROM file_full_paths fp, file_system fs
WHERE fs.parent = fp.id and fs.parent is not null
)
select * from file_full_paths order by path;
/* Affected rows: 0 찾은 행: 29,958 경고: 0 지속 시간 1 쿼리: 0.484 sec. (+ 0.031 sec. network) */
우선 자체적인 테이블 조회 결과로는 0.4초가 기본적으로 깔려 있는 것을 알 수 있다.
이거만으로 성능이 안좋네.. 라며 끝낼 순 있지만 내가원하는건 이게 아니므로 좀 더 해본다.
ALTER TABLE media_log
add (file_system_id int(11) unsigned);
ALTER TABLE `media_log`
ADD INDEX `file_system_fk` (`file_system_id`);
update media_log set file_system_id=floor(Rand()*((select count(1) from file_system)-1));
with recursive file_full_paths (id, path) as (
select fs1.id, fs1.path from file_system fs1 where fs1.parent is null
union
SELECT fs.id as id,
CONCAT(fp.path, '/', fs.path) as path
FROM file_full_paths fp, file_system fs
WHERE fs.parent = fp.id and fs.parent is not null
)
select media_log.id, media_log.file_system_id, media_log.agent_name, file_full_paths.path from media_log join file_full_paths
on media_log.file_system_id = file_full_paths.id;
/* Affected rows: 0 찾은 행: 1 경고: 0 지속 시간 1 쿼리: 0.515 sec. */
create view file_path_view as (
with recursive file_full_paths (id, path) as (
select fs1.id, fs1.path from file_system fs1 where fs1.parent is null
union
SELECT fs.id as id,
CONCAT(fp.path, '/', fs.path) as path
FROM file_full_paths fp, file_system fs
WHERE fs.parent = fp.id and fs.parent is not null)
select * from file_full_paths
)
select media_log.id, media_log.file_system_id, media_log.agent_name, file_path_view.path from media_log join file_path_view
on media_log.file_system_id = file_path_view.id;
/* Affected rows: 0 찾은 행: 1 경고: 0 지속 시간 1 쿼리: 0.515 sec. */
select a.id, a.file_system_id, a.agent_name, b.path from user_usb_log a join file_path_view b
on a.file_system_id = b.id;
/* Affected rows: 0 찾은 행: 117,350 경고: 0 지속 시간 1 쿼리: 0.547 sec. (+ 0.250 sec. network) */
select a.id, a.file_system_id, a.agent_name, b.path from user_usb_log a join file_path_view b
on a.file_system_id = b.id
order by a.id;
/* Affected rows: 0 찾은 행: 117,350 경고: 0 지속 시간 1 쿼리: 1.093 sec. (+ 0.172 sec. network) */
select * from file_path_view;
/* Affected rows: 0 찾은 행: 21,958 경고: 0 지속 시간 1 쿼리: 0.266 sec. (+ 0.015 sec. network) */
select * from file_path_view;
/* Affected rows: 0 찾은 행: 503,575 경고: 0 지속 시간 1 쿼리: 12.641 sec. (+ 0.109 sec. network) */
ALTER TABLE `file_system`
ADD UNIQUE INDEX `path_name` (`path`, `parent`) USING HASH;
select * from file_path_view;
/* Affected rows: 0 찾은 행: 49,974 경고: 0 지속 시간 1 쿼리: 0.891 sec. (+ 0.015 sec. network) */
select * from file_path_view where path like 'D://vw%';
/* Affected rows: 0 찾은 행: 5,008 경고: 0 지속 시간 1 쿼리: 0.907 sec. */
with recursive file_full_paths (id, path) as (
select fs1.id, fs1.path from file_system fs1 where fs1.parent is null
union
SELECT fs.id as id,
CONCAT(fp.path, '/', fs.path) as path
FROM file_full_paths fp, file_system fs
WHERE fs.parent = fp.id and
fs.parent is not null and
fs.path like 'vw%'
)
select * from file_full_paths ;
/* Affected rows: 0 찾은 행: 48 경고: 0 지속 시간 1 쿼리: 0.031 sec. */
쓰다보니 두서도 없고 의식과 공부의 흐름대로 쓴 포스팅이 되었는데 이제까지 포스팅중 가장 길다… 누가 이 포스팅을 읽을지 모르겠지만. 나한텐 큰 도움이 되었다. CTE도 알게 되었고 마이크로한 DB성능 테스트 등 도 하게되었다. 나한테 좋은 공부가 되었다. 잊지 말자!.