MySQL查询原理与优化

JOIN方式

驱动表的定义【优先选择结果集最小的那张表作为驱动表】

匹配过程

简单粗暴容易理解，就是通过双层循环比较数据来获得结果

特点

Simple Nested-Loop Join( 简单的嵌套循环连接 )

Index Nested-Loop Join( 索引嵌套循环连接 )

并不会简单粗暴的应用 SNL 算 法，而是加入了 buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。

在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进行 join 用到的列都缓存到 buffer 中。buffer 中的数据有一个特点，里面的记录不需要一条一条地取出来和 order 表进 行比较，而是整个 buffer 和 order表进行批量比较。

Block Nested-Loop Join( 块嵌套循环连接 )

三种JOIN算法

1. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)

2. 为匹配的条件增加索引(减少内层表的循环匹配次数)

3. 增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)【set session join_buffer_size=262144;默认256K】

4. 减少不必要的字段查询(字段越少，join buffer 所缓存的数据就越多

总结

JOIN优化

SELECT * FROM employee e WHERE e.dep_id IN (SELECT id FROM department);

执行原理

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用

结论

in 函数

explain SELECT * FROM employee e WHERE EXISTS (SELECT id FROM department d WHERE d.id = e.dep_id);

先循环: SELECT * FROM employee e;

再判断: SELECT id FROM department d WHERE d.id = e.dep_id

exists 函数

一句话: in后面跟的是小表，exists后面跟的是大表

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in

如果主查询得出的结果集记录较少，子查询中的表较大且又有索引时应该用 exists

区别

in和exists函数

例子图

通过有序索引顺序扫描直接返回有序数据

索引排序【Extra显示为Using index】

对返回的数据进行文件排序

含义

MySQL 为每个线程各维护了一块内存区域 sort_buffer ，用于进行排序。sort_buffer 的大小可以 通过 sort_buffer_size 来设置。

加载的记录字段总长度小于 sort_buffer_size ，使用 sort_buffer 排序;

Sort_Buffer

超过则使用 sort_buffer + 临时文件进行排序。

内存临时表大小超过 tmp_table_size ，那么就会转成磁盘临时表

决策

内存临时表

磁盘临时表

类型

临时文件种类

Sort_Buffer + 临时文件

按执行位置划分

max_length_for_sort_data 参数与用于排序的单条记录字段长度决定的

全字段排序就是将查询的所有字段全部加载进来进行排序

优点:查询快，执行过程简单

缺点:需要的空间大。

执行流程

全字段排序【<= max_length_for_sort_data】

rowid 排序相对于全字段排序，不会把所有字段都放入sort_buffer。所以在sort buffer中进行排序之后 还得回表查询。

优点:所需的空间更小

缺点:会产生更多次数的回表查询，查询可能会慢一些。

rowid 排序【> max_length_for_sort_data】

MySQL 认为内存足够大，会优先选择全字段排序，内存够，把需要的字段都放到 sort_buffer。不需要载回表。要多利用内存，尽量减少磁盘访问

按执行方式划分

额外排序【Extra显示为Using filesort】

排序方式

尽量减少额外的排序，通过索引直接返回有序数据。

优化的核心原则

例子1-3

例子4-5

例子6-7

添加索引

排序优化

order by优化

执行效果

执行效率

count(*) 和 count(1)和count(列名)区别

MySQL查询优化