具有相同表的大型数据查询的MySQL优化左联接

Question

我这里有一个表格SQL

| id | dataid |  datarow | datacolumn | datavalue
_________________________________________________
|  1  |  1     |     1    | FirstName   |  John
|  1  |  1     |     1    | LastName    |  Lobo
|  1  |  1     |     1    | Age         |  35
|  1  |  1     |     2    | FirstName   |  Mich
|  1  |  1     |     2    | LastName    |  Handness
|  1  |  1     |     2    | Age         |  22
|  1  |  1     |     3    | FirstName   |  Mike
|  1  |  1     |     3    | LastName    |  Longbow
|  1  |  1     |     3    | Age         |  55

它将输出如下内容

FirstName  LastName Age
   John      Lobo   35
   Mich      Handness 22
   Mike      Longbow  55
SELECT DISTINCT     
        t1.datavalue AS Firstname, 
        t2.datavalue AS Lastname,
        t3.datavalue AS Age

FROM largedatatable t1 
        LEFT JOIN (SELECT datavalue,datarow FROM largedatatable WHERE           datacolumn='LastName') t2 ON t1.datarow = t2.datarow
        LEFT JOIN (SELECT datavalue,datarow FROM largedatatable WHERE datacolumn='Age') t3 ON t1.datarow = t3.datarow

WHERE t1.dataid = 1 

给我的结果就像

Firstname  LastName   Age
John        Lobo      35
Mich      Handness    22
Mike      Longbow     55

现在，这个脚本运行得非常好；但是如果它是一个大数据集，那么查询就会很慢。我是否仍然可以优化这个SQL查询。谢谢!

Answer 1

我认为这将解决您的问题，或者，至少它会运行得更快，因为它只获取表一次：

select 
    max(case datacolumn when 'FirstName' then datavalue else '' end) as FirstName,
    max(case datacolumn when 'LastName' then datavalue else '' end) as LastName,
    max(case datacolumn when 'Age' then datavalue else '' end) as Age
from 
    large
group by datarow

在小提琴上看到它：http://sqlfiddle.com/#!2/f4045/11

Answer 2

我会确保您有一个索引( datarow，datacolumn )来帮助您的连接，但是您的连接可以简化为

SELECT 
        t1.datavalue AS Firstname, 
        t2.datavalue AS Lastname,
        t3.datavalue AS Age
   FROM 
      largedatatable t1
         LEFT JOIN largedatatable t2
            on t1.datarow = t2.datarow
           and t2.datacolumn = 'LastName'
         LEFT JOIN largedatatable t3
            on t1.datarow = t3.datarow
           and t3.datacolumn = 'Age'
   WHERE
      t1.datacolumn = 'FirstName'

这将只基于第一个name列对主表进行一次遍历，但是从第二个实例中提取这些列，而不需要聚合的MAX()或GROUP。