Now want to design a file management system, based on mysql database, file storage based on oss, said file by file_id said folder to folder

Now my design looks something like this:

/folder_1/folder_2/...
/folder_3 file_1
/folder_4 file_5

Folder is theoretically infinite hierarchy, met a very tricky question now: copy the folder

Copy the folder will produce a recursive search and insert a large amount of data, that performance is very poor, you have what good method to optimize the model design or in some good advice, please
Thank you for the

CodePudding user response:

Each file to add a full path to the fields and indexes, such as/folder_1/folder_1_1/folder_1_1_1/file_1
If you want to copy/folder_1/folder_1_1 folders, query/folder_1 folder_1_1 directly at the beginning of file (like '/folder_1 folder_1_1 %' can go index)
As for a large amount of data insertion should be inevitable, insert the data won't be very low efficiency

CodePudding user response:

reference 1st floor evanweng response:

each file to add a full path to the fields and indexes, such as/folder_1/folder_1_1/folder_1_1_1/file_1
If you want to copy/folder_1/folder_1_1 folders, query/folder_1 folder_1_1 directly at the beginning of file (like '/folder_1 folder_1_1 %' can go index)
As for a large amount of data insertion should be inevitable, insert the data won't be very low efficiency

As well as a detail change, query with like '/folder_1/folder_1_1/%, add a forward slash, avoid prefix queries to the same folder

CodePudding user response:

reference 1st floor evanweng response:

each file to add a full path to the fields and indexes, such as/folder_1/folder_1_1/folder_1_1_1/file_1
If you want to copy/folder_1/folder_1_1 folders, query/folder_1 folder_1_1 directly at the beginning of file (like '/folder_1 folder_1_1 %' can go index)
As for a large amount of data insertion should be inevitable, insert the data won't be very low efficiency

Through left prefix can find all the files in the folder, folder, however, might also contains folder, so I need new folder and keep the file hierarchy, any good solution for this problem, thank you

CodePudding user response:

reference 5 floor twilight larvae reply:

Quote: refer to 1st floor evanweng response:

each file to add a full path to the fields and indexes, such as/folder_1/folder_1_1/folder_1_1_1/file_1
If you want to copy/folder_1/folder_1_1 folders, query/folder_1 folder_1_1 directly at the beginning of file (like '/folder_1 folder_1_1 %' can go index)
As for a large amount of data insertion should be inevitable, insert the data won't be very low efficiency

Through left prefix can find all the files in the folder, folder, however, might also contains folder, so I need a new folder and keep file hierarchy, any good solution for this problem, thank you

This seems to only need to put the file path, the original replace the target folder path to the folder? Or consider to have some empty folder?

CodePudding user response:

Folder is infinite hierarchy, that is the path of the file, may be infinitely long, so what do you use the field type, store this path,
Use the INSERT INTO TABLE_NAME SELECT * FROM TABLE_NAME WHERE... Can greatly solve the performance problems, because all the data sets the basic content of database replication, does not involve the network problems,

CodePudding user response:

refer to 6th floor evanweng response:

Quote: refer to the 5 floor twilight larvae response:

Quote: refer to 1st floor evanweng response:

each file to add a full path to the fields and indexes, such as/folder_1/folder_1_1/folder_1_1_1/file_1
If you want to copy/folder_1/folder_1_1 folders, query/folder_1 folder_1_1 directly at the beginning of file (like '/folder_1 folder_1_1 %' can go index)
As for a large amount of data insertion should be inevitable, insert the data won't be very low efficiency

Through left prefix can find all the files in the folder, folder, however, might also contains folder, so I need a new folder and keep file hierarchy, any good solution for this problem, thank you

This seems to only need to put the file path, the original replace the target folder path to the folder? Or consider to have some empty folder?

Thinking is like this, but once the hierarchy is deep, the distribution of the path is in trouble

CodePudding user response:

refer to 7th floor AHUA1001 response:

folder is infinite hierarchy, that is the path of the file, can be infinitely long, so what do you use the field type, store this path,
Use the INSERT INTO TABLE_NAME SELECT * FROM TABLE_NAME WHERE... Can greatly solve the performance problems, because all the data sets of basic content in database replication, does not involve the network problems,

Now the field type is VARCHAR (1024), because I don't store clear path, storage folder id, for example/1/2/3..
Yes, all problems are concentrated in the performance of database operations, or an old problem, level of deep and complex cases, file hierarchy difficult to replicate, brother have what good way

CodePudding user response:

I got a bit chaotic, but curious,
The original poster can go into more detail about the business scene, such as why can produce infinite levels?

Back to the question, only said my own way to solve the problem, isn't it... B: well...


As for dealing with other data, and then probably come,

CodePudding user response:

The

references to the tenth floor hgwyl response:

I got a bit chaotic, but curious,
The original poster can go into more detail about the business scene, such as why can produce infinite levels?

Back to the question, only said my own way to solve the problem, isn't it... B: well...


As for dealing with other data, and then probably,

Good I said about the specific business scenarios:
Is a file management system, similar to the cloud disk, network backup, folder layer is theoretically infinite, but it may do business restrictions, physical storage file on the self-built oss, database as the file_id to refer to,
Main business are these:
1. Can show the complete file directory structure
2. Can realize copying files and folders, shearing functions such as
3. Statistics can be a folder under the number of files (this can be left prefix matching, need not consider)

Saw your table structure design, as if still cannot solve the folder copy problem, also please help the brother again

CodePudding user response:

11 references, twilight larvae response:

1. Can show full file directory structure
2. Can realize copying files and folders, shearing functions such as
3. Statistics can be a folder under the number of files (this can be left prefix matching, need not consider)

You try on the drawing database solve

1. Can show the complete file directory structure
Executive level hierarchy
=folder belong toIf performs level & lt;> 1 then
Path=parent folder name. "/". The current folder name
Loop execution until the execution level=1
End the if

2. Can realize copying files and folders, shearing functions such as
Just copy and paste files,
Specific level by "folder belongs in a table hierarchy", the "parent folder name" to control,

Didn't feel not achieve them...

CodePudding user response:

refer to 12 floor hgwyl reply:

Quote: refer to the evening 11 wang reply:

1. Can show the complete file directory structure
2. Can realize copying files and folders, shearing functions such as
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull