mysql using where_MySQL_MySQL数据库优化概述三，Using where WHERE 子句将用来 - phpStudy...

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 的结果如下：

本文作者:相关阅读:

PHP开启gzip页面压缩实例代码

微软透露Vista系统可升级至Windows 7

针对chrome的css hack 使用方法

php中取得文件的后缀名？

asp.net下实现URL重写技术的代码

硬盘文件搜索代码(ASP类)

实用的标签效果

Oracle PL/SQL 过程调试的输出方法

用javascript实现记录来宾姓名的代码

Javascript实例教程:共享onload事件

Oracle Cache表

mysql mysqldump只导出表结构或只导出数据的实现方法

用 servlet 将jsp文件内容转为html

SQL Server 2008的独到之处