MySQL查询原理与优化
2024-04-02 15:17:04 7 举报AI智能生成
数据库
作者其他创作
大纲/内容
JOIN优化
JOIN方式
驱动表的定义【优先选择结果集最小的那张表作为驱动表】
多表关联查询时,第一个被处理的表就是驱动表,使用驱动表去关联其他表.
驱动表的确定非常的关键,会直接影响多表关联的顺序,也决定后续关联查询的性能
三种JOIN算法
Simple Nested-Loop Join( 简单的嵌套循环连接 )
匹配过程
特点
简单粗暴容易理解，就是通过双层循环比较数据来获得结果
查询效率会非常慢,假设 A 表有 N 行，B 表有 M 行。SNL 的开销如下:
A 表扫描 1 次。
B 表扫描 M 次。
一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N * M 次
A 表扫描 1 次。
B 表扫描 M 次。
一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N * M 次
Index Nested-Loop Join( 索引嵌套循环连接 )
匹配过程
特点
优化的思路: 主要是为了减少内层表数据的匹配次数 , 最大的区别在
于，用来进行 join 的字段已经在被驱动表中建立了索引
于，用来进行 join 的字段已经在被驱动表中建立了索引
从原来的 匹配次数=外表层行数*内表层行数,
变成了 匹配次数 = 外层表的行数 * 内层表索引
变成了 匹配次数 = 外层表的行数 * 内层表索引
Block Nested-Loop Join( 块嵌套循环连接 )
匹配过程
特点
并不会简单粗暴的应用 SNL 算 法，而是加入了 buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。
在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进行 join 用到的列都缓存到 buffer 中。buffer 中的数据有一个特点，里面的记录不需要一条一条地取出来和 order 表进 行比较，而是整个 buffer 和 order表进行批量比较。
总结
1. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)
2. 为匹配的条件增加索引(减少内层表的循环匹配次数)
3. 增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)【set session join_buffer_size=262144;默认256K】
4. 减少不必要的字段查询(字段越少，join buffer 所缓存的数据就越多
in和exists函数
in 函数
执行原理
SELECT * FROM employee e WHERE e.dep_id IN (SELECT id FROM department);
in 语句, 只执行一次, 将 department 表中的所有id字段查询出来并且缓存.
检查 department 表中的id与 employee 表中的 dep_id 是否相等, 如果相等 添加到结果
集,直到遍历完department 所有的记录.
集,直到遍历完department 所有的记录.
结论
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用
exists 函数
执行原理
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
区别
一句话: in后面跟的是小表，exists后面跟的是大表
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in
如果主查询得出的结果集记录较少，子查询中的表较大且又有索引时应该用 exists
order by优化
排序方式
索引排序【Extra显示为Using index】
例子图
通过有序索引顺序扫描直接返回有序数据
额外排序【Extra显示为Using filesort】
含义
对返回的数据进行文件排序
按执行位置划分
Sort_Buffer
MySQL 为每个线程各维护了一块内存区域 sort_buffer ，用于进行排序。sort_buffer 的大小可以 通过 sort_buffer_size 来设置。
加载的记录字段总长度小于 sort_buffer_size ，使用 sort_buffer 排序;
Sort_Buffer + 临时文件
超过则使用 sort_buffer + 临时文件进行排序。
临时文件种类
决策
内存临时表大小超过 tmp_table_size ，那么就会转成磁盘临时表
类型
内存临时表
磁盘临时表
按执行方式划分
决策
max_length_for_sort_data 参数与用于排序的单条记录字段长度决定的
类型
全字段排序【<= max_length_for_sort_data】
全字段排序就是将查询的所有字段全部加载进来进行排序
优点:查询快，执行过程简单
缺点:需要的空间大。
执行流程
rowid 排序【> max_length_for_sort_data】
rowid 排序相对于全字段排序，不会把所有字段都放入sort_buffer。
所以在sort buffer中进行排序之后 还得回表查询。
所以在sort buffer中进行排序之后 还得回表查询。
优点:所需的空间更小
缺点:会产生更多次数的回表查询，查询可能会慢一些。
执行流程
总结
MySQL 认为内存足够大，会优先选择全字段排序，
内存够，把需要的字段都放到 sort_buffer。不需要载回表。
要多利用内存，尽量减少磁盘访问
内存够，把需要的字段都放到 sort_buffer。不需要载回表。
要多利用内存，尽量减少磁盘访问
优化的核心原则
尽量减少额外的排序，通过索引直接返回有序数据。
排序优化
添加索引
-- 联合索引
ALTER TABLE employee ADD INDEX idx_name_age(NAME,age);
-- 为薪资字段添加索引
ALTER TABLE employee ADD INDEX idx_salary(salary);
ALTER TABLE employee ADD INDEX idx_name_age(NAME,age);
-- 为薪资字段添加索引
ALTER TABLE employee ADD INDEX idx_salary(salary);
例子1-3
例子4-5
例子6-7
count(*) 和 count(1)和count(列名)区别
执行效果
count(*) 包括了所有的列,在统计时 不会忽略列值为null的数据.
count(1) 用1表示代码行,在统计时,不会忽略列值为null的数据.
count(列名)在统计时,会忽略列值为空的数据,就是说某个字段的值为null时不统计.
执行效率
列名为主键, count(列名)会比count(1)快
列名为不是主键, count(1)会比count(列名)快
如果表没有主键,count(1)会比count(*)快
如果表只有一个字段,则count(*) 最优.
0 条评论
下一页
