多表查询优化

多表查询优化

（1）基本连接方法（内连接、外连接）：

一）内连接：用比较运算符根据每个表共有的列的值匹配两个表中的行（=或>、

意思是：检索商品分类和商品表“分类描述”相同的行

select

        d.Good_ID ,

        d.Classify_ID,

        d.Good_Name

        from

        Commodity_list d

        inner join commodity_classification c

        on d.Classify_Description=c.Good_kinds_Name

得到的满足某一条件的是A，B内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

二）外连接之左连接

//意思：查得商品分类表的所有数据，以及满足条件的商品详情表的数据

select

        *

        from

        commodity_classification c

        left join commodity_list d

        on d.Classify_Description=c.Good_kinds_Name

首先是左表数据全部罗列，然后有满足条件的右表数据都会全部罗列出。若两条右表数据对左表一条数据，则会用对应好的左表数据补足作为一条记录。

左连接升级：

[left join 或者left outer join(等同于left join)] + [where B.column is null]

//就是只查分类表数据，但是减去跟商品详情表有联系的数据。

select

        *

        from

        commodity_classification c

        left join commodity_list d

        on d.Classify_Description=c.Good_kinds_Name

        where d.Classify_Description is null

三）外连接之右连接

//意思是查得商品详情表的所有数据以及在分类描述相同条件下的商品分类表数据

select

        *

        from

        commodity_classification c

        right join commodity_list d

        on d.Classify_Description=c.Good_kinds_Name

与左连恰恰相反，首先是右表数据全部罗列，然后有满足条件的左表数据都会全部罗列出。若两条左表数据对右表一条数据，则会用对应好的右表数据补足作为一条记录。

右连接升级：

//意思：查询商品详情表的所有数据，但是要减去和商品分类表有联系的数据

select

        *

        from

        commodity_classification c

        right join commodity_list d

        on d.Classify_Description=c.Good_kinds_Name

        where c.Good_kinds_Name is null  

查询编写的注意点：

（1）对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

（2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

//最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

select id from t where num is null

备注、描述、评论之类的可以设置为 NULL，其他最好不要使用NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num = 0

（3）in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)



//用这个去替换

select num from a where exists(select 1 from b where num=a.num)

（4）下面的查询也将导致全表扫描：

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

（5）尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

（6）任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

（7）尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

（8）在Join表的时候使用相当类型的例，并将其索引

如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）

//在state中查找company

SELECT company_name FROM users

    LEFT JOIN companies ON (users.state = companies.state)

    WHERE users.id = $user_id"

    //两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集