多表查询优化

驱动表理论

1. 外连接

   左外连接：一般来说：坐表为驱动表，右表为被驱动表

2. 内连接

   对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在表会被作为被驱动表出现

   对于内连接来讲，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。以小表驱动大表

EXISTS 和 IN 的区分

问题

    不太理解哪种情况应该使用EXISTS，哪种情况应该使用IN。选择的标准是看能否使用表的索引吗？

回答

    索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种方式下的效率是最高的。

    比如下面这样：

1
2
3

select * from A where cc IN (select cc from B)

select * from A where EXISTS (select cc from B where B.cc = A.cc)

当A小于B时，用EXISTS。因为EXISTS的实现，相当于外表循环，实现的逻辑类似于：

1
2
3

for i in A
    for j in B
        if j.cc == i.cc then ...

当B小于A时用IN，因为实现的逻辑类似于：

1
2
3

for i in B
    for j in A
        if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A表小就用EXISTS，B表小就用IN。

• in是把外表和内表作hash连接

• 而exists是对外表作loop循环，每次loop循环再对内表进行查询，关联子查询

小结

1. 如果查询的两张表大小相当，那么用in和exists差别不大；

2. 如果两个表中一个较小一个较大，则子查询表大（内表大）的用exists；

3. 如果两个表中一个较小一个较大，自查询表小（内表小）的用in。

在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。

                                                                    图-MySQL查询过程

explain能做什么

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

my.ini配置如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

[mysql] 

# 设置mysql客户端默认字符集 
default-character-set=utf8

[mysqld] 
#设置3306端口 
port = 3306 

#允许最大连接数 
max_connections=3000

#服务端使用的字符集默认为8比特编码的latin1字符集 
character-set-server=utf8

#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

#建议2G以上，可根据服务器内存适当调整
innodb_buffer_pool_size=40G

#服务器有几个CPU就设置为几，建议用默认设置，一般为8.
innodb_thread_concurrency=16

innodb_buffer_pool_instances=4

#thread_cache_size的值可以设置为8*内存大小,假如服务器内存是8G,那么可以设置为64
thread_cache_size=256

tmp_table_size=3G
max_heap_table_size=6G

query_cache_size=1G


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

basedir=D:\MySQL56\MySQL\mysql-5.6.49-winx64 

datadir=D:\MySQL56\MySQL\mysql-5.6.49-winx64\data 

# 开启慢查询
slow_query_log=ON
long_query_time=5