在MySQL中利用关系搜索本体树

Question

我正在使用以下模式处理一个MySQL数据库：

​

​

其中我们保存来自不同本体(外翻)的信息。其中一些术语有关系(exmp: MS:1000004，样本质量)，这些关系由relationship:或is_a:表示。为了解决这个问题，让我们把重点放在is_a关系上。我们现在想提供一个选项来搜索这些is_a关系。因此，一个函数，我们给MS:1000004/sample mass作为输入，并得到所有通过is_a关系连接到它的术语。但不仅仅是直接与MS:1000004相关的所有术语，还包括所有与孩子相关的术语等等。这个图像更好地描述了这一点，thing将是MS:1000004/sample mass，下面的一切都是我想要的结果。目前，我们使用一个相当未优化的递归函数作为存储过程来执行此操作：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllTermsByParentTerm`(IN `parentOntology` varchar(512))
BEGIN
    WITH RECURSIVE previous (accession, FK_OntologyName, name, definition, xrefvaluetype, isobsolete, fk_termAccession, relationshiptype, fk_termAccession_related, depth_level) AS (
        SELECT 
            t.accession, 
            t.FK_OntologyName, 
            t.name, 
            t.definition, 
            t.xrefvaluetype, 
            t.isobsolete, 
            trt.fk_termAccession, 
            trt.relationshiptype, 
            trt.fk_termAccession_related,
            0 depth_level
        FROM Term t
        INNER JOIN (TermRelationship AS trt, Term AS ref) ON(
            t.Accession = trt.FK_TermAccession
            AND trt.FK_TermAccession_Related = ref.Accession
            AND (trt.RelationshipType = `is_a` OR trt.RelationshipType = `part_of`)
            AND
                ( 
                    trt.FK_TermAccession_Related = ref.Accession
                    AND ref.Name = parentOntology
                )
        )
        UNION All
        SELECT 
            t2.accession, 
            t2.FK_OntologyName, 
            t2.name, 
            t2.definition, 
            t2.xrefvaluetype, 
            t2.isobsolete, 
            trt2.fk_termAccession, 
            trt2.relationshiptype, 
            trt2.fk_termAccession_related,
            (previous.depth_level+1) depth_level
        FROM Term t2
        INNER JOIN (TermRelationship AS trt2, previous) ON(
            t2.Accession = trt2.FK_TermAccession
            AND trt2.FK_TermAccession_Related = previous.Accession
        )
    )
    SELECT 
        t.Accession,
        t.FK_OntologyName,
        t.Name,
        t.Definition,
        t.xRefValueType,
        t.IsObsolete,
        p.depth_level
    FROM previous p
    Inner JOIN Term AS t ON (
        p.Accession = t.Accession
    );
END$$
DELIMITER ;

对于这个问题:我对MySQL没有那么丰富的经验，所以是否有任何选择来优化这个函数，或者MySQL总体上是一个错误的工具？

示例行：

术语

| Accession  | FK_OntologyName | Name                                                           | Definition                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | XRefValueType                                                     | IsObsolete |
|------------|-----------------|----------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------------------------------------------------------|------------|
| TEST:0000000 | TEST          | Proteomics Standards Initiative Mass Spectrometry Vocabularies | "Proteomics Standards Initiative Mass Spectrometry Vocabularies." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |                                                                   |          0 |
| TEST:1000001 | TEST          | sample number                                                  | "A reference number relevant to the sample under study." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | value-type:xsd\:string "The allowed value-type for this CV term." |          0 |
| TEST:1000002 | TEST          | sample name                                                    | "A reference string relevant to the sample under study." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | value-type:xsd\:string "The allowed value-type for this CV term." |          0 |
| TEST:1000003 | TEST          | sample state                                                   | "The chemical phase of a pure sample, or the state of a mixed sample." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |                                                                   |          0 |
| TEST:1000004 | TEST          | sample mass                                                    | "Total mass of sample used." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | value-type:xsd\:float "The allowed value-type for this CV term."  |          0 |
| TEST:1000005 | TEST          | sample volume                                                  | "Total volume of solution used." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | value-type:xsd\:float "The allowed value-type for this CV term."  |          0 |
| TEST:1000006 | TEST          | sample concentration                                           | "Concentration of sample in picomol/ul, femtomol/ul or attomol/ul solution used." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | value-type:xsd\:float "The allowed value-type for this CV term."  |          0 |
| TEST:1000007 | TEST          | inlet type                                                     | "The nature of the sample inlet." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |                                                                   |          0 |
| TEST:1000008 | TEST          | ionization type                                                | "The method by which gas phase ions are generated from the sample." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |                                                                   |          0 |
| TEST:1000011 | TEST          | mass resolution                                                | "Smallest mass difference between two equal magnitude peaks so that the valley between them is a specified fraction of the peak height." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | value-type:xsd\:string "The allowed value-type for this CV term." |          0 |
| TEST:1000012 | TEST          | resolution measurement method                                  | "Which of the available standard measures is used to define whether two peaks are separate." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |                                                                   |          0 |
| TEST:1000014 | TEST          | accuracy                                                       | "Accuracy is the degree of conformity of a measured mass to its actual value." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | value-type:xsd\:float "The allowed value-type for this CV term."  |          0 |
| TEST:1000015 | TEST          | scan rate                                                      | "Rate in Th/sec for scanning analyzers." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | value-type:xsd\:float "The allowed value-type for this CV term."  |          0 |
| TEST:1000016 | TEST          | scan start time                                                | "The time that an analyzer started a scan, relative to the start of the MS run." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | value-type:xsd\:float "The allowed value-type for this CV term."  |          0 |
| TEST:1000018 | TEST          | scan direction                                                 | "Direction in terms of m/z of the scan for scanning analyzers (low to high, or high to low)." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |                                                                   |          0 |
| TEST:1000019 | TEST          | scan law                                                       | "Describes the function in control of the m/z scan (for scanning instruments). Commonly the scan function is linear, but in principle any function can be used." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                   |          0 |
| TEST:1000021 | TEST          | reflectron state                                               | "Status of the reflectron, turned on or off." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |                                                                   |          0 |
| TEST:1000022 | TEST          | TOF Total Path Length                                          | "The length of the field free drift space in a time of flight mass spectrometer." [PSI:MS]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | value-type:xsd\:float "The allowed value-type for this CV term."  |          0 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | value-type:xsd\:int "The allowed value-type for this CV term."    |          0 |

TermRelationship

| ID     | FK_TermAccession | RelationshipType | FK_TermAccession_Related | FK_OntologyName |
|--------|------------------|------------------|--------------------------|-----------------|
| 0 | TEST:1000001          | is_a             | TEST:0000000             | TEST            |
| 1 | TEST:1000002          | is_a             | TEST:0000000             | TEST            |
| 2 | TEST:1000003          | is_a             | TEST:1000002             | TEST            |
| 3 | TEST:1000004          | is_a             | TEST:1000002             | TEST            |
| 4 | TEST:1000005          | is_a             | TEST:1000002             | TEST            |
| 5 | TEST:1000006          | is_a             | TEST:1000002             | TEST            |
| 6 | TEST:1000007          | is_a             | TEST:1000002             | TEST            |
| 7 | TEST:1000008          | is_a             | TEST:1000007             | TEST            |
| 8 | TEST:1000011          | is_a             | TEST:1000007             | TEST            |
| 9 | TEST:1000022          | is_a             | TEST:0000000             | TEST            |

预期的结果是为TEST:0000000/Proteomics Standards Initiative Mass Spectrometry Vocabularies执行函数并获得以下所有术语，因为它们是TEST:0000000的子/外孙术语。

TEST:1000001
TEST:1000002
TEST:1000003
TEST:1000004
TEST:1000005
TEST:1000006
TEST:1000007
TEST:1000008
TEST:1000011
TEST:1000022

Answer 1

其中一些可能有助于业绩：

t:  INDEX(Accession)
trt:  INDEX(FK_TermAccession_Related,  relationshiptype, FK_TermAccession)
trt:  INDEX(FK_TermAccession, FK_TermAccession_Related,  relationshiptype)
ref:  INDEX(Accession,  Name)
previous:  INDEX(Accession,  depth_level)

请使用JOIN...ON而不是(TermRelationship AS trt, Term AS ref)

parentOntology在哪张桌子上？

trt.FK_TermAccession_Related = ref.Accession冗余地出现了。