MySQL数据库优化概述三


Using where

  WHERE 子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录,否则的话当查询的 Extra 字段值不是 Using where 并且表连接类型是 ALL 或 index 时可能表示有问题。

  如果你想要让查询尽可能的快,那么就应该注意 Extra 字段的值为Using filesort 和 Using temporary 的情况。

  你可以通过 EXPLAIN 的结果中 rows 字段的值的乘积大概地知道本次连接表现如何。它可以粗略地告诉我们MySQL在查询过程中会查询多少条记录。如果是使用系统变量 max_join_size 来取得查询结果,这个乘积还可以用来确定会执行哪些多表 SELECT 语句。详情请看"7.5.2 Tuning Server Parameters"。

  下面的例子展示了如何通过 EXPLAIN 提供的信息来较大程度地优化多表联合查询的性能。

  假设有下面的 SELECT 语句,正打算用 EXPLAIN 来检测:


EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;
 


  在这个例子中,先做以下假设:

  • 要比较的字段定义如下:
    Table Column Column Type
    tt ActualPC CHAR(10)
    tt AssignedPC CHAR(10)
    tt ClientID CHAR(10)
    et EMPLOYID CHAR(15)
    do CUSTNMBR CHAR(15)

  • 数据表的索引如下:
    Table Index
    tt ActualPC
    tt AssignedPC
    tt ClientID
    et EMPLOYID (primary key)
    do CUSTNMBR (primary key)

  • tt.ActualPC 的值是不均匀分布的。

      在任何优化措施未采取之前,经过 EXPLAIN 分析的结果显示如下:

    table type possible_keys key  key_len ref  rows  Extra
    et    ALL  PRIMARY       NULL NULL    NULL 74
    do    ALL  PRIMARY       NULL NULL    NULL 2135
    et_1  ALL  PRIMARY       NULL NULL    NULL 74
    tt    ALL  AssignedPC,   NULL NULL    NULL 3872
               ClientID,
               ActualPC
          range checked for each record (key map: 35)

      由于字段 type 的对于每个表值都是 ALL,这个结果意味着MySQL对所有的表做一个迪卡尔积;这就是说,每条记录的组合。这将需要花很长的时间,因为需要扫描每个表总记录数乘积的总和。在这情况下,它的积是 74 * 2135 * 74 * 3872 = 45,268,558,720 条记录。如果数据表更大的话,你可以想象一下需要多长的时间。

      在这里有个问题是当字段定义一样的时候,MySQL就可以在这些字段上更快的是用索引(对 ISAM 类型的表来说,除非字段定义完全一样,否则不会使用索引)。在这个前提下,VARCHAR 和 CHAR是一样的除非它们定义的长度不一致。由于 tt.ActualPC 定义为 CHAR(10),et.EMPLOYID 定义为 CHAR(15),二者长度不一致。

      为了解决这个问题,需要用 ALTER TABLE 来加大 ActualPC 的长度从10到15个字符

    mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

      现在 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15)

      了。再来执行一次 EXPLAIN 语句看看结果:


    table type   possible_keys key     key_len ref         rows    Extra
    tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
                 ClientID,                                         where
                 ActualPC
    do    ALL    PRIMARY       NULL    NULL    NULL        2135
          range checked for each record (key map: 1)
    et_1  ALL    PRIMARY       NULL    NULL    NULL        74
          range checked for each record (key map: 1)
    et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

      这还不够,它还可以做的更好:现在 rows 值乘积已经少了74倍。这次查询需要用2秒钟。

      第二个改变是消除在比较 tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 中字段的长度不一致问题:

    mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
        ->                MODIFY ClientID   VARCHAR(15);

      现在 EXPLAIN 的结果如下:


    table type   possible_keys key      key_len ref           rows Extra
    et    ALL    PRIMARY       NULL     NULL    NULL          74
    tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
                 ClientID,                                         where
                 ActualPC
    et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
    do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

      这看起来已经是能做的最好的结果了。

      遗留下来的问题是,MySQL默认地认为字段tt.ActualPC 的值是均匀分布的,然而表 tt 并非如此。幸好,我们可以很方便的让MySQL分析索引的分布:  


    mysql> ANALYZE TABLE tt;

      到此为止,表连接已经优化的很完美了,EXPLAIN 的结果如下:

  • 本文作者:
    « 
    » 
    快速导航

    Copyright © 2016 phpStudy | 豫ICP备2021030365号-3