SQL 查询优化

最新推荐文章于 2024-10-14 16:48:23 发布

QunKrys

最新推荐文章于 2024-10-14 16:48:23 发布

阅读量125

点赞数

分类专栏： MySQL高级—黑马视频笔记文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/qq_38560825/article/details/121118795

版权

MySQL高级—黑马视频笔记专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1. 索引优化

单表索引优化

例：存在article 表，且未建立索引。explain 下列的SQL 语句，发现属于全表扫描，并且存在using filesort。

优化一

先建立category_id，comments，views的联合索引，之后再执行上图的语句，发现

type 从ALL 改为了 range
但是仍然存在 using filesort，这个结果在大数据集下仍然不可接受

优化二

删除优化一中建立的联合索引，再建立category_id，views的联合索引，之后再执行图中的语句发现

type 变为了ref
并且不存在using filesort

双表索引优化

例：存在class 表和 book 表，且未建立索引。explain 下列的SQL 语句，发现对两张表都进行了全表扫描。

优化

分别对class和book表中的card 列建立单列索引，并执行explain 语句，查看效果。

1. 对book 表的card 属性建立索引

2. 对class 表的card 属性建立索引（前提：删除上述建立的索引）

总结：

现象：
当索引在book 时，book 表的type为 ref，class表type 为ALL；
而当索引在class时，class 表的type为 index，book 表的type 为index。
说明
根据之前对type 取值的讲解可知，ref 优于index
结论
当进行左连接时，应该在右表对应的列进行索引的添加
当进行右连接时，应该在左表对应的列进行索引的添加

三表索引优化

例：存在class 表、book 表、phone表，且未建立索引。explain 下列的SQL 语句，发现对三张表都进行了全表扫描。

优化

1. 按照两表得出的结论进行索引建立，所以分别在book 和phone 的card 字段上建立索引

总结：

现象：
发现这种优化的结果非常好，book 和phone 的type 都变为了ref
结论
三表查询时，当是左连接时，同样需要在两个右表的对应字段进行索引的建立

结论

尽可能减少join 语句中的NestedLoop（嵌套循环）的循环总次数；永远用小结果集驱动大结果集；
优先优化NestedLoop 的内层循环；
保证Join 语句中被驱动表上Join 条件字段已经被索引；
当无法保证被驱动表的Jion条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置

2. Order by 关键字优化

尽量使用index 方式排序，避免使用filesort 方式排序
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀（字段顺序很重要）
如果不在索引列上，filesort 有两种算法
- 双路排序
  - MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据
  - 读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
  - 在磁盘取排序字段，在buffer 进行排序，再从磁盘取其他字段
- 单路排序
  - MySQL 4.1 之后提出的一种算法
  - 从磁盘读取查询需要的所有列，按照order by列在buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成了顺序IO，但是他会使用更多的空间，因为它把每一行都保存在内存中
- 结论和引申出的问题
  - 总体上单路好过双路
  - 使用单路存在的问题
    - 在sort_buffer 中，方法B 比方法A 要多占用更多的空间，因为方法B 是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer 的容量，导致每次只能取sort_buffer 容量大小的数据，进行排序（创建tmp 文件，多路合并），排完再取sort_buffer 容量大小，再排... 从而需要进行多次IO。
优化策略
- 增大sort_buffer_size 参数的设置
  - 不管哪个算法，提高这个参数都会提高效率
- 增大max_length_for_sort_data 参数的设置
总结